Enabling slow_query_log and log_queries_not_using_indexes will help to identify slow queries and queries without indexes that affect the performance of the MySQL server.
Connect to the MySQL server and see the current settings:
mysql -u root -p SHOW GLOBAL VARIABLES LIKE 'slow\_%'; SHOW GLOBAL VARIABLES LIKE 'long_query_time'; SHOW GLOBAL VARIABLES LIKE 'log_queries_not_using_indexes';
You can change these parameters without restarting MySQL (I note that in versions of MySQL below 5.7 slow_query_log is called log_slow_queries):
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log'; SET GLOBAL long_query_time = 5; SET GLOBAL slow_query_log = 'ON';
To enable query logging without indexes:
SET GLOBAL log_queries_not_using_indexes = 'ON';
long_query_time indicates the minimum query execution time in seconds, after which it is considered slow, by default 10 seconds.
You can also specify these parameters in the configuration file:
slow_query_log=ON slow_query_log=/var/log/mysql/slow.log long_query_time=5 log_queries_not_using_indexes=ON
And restart the MySQL server to apply the changes:
sudo /etc/init.d/mysql restart
For a more readable format, you can use the pt-query-digest utility:
apt install percona-toolkit pt-query-digest /var/log/mysql/slow.log > /var/log/mysql/slow_ixnfo.com.txt
Do not forget to disable these parameters after the analysis of logs is completed:
SET GLOBAL slow_query_log = 'OFF'; SET GLOBAL log_queries_not_using_indexes = 'OFF';
See also my articles:
Time mismatch in error logs and slow queries logs
Installing and using mysqltuner.pl
How to change join_buffer_size