MySQL8.0参数解读

MySQL8.0参数解读

1.硬盘读写参数

硬盘的读写通常是对数据库性能最大的因素之一。这里介绍几个影响硬盘读写的重要参数。
innodb_flush_log_trx_commit
sync_binlog
innodb_flush_method
innodb_io_capacity和innodb_io_capacity_max

1.1 innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit参数控制事务提交时写重做日志的行为方式，它有三个值：0、1和2。
（1）默认值为1，每次事务提交的时候都会将日志缓存中的数据写入到日志文件，同时还会触发文件系统到磁盘的同步，如果发生系统崩溃，数据是零丢失，这种方式对数据是最安全的，但性能是最慢的，因为把数据从缓存同步到磁盘的成本很高。这种方式适用于对数据安全性要求高的行业，如银行业。但很多互联网的应用，对数据的安全性要求不太高，而对性能的要求很高，设置成0或2会更合适。
（2）设置成0时，事务提交的时候不会触发写日志文件的操作，日志缓存中的数据以每秒一次的频率写入到日志文件中，同时还会进行文件系统到磁盘的同步操作。
（3）设置成2时，事务提交的时候会写日志文件，但文件系统到磁盘的同步是每秒进行一次。0和2都是每秒进行一次文件系统到磁盘的同步，因此这两种方式的性能都差不多，当系统崩溃时，最多丢失1秒的数据。但0和2还有细微的不同，当设置成2时，每次事务提交都写日志文件，因此数据已经从MySQL的日志缓存刷新到了操作系统的文件缓存，如果只是MySQL崩溃，而操作系统没有崩溃，将不会丢失数据。因此0 和2比较起来，通常设置为2比较好。

1.2 sync_binlog

sync_binlog参数控制事务提交时写二进制日志的行为方式，它有三个值：0、1和N。
（1）默认值为1，每次事务提交的时候都会把二进制日志刷新到磁盘，这种方式对数据是最安全的，但性能是最慢的。
（2）设置成0时，事务提交的时候不会把二进制日志刷新到磁盘，刷磁盘的动作由操作系统控制。
（3）设置成N（N不等于0或1）时，每进行N事务提交后会进行一次把二进制日志刷新到磁盘的动作。没有备库和使用二进制日志进行时间点恢复的需求时，可以把sync_binlog参数设置为0或N，设置为0是把刷新二进制日志文件的操作交给操作系统决定，但操作系统可能会在二进制日志文件写满进行切换时才刷新磁盘文件，这样会造成数秒的延迟，在这期间事务无法提交，因此把这个参数设置成100或1000之类的一个合理数值比设置成0好。

如果使用二进制日志进行主库和备库之间的数据同步，或者使用二进制日志进行时间点恢复，并且对数据一致性要求高时，把sync_binlog参数设置为1，同时要把innodb_flush_log_trx_commit参数也设置为1。把这两个参数都设置成1对性能的负面影响很大，为了提高性能，这时使用的存储应该是带缓存的，并且设置成Write-back，而不是Write-through，这样数据只写入到存储的缓存中即返回。但存储的缓存应该是带电池的，如果缓存不带电池，或者电池没有电，突然发生掉电的时候，不仅数据会丢失，而且会造成数据库损坏，无法启动，这种情况要比丢失一秒钟的数据要糟糕得多。
写二进制日志的成本比写重做日志的成本要高得多，因为重做日志的大小和文件名是固定的，重做日志循环写入日志文件。而每次写二进制日志时，文件都会进行扩展，如果写满了还要新建文件，这样每次写二进制日志不但要写数据，还要修改二进制日志文件的元数据，因此把sync_binlog设置成1比把innodb_flush_log_trx_commit设置成1对性能负面影响还要大得多。

1.3 innodb_flush_method

innodb_flush_method参数控制MySQL将数据刷到InnoDB的数据文件和日志文件的动作。在Windows系统上有两个选项：unbuffered是默认和推荐的选项，另外一个是normal。Linux系统上，常用的选项有一下几种：
1. fsync：是默认值，使用fsync()系统调用刷新数据文件和日志文件，数据会在操作系统的缓存中保存。
2. O_DSYNC：InnoDB使用O_SYNC打开和刷新日志文件，使用fsync()刷新数据文件。
3. O_DIRECT：使用O_DIRECT打开数据文件，使用fsync()系统调用刷新数据文件和日志文件，数据不会在操作系统的缓存中保存。
4. O_DIRECT_NO_FSYNC：使用O_DIRECT刷新I/O，但写磁盘时不执行fsync()。

1.4 innodb_flush_method

通常对于硬盘性能好的服务器，可以设置成O_DIRECT，这样避免在InnoDB缓存和操作系统缓存中存有两份数据，而且InnoDB缓存比操作系统缓存效率要高，因为InnoDB缓存是专门针为InnoDB的数据设计的，而操作系统缓存是为通用的数据设计的。

设置成O_DIRECT_NO_FSYNC时，因为写磁盘时不执行fsync()，速度可能会快，但突然断电时可能会丢失数据。对于读操作大大多于写操作的应用，设置成fsync会比设置成O_DIRECT性能略好。

但如何选择这些参数最终需要经过测试才能确定，测试时要注意观察状态参数Innodb_data_fsyncs，它记录着调用fsync()的次数。通常fsync和O_DIRECT调用fsync()的次数差不多，O_DIRECT_NO_FSYNC调用fsync()的次数最少。

1.5 innodb_io_capacity和innodb_io_capacity_max

InnoDB后台线程会进行一些I/O操作，例如把缓冲池中的脏页刷新到磁盘，或将更改从更改缓冲区写入到对应的二级索引。InnoDB试图以不影响服务器正常工作的方式执行这些I/O操作，这需要它知道系统的I/O的处理能力，它根据参数innodb_io_capacity评估系统的I/O带宽。
参数innodb_io_capacity_max值定义了系统I/O能力的上限，防止在I/O的峰值时消耗服务器的全部I/O 带宽。

通常可以把innodb_io_capacity设置得低一些，但不要低到后台I/O滞后的程度。如果该值太高，数据将很快从缓冲池中被移除，不能充分发挥缓存的优势。但对于繁忙而且具有较高I/O处理能力的系统，可以设置一个较高的值来帮助服务器处理与数据快速变更相关联的后台维护工作。

innodb_io_capacity和innodb_io_capacity_max这两个参数的默认值如下：

root@db 11:03: [(none)]> show variables like 'innodb_io_capacity%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
+------------------------+-------+

这两个参数的设定是基于系统的每秒能处理的I/O数量（IOPS），可以把innodb_io_capacity_max设置成极限的IOPS，innodb_io_capacity设置成它的一半左右。目前业界有很多I/O测试软件可以测出系统的IOPS，也可以通过硬盘配置进行估算，例如一块15K转速的传统硬盘的IOPS的参考值大约是200，高端SSD盘可以达到60万。状态参数Innodb_data_fsyncs记录着数据刷新到磁盘的次数，把innodb_io_capacity调大后，可以看到这个状态参数也相应的增加了。

2. 自适应参数innodb_dedicated_server

MySQL8中新引进了一个参数innodb_dedicated_server，这个参数的默认值是off，就像这个参数名所建议的一样，当MySQL独占当前服务器资源的时候，可以把这个参数设置为on，这时MySQL会自动探测当前服务器的内存大小并设置下面4个参数：
（1） innodb_buffer_pool_size
（2） innodb_log_file_size
（3） innodb_log_files_in_group
（4） innodb_flush_method
其中前面3个参数是根据当前服务器的内存大小计算出来的，这样对运维在虚拟机或云上运行的MySQL很方便，当调整了内存的大小后，MySQL会在启动时自动调整这3个参数，省去了每次手工修改参数的工作。

root@db 11:04: [(none)]> show variables like '%innodb_dedicated_server%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_dedicated_server | OFF |
+-------------------------+-------+
1 row in set (0.00 sec)

vim /etc/my.cnf 增加参数，保存退出后

#自适应参数
innodb_dedicated_server=ON

重启MySQL服务后，innodb_dedicated_server参数值为ON
root@db 11:18: [(none)]> show variables like '%innodb_dedicated_server%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_dedicated_server | ON |
+-------------------------+-------+
1 row in set (0.02 sec)

