Log slow queries or all statements in MySQL

Use the slow query log to investigate expensive statements. Use the general query log when you need to see what clients send to MySQL during a short diagnostic window. Neither should be enabled without considering log volume and sensitive SQL values.

Configure a slow query log

For MySQL 8.4, place this in the server's active option file:

[mysqld]
log_output = FILE
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1

The path must be writable by the MySQL service. The threshold is in seconds and accepts fractional values. Additional conditions, including min_examined_row_limit, affect which statements qualify.

To capture queries that do not use indexes, you can also set:

log_queries_not_using_indexes = ON

This can include fast, legitimate scans and sharply increase volume. Enable it deliberately and consider the corresponding throttling option, rather than assuming every scan is a performance problem.

Change settings without restarting

SET GLOBAL log_output = 'FILE';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- If this diagnostic connection should use the new threshold too:
SET SESSION long_query_time = 1;

Existing connections keep their session threshold when you change its global default. Reconnect application sessions or change the relevant session setting when testing. Runtime changes require appropriate privileges and do not automatically replace persistent configuration. Changing log_output also affects the general query log.

Capture client statements with the general log

[mysqld]
log_output = FILE
general_log = ON
general_log_file = /var/log/mysql/mysql.log

The general log records received statements and connection activity; it is not proof that each statement completed successfully. Keep the capture window short, protect access, and restore the previous setting afterward:

SET GLOBAL general_log = 'OFF';

Analyze the slow log

mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log
pt-query-digest /var/log/mysql/slow-query.log

The first command summarizes query patterns using MySQL's utility. The second uses Percona Toolkit and must be installed separately. Compare query frequency as well as duration: many moderately slow calls can matter more than one outlier.

Common questions

What threshold is reasonable? Pick one from the application's latency budget and observed distribution. Values such as 0.5–1 second for interactive requests or several seconds for analytical workloads are starting points, not universal recommendations.

Is slow logging free? No. Its cost depends on how many statements qualify, their size, and the destination. A very low threshold or unindexed-query logging can make it high-volume.

What do Query_time and Lock_time mean? They report statement timing and time associated with acquiring locks as defined by MySQL's log format. They are not an exhaustive decomposition of all waits; in particular, do not equate Lock_time with every InnoDB row-lock wait. Correlate a suspected contention problem with lock-wait instrumentation.

See the MySQL slow query log reference.