0

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

10
  • 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, 2023 at 16:48
  • @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, 2023 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, 2023 at 20:27
  • @RickJames Updated with the JOIN...ON syntax. Also, I did find the query in the slow log, and it said it took 4 seconds, but when I run the same query in MySQL Workbench it runs in approximately 0.1 seconds. So, still not super fast, but I don't understand why it would be so much slower when run from a PHP page.
    – Vincent
    Aug 4, 2023 at 20:47
  • That query is probably stumbling over all the other copies of it. Hence the "average" time could be 4 seconds even when it "should" be much faster. The time is elapsed time. My indexes may make it faster and/or block other copies to a lesser degree.
    – Rick James
    Aug 4, 2023 at 20:55

2 Answers 2

0

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.

11
  • Turns out my database version is 5.5 so I can not do a format=json.
    – Vincent
    Aug 4, 2023 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, 2023 at 20:59
  • Unless the server is tight on RAM, consider increasing innodb_buffer_pool_size to 20G.
    – Rick James
    Aug 4, 2023 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, 2023 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, 2023 at 21:21
0

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.

Your Answer

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

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