关于insert buffer的效果

叶老师在优化班讲到insert buffer的时候,提到了对其效果的计算方式,如下图:
ibuf-1.jpg

 
老师当时说,21%表示大约每5次更新操作合并成1次操作。
今天我看了下公司线上的MySQL状态,见下图:

ibuf-2.jpg

 按ppt上的算法,我这边得出来的值是3.74,就是374%。
跟上面的例子的效果完全相反,相当于每1次更新操作“合并”成大约4次操作。
但这讲不通啊,我的理解是,在最坏的情况下,1次更新操作最多对应1次合并,相当于没有启用insert buffer。
是我的环境、配置有问题还是说我的理解有问题?
请大家指教,谢谢!
 
 
-----------------------------------------------------------------------------------
 
以下是SHOW ENGINE INNODB STATUS的全部信息,为了节省篇幅,省略了死锁和只读事务的信息:
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2018-01-30 13:58:15 0x7f03cd4a9700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 51 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 17164651 srv_active, 0 srv_shutdown, 75879 srv_idle
srv_master_thread log flush and writes: 17240454
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 7257651
OS WAIT ARRAY INFO: signal count 1746357475
RW-shared spins 0, rounds 2700569915, OS waits 6576776
RW-excl spins 0, rounds 1345286537, OS waits 454008
RW-sx spins 4512141, rounds 42693154, OS waits 12894
Spin rounds per wait: 2700569915.00 RW-shared, 1345286537.00 RW-excl, 9.46 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
...
------------
TRANSACTIONS
------------
Trx id counter 222870369
Purge done for trx's n:o < 222870369 undo n:o < 0 state: running but idle
History list length 34
LIST OF TRANSACTIONS FOR EACH SESSION:
...
--------
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 (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
101786 OS file reads, 251464660 OS file writes, 150118958 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 5.14 writes/s, 5.14 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 3967 merges
merged operations:
 insert 929, delete mark 75, delete 33
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 18920921, node heap has 7494 buffer(s)
Hash table size 18920921, node heap has 7542 buffer(s)
Hash table size 18920921, node heap has 27345 buffer(s)
Hash table size 18920921, node heap has 16941 buffer(s)
Hash table size 18920921, node heap has 20480 buffer(s)
Hash table size 18920921, node heap has 18263 buffer(s)
Hash table size 18920921, node heap has 28580 buffer(s)
Hash table size 18920921, node heap has 10294 buffer(s)
31972.12 hash searches/s, 6747.71 non-hash searches/s
---
LOG
---
Log sequence number 250399882817
Log flushed up to   250399882817
Pages flushed up to 250398286164
Last checkpoint at  250398286164
0 pending log flushes, 0 pending chkp writes
137338501 log i/o's done, 5.14 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 70363643904
Dictionary memory allocated 47882912
Buffer pool size   4193792
Free buffers       32021
Database pages     4024832
Old database pages 1485565
Modified db pages  4841
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1695747, not young 5393255
0.00 youngs/s, 0.00 non-youngs/s
Pages read 99530, created 4523795, written 109803832
0.00 reads/s, 0.02 creates/s, 0.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: 4024832, 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   524224
Free buffers       4004
Database pages     502969
Old database pages 185646
Modified db pages  656
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 268351, not young 434687
0.00 youngs/s, 0.00 non-youngs/s
Pages read 7562, created 561701, written 14213935
0.00 reads/s, 0.00 creates/s, 0.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: 502969, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   524224
Free buffers       4001
Database pages     503111
Old database pages 185698
Modified db pages  503
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 140544, not young 1062242
0.00 youngs/s, 0.00 non-youngs/s
Pages read 18746, created 562769, written 12666840
0.00 reads/s, 0.00 creates/s, 0.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: 503111, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   524224
Free buffers       4003
Database pages     503107
Old database pages 185697
Modified db pages  444
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 289452, not young 16233
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1039, created 559591, written 12629087
0.00 reads/s, 0.00 creates/s, 0.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: 503107, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   524224
Free buffers       4002
Database pages     503133
Old database pages 185706
Modified db pages  555
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 136577, not young 874490
0.00 youngs/s, 0.00 non-youngs/s
Pages read 19270, created 562141, written 15931429
0.00 reads/s, 0.02 creates/s, 0.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: 503133, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   524224
Free buffers       4004
Database pages     503178
Old database pages 185723
Modified db pages  601
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 142559, not young 847102
0.00 youngs/s, 0.00 non-youngs/s
Pages read 18549, created 561780, written 13616517
0.00 reads/s, 0.00 creates/s, 0.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: 503178, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   524224
Free buffers       4002
Database pages     503045
Old database pages 185674
Modified db pages  580
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 136279, not young 813260
0.00 youngs/s, 0.00 non-youngs/s
Pages read 18367, created 560655, written 13120177
0.00 reads/s, 0.00 creates/s, 0.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: 503045, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   524224
Free buffers       4001
Database pages     503136
Old database pages 185707
Modified db pages  763
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 294863, not young 953861
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9631, created 593535, written 14357000
0.00 reads/s, 0.00 creates/s, 0.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: 503136, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   524224
Free buffers       4004
Database pages     503153
Old database pages 185714
Modified db pages  739
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 287122, not young 391380
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6366, created 561623, written 13268847
0.00 reads/s, 0.00 creates/s, 0.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: 503153, 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=32913, Main thread ID=139658088404736, state: sleeping
Number of rows inserted 4701456051, updated 131359961, deleted 27301375, read 3739858722164
48.51 inserts/s, 3.04 updates/s, 0.02 deletes/s, 167842.20 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)
 
 

A475-陈涛-苏州
已邀请:

yejr

赞同来自:

多跑几次命令,多采集几次,可能是短时间内采集的数据有问题。
还有,你这个实例其他数据也贴一下。

要回复问题请先登录注册