Impact of Setting MySQL long_query_time to 0

Setting the MySQL long_query_time system variable to zero is a diagnostic technique used to log every single SQL query executed by the database server to the slow query log. While this provides comprehensive visibility for query profiling, debugging, and performance tuning, it introduces severe operational trade-offs in production environments. This article examines the immediate and long-term operational impacts of this configuration on database performance, disk storage, and system stability.

High Disk I/O Utilization

The most immediate operational impact of setting long_query_time to 0 is a massive spike in disk write activity.

Under normal operations, MySQL only logs queries that exceed a specific duration threshold (e.g., 1 or 2 seconds). When the threshold is set to zero, every single transaction—including rapid primary key lookups taking fractions of a millisecond—must be written to the slow query log.

On high-throughput systems processing thousands of queries per second (QPS), this creates a continuous stream of write operations. If the log is stored on the same physical disk as the database data directory, this I/O contention will degrade the performance of standard database reads and writes, leading to increased query latency across the entire application.

Rapid Storage Depletion

Because every query is recorded, the slow query log file (or the mysql.slow_log system table) will grow exponentially.

• File-based Logging (log_output = 'FILE'): Log files can quickly grow to tens or hundreds of gigabytes within hours. If disk space is completely consumed, MySQL may freeze, fail to commit transactions, or crash entirely.
• Table-based Logging (log_output = 'TABLE'): If configured to log to a database table, MySQL must perform SQL inserts into the mysql.slow_log table for every query executed. This not only consumes storage but also utilizes the InnoDB buffer pool, displacing active application data and indexes from memory.

Increased CPU Overhead

In addition to disk bottlenecks, logging every transaction imposes a measurable CPU tax. The MySQL server must allocate CPU cycles to serialize query strings, capture execution metrics (such as lock time, rows examined, and rows sent), and format this metadata before writing it to the destination log. In CPU-bound environments, this extra processing overhead can trigger CPU starvation, slowing down query execution times.

Concurrency Bottlenecks and Lock Contention

If the database is configured to output logs to a table rather than a file, severe lock contention can occur. The mysql.slow_log table uses the CSV storage engine by default, which relies on table-level locking. Under high concurrency, multiple database threads attempting to write to the log table simultaneously will block one another. This lock contention quickly cascades, causing application connection pools to saturate and causing widespread application timeouts.

Best Practices and Mitigation Strategies

If you must set long_query_time to zero to diagnose a performance issue, adhere to the following safety measures:

• Use Microsecond Resolution: Instead of logging absolutely everything with 0, target sub-second queries by using decimal values. Setting the variable to 0.05 (50 milliseconds) or 0.1 (100 milliseconds) captures slow queries without logging high-frequency, ultra-fast queries.
• Limit Session-Level Scope: Avoid changing the global variable on a busy production server. Instead, set the variable at the session level (SET SESSION long_query_time = 0;) for specific test connections or staging environments.
• Enable Log Rotation: Ensure aggressive log rotation (e.g., hourly rotation via logrotate) is active and configured to compress or purge older logs before disk space is exhausted.
• Leverage Performance Schema: For modern MySQL deployments (5.7 and 8.0+), utilize the performance_schema statement instrumentations. This framework tracks query execution statistics in memory with significantly lower overhead than the slow query log.