Query Log Configuration
When a dynamic endpoint slows down, guessing which plugin/query is responsible usually wastes time. Use database logs to capture evidence: which queries are slow, how often they run, and what they are doing.
- Prefer the slow query log for production diagnostics.
- Avoid the general log on production (it can generate huge volumes).
- Analyze slow logs with a summarizer (
mysqldumpsloworpt-query-digest) rather than reading raw logs.
The Operational Logging Matrix
Understanding the difference securely between the various database logs is vital. Activating the incorrect logging array on a massive production node will exhaust entire disk allocations within hours.
| Native Log Type | Primary Purpose | WordPress Use Case |
|---|---|---|
| Error Log | Startup/shutdown information, engine crashes, fatal hardware failures. | Diagnosing sudden MySQL daemon crashes or "Out of Memory" kills physically enacted by the Linux kernel. |
| General Log | Records absolutely every single query executed. | Strictly designed for isolated staging environments to audit exactly what a custom WordPress plugin attempts to execute. Never enable in production. |
| Slow Query Log | Records strictly the specific queries exceeding your defined time threshold. | Finding the exact WooCommerce cart or massive LMS reporting queries stalling the checkout gateway. |
| Binary Log | Data replication and point-in-time recovery streams strictly. | Structuring continuous backups or configuring complex High-Availability (HA) read-replica database topologies. |
The Implementation Procedure
You must explicitly configure the Slow Query Log actively to intercept bad queries without overwhelming the disk subsystem.
1. Verify Current Logging Activity
Before activating new logs, ensure you are not actively flooding the disk organically.
# Query the active daemon directly avoiding configuration files temporarily
mysql -e "SHOW VARIABLES LIKE 'log_output';"
Prefer FILE output. TABLE output writes logs into the mysql schema and is rarely what you want for performance diagnostics.
2. Enable the Slow Query Log Persistently
You must force the engine to record offenders directly by editing the core configuration file.
sudo nano /etc/mysql/my.cnf
Depending on your distro, you may prefer adding a file under /etc/mysql/conf.d/ or /etc/mysql/mariadb.conf.d/.
Inject the Logging Directives
[mysqld]
# Physically enable the tracking engine
slow_query_log = 1
# Define exactly where the disk writes the output securely
slow_query_log_file = /var/log/mysql/slow.log
# Define the threshold explicitly (1 second is perfectly optimal for catching major stalls)
long_query_time = 1
# Ensure it writes natively to the fast filesystem exclusively
log_output = FILE
Cycle the database natively to apply the logic completely:
sudo systemctl restart mysql
Advanced Log Analysis Tools
Do not attempt to read a massive 5GB slow query log exclusively using cat. You must aggregate and summarize the identical queries mathematically.
Utilizing mysqldumpslow
This tool automatically ships directly with MySQL and perfectly groups identical slow queries, replacing the distinct dynamic variables logically with integers explicitly to calculate the sheer count of failures dynamically.
# Sort completely by the total execution time natively securely
mysqldumpslow -s t /var/log/mysql/slow.log | head -n 20
Expected Analysis Output:
Count: 154 Time=3.23s (497s) Lock=0.00s Rows=5000
SELECT * FROM wp_postmeta WHERE meta_key = 'S_current_user_location'
Diagnostic Deduction: This specific meta query fired 154 times, universally demanding 3.23 seconds inherently. This strictly requires a custom index immediately on wp_postmeta.
Common Mistakes & Troubleshooting
| Configuration Failure | Operational Symptom | Remediation Protocol |
|---|---|---|
| Full disk exhaustion | The node hits No space left on device. | Do not set long_query_time = 0 on busy production and avoid general_log in production. |
| Log File Permission Denials | The slow_query_log variable reports ON, but the physical /var/log/mysql/slow.log remains totally empty. | The database daemon lacks the root Linux permissions required to write explicitly into that designated directory. Ensure the mysql user formally owns the directory exactly. chown mysql:mysql /var/log/mysql/slow.log |