Evaluating When to Turn On Slow Query Logging for Thread Lookups

By 4FreeForums Editors · Published October 30, 2026 · Sources linked below

When community discussions stall or topic pages take an eternity to load, unoptimized database queries are frequently responsible. Tracking down sluggish lookups by turning on full diagnostic logging can help, but indiscriminate logging risks exhausting input and output capacity on your production storage array. Forum administrators must decide when diagnostics are truly warranted and how to structure capture settings so that disk write volumes remain strictly under control. You can balance troubleshooting depth with disk safety by following this structured decision framework.

Decision Factor 1: Separate Lock Contention From Execution Latency

Before recording lookups to disk, verify whether delayed thread fetches stem from query execution or connection locks. Under the MySQL 8.4 Reference Manual on The Slow Query Log, the server does not count initial lock acquisition time toward statement execution time. Statements are written to the log only after execution finishes and after all locks have been released. Consequently, the order of entries in the file may not reflect the sequence in which the server processed the queries. If slow forum response times are primarily driven by write locks blocking thread reads, writing long query logs will consume storage bandwidth without capturing the true concurrency bottleneck.

Decision Factor 2: Establish Runtime Thresholds and Row Limits

Leaving a diagnostic log enabled with open-ended capture parameters will rapidly inflate log files on production disks. By default, the slow query log is disabled. When activated, the default long_query_time is 10 seconds, with a minimum possible value of 0 seconds. Admins can configure this duration down to microsecond resolution to spot minor stalls when speeding up forum performance. To prevent fast but repetitive scans from flooding disks, combine runtime limits with row inspection minimums. The database server applies a strict hierarchy of controlling parameters before appending any statement to disk:

• The statement must either not be administrative, or administrative statement logging must be active.
• The statement execution duration must reach at least long_query_time seconds, or unindexed query capture must be enabled for a query that skipped indexes.
• The lookup must examine at least min_examined_row_limit rows.
• The query must not be suppressed by the per-minute throttle limit.

Decision Factor 3: Restrict Unindexed Query Logging With Quotas

Thread lookups on older boards often slow down because an extension or custom feature performs full table scans. Turning on log_queries_not_using_indexes helps locate missing keys, but doing so can quickly saturate disk arrays during heavy forum traffic. The server automatically excludes tables that contain fewer than 2 rows, as an index offers no lookup advantage there. For larger tables, protect your storage by configuring log_throttle_queries_not_using_indexes. By default, this throttle is 0, which imposes no limit. Setting a positive number establishes a ceiling per minute. The first unindexed query initiates a 60-second window during which the database writes queries up to the defined ceiling. Any additional unindexed queries within that 60-second window are suppressed. Once the window closes, the server outputs a single summary line indicating the suppressed count and cumulative execution time.

Decision Factor 4: Select Destination Formats and Information Density

Choosing where and how the server stores diagnostic output is critical when balancing hardware needs against forum software server requirements. The database writes entries based on the log_output setting. If this variable is set to NONE, no queries are written even when logging is toggled on. If you select FILE, the server writes by default to host_name-slow.log inside the data directory unless you supply an absolute path to a different location.

To keep files compact on constrained storage, using the short log format writes less detail per query. Standard output logs execution duration, lock duration, rows sent, and rows examined. If administrators enable extra slow logging, additional metrics appear in file output, including temporary disk tables created, client thread identifiers, bytes sent, bytes received, and handler read operations. Note that passwords inside queries are automatically rewritten so they never appear in plain text, and statements with syntax errors are excluded from the log.

Decision Factor 5: Safely Toggle Diagnostics at Runtime

You do not need to restart your production forum server to engage diagnostic logging. Administrators can toggle the global slow_query_log variable between 0 to disable and 1 to enable. You can also reassign the file destination on the fly using the global slow_query_log_file variable, which automatically closes the existing log and opens the newly designated file. Once lookups have been collected over a targeted diagnostic interval, set the logging variable back to 0 immediately to prevent unneeded disk writes. You can then process the resulting log offline using the mysqldumpslow tool, which parses and summarizes the file without adding load to your live database disk array.

Sources

MySQL 8.4 Reference Manual: 7.4.5 The Slow Query Log

Published October 30, 2026. No affiliate links on this page. Facts come from the sources linked above; check them for updates.