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:
- How to reset MODX admin password over MySQL cli
- Magento how to check whether SSL redirection is enabled using MySQL command line and disable it if needed
- How to tune/calculate table_open_cache, open_files_limit, table_definition_cache, table_open_cache_instances
- How to calculate innodb_buffer_pool_size value
- How to get rid of Mariadb Aborted connection Got an error reading communication packets