Log Slow queries
By default slow query log is disabled in Mysql, run the below queries to enable the slow query log:
mysql > SET GLOBAL slow_query_log = 1;
mysql > SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow-query.log';
mysql > SET GLOBAL long_query_time = 3;
mysql > SET GLOBAL log_queries_not_using_indexes = 1;Let’s first understand what each query does:
SET GLOBAL slow_query_log – This will allow MySql to log the slow queries
SET GLOBAL slow_query_log_file – specifies the file path where the logs will be written
SET GLOBAL long_query_time – By default, when the slow query log feature is activated, MySQL logs any query that has a duration longer than 10 seconds. However, it is possible to adjust this setting to log queries that take longer than three seconds by modifying the appropriate configuration parameter.
To identify the most frequently used and slowest queries, it is advisable to configure long_query_time=0 to log all queries initially. However, caution should be exercised when setting long_query_time=0 as this can increase the I/O workload on the system due to frequent write operations, which may fill up disk space. A recommended approach is to enable this setting for a limited time and then revert back to only logging slow queries once the analysis is complete.
SET GLOBAL log_queries_not_using_indexes – Log queries that not using indexes.
Although it is essential to log all queries that are not utilizing indexes, it may not necessarily imply that the query is slow. This is because there are instances when the query optimizer selects a full table scan instead of leveraging available indexes, such as when the table is still small.
Running these queries in MySql will lose once the Mysql is Rebooted. So, you need to edit the MySQL configuration file, usually located at /etc/mysql/my.cnf or /etc/my.cnf. Add the following lines to the file:
slow_query_log = 1;
slow_query_log_file = '/var/log/mysql/mysql-slow-query.log';
long_query_time = 3;
log_queries_not_using_indexes = 1;Restart MySQL: After making changes to the configuration file, you need to restart MySQL for the changes to take effect.
Verify slow query logs are enabled
You can verify whether the slow query logs and any other configurations that you have implemented are activated by executing the following queries.
mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';Analyze slow queries
Analyze the slow query log: Once the slow query log is enabled, MySQL will log all queries that exceed the specified long_query_time. You can use various tools to analyze the logs, such as the mysqldumpslow command-line tool, or the Percona Toolkit. For example, to analyze the slow query log with mysqldumpslow, use the following command:
mysqldumpslow /var/log/mysql/mysql-slow.logThis will display a summary of the slow queries, sorted by the number of times each query occurred, along with the total execution time, average execution time, and other statistics.
Count: 1 Time=1281.83s (1281s) Lock=0.00s (0s) Rows=585393.0 (585393)The “Count” column shows the total number of times that a query has been logged. The “Time” column provides information on the query’s average processing time and total processing and waiting time. The “Rows” column indicates the number of rows that were returned by the query.
To identify the queries that contribute the most to the server’s workload, we can sort the output by count, which represents the number of times the query is found in the slow query log. Additionally, we can display the top 5 queries sorted by the average query time to determine the queries that take the longest time to execute.
//Sort the queries by count
mysqldumpslow -a -s c -t 5 /var/log/mysql/mysql-slow.log
//Top five queries sort by average time
mysqldumpslow -t 5 -s at /var/log/mysql/localhost-slow.log
mysqldumpslow supports the following options.
mysqldumpslow Options
Below is the list of options that can be used with mysqldumpslow.
