MySQL 四个重要参数设置_查看mysql参数设置-优快云博客

本文链接：https://blog.youkuaiyun.com/IT_ZRS/article/details/120622265

前言

在实际工作中，研发、测试人员往往熟悉SQL语句的撰写、表结构的设计，而不熟悉MySQL的配置，这可能就会出现一些奇怪现象，就是在线上运行良好的查询，到了线下就变慢了。接下来和大家一起来分享一下 MySQL 四个重要的参数，配置好这几个参数可以满足大部分开发环境和测试环境的要求。

环境：

CentOS 7.4
MySQL 5.7.32
安装方式：二进制部署

一、innodb_buffer_pool_size

1.1 基本概念

一般地，数据库为了提升其写性能，会把要写的数据先在缓冲区（buffer）中合并，让后再到下一级存储，这样可以提高磁盘 I/O 操作的效率。因此，该参数就是 innodb 用来缓存它的数据和索引的内存缓冲区，理论上，该参数的值设置得越高，访问数据所需要的磁盘 I/O 就越少， innodb_buffer_pool_size默认大小为128M，官方推荐其配置为系统内存的 50% 到 75% 。一般innodb_buffer_pool_size要结合以下两个参数来设置：

innodb_buffer_pool_chunk_size

从 MySQL 5.7.5 开始，我们可以动态修改 InnoDB Buffer Pool 的大小。这个新特性同时也引入了一个参数 innodb_buffer_pool_chunk_size，buffer pool 会根据这个参数值的整数倍增加或减小。这个参数不是动态修改的，如果配置错误，可能会导致不想看到的结果。增大或减小缓冲池大小时，将以 chunk（块）的形式执行操作。chunk 大小由innodb_buffer_pool_chunk_size配置选项定义，默认值为 128 MB。
innodb_buffer_pool_instances

一般地，mysql 5.7、8.0 下 INNODB_BUFFER_POOL_INSTANCES 默认为1（单实例），若 mysql 存在高并发和高负载访问，设置为1则会造成大量线程对 BUFFER_POOL 的单实例互斥锁竞争，这样会存在一定量的性能问题， 该参数可以设置为服务器 CPU 核心数，这样可在一定程度上提供并发性能。

该参数仅在你设置innodb_buffer_pool_size 等于1GB或大于1GB时才生效，该参数的主要作用在于，对缓冲池在数 GB 范围内的系统，将缓冲池划分为单独的实例可以通过减少不同线程读取和写入缓存页面时的争，以此来提高 MySQL 的并发性（相当于多实例）

关于 innodb_buffer_pool_size、innodb_buffer_pool_instances、innodb_buffer_pool_chunk_size 之间的关系，大家可参考这篇文章
文章链接：https://www.percona.com/blog/2018/06/19/chunk-change-innodb-buffer-pool-resizing/

总体来说就是： buffer pool 可以存放多个 instance，每个instance由多个chunk组成。instance的数量范围和chunk的总数量范围分别为1-64，1-1000。

在这里插入图片描述

举个例子：

一个 3G 内存的服务器，128MB 的 chunk 值，2GB 的 buffer pool，8个 instance，那么每个 instance 就有2个 chunk。

1.2 配置方案

innodb_buffer_pool_size（我记为A）、innodb_buffer_pool_chunk_size（我记为B）、innodb_buffer_pool_instances （我记为C）， N 为正整数;。

1、缓冲池（A）大小必须等于或者是 A x B 的整数倍： A = N（B x C）

2、否则将会自动调整为等于或者是 A x B 的倍数。

比如我一台 4C/8G 的服务器，可配置如下：

innodb_buffer_pool_chunk_size=128M
innodb_buffer_pool_instances=4
innodb_buffer_pool_size=4608M

(128 x 4) x N = ？

这个时候你就需要把大小控制在 8G 的 50%~75% 范围内即可，比如 N 取9，则最终值为4608。

1.3 常用操作

服务器配置：2C/2G
innodb_buffer_pool_chunk_size=128M
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 4

以上面这些值为例，进行相关操作

1、查看 innodb_buffer_pool_xxx 状态信息

