Mysql优化之innodb_buffer_pool_size篇

2022年1月25日 0条评论 103次阅读 0人点赞

MyISAM：仅在内存中保存索引。

InnoDB：在内存中保存索引和数据。保存在内存的内容访问速度要比磁盘上的更快。对此（假设你的服务器仅仅运行 MySQL），公认的“经验法则”是设置为你的服务器物理内存的 80%。在保证操作系统不使用交换分区而正常运行所需要的足够内存之后，尽可能多地为 MySQL 分配物理内存。

查看当前的innodb_buffer_pool_size

show variables like 'innodb_buffer_pool%';

Variable_name	Value
innodb_buffer_pool_chunk_size	134217728(块大小,默认128M,不需要改)
innodb_buffer_pool_dump_at_shutdown	ON
innodb_buffer_pool_dump_now	OFF
innodb_buffer_pool_dump_pct	25
innodb_buffer_pool_filename	ib_buffer_pool
innodb_buffer_pool_in_core_file	ON
innodb_buffer_pool_instances	8(缓冲池实例数,改为8)
innodb_buffer_pool_load_abort	OFF
innodb_buffer_pool_load_at_startup	ON
innodb_buffer_pool_load_now	OFF
innodb_buffer_pool_size	268435456(建议大于2G,总内存的50%为佳,越大越好)

判断当前的innodb_buffer_pool_size大小是否合适？

show status like 'innodb_buffer_pool_read%';

Variable_name	Value
Innodb_buffer_pool_read_ahead_rnd	0
Innodb_buffer_pool_read_ahead	0
Innodb_buffer_pool_read_ahead_evicted	0
Innodb_buffer_pool_read_requests	3304724 (缓存读取数)
Innodb_buffer_pool_reads	26203 (硬盘读取数)

查看缓冲池的性能

占比:
Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests*100 即 1700/940668*100=0.18072263540378
意味着InnoDB可以满足缓冲池本身的大部分请求。从磁盘完成读取的百分比非常小。因此无需增加innodb_buffer_pool_size值。

命中率：
公式:innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100
3304724 ÷ (26203+3304724)*100 =‬ 99.21334211167041% 值越大命中越高
此值低于99%，则可以考虑增加innodb_buffer_pool_size。

调整InnoDB缓冲池大小

//当缓冲池大小大于1G时，将innodb_buffer_pool_instances设置大于1的值可以提高服务器的可扩展性。
//设置为你的服务器物理内存的 80%。在保证操作系统不使用交换分区而正常运行所需要的足够内存之后 ，尽可能多地为 MySQL 分配物理内存。
innodb_buffer_pool_size=256M  #设置为你的服务器物理内存的 80%
innodb_buffer_pool_size=2G 
innodb_buffer_pool_instances=2

查看InnoDB状态

show engine innodb status \G;

=====================================
2023-10-11 09:11:47 139649190155840 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 21 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 926 srv_active, 0 srv_shutdown, 125 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 178
OS WAIT ARRAY INFO: signal count 237
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 78640965
Purge done for trx's n:o < 78640965 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421125150361112, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421125150359496, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421125150358688, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421125150360304, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421125150357880, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421125150357072, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421125150356264, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421125150354648, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421125150355456, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421125150353840, not started flushing log
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421125150353032, 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: complete io for buf page (write thread)
I/O thread 7 state: complete io for buf page (write thread)
I/O thread 8 state: complete io for buf page (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, 1, 0] ,
 ibuf aio reads:, log i/o's:
Pending flushes (fsync) log: 1; buffer pool: 3
76838 OS file reads, 14151 OS file writes, 10015 OS fsyncs
0 pending preads, 1 pending pwrites
3.67 reads/s, 16384 avg bytes/read, 12.98 writes/s, 11.24 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 8579, seg size 8581, 862 merges
merged operations:
 insert 2144, delete mark 2423, delete 1669
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1328213, node heap has 5 buffer(s)
Hash table size 1328213, node heap has 3 buffer(s)
Hash table size 1328213, node heap has 30 buffer(s)
Hash table size 1328213, node heap has 2 buffer(s)
Hash table size 1328213, node heap has 1 buffer(s)
Hash table size 1328213, node heap has 598 buffer(s)
Hash table size 1328213, node heap has 8 buffer(s)
Hash table size 1328213, node heap has 7 buffer(s)
49.57 hash searches/s, 72.19 non-hash searches/s
---
LOG
---
Log sequence number          238418391407
Log buffer assigned up to    238418391407
Log buffer completed up to   238418391407
Log written up to            238418391116
Log flushed up to            238418391106
Added dirty pages up to      238418391407
Pages flushed up to          238408449218
Last checkpoint at           238408446326
Log minimum file id is       72801
Log maximum file id is       72805
10798 log i/o's done, 3.95 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 1120218 //表示Buffer Pool向操作系统申请的连续内存空间总大小(包括全部控制块、缓存页、以及碎片的字节大小)
Buffer pool size   327651  //表示该Buffer Pool可以容纳多少缓存页，注意，单位是页！
Free buffers       249963 //表示当前Buffer Pool还有多少空闲缓存页,注意，单位是页！
Database pages     77034
Old database pages 28327
Modified db pages  2843 //表示脏页数量，也就是flush链表中节点的数量。
Pending reads      0 //表示正在等待从磁盘上加载到Buffer Pool中的页面数量，需要注意的s当准备从磁盘中加载某个页面时，会先为这个页面在Buffer Pool中分配一个缓存页以及它对应的控制块，然后把这个控制块添加到LRU的old区域的头部，但是这个时候真正的磁盘页并没有被加载进来，Pending reads的值会跟着加1。
Pending writes: LRU 0, flush list 3, single page 0
Pages made young 14757, not young 143864
2.94 youngs/s, 26.88 non-youngs/s
Pages read 76756, created 278, written 2037
3.67 reads/s, 0.00 creates/s, 4.36 writes/s

Buffer pool hit rate 993 / 1000,   //表示在过去某段时间，平均访问1000次页面，有多少次该页面已经被缓存到Buffer Pool了

young-making rate 6 / 1000 not 55 / 1000  //表示在过去某段时间，平均访问1000次页面，有多少次访问使页面移动到young区域的头部了


Pages read ahead 0.00/s,   //表示每秒读入的pages

evicted without access 0.00/s,  //表示每秒读出的pages

Random read ahead 0.00/s  //表示随机读人的pages

LRU len: 77034, unzip_LRU len: 0
I/O sum[17040]:cur[24], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   40957
Free buffers       31296
Database pages     9580
Old database pages 3528
Modified db pages  291
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1922, not young 16815
0.41 youngs/s, 1.47 non-youngs/s
Pages read 9552, created 28, written 256
0.55 reads/s, 0.00 creates/s, 0.55 writes/s
Buffer pool hit rate 988 / 1000, young-making rate 9 / 1000 not 32 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9580, unzip_LRU len: 0
I/O sum[2130]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   40956
Free buffers       30743
Database pages     10130
Old database pages 3719
Modified db pages  405
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1946, not young 17044
0.32 youngs/s, 2.71 non-youngs/s
Pages read 10125, created 5, written 251
0.32 reads/s, 0.00 creates/s, 0.60 writes/s
Buffer pool hit rate 996 / 1000, young-making rate 4 / 1000 not 39 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 10130, unzip_LRU len: 0
I/O sum[2130]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   40957
Free buffers       31549
Database pages     9327
Old database pages 3424
Modified db pages  275
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1800, not young 17585
0.50 youngs/s, 2.11 non-youngs/s
Pages read 9323, created 4, written 245
0.60 reads/s, 0.00 creates/s, 0.55 writes/s
Buffer pool hit rate 982 / 1000, young-making rate 15 / 1000 not 66 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9327, unzip_LRU len: 0
I/O sum[2130]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   40957
Free buffers       31697
Database pages     9178
Old database pages 3376
Modified db pages  445
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1616, not young 15661
0.23 youngs/s, 13.71 non-youngs/s
Pages read 9148, created 30, written 252
0.60 reads/s, 0.00 creates/s, 0.55 writes/s
Buffer pool hit rate 987 / 1000, young-making rate 5 / 1000 not 299 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9178, unzip_LRU len: 0
I/O sum[2130]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   40956
Free buffers       31478
Database pages     9395
Old database pages 3459
Modified db pages  378
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1706, not young 18178
0.14 youngs/s, 1.01 non-youngs/s
Pages read 9387, created 8, written 248
0.23 reads/s, 0.00 creates/s, 0.50 writes/s
Buffer pool hit rate 998 / 1000, young-making rate 1 / 1000 not 9 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9395, unzip_LRU len: 0
I/O sum[2130]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   40955
Free buffers       30712
Database pages     10162
Old database pages 3736
Modified db pages  341
Pending reads      0
Pending writes: LRU 0, flush list 1, single page 0
Pages made young 1860, not young 26803
0.41 youngs/s, 5.00 non-youngs/s
Pages read 10140, created 22, written 243
0.55 reads/s, 0.00 creates/s, 0.50 writes/s
Buffer pool hit rate 987 / 1000, young-making rate 9 / 1000 not 120 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 10162, unzip_LRU len: 0
I/O sum[2130]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   40957
Free buffers       30978
Database pages     9897
Old database pages 3640
Modified db pages  277
Pending reads      0
Pending writes: LRU 0, flush list 1, single page 0
Pages made young 2046, not young 20017
0.46 youngs/s, 0.87 non-youngs/s
Pages read 9822, created 75, written 271
0.83 reads/s, 0.00 creates/s, 0.55 writes/s
Buffer pool hit rate 993 / 1000, young-making rate 4 / 1000 not 7 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9897, unzip_LRU len: 0
I/O sum[2130]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   40956
Free buffers       31510
Database pages     9365
Old database pages 3445
Modified db pages  431
Pending reads      0
Pending writes: LRU 0, flush list 1, single page 0
Pages made young 1861, not young 11761
0.46 youngs/s, 0.00 non-youngs/s
Pages read 9259, created 106, written 271
0.00 reads/s, 0.00 creates/s, 0.55 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 17 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9365, unzip_LRU len: 0
I/O sum[2130]:cur[3], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=172805, Main thread ID=139649209050688 , state=sleeping
Number of rows inserted 62, updated 6996, deleted 0, read 3120415
0.00 inserts/s, 1.48 updates/s, 0.00 deletes/s, 79.42 reads/s
Number of system rows inserted 8, updated 4427, deleted 8, read 14669
0.00 inserts/s, 1.38 updates/s, 0.00 deletes/s, 2.90 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

小韦's blog

Mysql优化之innodb_buffer_pool_size篇

查看当前的innodb_buffer_pool_size

判断当前的innodb_buffer_pool_size大小是否合适？

查看缓冲池的性能

调整InnoDB缓冲池大小

查看InnoDB状态

本作品采用知识共享署名-相同方式共享 4.0 国际许可协议进行许可

发表评论取消回复

查看当前的innodb_buffer_pool_size

判断当前的innodb_buffer_pool_size大小是否合适？

查看缓冲池的性能

调整InnoDB缓冲池大小

查看InnoDB状态

本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可

发表评论 取消回复

本作品采用知识共享署名-相同方式共享 4.0 国际许可协议进行许可

发表评论取消回复