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.

enter image description here

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

enter image description here

Cache variables

enter image description here

Thread variables

enter image description here

Table variables

enter image description here

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

  • The symptoms you describe are probably caused by some slow queries. Find them via the SlowLog Then, let's improve them and/or the INDEXes.
    – Rick James
    Aug 4 at 16:48
  • Please provide info as text, not images. Most of those SELECTs in one image look similar; please provide the text of a full one.
    – Rick James
    Aug 4 at 16:52
  • @RickJames I've added the relevant select to the question, and I have a copy of the slow log but it's 20 GB in size so I'm not sure how to open it. My local machine has only 8 GB ram, but I have a dev server with 32 GB ram so I'm copying it up to that server to open it there.
    – Vincent
    Aug 4 at 19:31
  • Use pt-query-digest from percona.com. It will give a list of the 'different' queries ordered, started from the "worst". I'll lool at the one you show us; see if that was the first in percona's list.
    – Rick James
    Aug 4 at 20:27
  • Please use the JOIN...ON syntax so we can see the 'relations'.
    – Rick James
    Aug 4 at 20:31

2 Answers 2


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.

  • Turns out my database version is 5.5 so I can not do a format=json.
    – Vincent
    Aug 4 at 20:56
  • Long overdue for upgrading! Be aware that you will need to make multiple steps: 5.5 -> 5.6 -> 5.7 -> 8.0 Or get a logical dump with mysqldump and see if it loads directly into 8.0.
    – Rick James
    Aug 4 at 20:59
  • Unless the server is tight on RAM, consider increasing innodb_buffer_pool_size to 20G.
    – Rick James
    Aug 4 at 21:02
  • I wish I could upgrade but there are over 2000 references to mysql_ so I can not upgrade to 8.0 until that code is upgraded, but I am moving to a new server soon and will use 5.6 on that server. I also can not add that composite index because it times out. That table is 1.3 GB in size. I thought increasing the buffer pool size might make it worse? Somebody else said closing a tmp table takes longer if the buffer pool is bigger.
    – Vincent
    Aug 4 at 21:05
  • If I remove the joins to staff and tasks, it executes instantly. I can then get the staff name and certification for each staff in the result set, as I loop through it. That will probably be much faster than trying to join them all together because the results will typically only return 5 staff or less so it's a very short loop. Only problem is, then I can't sort by staff name. :/ But I can live with that.
    – Vincent
    Aug 4 at 21:21

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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.