root@mysql 10:24:  [(none)]>show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| 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_instances        | 2              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 1610612736     |
+-------------------------------------+----------------+
10 rows in set (0.01 sec)

2、在线调整InnoDB缓冲池大小

SET GLOBAL innodb_buffer_pool_size = 2147483648;

# 重启mysql后失效

3、查看缓冲池调整进度

SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';

4、Innodb 实际使用内存大小

root@mysql 12:59:  [(none)]>set @a = (select variable_value from information_schema.global_status where variable_name = 'innodb_buffer_pool_pages_data');
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@mysql 12:59:  [(none)]>set @b = (select variable_value from performance_schema.global_status where variable_name = 'innodb_page_size');
Query OK, 0 rows affected (0.00 sec)

root@mysql 12:59:  [(none)]>set @c = @a * @b / (1024*1024*1024);
Query OK, 0 rows affected (0.00 sec)

root@mysql 12:59:  [(none)]>select @c;
+--------------------+
| @c                 |
+--------------------+
| 0.0040130615234375 |
+--------------------+
1 row in set (0.00 sec)

# 已经换算单位为GB，因此Innodb实际使用的内存大小为0.004GB

1.4 什么情况下调整该参数

1、Innodb 缓存命中率低的情况

P = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

可根据上述 Innodb 缓存命中率公式来评估是否调整参数值

Innodb_buffer_pool_reads：从物理磁盘读取页的次数；
Innodb_buffer_pool_read_requests：从缓冲池中读取页的次数。

root@mysql 11:42:  [(none)]>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      | 1169  |
| Innodb_buffer_pool_reads              | 230   |
+---------------------------------------+-------+
5 rows in set (0.00 sec)

p=1169/(1169+230)*100=83.56  # 大概就是84%的缓存命中率，还行。

如果缓存命中率过低，则我们需要考虑扩充内存的大小或者是 innodb_buffer_pool_size 的值。

2、Innodb 空闲内存较多的情况

...
...
Total large memory allocated 1649147904
Dictionary memory allocated 117329
Buffer pool size   98292
Free buffers       98029
Database pages     263
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 229, created 34, written 36
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 263, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
...
...

如果 Free buffers 长期都较大，则可以考虑调小 innodb_buffer_pool_size 的值。

二、innodb_log_file_size

2.1 基本概念

1、Redo Log

Redo Log 是一种基于磁盘的数据结构，用于在崩溃恢复期间纠正不完整事务写入的数据。在正常操作期间，Redo Log 对由 SQL 语句或低级 API 调用产生的更改表数据的请求进行编码。在初始化期间和接受连接之前，会自动重播在意外关闭之前未完成更新数据文件的修改。

Redo Log 文件

InnoDB默认情况下在数据目录中创建两个 5MB 的 Redo Log 文件，命名为 ib_logfile0和 ib_logfile1。Redo log 的大小通过 innodb_log_file_size 和 innodb_log_files_in_group（默认为2）参数来调节。Redo log 总大小为 innodb_log_file_size 和 innodb_log_files_in_group 的乘积，通常情况下通过 innodb_log_file_size 来调节。
- 可以自定义 Redo Log 文件路径；
```
[mysqld]
innodb_log_group_home_dir = /dir/iblogs

# 如果未配置此选项， InnoDB则会在 MySQL 数据目录 (datadir) 中创建日志文件。
```
- /dir/iblogs 路径需提前创建，且赋予目录 mysql:mysql（属主和属组）权限，否则无法创建；
innodb_log_files_in_group 参数

定义日志组中的日志文件数，默认为2且推荐设置为默认值。
innodb_log_file_size 参数

