I have a web application using PHP 7.0 and MySQL 5.5.54-0+deb8u1-log.
Occasionally, there are long waiting times in the application, which seem to be related to slow database queries. In the slow query log of MySQL, I can find some queries which have been executed unusually slow, though they normally are finished in milliseconds. I use MyISAM which is obviously prone for waiting for locks, but – what’s strange is that the queries do not have to wait for locks at all.
Of course you can argue that the query is not optimized and all – regarding multiple joins, sorting, union all etc. But if I run the following example in the database while the application is on its load peak, it usually runs just nice and quick!
Here one example:
# Query_time: 31.449621 Lock_time: 0.000284 Rows_sent: 22 Rows_examined: 131 SET timestamp=1494921515; (SELECT table1.*, table2.userName, table2.userId, table2.userPictureId, table3.groupName, table3.viewOption, table3.force_private FROM table4 INNER JOIN table1 ON table4.userId1='192203' AND table4.userId2=table1.userId INNER JOIN table3 ON table1.groupId=table3.groupId AND table3.deleted=0 INNER JOIN table2 ON table1.userId=table2.userId AND table2.class IN ('A','B') AND NOT table1.actionId='20' ORDER BY date DESC LIMIT 0,50) UNION ALL (SELECT table1.*, table2.userName, table2.userId, table2.userPictureId, table3.groupName, table3.viewOption, table3.force_private FROM table1 INNER JOIN table3 ON table1.groupId=table3.groupId AND table3.deleted=0 INNER JOIN table2 ON table1.userId=table2.userId AND table2.userId='192203' AND NOT table1.actionId='20' ORDER BY date DESC LIMIT 0,50) ORDER BY date DESC LIMIT 0,100;
So – what I’m looking for is not a way to make this specific query faster. Of course it wouldn’t hurt, but normally this specific query is finished in about 0,01s!
If this occurs, then there are usually some more normally fast queries which are executed slowly. I see it in the slow query log.
One observation is that the execution time seems always to be around 30 seconds – and the time limit for PHP scripts is 30 seconds in my case, so I wonder if there is any relation. If all related MySQL queries are automatically canceled when a PHP script is killed, then this would explain it. By the way, I use PDO to connect PHP to MySQL.
One idea I had is that maybe the actual query execution time is not so bad, but maybe MySQL has issues while trying to hand over the resultset to PHP – for an unknown reason. But on the other hand, I’m not sure if this waiting time would be added to the query execution time in the slow query log! Does anybody know? What else could be the problem?
If you are looking for the latest 100, you have not quite gotten it. You need
LIMIT 100 in the inner queries, too. (OK, that will slow down the query a little, but it might be ‘correct’.)
Back to performance…
Short answer: Switch to InnoDB; it is likely to help, especially in avoiding strange locks. (Instead, you will other strange locks, but usually not for as long.)
Which table is
date in? Hard to analyze the query without having all columns qualified and
SHOW CREATE TABLE.
EXPLAIN SELECT ... would also help. But I will try…
table4: INDEX(userId1, userId2) -- in this order table1: INDEX(userId) table3: INDEX(groupId) table2: INDEX(userId, class) -- in this order