Mariadb
Mariadb 10.3 高磁碟寫入率
在其他一些優秀人士的幫助下，我發現我們最近遇到的高負載與 iotop 中看到的 mysqld 高磁碟寫入率有關。現在的平均負載為 3-6，而它曾經保持在 1 左右。我安裝了 mytop 來查看所有內容，因為 iotop 中報告了持續的磁碟寫入，並且根本看不到太多活動：
MySQL on localhost (10.3.7-MariaDB-log) up 0+01:09:53 [11:58:50] Queries: 959.6k qps: 234 Slow: 125.0 Se/In/Up/De(%): 129/01/02/00 qps now: 100 Slow qps: 0.0 Threads: 3 ( 6/ 2) 113/00/03/00 Cache Hits: 357.2k Hits/s: 87.2 Hits now: 22.4 Ratio: 28.8% Ratio now: 19.7% Key Efficiency: 100.0% Bps in/out: 127.0k/ 1.9M Now in/out: 73.4k/901.5k Id User Host/IP DB Time Cmd Query or State -- ---- ------- -- ---- --- ---------- 1 system us 0 Daemon InnoDB purge coordinator 2 system us 0 Daemon InnoDB purge worker 3 system us 0 Daemon InnoDB purge worker 4 system us 0 Daemon InnoDB purge worker 5 system us 0 Daemon InnoDB shutdown handler 5968 root localhost wmnf_www 0 Query show full processlist 6616 wmnfdb localhost wmnf_www 0 Sleep當 iotop 看起來像這樣時：
Total DISK READ : 0.00 B/s | Total DISK WRITE : 1638.41 K/s Actual DISK READ: 0.00 B/s | Actual DISK WRITE: 1852.28 K/s TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND 4075 be/3 root 0.00 B/s 3.82 K/s 0.00 % 34.67 % [jbd2/sdc-8] 7745 be/4 mysql 0.00 B/s 1497.10 K/s 0.00 % 3.73 % mysqld 7747 be/4 mysql 0.00 B/s 30.55 K/s 0.00 % 2.34 % mysqld 7748 be/4 mysql 0.00 B/s 45.83 K/s 0.00 % 1.92 % mysqld 7741 be/4 mysql 0.00 B/s 0.00 B/s 0.00 % 1.64 % mysqld 7746 be/4 mysql 0.00 B/s 30.55 K/s 0.00 % 0.21 % mysqld 7742 be/4 mysql 0.00 B/s 0.00 B/s 0.00 % 0.08 % mysqld 14410 be/4 mysql 0.00 B/s 15.28 K/s 0.00 % 0.00 % mysqld我總是看到幾個程序不斷地進行磁碟寫入。我最近確實增加了 innodb_buffer_pool_size，這是完整的設置，innodb_ 變數之後的所有設置都是我在沒有真正幫助的情況下執行 mysqltuner 後一直在嘗試的：
[mysqld] datadir=/db/mysql tmpdir=/dev/shm socket=/var/lib/mysql/mysql.sock max_connections=125 symbolic-links=0 max_allowed_packet=512M innodb_file_per_table innodb_flush_method=O_DIRECT innodb_buffer_pool_size=6G innodb_log_file_size=1500M innodb_buffer_pool_instances=6 query_cache_size=16M query_cache_type=1 thread_cache_size=4 join_buffer_size=16M tmp_table_size=256M max_heap_table_size=256MInnoDB 清除程序是否可能導致如此高的磁碟寫入？這是一個 WordPress 網站，我一直在查看慢查詢日誌，到處都是，但與所看到的高負載無關。
這是 top 大部分時間的樣子：
top - 12:16:43 up 13:02, 3 users, load average: 5.32, 4.26, 4.25 Tasks: 202 total, 6 running, 131 sleeping, 0 stopped, 0 zombie %Cpu(s): 15.5 us, 5.7 sy, 0.0 ni, 38.3 id, 1.2 wa, 0.0 hi, 0.5 si, 38.8 st KiB Mem : 16419324 total, 289672 free, 4549808 used, 11579844 buff/cache KiB Swap: 524284 total, 524284 free, 0 used. 11506900 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 7731 mysql 20 0 10.1g 2.7g 20896 S 28.8 17.2 29:29.86 mysqld 15121 wmnf_ad+ 20 0 724508 107340 81852 S 27.2 0.7 0:01.83 httpd 15085 wmnf_ad+ 20 0 652008 115456 79932 R 22.2 0.7 0:01.90 httpd 15119 wmnf_ad+ 20 0 733080 111168 78336 S 11.9 0.7 0:02.73 httpd 14776 wmnf_ad+ 20 0 733724 126108 91168 S 10.9 0.8 0:05.19 httpd 14985 wmnf_ad+ 20 0 736768 119092 83020 S 9.6 0.7 0:02.72 httpd 14993 wmnf_ad+ 20 0 733832 118872 83732 S 9.6 0.7 0:05.91 httpd 14744 wmnf_ad+ 20 0 735768 129408 92820 S 8.6 0.8 0:05.60 httpd 13914 wmnf_ad+ 20 0 739364 131340 92704 S 7.9 0.8 0:14.55 httpd 14948 wmnf_ad+ 20 0 732920 110984 77456 R 6.3 0.7 0:03.08 httpd我確實看到 innodb 正在寫入這些 ibd 文件，而程序列表中沒有任何內容，這是 SHOW ENGINE INNODB STATUS 顯示的內容：
===================================== 2018-05-29 12:40:26 0x7ff100325700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 7 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 5814 srv_active, 0 srv_shutdown, 527 srv_idle srv_master_thread log flush and writes: 6338 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 1030737 OS WAIT ARRAY INFO: signal count 588517 RW-shared spins 0, rounds 70740, OS waits 33434 RW-excl spins 0, rounds 94359, OS waits 4228 RW-sx spins 2111, rounds 55933, OS waits 1523 Spin rounds per wait: 70740.00 RW-shared, 94359.00 RW-excl, 26.50 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 1213126 Purge done for trx's n:o < 1213126 undo n:o < 0 state: running but idle History list length 3 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 422148390082168, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422148390081352, not started 0 lock struct(s), heap size 1136, 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 124952 OS file reads, 245278 OS file writes, 82495 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 24.28 writes/s, 8.28 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 63, seg size 65, 70 merges merged operations: insert 37, delete mark 607, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 1593833, node heap has 1953 buffer(s) Hash table size 1593833, node heap has 178 buffer(s) Hash table size 1593833, node heap has 97 buffer(s) Hash table size 1593833, node heap has 2315 buffer(s) Hash table size 1593833, node heap has 128 buffer(s) Hash table size 1593833, node heap has 135 buffer(s) Hash table size 1593833, node heap has 35 buffer(s) Hash table size 1593833, node heap has 39 buffer(s) 765.60 hash searches/s, 525.21 non-hash searches/s --- LOG --- Log sequence number 12869849455 Log flushed up to 12869849455 Pages flushed up to 12869849455 Last checkpoint at 12869849331 0 pending log flushes, 0 pending chkp writes 53483 log i/o's done, 3.71 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 6627000320 Dictionary memory allocated 3675472 Buffer pool size 393216 Free buffers 262869 Database pages 125467 Old database pages 46429 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 24, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 124488, created 979, written 162905 0.00 reads/s, 0.00 creates/s, 18.28 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: 125467, 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 65536 Free buffers 43142 Database pages 21580 Old database pages 7985 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 21405, created 175, written 28103 0.00 reads/s, 0.00 creates/s, 5.57 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: 21580, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 65536 Free buffers 43698 Database pages 21029 Old database pages 7782 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 20714, created 315, written 20255 0.00 reads/s, 0.00 creates/s, 6.00 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: 21029, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 65536 Free buffers 43979 Database pages 20742 Old database pages 7676 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 10, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 20617, created 125, written 35035 0.00 reads/s, 0.00 creates/s, 4.00 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: 20742, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 65536 Free buffers 43611 Database pages 21117 Old database pages 7815 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 4, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 20991, created 126, written 30631 0.00 reads/s, 0.00 creates/s, 1.71 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: 21117, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 65536 Free buffers 44135 Database pages 20585 Old database pages 7618 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 20469, created 116, written 30474 0.00 reads/s, 0.00 creates/s, 0.71 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: 20585, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 65536 Free buffers 44304 Database pages 20414 Old database pages 7553 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 7, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 20292, created 122, written 18407 0.00 reads/s, 0.00 creates/s, 0.29 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: 20414, 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=7731, Main thread ID=140666264016640, state: sleeping Number of rows inserted 8611, updated 31931, deleted 19012, read 210937170 0.43 inserts/s, 1.86 updates/s, 0.00 deletes/s, 2021.00 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 ============================
如果
top您包含的輸出是典型的，那麼您的問題與 MySQL 無關。%Cpu(s): 15.5 us, 5.7 sy, 0.0 ni, 38.3 id, 1.2 wa, 0.0 hi, 0.5 si, 38.8 st ^^^^^^^注意到我強調的最後一點了嗎？“st”代表“竊取時間”。由於主機上執行的其他虛擬機或程序，38.8% 的 CPU 時間不可用。
如果您的虛擬機位於公共雲中，請聯繫您的託管服務提供商，如果問題仍然存在，請考慮轉移到另一台主機。
如果您的虛擬機是在現場託管的，那麼您已經超額訂閱了您的 VM 主機。添加更多物理主機，或減少其他虛擬機上的 CPU 負載。