定义日志组中每个日志文件的大小（以字节为单位），合并的日志文件大小 (innodb_log_file_size* innodb_log_files_in_group不能超过最大值 512GB

2、Group Commit for Redo Log Flushing

InnoDB，像任何其他符合 ACID的数据库引擎一样，在事务提交之前刷新事务的 Redo Log。InnoDB 使用组提交功能将，多个刷新请求组合在一起，以避免每次提交一次刷新。使用组提交，可提高吞吐量。

2.2 配置方案

1、正确配置步骤（官方）

停止 MySQL 服务器并确保它关闭没有错误；
编辑my.cnf以更改日志文件配置；
- 更改日志文件大小：innodb_log_file_size
- 增加日志文件的数量：innodb_log_files_in_group
再次启动 MySQL 服务器。

2、参数值大小设置

官方文档推荐的值为从1MB ~ 1/N 的缓冲池大小，其中N是日志组里日志文件的数目（由innodb_log_files_in_group 变量来确定，一般默认为2）。

3、案例

systemctl stop mysqld         # 关闭mysql服务
cd /data/mysql_57/data        # 进入Redo Log文件所在目录
rm -rf ib_logfile*            # 删除原Redo Log文件或mv至其他目录
vim /etc/my.cnf               # 编辑配置文件
innodb_log_file_size = 256M
systemctl start mysqld        # 启动mysql

在这里插入图片描述

修改成功，如上图。

2.3 常用操作

1、查看 innodb_log_file_size 大小

root@mysql 16:14:  [(none)]>show variables like 'innodb_log_file_size';
+----------------------+-----------+
| Variable_name        | Value     |
+----------------------+-----------+
| innodb_log_file_size | 134217728 |
+----------------------+-----------+
1 row in set (0.06 sec)

2、实时观察 Redo Log 写入量

a=$(mysql -uuser -p'passwd' -e "show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}'); sleep 60; b=$(mysql -uuser -p'passwd' -e "show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}'); let "res=($b-$a)*60/1024/1024";echo $res

用于监控是很有用的，随时查看参数值的变化情况。

2.4 什么情况下调整该参数

innodb_log_file_size 值越大，在缓冲池中需要检查点刷新的行为就越少，因此也越节约磁盘I/O，但更大的日志文件也意味着在崩溃时恢复得更慢。建议将日志文件的大小设置为 256MB 或更大，这样可以满足一般情况下的需要。

当然，大家要根据实际情况，结合 innodb_log_files_in_group、innodb_buffer_pool_size 的值来确定最合适当前服务的 innodb_log_file_size 值。

三、innodb_flush_log_at_trx_commit

3.1 基本概念

您可以通过更改默认值来获得更好的性能，但是您可能会在崩溃时丢失事务。

默认值为 1：在每次事务提交时写入日志并刷新到磁盘。
设置为 0 时：每秒写入日志并刷新到磁盘一次。未刷新日志的事务可能会在崩溃中丢失。
设置为 2 时：每次事务提交后写入日志，并每秒刷新到磁盘一次。未刷新日志的事务可能会在崩溃中丢失。

在这里插入图片描述

3.2 配置方案

如果不在乎事务丢失，0和2能获得更高的性能，这里建议将 innodb_flush_log_at_trx_commit 设置为2，因为写入磁盘的速度是很慢的，再加上系统本身的延迟，MySQL 的性能会明显地下降，因此综合考虑建议设置为2。

3.3 常用操作

1、查看参数值

root@mysql 21:22:  [(none)]>select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

2、修改参数

vim /etc/my.cnf

[mysqld]
innodb_flush_log_at_trx_commit = 2

# 修改保存完成后重启mysql
systemctl restar mysqld

# 登录mysql并查看其状态
root@mysql 21:25:  [(none)]>select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                2 |
+----------------------------------+
1 row in set (0.00 sec)

3.4 什么情况下调整该参数

对于这个参数建议大家设置为2，因为在面对高并发场景下，再加上系统本身性能的影响，如果默认为1的话，可能就会出现一些延迟现象，会影响 MySQL 性能。

四、sync_binlog

4.1 基本概念

sync_binlog = 0 （mysql默认值）由文件系统决定将 binlog 同步到硬盘。
sync_binlog = 1 每提交一次事务，写一次 binlog，并使用 fsync() 同步到硬盘（mysql 在 autocommit模式下）。
sync_binlog = n 每提交一次事务，写一次 binlog，达到 n 次后，调用 fsync() 同步到硬盘。

4.2 配置方案

如果在主从复制架构下：
- 在 sync_logbin = 1 的情况下，每次提交事务都会同步到磁盘，保证日志的持久性，也可以保证主从复制的一致性。
- 在 sync_logbin = 0，或者>1的值，很有可能机器出现crash，日志并没有同步到磁盘，重启后，二进制日志的 position 比备库同步过去的 position 小，造成数据不一致情况。
没有主从架构，单独数据库实例：
- 在 sync_logbin = 1 的情况下，每次提交事务都会同步到磁盘，保证日志的持久性，能够保证存储下了所有提交的事务。能够用来进行恢复。
- 在 sync_logbin = 0，或者>1的值，很有可能机器出现 crash，日志并没有同步到磁盘，重启后，二进制日志很可能丢失已提交事务，所以不能用来进行恢复操作，因为它有丢失事务。

引用：https://blog.51cto.com/wangwei007/2487410

建议设置为 0

4.3 常用操作

1、查看参数值

root@mysql 10:32:  [(none)]>select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
|             1 |
+---------------+
1 row in set (0.01 sec)

2、修改参数

vim /etc/my.cnf

[mysqld]
sync_binlog = 0

# 修改保存完成后重启mysql
systemctl restar mysqld

# 登录mysql并查看其状态
root@mysql 10:35:  [(none)]>select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
|             0 |
+---------------+
1 row in set (0.01 sec)

4.4 什么情况下调整该参数

在 4.2 小结可以根据情况来选择，你是单机还是集群？我个人认为不管是单机还是集群，这个值最好设置为默认值 0，因为你要考虑到并发场景下的 MySQL，其中不仅涉及到 MySQL 性能的优化，同时也涉及到 OS 本身的性能优化，所以要综合考虑到各方面因素。

五、FAQ

1、show_compatibility_56 参数未启动

从 mysql5.7.6 开始 information_schema.global_status 已经开始被舍弃，为了兼容性，此时需要打开show_compatibility_56

问题

root@mysql 12:42:  [(none)]>select variable_value from information_schema.global_status where variable_name = 'innodb_buffer_pool_pages_data';
ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'

解决

root@mysql 12:43:  [(none)]>set global show_compatibility_56=on;
root@mysql 12:40:  [(none)]>show variables like '%show_compatibility_56%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| show_compatibility_56 | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

2、修改 innodb_log_file_size 后报错

问题

修改完 innodb_log_file_size 参数后启动 mysql，输入密码后无法正常登录。

mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql_57/data/mysql.sock' (2)

原因

旧的Redo Log文件，与改后的innodb_log_file_size不匹配，所以造成 mysql 不能正常工作。
解决

停止 mysql 服务后先删除旧的 Redo Log 文件（或移动到其他目录），再次启动，即可正常登录。

总结

innodb_buffer_pool_size：数据缓冲区（ InnoDB 表、索引和其他辅助缓冲区的缓存数据）；
innodb_log_buffer_size：日志缓冲区（InnoDB 用于写入磁盘上的日志文件的缓冲区的大小，默认大小为 16MB）；
innodb_data_file_path：系统表空间数据文件（定义 InnoDB 系统表空间数据文件的名称、大小和属性。如果在初始化 MySQL 服务器之前没有配置此选项，默认行为是创建一个自动扩展的数据文件，略大于 12MB，名为 ibdata1），指定格式如下：
```
[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

# 指定具体路径
[mysqld]
innodb_data_home_dir = /myibdata/       # 指定路径，注意赋予相关权限
innodb_data_file_path=ibdata1:50M:autoextend
```
innodb_log_group_home_dir：定义 InnoDB 日志文件的目录路径（如果未配置此选项， InnoDB则会在 MySQL 数据目录 datadir 中创建日志文件）
innodb_log_files_in_group：定义日志组中的日志文件数（默认和推荐值为 2）
innodb_log_file_size：定义日志组中每个日志文件的大小（以字节为单位）
innodb_doublewrite：双写缓冲区（默认情况下 innodb_doublewrite=ON，双写缓冲区增加了意外退出或断电后恢复的安全性，并通过减少 fsync() 操作需求来提高大多数 Unix 品种的性能）
在 Linux 上，如果内核启用了大页面支持，则 InnoDB 可以使用大页面为其缓冲池分配内存。
更多配置可参考官方：

InnoDB 配置

启动项和系统变量