MySQL innodb_buffer_pool_size 优化

本文主要是介绍MySQL innodb_buffer_pool_size 优化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

InnoDB需要innodb buffer pool中处理缓存。所以非常需要有足够的InnoDB buffer pool空间。InnoDB buffer pool 里包含：

数据缓存：InnoDB数据页面

索引缓存：索引数据

缓冲数据：脏页（在内存中修改尚未刷新(写入)到磁盘的数据）

内部结构：如自适应哈希索引，行锁等。

innodb_buffer_pool_size 默认大小为128M。最大值取决于CPU的架构。在32-bit平台上，最大值为2**32 -1,在64-bit平台上最大值为2**64-1。

当缓冲池大小大于1G时，将innodb_buffer_pool_instances设置大于1的值可以提高服务器的可扩展性。

大的缓冲池可以减小多次磁盘I/O访问相同的表数据。在只有MySQL的服务器上，可以将缓冲池大小设置为服务器物理内存的80%。但如果还有其他系统在跑，需要考虑内存占用的合适比例。

InnoDB为缓冲区和control structures保留了额外的内存，因此总分配空间比指定的缓冲池大小大约大10％。

缓冲池的地址空间必须是连续的，这在带有在特定地址加载的DLL的Windows系统上可能是一个问题。

配置缓冲池大小时，请注意以下潜在问题

• 物理内存争用可能导致操作系统频繁的paging

• InnoDB为缓冲区和control structures保留了额外的内存，因此总分配空间比指定的缓冲池大小大约大10％。

• 缓冲池的地址空间必须是连续的，这在带有在特定地址加载的DLL的Windows系统上可能是一个问题。

• 初始化缓冲池的时间大致与其大小成比例。在具有大缓冲池的实例上，初始化时间可能很长。要减少初始化时间，可以在服务器关闭时保存缓冲池状态，并在服务器启动时将其还原。

  • innodb_buffer_pool_dump_pct：指定每个缓冲池最近使用的页面读取和转储的百分比。 范围是1到100。默认值是25。例如，如果有4个缓冲池，每个缓冲池有100个page，并且innodb_buffer_pool_dump_pct设置为25，则dump每个缓冲池中最近使用的25个page。
  • innodb_buffer_pool_dump_at_shutdown：默认启用。指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面，以便在下次重新启动时缩短预热过程。
  • innodb_buffer_pool_load_at_startup：默认启用。指定在MySQL服务器启动时，InnoDB缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用。

增大或减小缓冲池大小时，将以chunk的形式执行操作。chunk大小由innodb_buffer_pool_chunk_size配置选项定义，默认值为128 MB。

缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。

如果将缓冲池大小更改为不等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值，则缓冲池大小将自动调整为等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值。

比如分配2G的innodb buffer pool

vi /etc/my.cnf

[mysqld]

innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 16

执行命令查看池情况

show variables like 'innodb_buffer_pool%';

----------------------------------------------------------------------------

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_in_core_file    ON
innodb_buffer_pool_instances    16 = 2147483648 / 134217728
innodb_buffer_pool_load_abort    OFF
innodb_buffer_pool_load_at_startup    ON
innodb_buffer_pool_load_now    OFF
innodb_buffer_pool_size    2147483648 = 1024*1024*1024*2 = 134217728 * 16

当前配置的innodb_buffer_pool_size是否合适，可以通过分析InnoDB缓冲池的性能来验证。可以使用以下公式计算InnoDB缓冲池性能：

show status like 'innodb_buffer_pool_read%';

--------------------------------------------------------------------------------

Innodb_buffer_pool_read_ahead_rnd    0
Innodb_buffer_pool_read_ahead    3989
Innodb_buffer_pool_read_ahead_evicted    0
Innodb_buffer_pool_read_requests    49967223
Innodb_buffer_pool_reads    9233

性能值% = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100

命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100

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

如果性能值%很小，命中率很高，说明需要从磁盘读取的数据少，性能比较高

在只有MySQL的服务器上，多余的innodb_buffer内存不会有问题，但是和其他系统共享服务器时，需要考虑合适的分配比例。可以使用show engine innodb status 命令检查内存状态：

show engine innodb status
-----------------------------------------
...
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
...
Buffer pool size   8191
Free buffers       7320
Database pages     838
Old database pages 329

Free buffers :表示有多少空闲buffer。如果 此值长时间都较高，则可以考虑减小InnoDB缓冲池大小。

这篇关于MySQL innodb_buffer_pool_size 优化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！