MySQL struggling to remove tmp tables

Question

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?

Buffer variables

Cache variables

Thread variables

Table variables

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

Answer 1

For the one query listed (so far)...

Index_merge...Using intersect(taskid,date) -- This usually indicates that a composite index would be much preferred. Suggest you replace the single-column indexes you currently on taskid and date with

INDEX(taskid, date),
INDEX(date, taskid)

(I don't know which one the Optimizer would prefer for the JOIN.)

Please provide SHOW CREATE TABLE for the tables involved. Also EXPLAIN FORMAT=JSON SELECT .... This may give a clue of which step needed the temp table.

Answer 2

Turns out I needed to increase my innodb_buffer_pool_instances. I had 1 instance of 10 GB. I changed it to 10 instances. I added the following to my.ini.

innodb_buffer_pool_instances=10

This automatically divides the 10 GB allocated, so that each instance uses 1 GB. This change has entirely eliminated "abruptly terminated connections" (which I had gotten so used to that I assumed they were just a normal part of life) and the thread count stays low even during peak times (below 25), and performance is excellent all day long.