Mysql8.0优化参数项

最新推荐文章于 2024-10-11 20:56:41 发布

原创最新推荐文章于 2024-10-11 20:56:41 发布 · 4.4k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

本文介绍了如何查看MySQL 8.0的配置参数，包括innodb_buffer_pool_size、innodb_log_file_size等，并展示了当前的配置值。针对8.0的新特性，讨论了参数自动配置原则，如innodb_dedicated_server和innodb_flush_method的设定。还提出了优化建议，如调整innodb_flush_log_at_trx_commit和sync_binlog的值，以提高系统性能和数据安全性。

Mysql8.0优化参数项

查看当前Mysql配置

M_USER=root
M_AUTH=pass
mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_dedicated_server";' 2>/dev/null
mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_buffer_pool_size";' 2>/dev/null
mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_log_file_size";' 2>/dev/null
mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_flush_method";' 2>/dev/null
mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_log_files_in_group";' 2>/dev/null

mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_autoextend_increment";' 2>/dev/null
mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_log_buffer_size";' 2>/dev/null
mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_log_file_size";' 2>/dev/null


mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_flush_log_at_trx_commit";' 2>/dev/null
mysql -u $M_USER -p$M_AUTH -e 'show variables like "sync_binlog";' 2>/dev/null

返回结果

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_dedicated_server | OFF   |
+-------------------------+-------+
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_buffer_pool_size";' 2>/dev/null
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_log_file_size";' 2>/dev/null
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_flush_method";' 2>/dev/null
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_flush_method | fsync |
+---------------------+-------+
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_log_files_in_group";' 2>/dev/null
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+
[root@manyun /]# 
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_autoextend_increment";' 2>/dev/null
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 64    |
+-----------------------------+-------+
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_log_buffer_size";' 2>/dev/null
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_log_file_size";' 2>/dev/null
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "innodb_flush_log_at_trx_commit";' 2>/dev/null
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
[root@manyun /]# mysql -u $M_USER -p$M_AUTH -e 'show variables like "sync_binlog";' 2>/dev/null
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1000  |
+---------------+-------+

修改mysql.env后up mysql测试

innodb_dedicated_server=off

若innodb_dedicated_server=on动态调整参数会根据宿主机的内存配置进行修改，而不是deploy limit中的内存限制。请参考下面的自动配置原则：

自动配置原则如下：

innodb_buffer_pool_size
根据专用内存大小进行配置。

专用内存大小	innodb_buffer_pool_size
小于1GB	128mb(默认值)
1GB–4GB	detected server memory * 0.5
大于4GB	detected server memory * 0.75

innodb_log_file_size
从mysql8.0.14开始，根据buffer pool size的大小进行配置

buffer pool size	log file size
小于8GB	512mb)
8GB–128GB	1024mb
大于128GB	2048mb

innodb_log_files_in_group
MySQL 8.0.14中，根据buffer pool size的大小进行自动配置（单位是GB）。
如果buffer pool size小于2GB，innodb_log_files_in_group最小值是2。

buffer pool size	log file number
小于8GB	ROUND(buffer pool size))
8GB–128GB	ROUND(buffer pool size * 0.75)
大于128GB	64

innodb_log_files_in_group
开启innodb_dedicated_server开启后，innodb_flush_method 被设置为 O_DIRECT_NO_FSYNC；如果O_DIRECT_NO_FSYNC不可用，innodb_flush_method就使用默认值。
InnoDB使用O_DIRECT刷新i/o，会略过操作系统的fsync()系统调用。

在mysql 8.0.14之前，该设置不适用于xfs、ext4文件系统，因为需要系统调用fsync()同步文件系统元数据。
从8.0.14开始，创建一个新的文件后、增加文件大小后、或关闭文件后，就会调用fsync(),确保文件系统元数据被同步。但是没次写操作仍然会略过fsync()系统调用。

并且根据8.0的新特性中部分参数已经进行过优化

#最数据包大小存入限制
调整max_allowed_packet 的默认值，从4M增加到64M。
调整bin_log,log_slave_updates默认值为on。

优化参数项

# innodb_flush_log_at_trx_commit
## 0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作
## 1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认
## 2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作
add.innodb_flush_log_at_trx_commit=2
# sync_binlog=n
## 当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
add.sync_binlog=1000
# 关闭自动参数配置，开启后会直接占用宿主机总内存量，而非mysql容器的内存量
add.innodb_dedicated_server=off
# 给容器分配的内存的75% 以mysql容器占用8G为例，当前分配6G
add.innodb_buffer_pool_size=6442450944
# buffer小于等于8G 分配512M
add.innodb_log_file_size=536870912