MySQL之Innodb_buffer_pool_size怎么设置

    Innodb_buffer_pool_size设置方式

    InnoDB表、索引以及其他辅助缓冲区的缓存数据被存储在缓冲池中的内存区域。缓冲池的大小对于系统性能很重要。更大的缓冲池可以减少磁盘I/O来多次访问同一表数据。缓冲池大小在专用数据库服务器上设置为计算机物理内存大小的80%是可行的

    缓冲池相关参数说明

    1）系统变量参数

    • Innodb_page_size

    • 如何设置MySQL的Innodb_buffer_pool_size从性能到原理，全方位解析！

      InnoDB页面大小（默认为16KB）。页面中包含许多值，页面大小使它们可以轻松转换为字节。

    • Innodb_buffer_pool_chunk_size

    • innodb_buffer_pool_chunk_size 定义InnoDB缓冲池大小调整操作的块大小。默认128M。最大值可设置innodb_buffer_pool_size / innodb_buffer_pool_instances

    • innodb_buffer_pool_instances

    • InnoDB 缓冲池划分为的区域数。

    • Innodb_buffer_pool_pages_data

    • 数页在 InnoDB 缓冲池中包含的数据。该数字包括 脏页和干净页。使用压缩表时，报告的 Innodb_buffer_pool_pages_data 值可能大于 Innodb_buffer_pool_pages_total （错误＃59550）。

    • Innodb_buffer_pool_pages_total

    • InnoDB 缓冲池 的总大小（以页为单位）。使用压缩表时，报告的 Innodb_buffer_pool_pages_data 值可能大于 Innodb_buffer_pool_pages_total （错误＃59550）

    2）运行状态变量

    • Innodb_buffer_pool_pages_flushed

    • 从缓冲池刷新页面的请求数 。

    • Innodb_buffer_pool_read_requests

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

    • Innodb_buffer_pool_reads

    • InnoDB 不能从缓冲池满足的逻辑读取的数量，必须直接从磁盘读取。

    • Innodb_buffer_pool_wait_free

    • 通常，对InnoDB缓冲池的写入是在后台进行的。当InnoDB需要读取或创建一个页面而没有可用的干净页面时，InnoDB会首先刷新一些脏页面并等待该操作完成。此计数器统计这些等待的实例。如果设置正确，innodb_buffer_pool_size应该是一个较小的值。

    合理的设置缓存池相关参数

    1、innodb_buffer_pool_size 默认设置系统内存百分之80%,后按如下规则配合实际情况调整

    mysql>
    show global status like '
    Innodb_buffer_pool_pages_data'
    ;

    +-------------------------------+---------+
    | Variable_name | Value |
    +-------------------------------+---------+
    | Innodb_buffer_pool_pages_data | 1894874 |
    +-------------------------------+---------+
    1 row in set (0.00 sec)

    mysql>
    show global status like '
    Innodb_buffer_pool_pages_total'
    ;

    +--------------------------------+---------+
    | Variable_name | Value |
    +--------------------------------+---------+
    | Innodb_buffer_pool_pages_total | 1965960 |
    +--------------------------------+---------+
    1 row in set (0.00 sec)

    # 计算是否应该添加内存
    使用率 = Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
    当结果 >
    95% 则增加 innodb_buffer_pool_size
    当结果 <
    95% 则减少 innodb_buffer_pool_size， 可适当较少，当然独享业务机器多了也没啥问题

    2、innodb_buffer_pool_instances 默认设置为8,最小1，最大64

    对于缓冲池在数千兆字节范围内的系统，通过减少争用不同线程读写缓存页面的争用，将缓冲池划分为多个单独的实例可以提高并发性。此功能通常用于缓冲池大小在数GB范围内的系统。使用innodb_buffer_pool_instances 配置选项配置了多个缓冲池实例 ，您也可以调整该innodb_buffer_pool_size值。

    许多数据请求可以通过从内存中检索来满足，特别是在InnoDB缓冲池很大的情况下。您可能会遇到多个线程试图立即访问缓冲池的瓶颈。您可以启用多个缓冲池以最小化此争用。使用哈希函数将每个页面随机映射到缓冲池中的一个位置，以存储或读取缓冲池中的页面。每个缓冲池管理自己的空闲列表，刷新列表，LRU和连接到缓冲池的所有其他数据结构，并受其自己的缓冲池互斥量保护。

    要启用多个缓冲池实例，请将innodb_buffer_pool_instances配置选项设置为 大于1（默认）的值，最大为64（最大）。此选项仅在将innodb_buffer_pool_size设置为1GB或更大时才会生效。您指定的总大小将分配给所有缓冲池。为了获得最佳效率，指定的组合 innodb_buffer_pool_instances 和innodb_buffer_pool_size，使得每个缓冲池实例是至少为1GB。

    总结：

    # cat /etc/my.cnf
    [mysqld]
    ......
    innodb_buffer_pool_size = 系统内存%80
    innodb_buffer_pool_instances = 大于8的情况下，每个缓冲池实例至少1GB
    ......

    Reference：

    • https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

    • https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.htm

    • https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

    设置innodb_buffer_pool_size参数

    用于缓存索引和数据的内存大小，这个当然是越多越好， 数据读写在内存中非常快， 减少了对磁盘的读写。

    当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中。

    然而内存还有操作系统或数据库其他进程使用， 根据经验，推荐设置innodb-buffer-pool-size为服务器总可用内存的75%。 若设置不当， 内存使用可能浪费或者使用过多。

    对于繁忙的服务器， buffer pool 将划分为多个实例以提高系统并发性， 减少线程间读写缓存的争用。buffer pool 的大小首先受 innodb_buffer_pool_instances 影响， 当然影响较小。

    1.Innodb_buffer_pool_pages_data： Innodb buffer pool缓存池中包含数据的页的数目，包括脏页。单位是page。

    show global status like '
    Innodb_buffer_pool_pages_data'
    ;

    2.Innodb_buffer_pool_pages_total： innodb buffer pool的页总数目。单位是page。

    show global status like '
    Innodb_buffer_pool_pages_total'
    ;

    3.show global status like '
    Innodb_page_size'
    ;
    查看@@innodb_buffer_pool_size大小，单位字节

    SELECT @@innodb_buffer_pool_size/1024/1024/1024;
    #字节转为G

    4.在线调整InnoDB缓冲池大小，如果不设置，默认为128M

    set global innodb_buffer_pool_size = 4227858432;
    ##单位字节

    计算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%

    • 当结果 >
      95% 则增加 innodb_buffer_pool_size， 建议使用物理内存的 75%

    • 当结果 <
      95% 则减少 innodb_buffer_pool_size， 建议设置大小为： Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)



    了解Innodb_buffer_pool_size前需要了解MySQL内存模型
    MySQL内存模型的架构设计
    Innodb_buffer_pool_size是什么？
    Innodb_buffer_pool_size的最佳实践：如何优化MySQL性能
    MySQL作为当今最流行的关系型数据库之一，对于许多企业和开发者至关重要。如何优化MySQL的性能是一个备受关注的话题。其中，Innodb_buffer_pool_size的设置对MySQL的性能影响巨大，是MySQL性能优化的一个重要部分。下文将从MySQL内存模型、Innodb_buffer_pool_size是什么以及最佳实践等方面阐述如何设置Innodb_buffer_pool_size达到最优化的MySQL性能。
    了解Innodb_buffer_pool_size前需要了解MySQL内存模型
    MySQL的内存模型由三个主要的内存池组成：查询缓存、InnoDB缓冲池和MyISAM缓冲池。InnoDB缓冲池是MySQL内存模型的重要部分之一，决定了MySQL性能的核心因素之一。
    MySQL内存模型的架构设计
    Innodb_buffer_pool_size是什么？
    InnoDB Buffer Pool是一个内存池，用于缓存InnoDB存储引擎表的数据和索引，以减少I/O操作和提高性能。它是MySQL内存模型的主要组成部分，可以使查询速度更快，提高响应时间和并发度。Innodb_buffer_pool_size即为InnoDB Buffer Pool的大小。
    Innodb_buffer_pool_size的最佳实践：如何优化MySQL性能
    InnoDB Buffer Pool的大小对MySQL的性能影响很大，需要进行合理的设置。如果设置太小，会导致缓存中放不下太多的数据和索引，从而增加I/O操作，降低性能。反之，如果设置太大，会导致内存浪费，增加GC负载，也会影响性能。
    对于如何设置Innodb_buffer_pool_size，可以遵循以下最佳实践：
    1.根据应用程序的性质和内存容量来设置Innodb_buffer_pool_size的大小。
    2.将Innodb_buffer_pool_size设置为物理内存的70%~80%。
    3.在设置Innodb_buffer_pool_size时请确保物理内存足够，以避免过度使用交换空间。
    4.在应用程序中使用多个InnoDB表时，需要考虑表的大小和热点数据，并相应地增加Innodb_buffer_pool_size的大小。
    综上所述，合理设置Innodb_buffer_pool_size是MySQL性能优化的重要部分，需要根据应用程序的特性和内存容量来设置。为了达到最佳的MySQL性能表现，我们需要根据最佳实践来设置Innodb_buffer_pool_size的大小。