MySQL底层概述—5.InnoDB参数优化

发布于:2024-11-29 ⋅ 阅读:(66) ⋅ 点赞:(0)

大纲

1.内存相关参数优化

(1)缓冲池内存大小配置

(2)配置多个Buffer Pool实例

(3)Chunk(块)大小配置

(4)InnoDB缓存性能评估

(5)Page管理相关参数

(6)Change Buffer相关参数优化

2.日志相关参数优化

(1)日志缓冲区相关参数配置

(2)日志文件参数优化

3.IO线程相关参数优化

(1)查询缓存相关的参数

(2)脏页刷盘相关的参数

(3)LRU链表相关的参数

(4)脏页刷盘相关的参数

1.内存相关参数优化(Buffer Pool参数优化)

(1)缓冲池内存大小配置

(2)配置多个Buffer Pool实例

(3)Chunk(块)大小配置

(4)InnoDB缓存性能评估

(5)Page管理相关参数

(6)Change Buffer相关参数优化

(1)缓冲池内存大小配置

一个大的日志缓冲区允许大量的事务在提交之前不写日志到磁盘,因此如果有很多增删改操作,通过设置该参数可大量减少磁盘IO次数。

专用数据库服务器上,可将缓冲池大小设置为物理内存的60% - 80%。

一.查看缓冲池大小

Buffer Pool的默认大小是128M。

# 134217728 / 1024 / 1024 = 128
mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

二.在线调整InnoDB缓冲池大小

innodb_buffer_pool_size可以动态设置,允许在不重启服务器情况下动态调整缓冲池大小。

mysql> SET GLOBAL innodb_buffer_pool_size = 268435456; -- 256M
Query OK, 0 rows affected (0.10 sec)

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+

