innodb_buffer_pool_size默认大小为128M。当缓冲池大小大于1G时,将innodb_buffer_pool_instances设置大于1的值可以提高服务器的可扩展性。
大的缓冲池可以减小多次磁盘I/O访问相同的表数据。在专用数据库服务器上,可以将缓冲池大小设置为服务器物理内存的80%。
可以使用以下公式计算InnoDB缓冲池性能:
Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100
innodb_buffer_pool_reads:表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。
innodb_buffer_pool_read_requests:表示从内存中读取逻辑的请求数。
mysql> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| 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_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 25769803776 |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)
--在线调整InnoDB缓冲池大小
mysql> SET GLOBAL innodb_buffer_pool_size = 1073741824;
Query OK, 0 rows affected (0.06 sec)
--监控在线缓冲池调整进度
mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+-----------------------------------------------+
| Variable_name | Value |
+----------------------------------+-----------------------------------------------+
| Innodb_buffer_pool_resize_status | buffer pool 2 : resizing with chunks 24 to 1. |
+----------------------------------+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------------------------+
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 230403 10:01:04. |
+----------------------------------+----------------------------------------------------+
1 row in set (0.14 sec)
InnoDB缓冲池状态变量
mysql> show global status like '%innodb_buffer_pool_pages%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Innodb_buffer_pool_pages_data | 56967 |
| Innodb_buffer_pool_pages_dirty | 16 |
| Innodb_buffer_pool_pages_flushed | 234619722 |
| Innodb_buffer_pool_pages_free | 8192 |
| Innodb_buffer_pool_pages_misc | 369 |
| Innodb_buffer_pool_pages_total | 65528 |
+----------------------------------+-----------+
6 rows in set (0.01 sec)
Innodb_buffer_pool_pages_data
InnoDB缓冲池中包含数据的页数。 该数字包括脏页面和干净页面。 使用压缩表时,报告的Innodb_buffer_pool_pages_data值可能大于Innodb_buffer_pool_pages_total。
Innodb_buffer_pool_pages_dirty
显示在内存中修改但尚未写入数据文件的InnoDB缓冲池数据页的数量(脏页刷新)。
Innodb_buffer_pool_pages_flushed
表示从InnoDB缓冲池中刷新脏页的请求数。
Innodb_buffer_pool_pages_free
显示InnoDB缓冲池中的空闲页面
Innodb_buffer_pool_pages_misc
InnoDB缓冲池中的页面数量很多,因为它们已被分配用于管理开销,例如行锁或自适应哈希索引。此值也可以计算为Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data。
Innodb_buffer_pool_pages_total
InnoDB缓冲池的总大小,以page为单位。
innodb_buffer_pool_reads
表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。
innodb_buffer_pool_read_requests
它表示从内存中逻辑读取的请求数。
innodb_buffer_pool_wait_free
通常,对InnoDB缓冲池的写入发生在后台。 当InnoDB需要读取或创建页面并且没有可用的干净页面时,InnoDB首先刷新一些脏页并等待该操作完成。 此计数器计算这些等待的实例。 如果已正确设置innodb_buffer_pool_size,则此值应该很小。如果大于0,则表示InnoDb缓冲池太小。
innodb_buffer_pool_write_request
表示对缓冲池执行的写入次数。