dns是什么-mysql优化系统--innodb中心参数装备

概述

今日首要共享几个关于innodb的参数,以下考虑首要为 Innodb 引擎, key_buffer_size 不考虑。关于实例等级或线程等级参数设置,暂不考虑。


innodb_buffer_pool_size

用于缓存 索引 和 数据的内存巨细, 这个当然是越多越好, 数据读写在内存中非常快, 削减了对磁盘的读写。 当数据提交或满意检查点条件后才一次性将内存数据刷新到磁盘中。但是内存还有操作体系或数据库其他进程运用, 一般设置 buffer pool 巨细为总内存的 3/4 至 4/5。 若设置不妥, 内存运用或许糟蹋或许运用过多。

关于繁忙的服务器, buffer pool 将划分为多个实例以进步体系并发性, 削减线程间读写缓存的争用。buffer pool 的巨细首要受 innodb_buffer_pool_instances 影响, 当然影响较小。


innodb_buffer_pool_instances

buffer pool 被划分为多个缓存实例的数量, 为固定值,不动态改变。当较多数据加载到内存时, 运用多缓存实例能削减缓存争用状况。

当 innodb_buffer_pool_size 大于 1GB 时, innodb_buffer_pool_instances 默以为 8。如有更多buffer pool, 均匀每个instances 至少1GB。


innodb_buffer_pool_chunk_size

When increasing or decreasing innodb_buffer_pool_size, the operation is performed in chunks. Chunk size is defined by theinnodb_buffer_pool_chunk_size configuration option, which has a default of 128M.

innodb_buffer_pool_chunk_size 默许 128MB (更改不需重启),添加单位为 1MB 。

innodb_buffer_pool_chunk_size 的最大值预算如下:

MAX(innodb_buffer_pool_chunk_size) = innodb_buffer_pool_size / innodb_buffer_pool_instances

innodb_buffer_pool_size is set to 8G, and innodb_buffer_pool_instances is set to 16.innodb_buffer_pool_chunk_size is 128M, which is the default value.

8G is a valid innodb_buffer_pool_size value because 8G is a multiple of innodb_buffer_pool_instances=16 *innodb_buffer_pool_chunk_size=128M, which is 2G.

shell> mysqld --innodb-b闹洞房uffer-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 is set to 9G, and innodb_buffer_pool_instances is set to 16.innodb_buffer_pool_chunkdns是什么-mysql优化系统--innodb中心参数装备_size is 128M, which is the default value. In this case, 9G is not a multiple ofinnodb_buffdns是什么-mysql优化系统--innodb中心参数装备er_pool_instances=16 * innodb_buffer_pool_chunk_size=128M, so innodb_buffer_pool_size is adjusted to 10G, which is a multiple of 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 |
+------------------------------------------+

归纳以上三个参数:

buffer pool 预算公式:(N 为正整数; buffer pool 应为总内存的 3/4 至 4/5)

innodb_buffer_pool_size = N * (innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances)

当 N=1时, 使三个参数设置刚好满意以下公式:

innodb_buffer_pool_size = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

若此刻再设置 innodb_buffer_pool_chunk_size 少 1MB , 那么 buffer pool 巨dns是什么-mysql优化系统--innodb中心参数装备细简直翻倍, 由于 chunk_size * instances < buffer_pool_size, 此刻有 N=2, buffer_pool_size 为(chunk_size*instances) 的倍数巨细

innodb_buffer_pool_size = 2 * (innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances)

#所以较优设置:假定体系内存 = 128 GB, buffer pool 巨细估计100GB(128GB*80%)

innodb_buffer_pool_instances = 8 #默许值,或许逻辑CPU数量

innodb_buffer_pool_chunk_size = 128MB #默许值

innodb_buffer_pool_size = 100 GB # N*8*128MG = N GB ,N 刚好为正整数。设 N=100使得 buffer pool 为总内存的 3/4 至 4/5。


innodb_page_size

innodb_page_size 默许 16kb, 数据存储页, 应与操作体系块巨细共同(同 innodb_log_write_ahead_size)。 关于 SSD 更dns是什么-mysql优化系统--innodb中心参数装备小的页或许更好。innodb_page_size 为32k and 64k 时, 行长度最大为 16000 bytes, 且不支撑 ROW_FORMAT=COMPRESSED。

一个 innodb_buffer_pool_chunk_size 中包括的页数量取决于 innodb_page_size。

默许地: chunk可存储的页数量= innodb_buffer_pool_chunk_size / innodb_page_size = 128dns是什么-mysql优化系统--innodb中心参数装备*1024/16 = 8192

检查:

mysql> SELECT @@innodb_buffer_pool_size;
mysql> SELECT @@innodb_buffer_pool_instances;
mysql> SELECT @@innodb_buffer_pool_chunk_size;


后边会共享更多devops和DBA方面的内容,感兴趣的朋友能够重视下!