Why Mastering MySQL Slow Query Log Is Essential for Performance

Why You Must Master the Slow Query Log?

Have you ever seen a system stall even though CPU and memory are fine, while MySQL keeps running without returning results? Leaders ask “Which SQL is slow? Why?” – this article shows how to use the slow query log to answer those questions.

1. What Is the Slow Query Log? Component Relationships

Purpose: Records SQL statements whose execution time exceeds a configured threshold.

Use case: Identify slow SQL, assess performance impact, and target optimization.

Applicable scenarios: SQL optimization and performance‑bottleneck analysis.

In a master‑slave architecture:

master: Handles write operations; enable the slow query log only on the master.

node1, node2 (slaves): Usually keep the log disabled unless you specifically optimize slave queries.

Recommendation: Enable the log only on the write‑master to avoid unnecessary performance overhead.

2. Check and Enable the Slow Query Log (run on master)

Where to execute?

<code># SSH login to master server
ssh root@master

# Enter MySQL container (Docker deployment)
docker exec -it mysql-master bash

# Login to MySQL
mysql -uroot -p</code>

1) Verify whether it is enabled

<code>SHOW VARIABLES LIKE '%slow_query_log%';</code>

If the output shows slow_query_log = OFF , the log is disabled.

2) Temporarily enable (lost after restart)

<code>SET GLOBAL slow_query_log ON;
SET GLOBAL slow_query_log_file = '/var/lib/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1; -- queries >1 s are considered slow</code>

3) Permanently enable (edit my.cnf inside the container)

Note: Commands must be executed inside the container.

<code>vi /etc/my.cnf</code>

Add the following under the

[mysqld]

section (create the section if missing):

<code>[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 1</code>

Save and exit (

Esc

→

:wq

) then restart the container:

<code>docker restart mysql-master</code>

3. Solving “command not found” Errors

If you see

bash: mysql: command not found

, you need to enter the container first:

<code>docker exec -it mysql-master bash
mysql -uroot -p</code>

If Docker itself is missing, install it:

<code>yum install -y docker
systemctl start docker</code>

4. Where Is the Log File and How to Read It?

Enter the container and view the log:

<code>docker exec -it mysql-master bash
cd /var/lib/mysql/
cat mysql-slow.log</code>

Sample log entry (fields of interest):

<code># Time: 2025-06-12T12:00:12.123456Z
# User@Host: root[root] @ localhost []
# Query_time: 3.1415  Lock_time: 0.0001  Rows_sent: 1  Rows_examined: 10000
SELECT * FROM big_table WHERE name LIKE '%测试%';</code>

Key fields:

Query_time

: execution duration.

Lock_time

: time waiting for locks.

Rows_examined

: number of rows scanned (more rows → slower).

5. Filtering the Log to Show Only Slow Queries

<code>grep -A10 10 Query_time mysql-slow.log | grep -B5 SELECT</code>

Or use analysis tools such as

pt-query-digest

(to be covered later).