三.监控在线调整缓冲池的进度

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------------------------+
| Variable_name                    | Value                                                        |
+----------------------------------+----------------------------------------------------------------------+
| Innodb_buffer_pool_resize_status | Size did not change (old size = new size = 268435456. Nothing to do. |
+----------------------------------+----------------------------------------------------------------------+

(2)配置多个Buffer Pool实例

当Buffer Pool的大小是GB级别时:可以将一个Buffer Pool分割成几个独立的实例,这样能降低多个线程同时读写缓存页的竞争性而提高并发性。

通过innodb_buffer_pool_instances参数可以调整实例个数。如果有多个实例,则缓存的数据页会随机放置到任意的实例中,且每个实例都有独立的Buffer Pool所有的特性。

Buffer Pool可以存放多个Instance,每个Instance由多个Chunk组成。Instance的数量范围和Chunk的总数量范围分别为1-64,1-1000。

innodb_buffer_pool_instances的默认值是1,最大可调整成64,需要在初始化数据库时完成。

mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1     |
+------------------------------+-------+

(3)Chunk(块)大小配置

增大或减小缓冲池大小时,将以Chunk为单位进行操作,Chunk的大小是由参数innodb_buffer_pool_chunk_size决定的。引入Chunk是为了方便在线修改缓冲池大小,修改时以Chunk为单位拷贝Buffer Pool。

mysql> show variables like 'innodb_buffer_pool_chunk_size';
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| innodb_buffer_pool_chunk_size | 134217728 | 
+-------------------------------+-----------+

缓冲池大小innodb_buffer_pool_size:必须始终等于或者是chunk_size * instances的倍数,如果不等于则MySQL会自动调整。

假设innodb_buffer_pool_chunk_size=128M,而且innodb_buffer_pool_instances=16,那么可以计算出:innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances=2G。如果设置innodb_buffer_pool_size=9G,则会被自动调整为2G的倍数10G。

(4)InnoDB缓存性能评估

当前配置的innodb_buffer_pool_size是否合适,可通过分析InnoDB缓冲池的缓存命中率来验证,以下公式可以计算InnoDB Buffer Pool命中率:

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

参数1:innodb_buffer_pool_reads

表示缓冲池无法满足的请求数,要从磁盘读取。

参数2:innodb_buffer_pool_read_requests

表示从缓冲池中读取页的请求数,如果命中率低于90%,则可考虑增加innodb_buffer_pool_size。

mysql> show status like 'innodb_buffer_pool_read%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd     | 0     |
| Innodb_buffer_pool_read_ahead         | 0     |
| Innodb_buffer_pool_read_ahead_evicted | 0     |
| Innodb_buffer_pool_read_requests      | 12701 |
| Innodb_buffer_pool_reads              | 455   |
+---------------------------------------+-------+

-- 此值低于90%,则可以考虑增加innodb_buffer_pool_size;
mysql> select 12701 / (455 + 12701) * 100 ;
+-----------------------------+
| 12701 / (455 + 12701) * 100 |
+-----------------------------+
|                     96.5415 |
+-----------------------------+

(5)Page管理相关参数

innodb_page_size只能在初始化MySQL实例之前配置,不能在之后修改。如果没有指定值,则使用默认页面大小初始化实例。

查看Page页的大小(默认16K)如下:

mysql> show variables like '%innodb_page_size%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

查看Page页管理状态的相关参数如下:

mysql> show global status like '%innodb_buffer_pool_pages%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_pages_data    | 515   |
| Innodb_buffer_pool_pages_dirty   | 0     |
| Innodb_buffer_pool_pages_flushed | 334   |
| Innodb_buffer_pool_pages_free    | 15868 |
| Innodb_buffer_pool_pages_misc    | 0     |
| Innodb_buffer_pool_pages_total   | 16383 |
+----------------------------------+-------+

# pages_data:缓冲池中包含数据的页数,包括脏页和干净页
# pages_dirty:内存中修改但未写入文件的缓冲池数据页数量
# pages_flushed:表示从InnoDB缓冲池中刷新脏页的请求数
# pages_free:显示InnoDB缓冲池中的空闲页面
# pages_misc:用于管理或哈希索引而不能用作普通页的数目
# pages_total:缓存池的页总数目,单位是Page

一.优化建议

innodb_page_size的官方描述:

MySQL5.7增加了对32K和64K页面大小的支持,所以MySQL5.7支持最小4K最大64K的页面大小设置。

默认的16K或更大的页面大小适用于各种工作负载,特别是涉及表扫描的查询和涉及批量更新的DML操作。对于涉及许多小写操作的OLTP工作负载,较小的页面大小可能更有效。

二.Page大小对于行存储的影响

对于4K、8K、16K和32K的页大小,最大行大小略小于页大小的一半。当然,不包括存储在页外的任何可变长度的列。

三.Page大小对于索引的影响

如果在创建MySQL实例时通过指定innodb_page_size选项,将InnoDB页面大小减少到8K或4K,则索引键的最大长度将按比例降低。这是基于16K页面大小的3072字节限制。也就是说,当页面大小为8K时,最大索引键长度为1536字节。当页面大小为4K时,最大索引键长度为768字节。

(6)Change Buffer相关参数优化

Change Buffer是MySQL5.5加入的新特性,Change Buffer是Insert Buffer的加强。Insert Buffer只针对insert有效,Change Buffer对insert、delete、update、purge都有效。

一.配置Change Buffer使用模式

innodb_change_buffering配置参数说明:

mysql> show variables like '%innodb_change_buffering%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_change_buffering | all   |
+-------------------------+-------+

| 选项    | 说明                         |
| ------- | --------------------------- |
| inserts | 插入缓冲                     |
| deletes | 删除标记缓冲                  |
| changes | 更新缓冲,由两个缓冲区组成       |
| purges  | 缓冲在后台发生的物理删除操作     |
| all     | 表示启用上面所有配置(默认)      |
| none    | 表示不启用任何配置             |

二.配置Change Buffer大小

Change Buffer占用Buffer Pool空间,默认占25%,最大允许占50%。可以根据读写业务量来调整innodb_change_buffer_max_size,在写多读少+更新后数据不会被立即查询的场景,更适合用Change Buffer。

mysql> show variables like 'innodb_change_buffer_max_size';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
1 row in set (0.00 sec)

三.查看Change Buffer的工作状态

-- 查看Change Buffer的工作状态
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0


# size:表示已经合并到辅助索引页的数量
# free list len:表示空闲列表长度
# seg size:表示当前Change Buffer的大小
# merges:表示合并的次数
# merged operations:每个操作合并次数
# insert:表示插入操作
# delete mark:表示删除标记操作
# delete:表示物理删除操作

2.日志相关参数优化

(1)日志缓冲区相关参数配置

(2)日志文件参数优化

(1)日志缓冲区相关参数配置

日志缓冲区的大小,一般默认值16M都够用了。但如果事务中含有BLOB/TEXT等大字段,这个缓冲区会被很快填满会引起额外的IO负载。可以根据情况配置更大的日志缓冲区,从而有效提高InnoDB的效率。

一.通过参数innodb_log_buffer_size查看日志缓冲区大小

mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+

二.通过参数innodb_log_files_in_group查看日志组文件个数

日志组根据需要来创建,日志组的成员则需要至少2个,以实现循环写入并作为冗余策略。

mysql> show variables like 'innodb_log_files_in_group';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+

三.通过参数innodb_log_file_size查看redo日志文件大小

参数innodb_log_file_size用于设定InnoDB日志组中每个日志文件的大小。参数innodb_log_file_size默认48M,参数innodb_log_file_size是一个全局的静态参数,不能动态修改。每组的文件大小不能超过512G,所以每个日志文件的大小不能超过256G。

mysql> show variables like 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+

(2)日志文件参数优化

首先看日志文件大小设置对性能的影响。

一.设置过小

影响一:参数innodb_log_file_size设置太小,会导致redo log日志文件频繁切换。频繁触发数据库的检查点checkpoint,导致刷新脏页到磁盘的次数增加,从而影响性能。

影响二:处理大事务时,将所有的日志文件写满了,事务内容还没有写完,这样就会导致日志不能切换。

二.设置过大

参数innodb_log_file_size如果设置太大,虽然可以提升IO性能。但是当数据库意外宕机时,二进制日志文件可能会很大,那么恢复的时间就必然很长,而且恢复时间不可控,受多方面因素影响。

三.优化建议

如何设置合适的日志文件大小,根据实际生产的优化经验,一般是计算一段时间内生成的redo log大小。InnoDB的日志文件的大小最少应该承载一个小时的业务日志量,需要估算出当前系统的一小时内产生的日志数量。

步骤1:获取一分钟内的redo log日志数据量

想要估计redo log的大小,就需要抓取一段时间内Log Sequence Number的数据来计算。自系统修改开始,就不断修改页面,不断生成redo日志。为了记录一共生成了多少日志,InnoDB设计了全局变量Log Sequence Number,简称LSN。LSN不是从0开始的,而是从8704字节开始。

-- pager分页工具, 只获取sequence的信息
mysql> pager grep sequence;
PAGER set to 'grep sequence'

-- 查询状态,并倒计时一分钟
mysql> show engine innodb status\G select sleep(60);
Log sequence number 5399154
1 row in set (0.00 sec)

1 row in set (1 min 0.00 sec)

-- 一分时间内所生成的数据量 5406150
mysql> show engine innodb status\G select sleep(60);
Log sequence number 5406150

-- 关闭pager
mysql> nopager;
PAGER set to stdout

步骤2:根据一分钟的redo log日志数据量,推算一小时内的日志数据量

select (5406150 - 5399154) / 1024 as kb_per_min;
+------------+
| kb_per_min |
+------------+
|     6.8320 |
+------------+

select (5406150 - 5399154) / 1024 * 60 as kb_per_min;
+------------+
| kb_per_min |
+------------+
|   409.9219 |
+------------+

3.IO线程相关参数优化

(1)查询缓存相关的参数

(2)脏页刷盘相关的参数

(3)LRU链表相关的参数

(4)脏页刷盘相关的参数

数据库属于IO密集型的应用程序,其主要职责就是数据的管理及存储工作。从内存中读取一个数据库数据的时间是微秒级别,从一块普通硬盘上读取一个IO的时间是毫秒级别。要优化数据库,IO操作是必须要优化的,尽可能将磁盘IO转化为内存IO。

(1)查询缓存相关的参数

查询缓存Query Cache会保存SQL查询返回的完整结果。当查询命中查询缓存Query Cache时,会跳过解析、优化和执行阶段,立刻返回结果。查询缓存Query Cache会跟踪查询中涉及的每个表,如果这些表发生变化,那么和这些表相关的查询缓存都将失效。

一.查看查询缓存Query Cache是否开启

-- 查询是否支持查询缓存
show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

-- 查询是否开启查询缓存 默认关闭
show variables like '%query_cache_type%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+

二.在my.ini中添加参数开启查询缓存Query Cache

query_cache_size=128M
query_cache_type=1

# query_cache_type=0,缓存禁用;
# query_cache_type=1,缓存所有的结果;
# query_cache_type=DENAND,只缓存在select语句中通过SQL_CACHE指定需要缓存的查询;

三.测试能否缓存查询

mysql> show status like '%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 1       |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+

# Qcache_free_blocks:缓存中目前剩余的block数量
# Qcache_free_memory:空闲缓存的大小
# Qcache_hits:命中缓存次数
# Qcache_inserts:未命中然后进行正常查询
# Qcache_lowmem_prunes:查询因内存不足而被移除出查询缓存记录数
# Qcache_not_cached:没有被缓存的查询数量
# Qcache_queries_in_cache:当前缓存中缓存的查询数量
# Qcache_total_blocks:当前缓存的block数量

四.优化建议

开启查询缓存适用于读多写少的场景,如果写比较多,那么更新查询缓存会损耗性能。

查询缓存的开启主要需要两个参数配合:也就是query_cache_size和query_cache_type。如果数据变化不多(写少),一般缓存大小query_cache_size设置为256M,当然也可以通过计算Query Cache的命中率来进行调整:

Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100

(2)脏页刷盘相关的参数

一.参数innodb_max_dirty_pages_pct的作用

该参数是InnoDB用来设置Buffer Pool中脏页的百分比,默认是75。当脏页数量占比超过该参数设置的值时,InnoDB会启动刷脏页的操作。

-- innodb_max_dirty_pages_pct 参数可以动态调整,最小值为0,最大值为99.99,默认值为75
show variables like 'innodb_max_dirty_pages_pct';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| innodb_max_dirty_pages_pct | 75.000000 |
+----------------------------+-----------+

二.优化建议

该参数比例值越大,从内存到磁盘的写入操作就会相对减少,所以该参数比例值越大越能一定程度减少写入操作的磁盘IO。但是如果这个比例值过大,当数据库Crash后重启的时间可能会很长,因为会有大量的事务数据需要从日志文件恢复出来写入数据文件中。最大不建议超90,一般重启恢复的数据如果超1G,启动速度就会变慢。

(3)LRU链表相关的参数

一.两个相关参数的作用

参数innodb_old_blocks_pct是用来确定LRU链表中冷数据区域所占比例,参数innodb_old_blocks_pct默认37,表示冷数据区域默认占用37%。

mysql> show variables like '%innodb_old_blocks_pct%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+

参数innodb_old_blocks_time是用来控制冷数据区域中Page的转移策略,参数innodb_old_blocks_time默认值是1秒。新的Page页在进入LRU链表时,会先插入到冷数据区域的头部。然后Page需要在冷数据区域中停留innodb_old_blocks_time后,下一次对该Page的访问才会使其移动到热数据区域的头部。

mysql> show variables like '%innodb_old_blocks_time%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_old_blocks_time | 1000  |
+------------------------+-------+

二.优化建议

在没有大表扫描的情况下,并且数据多为频繁使用的数据时,可以增加innodb_old_blocks_pct的值,减小innodb_old_blocks_time的值,让数据页能够更快和更多的进入的热点数据区。

(4)与脏页刷盘相关的参数

一.两个相关参数的作用

InnoDB1.0.x版本开始提供innodb_io_capacity参数,它的作用在两个方面:

作用1:合并插入缓冲时,每秒合并插入缓冲数量为innodb_io_capacity值的5%,默认200*5%=10。

作用2:从缓冲区刷新脏页时(Checkpoint),每秒刷新脏页数量为innodb_io_capacity的值,默认是200。若用户使用了SSD类的磁盘,或者将几块磁盘做了RAID,即当存储设备拥有更高的IO时,可将innodbio_capacity_max的值调高,更好利用磁盘IO的吞吐量。

mysql> show variables like '%innodb_io_capacity%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 200   |
| innodb_io_capacity_max | 2000  |
+------------------------+-------+

二.优化建议

在有频繁写入的操作时,可以对该参数进行调整。该参数设置的大小取决于硬盘的IOPS,即每秒的输入输出量(或读写次数),什么样的磁盘配置应该设置innodb_io_capacity参数的值是多少。下面仅供参考,建议通过sysbench或其他基准测试工具对磁盘吞吐量测试。