1

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?

0

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...

Indexes needed:

table4: INDEX(userId1, userId2)  -- in this order
table1: INDEX(userId)
table3: INDEX(groupId)
table2: INDEX(userId, class)  -- in this order
  • Thanks for the suggestion! :) I'm kind of hesitant regarding InnoDB - in terms of locking, it surely is far superior to MyISAM, but from my experience it has some disadvantages: Restoring backups takes far more time, and - at least from what the support staff from my webhoster says - the probability to repair a broken table seems to be not as high as in the case of MyISAM. But maybe I'll have to switch to InnoDB anyways and solve these issues somehow - there are certainly possibilities. – toastbrot888 May 17 '17 at 9:38
  • Regarding the query: In 99% of all cases, it is executed in 0.02 sec. But sometimes - for an unknown reason - it takes 30 sec. Even if I take this exact query with the exact same parameters and execute it hours later, while the server is under heavy load, it is executes quickly. That led me to the assumption that maybe MySQL has trouble sending the resultset back to PHP. But Apache/PHP and MySQL are on the same server, so I wonder why. – toastbrot888 May 17 '17 at 9:42
  • 1
    Zero lock_time, only 131 rows_examined, yet 31+ seconds --> I don't see an obvious explanation. Frankly, I don't trust 'lock_time'; it may not cover all forms of locking. – Rick James May 17 '17 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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