How to enable Slow Query Log

Append into /etc/my.cnf the following:

slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2

Create /var/log/mysql-slow.log log file and set it as mysql user:

touch /var/log/mysql-slow.log
chown mysql:mysql /var/log/mysql-slow.log

Restart MySQL server:

/scripts/restartsrv_mysql

Monitor the logs using tail command:

tail -f /var/log/mysql-slow.log

Monitor the logs using mysqldumpslow command:

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

Example:

Sort by count

mysqldumpslow -a -s c -t 5 /var/log/mysql/mysql-slow.log

Sort by rows sent or average rows sent

mysqldumpslow -a -s r -t 5 /var/log/mysql/mysql-slow.log

Source: https://docs.cpanel.net/knowledge-base/sql/how-to-enable-the-slow-query-log-in-mysql-or-mariadb/

Source: https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

 

Similar Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *