• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 配置InnoDB缓冲池大小

    InnoDB在服务器运行时脱机(启动时)或联机配置缓冲池大小。本节中描述的行为适用于两种方法。有关在线配置缓冲池大小的更多信息,请参见在线配置InnoDB缓冲池大小。

    当增加或减少时innodb_buffer_pool_size,将按块执行操作。块大小由innodb_buffer_pool_chunk_size配置选项定义,其默认值为128M。有关更多信息,请参见配置InnoDB缓冲池块大小。

    缓冲池大小必须始终等于innodb_buffer_pool_chunk_size*或*的倍数innodb_buffer_pool_instances。如果配置innodb_buffer_pool_size为不等于innodb_buffer_pool_chunk_size*或* innodb_buffer_pool_instances的值,则缓冲池大小会自动调整为等于innodb_buffer_pool_chunk_size*或*的值innodb_buffer_pool_instances

    在以下示例中,innodb_buffer_pool_size设置为8G,并且innodb_buffer_pool_instances设置为16innodb_buffer_pool_chunk_size128M,这是默认值。

    8G是有效值,innodb_buffer_pool_size因为它是* 8G的倍数,即innodb_buffer_pool_instances=16*。innodb_buffer_pool_chunk_size=128M2G

    shell> mysqld --innodb-buffer-pool-size=8G --innodb-buffer-pool-instances=16
    
    mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
    +------------------------------------------	+
    | @@innodb_buffer_pool_size/1024/1024/1024 	|
    +------------------------------------------	+
    |                           8.000000000000 	|
    +------------------------------------------	+
    

    在此示例中,innodb_buffer_pool_size设置为9G,并且innodb_buffer_pool_instances设置为16innodb_buffer_pool_chunk_size128M,这是默认值。在这种情况下,9G不是innodb_buffer_pool_instances=16* 的倍数innodb_buffer_pool_chunk_size=128M,因此innodb_buffer_pool_size将调整为*10G,它是innodb_buffer_pool_chunk_size*的倍数innodb_buffer_pool_instances

    shell>mysqld --innodb-buffer-pool-size=9G --innodb-buffer-pool-instances=16
    
    mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
    +------------------------------------------	+
    | @@innodb_buffer_pool_size/1024/1024/1024 	|
    +------------------------------------------	+
    |                          10.000000000000 	|
    +------------------------------------------	+
    

    配置InnoDB缓冲池块大小

    innodb_buffer_pool_chunk_size可以以1MB(1048576字节)为单位增加或减少,但只能在启动时,命令行字符串或MySQL配置文件中进行修改。

    命令行:

    shell>mysqld --innodb-buffer-pool-chunk-size=134217728
    

    配置文件:

    [mysqld]
    innodb_buffer_pool_chunk_size=134217728
    

    更改时适用以下条件innodb_buffer_pool_chunk_size

    • 如果在初始化缓冲池时新 innodb_buffer_pool_chunk_size值*innodb_buffer_pool_instances大于当前缓冲池的大小,则将innodb_buffer_pool_chunk_size其截断为innodb_buffer_pool_size/innodb_buffer_pool_instances

      例如,如果使用大小为2GB(2147483648字节),4缓冲池实例和块大小为1GB(1073741824字节)初始化缓冲池,则块大小将被截断为等于innodb_buffer_pool_size/的值innodb_buffer_pool_instances,如下所示:

      shell>mysqld --innodb-buffer-pool-size=2147483648 --innodb-buffer-pool-instances=4
      --innodb-buffer-pool-chunk-size=1073741824;
      
      mysql> SELECT @@innodb_buffer_pool_size;
      +---------------------------	+
      | @@innodb_buffer_pool_size 	|
      +---------------------------	+
      |                2147483648 	|
      +---------------------------	+
      
      mysql> SELECT @@innodb_buffer_pool_instances;
      +--------------------------------	+
      | @@innodb_buffer_pool_instances 	|
      +--------------------------------	+
      |                              4 	|
      +--------------------------------	+
      
      # Chunk size was set to 1GB (1073741824 bytes) on startup but was
      # truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances
      
      mysql> SELECT @@innodb_buffer_pool_chunk_size;
      +---------------------------------	+
      | @@innodb_buffer_pool_chunk_size 	|
      +---------------------------------	+
      |                       536870912 	|
      +---------------------------------	+
      
    • 缓冲池大小必须始终等于innodb_buffer_pool_chunk_size* 或*的倍数innodb_buffer_pool_instances。如果您更改innodb_buffer_pool_chunk_sizeinnodb_buffer_pool_size则会自动调整为等于innodb_buffer_pool_chunk_size* 或*的倍数的值innodb_buffer_pool_instances。在初始化缓冲池时进行调整。下面的示例演示了此行为:

      # The buffer pool has a default size of 128MB (134217728 bytes)
      
      mysql> SELECT @@innodb_buffer_pool_size;
      +---------------------------	+
      | @@innodb_buffer_pool_size 	|
      +---------------------------	+
      |                 134217728 	|
      +---------------------------	+
      
      # The chunk size is also 128MB (134217728 bytes)
      
      mysql> SELECT @@innodb_buffer_pool_chunk_size;
      +---------------------------------	+
      | @@innodb_buffer_pool_chunk_size 	|
      +---------------------------------	+
      |                       134217728 	|
      +---------------------------------	+
      
      # There is a single buffer pool instance
      
      mysql> SELECT @@innodb_buffer_pool_instances;
      +--------------------------------	+
      | @@innodb_buffer_pool_instances 	|
      +--------------------------------	+
      |                              1 	|
      +--------------------------------	+
      
      # Chunk size is decreased by 1MB (1048576 bytes) at startup
      # (134217728 - 1048576 = 133169152):
      
      shell> mysqld --innodb-buffer-pool-chunk-size=133169152
      
      mysql> SELECT @@innodb_buffer_pool_chunk_size;
      +---------------------------------	+
      | @@innodb_buffer_pool_chunk_size 	|
      +---------------------------------	+
      |                       133169152 	|
      +---------------------------------	+
      
      # Buffer pool size increases from 134217728 to 266338304
      # Buffer pool size is automatically adjusted to a value that is equal to
      # or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
      
      mysql> SELECT @@innodb_buffer_pool_size;
      +---------------------------	+
      | @@innodb_buffer_pool_size 	|
      +---------------------------	+
      |                 266338304 	|
      +---------------------------	+
      

      此示例演示了相同的行为,但具有多个缓冲池实例:

      # The buffer pool has a default size of 2GB (2147483648 bytes)
      
      mysql> SELECT @@innodb_buffer_pool_size;
      +---------------------------	+
      | @@innodb_buffer_pool_size 	|
      +---------------------------	+
      |                2147483648 	|
      +---------------------------	+
      
      # The chunk size is .5 GB (536870912 bytes)
      
      mysql> SELECT @@innodb_buffer_pool_chunk_size;
      +---------------------------------	+
      | @@innodb_buffer_pool_chunk_size 	|
      +---------------------------------	+
      |                       536870912 	|
      +---------------------------------	+
      
      # There are 4 buffer pool instances
      
      mysql> SELECT @@innodb_buffer_pool_instances;
      +--------------------------------	+
      | @@innodb_buffer_pool_instances 	|
      +--------------------------------	+
      |                              4 	|
      +--------------------------------	+
      
      # Chunk size is decreased by 1MB (1048576 bytes) at startup
      # (536870912 - 1048576 = 535822336):
      
      shell> mysqld --innodb-buffer-pool-chunk-size=535822336
      
      mysql> SELECT @@innodb_buffer_pool_chunk_size;
      +---------------------------------	+
      | @@innodb_buffer_pool_chunk_size 	|
      +---------------------------------	+
      |                       535822336 	|
      +---------------------------------	+
      
      # Buffer pool size increases from 2147483648 to 4286578688
      # Buffer pool size is automatically adjusted to a value that is equal to
      # or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
      
      mysql> SELECT @@innodb_buffer_pool_size;
      +---------------------------	+
      | @@innodb_buffer_pool_size 	|
      +---------------------------	+
      |                4286578688 	|
      +---------------------------	+
      

      更改时应格外小心innodb_buffer_pool_chunk_size,因为更改此值可能会增加缓冲池的大小,如上面的示例所示。在进行更改之前innodb_buffer_pool_chunk_size,请计算对的影响,innodb_buffer_pool_size以确保生成的缓冲池大小可以接受。

    注意

    为避免潜在的性能问题,块(innodb_buffer_pool_size/innodb_buffer_pool_chunk_size)的数量不应超过1000。

    在线配置InnoDB缓冲池大小

    innodb_buffer_pool_size配置选项可以动态使用设置SET声明,让您调整缓冲池无需重新启动服务器。例如:

    mysql> SET GLOBAL innodb_buffer_pool_size=402653184;
    

    活动交易和通过执行的操作InnoDB在调整缓冲池大小之前,应先完成API。当启动调整大小操作时,该操作直到所有活动事务完成后才开始。调整大小操作进行后,需要访问缓冲池的新事务和操作必须等待直到调整大小操作完成。该规则的例外是,在对缓冲池进行碎片整理时,允许并发访问缓冲池;在减小缓冲池大小时,将撤回页面。允许并发访问的缺点是,在撤回页面时,这可能会导致可用页面的暂时短缺。

    注意

    如果在缓冲池大小调整操作开始后启动嵌套事务,则该事务可能会失败。

    监视在线缓冲池调整大小进度

    Innodb_buffer_pool_resize_status缓冲池大小调整进度报告。例如:

    mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
    +----------------------------------	+----------------------------------	+
    | Variable_name                    	| Value                            	|
    +----------------------------------	+----------------------------------	+
    | Innodb_buffer_pool_resize_status 	| Resizing also other hash tables. 	|
    +----------------------------------	+----------------------------------	+
    

    缓冲池调整大小的进度也记录在服务器错误日志中。此示例显示增加缓冲池大小时记录的注释:

    [Note] InnoDB: Resizing buffer pool from 134217728 to 4294967296. (unit=134217728)
    [Note] InnoDB: disabled adaptive hash index.
    [Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was added.
    [Note] InnoDB: buffer pool 0 : hash tables were resized.
    [Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
    [Note] InnoDB: completed to resize buffer pool from 134217728 to 4294967296.
    [Note] InnoDB: re-enabled adaptive hash index.
    

    此示例显示减小缓冲池大小时记录的注释:

    [Note] InnoDB: Resizing buffer pool from 4294967296 to 134217728. (unit=134217728)
    [Note] InnoDB: disabled adaptive hash index.
    [Note] InnoDB: buffer pool 0 : start to withdraw the last 253952 blocks.
    [Note] InnoDB: buffer pool 0 : withdrew 253952 blocks from free list. tried to relocate 0 pages.
    (253952/253952)
    [Note] InnoDB: buffer pool 0 : withdrawn target 253952 blocks.
    [Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was freed.
    [Note] InnoDB: buffer pool 0 : hash tables were resized.
    [Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
    [Note] InnoDB: completed to resize buffer pool from 4294967296 to 134217728.
    [Note] InnoDB: re-enabled adaptive hash index.
    

    在线缓冲池调整内部大小

    调整大小操作由后台线程执行。当增加缓冲池的大小时,调整大小操作:

    • 添加页面chunks(块大小由定义innodb_buffer_pool_chunk_size
    • 隐藏哈希表,列表和指针以使用内存中的新地址
    • 将新页面添加到空闲列表

    在进行这些操作时,其他线程将被阻止访问缓冲池。

    当减小缓冲池的大小时,调整大小操作:

    • 对缓冲池进行碎片整理并撤消(释放)页面
    • 删除中的页面chunks(块大小由定义innodb_buffer_pool_chunk_size
    • 转换哈希表,列表和指针以使用内存中的新地址

    在这些操作中,只有对缓冲池进行碎片整理和撤出页面才允许其他线程同时访问缓冲池。