我目前正在进行一些压力测试，我尽可能快地将插入推送到数据库。模拟每秒插入 10000 条记录。插入约 2 亿条记录后，我发现插入速度非常慢。所以我停止了模拟。
目前我已经停止向数据库推送任何事务。因此，没有插入、更新。在整个模拟过程中，没有对该数据库进行任何删除。然而，磁盘上正在进行如此多的 IO 操作。
当我检查 innodb 状态和 iostat 时，我可以看到 Mysql Innodb 引擎正忙于执行磁盘操作。我无法找出这些 IO 的原因。
IO 率非常高，因此造成了向数据库推送进一步请求的瓶颈。
任何帮助都非常感谢。我已附上 InnodB 引擎状态。
-----------------
my.cnf configuration:
-----------------
innodb_file_per_table=1
innodb_buffer_pool_size=6G
innodb_log_file_size=1G
innodb_flush_log_at_trx_commit=0
innodb_io_capacity=100
innodb_flush_method=O_DIRECT
innodb_doublewrite=OFF
innodb_max_dirty_pages_pct=75
innodb_max_dirty_pages_pct_lwm=60
innodb_buffer_pool_instances=1
| InnoDB | |
=====================================
2014-08-26 00:02:50 7fc41d38f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 28 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 16554 srv_active, 0 srv_shutdown, 6695 srv_idle
srv_master_thread log flush and writes: 23248
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 74717
--Thread 140480424269568 has waited at btr0cur.cc line 545 for 0.00 seconds the semaphore:
S-lock on RW-latch at 0x7fc5ecdc7af8 created in file dict0dict.cc line 2505
a writer (thread id 140480399091456) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: fffffffffff00000
Last time read locked in file btr0cur.cc line 545
Last time write locked in file /build/buildd/mysql-5.6-5.6.19/storage/innobase/ibuf/ibuf0ibuf.cc line 409
OS WAIT ARRAY INFO: signal count 72822
Mutex spin waits 234298, rounds 1620740, OS waits 42193
RW-shared spins 9405, rounds 170212, OS waits 5011
RW-excl spins 23541, rounds 884561, OS waits 26158
Spin rounds per wait: 6.92 mutex, 18.10 RW-shared, 37.58 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 5263484
Purge done for trx's n:o < 5198325 undo n:o < 0 state: running but idle
History list length 604
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 76, OS thread handle 0x7fc41d38f700, query id 1482925 localhost ddosprofile init
show engine innodb status
--------
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: complete io for buf page (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: complete io for buf page (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: 22 [3, 2, 8, 9] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 2
25561 OS file reads, 10271392 OS file writes, 138838 OS fsyncs
0 pending preads, 1 pending pwrites
23.57 reads/s, 16384 avg bytes/read, 263.95 writes/s, 8.36 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 562, free list len 24162, seg size 24725, 18712 merges
merged operations:
insert 169928, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 12749951, node heap has 3 buffer(s)
0.00 hash searches/s, 113.57 non-hash searches/s
---
LOG
---
Log sequence number 183651147315
Log flushed up to 183651127940
Pages flushed up to 183242751536
Last checkpoint at 183241671211
0 pending log writes, 0 pending chkp writes
98244 log i/o's done, 0.54 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 6593445888; in additional pool allocated 0
Dictionary memory allocated 87500
Buffer pool size 393215
Free buffers 1
Database pages 393211
Old database pages 145154
Modified db pages 178594
Pending reads 25
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 15953492, not young 42560
0.00 youngs/s, 0.00 non-youngs/s
Pages read 25519, created 1321453, written 10147162
22.71 reads/s, 0.00 creates/s, 263.45 writes/s
Buffer pool hit rate 969 / 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: 393211, unzip_LRU len: 0
I/O sum[11937]:cur[103], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 8090, id 140480331949824, state: doing insert buffer merge
Number of rows inserted 196205997, updated 0, deleted 972, read 1134
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
答案1
您的 InnoDB 数据库缓冲池大约为 6.6 GB。
当向数据库插入数据时，InnoDB引擎首先将数据添加到缓冲池中，然后再写入磁盘。
因此，这里发生的情况是，当缓冲池已满时，InnoDB 开始将其内容写入磁盘。因此插入性能下降。
现在，当您停止进程时，缓冲池中仍有未提交到磁盘的数据。因此，InnoDB 继续将数据写入磁盘，以达到所有数据都在磁盘上的状态。
这可以从缓冲池和内存输出的一部分。您可以看到缓冲池中有 178k 个已修改的页面。