MySQL 5.7 trying to improve performance

Question

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?

Answer 1

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