#显示设定了以下参数，就会优先生效
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=2

innodb_log_file_size =1G
innodb_log_files_in_group=3

root@db 11:18: [(none)]> show variables like '%innodb_buffer_pool_size%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 2147483648 |
+-------------------------+------------+
1 row in set (0.01 sec)

root@db 11:19: [(none)]> show variables like '%innodb_log_file_size%';
+----------------------+------------+
| Variable_name | Value |
+----------------------+------------+
| innodb_log_file_size | 1073741824 |
+----------------------+------------+
1 row in set (0.01 sec)

root@db 11:19: [(none)]> show variables like '%innodb_log_files_in_group%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 3 |
+---------------------------+-------+
1 row in set (0.00 sec)

innodb_buffer_pool_size根据物理内存的设置策略
内存大小 innodb_buffer_pool_size的值
小于1GB 128MB
1GB到4GB 物理内存×0.5
大于4GB 物理内存×0.75

innodb_log_file_size设置策略
innodb_log_file_size和innodb_log_files_in_group两个参数是根据innodb_buffer_pool_size计算出来的。
innodb_buffer_pool_size innodb_log_file_size
小于8GB 512MB
8GB到16GB 1024MB
大于16GB 2GB

innodb_log_file_in_group设置策略
innodb_buffer_pool_size innodb_log_files_in_group
小于8GB 以GB为单位对innodb_buffer_pool_size取整
8GB到128GB 以GB为单位对（innodb_buffer_pool_size*0.75）取整
大于128GB 64

显式设置的参数优先生效
当参数innodb_dedicated_server为ON时，如果还显式设置了这些参数，则显式设置的这些参数会优先生效，并且在MySQL的错误日志中会记录如下内容：[Warning] [MY-012360] [InnoDB] Option innodb_dedicated_server is ignored for innodb_log_file_size because innodb_log_file_size=2073034752 is specified explicitly.

显式指定某一个值，并不会影响另外3个参数值的自动设定。
MySQL的启动探测当参数innodb_dedicated_server为ON时，MySQL每次启动时会自动探测服务器的内存并自动调整上述几个参数值。在任何时候MySQL都不会将自适应值保存在持久配置中，利用这个参数就可以保证服务器（包括虚拟机或者容器）扩展以后，MySQL能“自动适应”，以尽量利用更多的服务器资源

3. 其他参数

max_connections skip_name_resolve binlog_order_commits

max_connections

系统参数max_connections设置了允许的服务器最多连接数，防止服务器因为连接数过多而造成资源耗尽，默认是151，这在生产环境通常偏小。这个参数应当设置为经过压力测试验证后系统能承受的最多连接数。可以参考状态参数Max_used_connections和Max_used_connections_time，它们记录了系统连接数曾经达到的最大值和发生时间。

root@db 11:53: [(none)]> show status like 'max_used%';
+---------------------------+---------------------+
| Variable_name | Value |
+---------------------------+---------------------+
| Max_used_connections | 1 |
| Max_used_connections_time | 2024-02-21 11:18:08 |
+---------------------------+---------------------+
2 rows in set (0.00 sec)

binlog_order_commits

系统参数binlog_order_commits默认为on，如果把这个参数设置为off将不能保证事务的提交顺序和写入二进制日志的顺序一致，这不会影响到数据一致性，在高并发场景下还能提升一定的吞吐量。

skip_name_resolve

系统参数skip_name_resolve默认为off，这时MySQL每收到一个连接请求，都会进行正向和反向DNS解析，建议设置成on，禁止域名解析，这样会加快客户端连接到MySQL服务器的速度。

当DNS服务器运行正常时，这个优势并不明显，如果DNS服务器出故障，或者变慢，进行域名解析的时间可能会很长，甚至会拒绝连接。如果解析不成功，在错误日志里面会有类似下面的提示：
[Warning] [MY-010055] [Server] IP address '********' could not be resolved: Name or service not known

把这个参数设置成on也有弊端，就是只能使用IP进行grant赋权，不能使用主机名，通常主机名不会变，而IP改变的可能性比主机名大。因此在一个生产主机上把skip_name_resolve从off改成on要小心，因为原来用主机名赋予的权限不能用了。