Windows Server 2016, MySQL 5.6, PHP 5.6, 64 GB of RAM.
My server is very busy, especially at certain times of the day. I get thousands of queries and sometimes hundreds of connections per second. Lately MySQL threads are climbing up into the hundreds and that's having a serious effect on performance. I noticed that the threads are climbing up because a lot of them are sticking around much too long, like 10 to 20 seconds. And these all appear to be trying to either create or remove a tmp table.
My database is 13 GB in size. My two largest tables are 2.5 GB and 1.3 GB. And most of those threads which are stuck in dropping the tmp table appear to be selecting from the 1.3 GB size table.
I've checked the folder where MySQL is storing these temp tables and it's not very full (< 200 files) so I don't think it's the file system that's bogging down.
My RAM usage is around 44% and my CPU usage jumps around between 10% and 30% so it doesn't appear to be a resource bottleneck.
Is there a problem with my config that's causing these threads to hang around so long? Is there a setting I can tweak to make those tmp tables create and drop more efficiently?
The select statement in those threads is as follows:
select staff.id staffid, concat(staff.firstname,' ',staff.lastname) staffname, staff.firstname,cert.name level from staffattendance attend inner join tasks on tasks.id = attend.taskid inner join groups on groups.taskid = tasks.id inner join staff on staff.id = attend.staffid left join certifications cert on cert.id = staff.certificationid where attend.date = '2023-03-24' and (attend.endtime > '09:51' or attend.endtime is null) and groups.id = '30778' order by staff.firstname,staff.lastname;
If I execute this statement in MySQL Workbench it finishes in anywhere from 0.1 to 0.5 seconds.
I have indexes on all the relevant columns, and the explain on this select shows the following:
1 SIMPLE groups const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort
1 SIMPLE tasks const PRIMARY PRIMARY 4 const 1 Using index
1 SIMPLE attend index_merge date,staffid,taskid,endtime taskid,date 4,3 1 Using intersect(taskid,date); Using where
1 SIMPLE staff eq_ref PRIMARY PRIMARY 4 timesavr_prod.attend.staffid 1
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 timesavr_prod.staff.certificationid 1