0

We have a PHP app running against MySQL 5.7 with innoDB. The app used to be really slow. MySQL takes 100% of the CPU for a really long time, for some queries. I tried running optimize table, which innoDB just interprets as re-create table. This made matters only worse.

I enabled slow query logging as:

slow_query_log          = ON
long_query_time         = 5
log_queries_not_using_indexes = ON
slow_query_log_file     = /var/log/mysql/mysql-slow.log

I only see the below in the log, repeated many times:

SET timestamp=1516955047;
SELECT * FROM `notification` WHERE `user_id`=4 ORDER BY `created_at` DESC LIMIT 5;
# Time: 2018-01-26T08:24:07.652626Z
# User@Host: root[root] @ localhost []  Id:   153
# Query_time: 0.000221  Lock_time: 0.000107 Rows_sent: 1  Rows_examined: 3
SET timestamp=1516955047;
SELECT
    kcu.constraint_name,
    kcu.column_name,
    kcu.referenced_table_name,
    kcu.referenced_column_name
FROM information_schema.referential_constraints AS rc
JOIN information_schema.key_column_usage AS kcu ON
    (
        kcu.constraint_catalog = rc.constraint_catalog OR
        (kcu.constraint_catalog IS NULL AND rc.constraint_catalog IS NULL)
    ) AND
    kcu.constraint_schema = rc.constraint_schema AND
    kcu.constraint_name = rc.constraint_name
WHERE rc.constraint_schema = database() AND kcu.table_schema = database()
AND rc.table_name = 'notification' AND kcu.table_name = 'notification';
# Time: 2018-01-26T08:24:07.653082Z
# User@Host: root[root] @ localhost []  Id:   153
# Query_time: 0.000155  Lock_time: 0.000040 Rows_sent: 1  Rows_examined: 143

I want something to go on, to be able whether performance could be improved with database tuning. How do I see a list of missing indices or something like that?

0

To analyze MySQL slow log, try tool mysqldumpslow https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

mysqldumpslow /var/log/mysql/mysql-slow.log

When you find slowest query, try EXPLAIN https://dev.mysql.com/doc/refman/5.7/en/explain.html It's easy to use:

EXPLAIN SELECT foo FROM slow_something WHERE ...;

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.