Slow Query Monitoring in MySQL and MariaDB

To enable slow query monitoring in MySQL or MariaDB, run these commands in your client:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;

This activates the slow query log and sets it to capture any queries taking longer than 0.5 seconds. You can adjust this threshold based on your performance requirements.

Slow queries will be logged to some file like:

/var/lib/mysql/*-slow.log

You can check or change that with:

SHOW VARIABLES LIKE 'slow_query_log_file';
SET GLOBAL slow_query_log_file = '/custom/path/slow-queries.log';

To make these changes permanent, add these lines to your my.cnf/my.ini file:

[mysqld]
slow_query_log = 1
long_query_time = 0.5
slow_query_log_file = /custom/path/slow-queries.log

To analyze slow queries, use:

mysqldumpslow /custom/path/slow-queries.log