我最近设置了一个包含 MariaDB 作为数据库的 docker 应用程序堆栈（邮件服务器）。此外，我注意到我的磁盘的 IO 写入速率很高（我猜是吧？）：（最高可达 40M/s）
Total DISK READ: 0.00 B/s | Total DISK WRITE: 13.50 M/s
Current DISK READ: 0.00 B/s | Current DISK WRITE: 13.35 M/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
23581 be/4 systemd- 0.00 B/s 3.90 M/s 0.00 % 0.76 % mysqld
23595 be/4 systemd- 0.00 B/s 3.00 M/s 0.00 % 0.61 % mysqld
23580 be/4 systemd- 0.00 B/s 2009.49 K/s 0.00 % 0.31 % mysqld
23578 be/4 systemd- 0.00 B/s 1271.89 K/s 0.00 % 0.27 % mysqld
23572 be/4 systemd- 0.00 B/s 1706.88 K/s 0.00 % 0.25 % mysqld
由于我不知道应用程序是如何构建的，我想知道我是否能以某种方式找出哪些 MariaDB 进程负责不断写入？我没有 IO 问题，只是想知道所有这些需要写入磁盘的数据来自哪里。
根据要求：
# mysql -p"$MYSQL_ROOT_PASSWORD" -e "SHOW FULL PROCESSLIST"|grep -vi sleep
Id User Host db Command Time State Info Progress
1 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
3 system user NULL Daemon NULL InnoDB purge worker NULL 0.000
2 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000
5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000
165 root localhost NULL Query 0 Init SHOW FULL PROCESSLIST 0.000
# mysql -p"$MYSQL_ROOT_PASSWORD" -e "SHOW ENGINE INNODB STATUS"
| InnoDB | |
=====================================
2020-10-03 14:06:38 0x7f75a80d3700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 32 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 4 srv_active, 0 srv_shutdown, 3934 srv_idle
srv_master_thread log flush and writes: 3938
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 20530
OS WAIT ARRAY INFO: signal count 11722
RW-shared spins 4030, rounds 120151, OS waits 3978
RW-excl spins 461, rounds 4074, OS waits 105
RW-sx spins 3, rounds 34, OS waits 1
Spin rounds per wait: 29.81 RW-shared, 8.84 RW-excl, 11.33 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 11691927
Purge done for trx's n:o < 11691927 undo n:o < 0 state: running but idle
History list length 29
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421645436261816, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421645436257600, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421645436253384, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421645436249168, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421645436244952, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421645436240736, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421645436236520, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421645436232304, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421645436228088, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421645436223872, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421645436219656, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421645436215440, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
93717 OS file reads, 55970 OS file writes, 38023 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 13.12 writes/s, 9.28 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 9, seg size 11, 1 merges
merged operations:
insert 1, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 6374941, node heap has 3 buffer(s)
Hash table size 6374941, node heap has 106 buffer(s)
Hash table size 6374941, node heap has 0 buffer(s)
Hash table size 6374941, node heap has 325 buffer(s)
Hash table size 6374941, node heap has 179 buffer(s)
Hash table size 6374941, node heap has 0 buffer(s)
Hash table size 6374941, node heap has 21 buffer(s)
Hash table size 6374941, node heap has 3 buffer(s)
6.50 hash searches/s, 11.12 non-hash searches/s
---
LOG
---
Log sequence number 17606418609
Log flushed up to 17606418292
Pages flushed up to 17606418292
Last checkpoint at 17606417955
0 pending log flushes, 0 pending chkp writes
12039 log i/o's done, 3.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 26340229120
Dictionary memory allocated 73904
Buffer pool size 1572840
Free buffers 1478380
Database pages 93823
Old database pages 34783
Modified db pages 4
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 93675, created 148, written 32090
0.00 reads/s, 0.00 creates/s, 7.34 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 93823, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 196608
Free buffers 184858
Database pages 11668
Old database pages 4322
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 11651, created 17, written 1986
0.00 reads/s, 0.00 creates/s, 0.72 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 11668, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 196608
Free buffers 184576
Database pages 11963
Old database pages 4436
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 11963, created 0, written 4069
0.00 reads/s, 0.00 creates/s, 0.34 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 11963, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 196608
Free buffers 185021
Database pages 11505
Old database pages 4264
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 11503, created 2, written 340
0.00 reads/s, 0.00 creates/s, 0.09 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 11505, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 196608
Free buffers 184908
Database pages 11622
Old database pages 4310
Modified db pages 3
Percent of dirty pages(LRU & free pages): 0.002
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 11621, created 1, written 14439
0.00 reads/s, 0.00 creates/s, 3.16 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 11622, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 196608
Free buffers 184668
Database pages 11861
Old database pages 4398
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 11861, created 0, written 4769
0.00 reads/s, 0.00 creates/s, 1.06 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 11861, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 196608
Free buffers 184471
Database pages 12058
Old database pages 4470
Modified db pages 1
Percent of dirty pages(LRU & free pages): 0.001
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 12058, created 0, written 3761
0.00 reads/s, 0.00 creates/s, 1.12 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 12058, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 196608
Free buffers 185183
Database pages 11338
Old database pages 4205
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 11274, created 64, written 2639
0.00 reads/s, 0.00 creates/s, 0.84 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 11338, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 196584
Free buffers 184695
Database pages 11808
Old database pages 4378
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 11744, created 64, written 87
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 11808, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140143358617344, state: sleeping
Number of rows inserted 1275, updated 5512, deleted 210, read 623592
0.00 inserts/s, 1.34 updates/s, 0.00 deletes/s, 8.62 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
答案1
安装mytop并将其指向数据库。这将向您显示当前正在运行的查询。
mytop -uroot -proot_password -d default_database