mysql innodb_buffer_pool_size 修改

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 * 100innodb_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_dataInnoDB缓冲池中包含数据的页数。 该数字包括脏页面和干净页面。 使用压缩表时，报告的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_miscInnoDB缓冲池中的页面数量很多，因为它们已被分配用于管理开销，例如行锁或自适应哈希索引。此值也可以计算为Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data。Innodb_buffer_pool_pages_totalInnoDB缓冲池的总大小，以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表示对缓冲池执行的写入次数。

来源地址：https://blog.csdn.net/weixin_44089900/article/details/129923614