Core MySQL Performance Monitoring Metrics

Slow Query Definition

Slow queries refer to SQL statements whose execution duration exceeds a predefined threshold. This logging capability helps teams identify poorly performing queries to target database performance optmiization efforts.

---

Enable Slow Query Logging

Key Configuration Parameters

Parameter	Description
slow_query_log	Toggles slow query logging functionality, accepts values ON (enabled) or OFF (disabled)
slow_query_log_file	Filesystem path where slow query log entries are written
long_query_time	Execution time threshold in seconds; any query taking longer then this value will be logged

Step 1: Verify Current Configuration

Run the following commands in the MySQL interactive terminal to check existing settings:

-- Check slow query log status and storage path
SHOW VARIABLES LIKE 'slow_query%';
/* Sample output:
+---------------------+------------------------------------------+
| Variable_name       | Value                                    |
+---------------------+------------------------------------------+
| slow_query_log      | OFF                                      |
| slow_query_log_file | /var/lib/mysql/ip-10-0-1-12-slow.log     |
+---------------------+------------------------------------------+
*/

-- Check current execution time threshold
SHOW VARIABLES LIKE 'long_query_time';
/* Sample output:
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
*/

Step 2: Apply Temporary Configuration

The following seettings take effect immediately but reset to default values after a MySQL service restart:

-- Enable slow query logging globally
SET GLOBAL slow_query_log = 'ON';

-- Specify custom log storage path (ensure MySQL has write access to the target directory)
SET GLOBAL slow_query_log_file = '/data/mysql/logs/slow_queries.log';

-- Set threshold to 0.5 seconds, queries taking longer than 500ms will be logged
SET long_query_time = 0.5;

Step 3: Persist Configuration (Restart Required)

To retain settings across MySQL restarts, add the following lines to the [mysqld] section of your MySQL configuration file (my.cnf on Linux, my.ini on Windows):

[mysqld]
slow_query_log = ON
slow_query_log_file = /data/mysql/logs/slow_queries.log
long_query_time = 0.5

---

Validate Slow Query Logging

Run a test query that intentionally exceeds the configured time threshold to confirm logging works as expected:

-- This query will pause execution for 3 seconds
SELECT SLEEP(3);

Check the log file for the entry using this Linux shell command:

# Show last 20 lines of the slow query log and follow new entries
tail -20f /data/mysql/logs/slow_queries.log

The SELECT SLEEP(3) statement will appear as a logged slow query in the output.

---

Slow Query Troubleshooting Checklist

When analyzing logged slow queries, walk through these common root causes first:

1. Target tables have grown to unmanageable row counts (evaluate table partitioning or database sharding for high-volume datasets)
2. Elevated concurrent request volume is exhausting database CPU, memory or I/O resources
3. Table or row level locks are blocking query execution
4. Required indexes are missing, or existing indexes are not being used due to improper query structure
5. SQL logic contains redundant joins, subqueries or implicit type conversions that force full table scans