Mysql优化之innodb_buffer_pool_size

追风書翁
1,722 阅读3分钟

1-背景：开发反馈线上数据库性能拉胯，SQL查询竟然把CPU干满

2-措施：新增mysql监控，上一片文章已经写明。然后对mysql库，参数进行检查。

ad40e342b41d40b7fd31ad3cbcd1b28.jpg 经检查数据库，缓存区压根没设置，还是初始的128M大小。 当缓冲区过小的时候，逻辑读就会从磁盘读写，严重影响性能

3-MySQL InnoDB buffer pool 里包含什么？

  • 数据缓存
    InnoDB数据页面
  • 索引缓存
    索引数据
  • 缓冲数据
    脏页（在内存中修改尚未刷新(写入)到磁盘的数据）
  • 内部结构
    如自适应哈希索引，行锁等。

4-怎么验证innodb_buffer_pool_size大小是否合适

当前配置的innodb_buffer_pool_size是否合适，可以通过分析InnoDB缓冲池的性能来验证。

可以使用以下公式计算InnoDB缓冲池性能：

Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100

innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。

innodb_buffer_pool_read_requests：表示从内存中读取逻辑的请求数。

InnoDB buffer pool 命中率：

InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100 此值低于99%，则可以考虑增加innodb_buffer_pool_size。

例如1，在我的两台服务器上，检查当前InnoDB缓冲池的性能 第一台基本只有，写，没有读。读基本在缓存中获取

InnoDB buffer pool=100%

查询命令：show status like 'innodb_buffer_pool_read%';

9dbda532f85bf0b11fde9878d005188.png

例如2，这台服务器就是之前128M缓存区，修改后的，修改成4G大小，已经满足实际需要。否则值非常小，基本都在磁盘读取，加上线上磁盘是机械硬盘，读写性能本身就差，CPU基本就跑满了。

InnoDB buffer pool=99.78%

image.png

5-如何设置innodb_buffer_pool_size

相关查看几个配置命令

sql> show global variables like 'innodb_buffer_pool_size';

sql> show global status like 'Innodb_buffer_pool_pages_data';

sql> show global status like 'Innodb_page_size';

官方对这个几个参数的解释： Innodb_buffer_pool_pages_data

The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and clean pages.

InnoDB缓冲池中包含数据的页面数。数字包括脏的和干净的页面。

Innodb_buffer_pool_pages_total

The total size of the InnoDB buffer pool, in pages.

InnoDB缓冲池的总大小（以页为单位）。

Innodb_page_size

InnoDB page size (default 16KB). Many values are counted in pages; the page size enables them to be easily converted to bytes

InnoDB页面大小（默认为16KB）。许多值在页面中计数；页面大小使它们能够易于转换为字节

调优参考计算方法：

val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100% val > 95% 则考虑增大 innodb_buffer_pool_size， 建议使用物理内存的75% val < 95% 则考虑减小 innodb_buffer_pool_size， 建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (102410241024)G

常规上，基本设置为物理内存或者宿主机内存的50%-75%即可。

故上面我们的mysql从128MB 调整到4GB

5.1- 在线调整InnoDB缓冲池大小

         SET GLOBAL innodb_buffer_pool_size = 3221225472

5.2- InnoDB缓冲池状态变量有哪些？

  • 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
    表示对缓冲池执行的写入次数。
avatar
文章
阅读
粉丝