Diagnosing MySQL Disk I/O Pressure with Linux System Tools

Introduction – Newcomers to MySQL often encounter high disk I/O without obvious cause; the article sets up a reproducible scenario to locate the source of the pressure.

## 生成一张大表，用于测试[root@tidb-master ~]# /usr/share/sysbench/oltp_write_only.lua --mysql-host=10.186.61.36 --mysql-user=sun --mysql-password='xx' --mysql-port=3306 --mysql-db=testdb --tables=1 --table_size=15000000 --auto_inc=off --report-interval=2 --db-ps-mode=disable --time=100000 --threads=1 prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Creating table 'sbtest1'...
Inserting 15000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
## 对生成的表进行写操作
[root@tidb-master ~]# /usr/share/sysbench/oltp_write_only.lua --mysql-host=10.186.61.36 --mysql-user=sun --mysql-password='xx' --mysql-port=3306 --mysql-db=testdb --tables=1 --table_size=15000000 --auto_inc=off --report-interval=2 --db-ps-mode=disable --time=100000 --threads=1 run
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 1
Report intermediate results every 2 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 2s ] thds: 1 tps: 24.47 qps: 148.33 (r/w/o: 0.00/98.89/49.44) lat (ms,95%): 86.00 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 1 tps: 53.02 qps: 318.62 (r/w/o: 0.00/212.58/106.04) lat (ms,95%): 48.34 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 1 tps: 60.50 qps: 363.50 (r/w/o: 0.00/242.50/121.00) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 1 tps: 65.50 qps: 391.02 (r/w/o: 0.00/260.01/131.01) lat (ms,95%): 39.65 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 1 tps: 55.50 qps: 334.51 (r/w/o: 0.00/223.51/111.00) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 1 tps: 57.00 qps: 340.99 (r/w/o: 0.00/227.00/114.00) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00

Problem Analysis – top – The top output shows a high wa (I/O wait) percentage, indicating the system is spending a lot of time waiting for disk operations.

top - 17:05:59 up 179 days,  7:05,  2 users,  load average: 0.84, 0.70, 0.93
Tasks: 181 total,   1 running, 180 sleeping,   0 stopped,   0 zombie
%Cpu0  :  1.3 us,  0.7 sy,  0.0 ni, 95.3 id,  2.7 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu1  :  6.9 us,  3.8 sy,  0.0 ni, 60.3 id, 29.0 wa,  0.0 hi,  0.0 si,  0.0 st
... (truncated for brevity) ...
PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
8858 actiont+  20   0 7249940   1.1g  11892 S  30.0  9.3 210:38.75 mysqld
26447 tidb      20   0 4000364   1.0g   4740 S   5.3  9.0  10018:23 TiFlashMain

Problem Analysis – iostat – Repeated iostat -xm 1 runs reveal sustained write throughput around 26 MB/s and a utilization (%util) often exceeding 80 %, confirming heavy disk activity.

[root@tidb-tikv01 ~]# iostat  -xm 1 
Linux 3.10.0-862.14.4.el7.x86_64 (tidb-tikv01)  04/22/2022      _x86_64_        (12 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.21    0.00    0.44    0.18    0.10   98.07

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
vda               0.01     0.18    3.53   22.99     0.08     0.25    25.84     0.19    7.16    5.64    7.40   0.71   1.88
...

Problem Analysis – iotop – The iotop output shows MySQL thread IDs (e.g., 22233) consuming over 50 % of disk I/O, with another thread (8879) responsible for most of the write traffic.

Total DISK READ :       5.29 M/s | Total DISK WRITE :      29.42 M/s
Actual DISK READ:       5.29 M/s | Actual DISK WRITE:      26.83 M/s
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
22233 be/4 actionte    2.56 M/s  473.07 K/s  0.00 % 52.59 % mysqld --defaults-file=/opt/mysql/etc/3306/my.cnf --d~ --socket=/opt/mysql/data/3306/mysqld.sock --port=3306
8879 be/4 actionte    0.00 B/s   27.31 M/s  0.00 % 17.76 % mysqld --defaults-file=/opt/mysql/etc/3306/my.cnf --d~ --socket=/opt/mysql/data/3306/mysqld.sock --port=3306

Mapping OS Threads to MySQL Sessions – By querying performance_schema.threads with the OS thread IDs, the article identifies the user sun executing a rapid UPDATE loop on sbtest1, and also shows the InnoDB page‑flush coordinator thread (ID 8879) constantly flushing dirty pages.

mysql> select * from performance_schema.threads where THREAD_OS_ID=22233\G
*************************** 1. row ***************************
          THREAD_ID: 14880
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 14830
   PROCESSLIST_USER: sun
   PROCESSLIST_HOST: 10.186.61.16
   PROCESSLIST_DB: testdb
   PROCESSLIST_COMMAND: Query
   PROCESSLIST_STATE: updating
   PROCESSLIST_INFO: UPDATE sbtest1 SET k=k+1 WHERE id=7899611
...

Resolution – Killing the offending connection (ID 14830) immediately drops the I/O pressure, as confirmed by a subsequent iostat run showing much lower utilization.

mysql> kill 14830;
Query OK, 0 rows affected (0.00 sec)

[... later iostat shows %util dropping to single‑digit values ...]

Conclusion – The step‑by‑step method demonstrates how to combine Linux monitoring tools with MySQL performance schema queries to pinpoint and mitigate disk I/O bottlenecks caused by runaway SQL statements.