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