MySQL 8.0 优化

一、软硬件配置

1. 服务器

• CPU 16核
• 内存 32G
• 硬盘 SSD

2. 软件

• 操作系统 ：统信服务器操作系统V20（1060）
• 数据库 ： MySQL 8.0 社区版本

二、参数调优原则及参数算法

对于一台 16 核心 32G 内存的服务器，以下是对 MySQL8.0 的优化配置建议：

1. 缓冲体系

• innodb_buffer_pool_size：该参数用于设置 InnoDB 缓冲池的大小，缓冲池用于缓存数据和索引，以加快数据库的读写速度，默认值128M。在 32GB 内存的服务器上，可以将该值设置为内存的 60%-70%，例如 18GB 左右。innodb_buffer_pool_size = 18G。
• innodb_buffer_pool_instances：缓冲池分区数，默认值1。每个分区独立管理以减少争用，计算公式：MIN(CPU核数，总缓冲池大小/1GB)，例如设置为 8。innodb_buffer_pool_instances = 8。
• innodb_page_cleaners：必须等于缓冲池实例数！每个实例分配一个专用清理线程，当innodb_page_cleaners > innodb_buffer_pool_instances时，多出的清理线程会空转浪费资源，例如设置为 8。innodb_page_cleaners = 8。

2. 日志体系

• innodb_log_file_size：它影响重做日志的性能，默认值48M。如果服务器的写操作较多，可以适当增加该值，例如设置为 2GB。innodb_log_file_size = 2G。
• 日志文件组innodb_log_files_in_group：SSD支持并行写入3个日志文件提升吞吐，默认值2，设置为3。innodb_log_files_in_group = 3。
• 日志缓冲区innodb_log_buffer_size：适配日志文件：2G×3=6G → 缓冲区，默认值16M，设置为64M。innodb_log_buffer_size = 64M。

3. I/O体系

• innodb_flush_method：该参数影响数据写入磁盘的方式，默认值fsync。对于高性能的存储设备，建议使用 O_DIRECT，它可绕过操作系统的缓存，减少数据写入的延迟。innodb_flush_method = O_DIRECT。注意：Ext4的系统配置，EXT4文件系统下可能数据损坏。
• innodb_io_capacity：用于指定 InnoDB 后台线程每秒的最大 I/O 操作次数，默认值200。如果服务器使用的是 SSD 存储，可以将其设置为 2000 或更高。innodb_io_capacity = 2000。
• innodb_io_capacity_max：设置在高负载情况下，InnoDB 后台线程每秒允许的最大 I/O 操作次数，默认值2000，通常设置为 innodb_io_capacity 的两倍。innodb_io_capacity_max = 4000。
• innodb_read_io_threads：
• innodb_write_io_threads：

4. 并发控制

• max_connections：根据应用程序的预期负载设置最大连接数，默认值151。对于中等规模的应用，可以设置为 500-1000；对于高并发应用，可能需要设置为 1000 以上。max_connections = 800。

  线程池(商业版)

  MySQL Enterprise Edition包含使用服务器插件实现的MySQL Enterprise Thread Pool。

• thread_handling：

• thread_pool_max_threads：
• thread_pool_stall_limit：
• thread_pool_size：线程池插件可以有效管理并发连接，降低线程切换的开销，线程组数量。在 16 核心的服务器上，可以将其设置为 CPU 核心数的 0.5-1 倍。thread_pool_size = 16。 需要先检查线程池插件是否已安装

  SELECT PLUGIN_NAME, PLUGIN_STATUS
  FROM INFORMATION_SCHEMA.PLUGINS
  WHERE PLUGIN_NAME LIKE 'thread%';
  

  如果插件未安装，需要先安装，修改配置后，需要重启MySQL服务使更改生效。

其他期待方案：

1. Percona Server（推荐首选）
2. ProxySQL

5. 持久性与性能平衡

• sync_binlog：该参数用于控制二进制日志的同步频率，默认值1。设置为 1 时，每次事务提交都会将二进制日志同步到磁盘，这能确保数据的持久性，但会带来一定的性能开销。如果对数据的可靠性要求较高，可以保持默认值 1；如果更注重性能，可以将其设置为更大的值，如 100。sync_binlog = 100。
• innodb_flush_log_at_trx_commit：用于控制事务日志的写入和刷新行为，默认值1。设置为 1 时，每次事务提交都会将日志缓冲区的内容写入日志文件并刷新到磁盘，这提供了最高的数据安全性，但性能开销较大。如果愿意在性能和持久性之间做出一定的权衡，可以将其设置为 2。innodb_flush_log_at_trx_commit = 2。
• innodb_flush_log_at_timeout：
• innodb_adaptive_flushing：

  多核CPU优化

• innodb_numa_interleave（关键参数）：让InnoDB在NUMA节点间交错分配内存，避免CPU核心访问远程内存导致的性能衰减，服务器需启用NUMA（numactl --hardware验证），默认值OFF，16核服务器必开设置为ON。innodb_numa_interleave = ON。

  6. SSD特化

• innodb_flush_neighbors：
• innodb_change_buffer_max_size：
• innodb_use_native_aio：

7. 系统优化建议

• 内存优化：关闭不必要的服务和应用程序，释放更多内存供 MySQL 使用。例如，禁用系统的图形界面（如果有），减少内存占用。
• CPU 优化：确保 MySQL 进程能够充分利用服务器的多核 CPU。可以通过调整线程池参数（如 thread_pool_size）来优化 CPU 使用率。
• 磁盘优化：使用高性能的存储设备（如 SSD），并定期检查磁盘的健康状况和性能。对于数据库文件，可以考虑使用 RAID 配置来提高数据的可靠性和读写性能。
• 网络优化：优化网络配置，减少网络延迟和丢包率。确保服务器的网络带宽充足，以满足数据库的远程访问需求。
• 查询优化：分析并优化数据库的查询语句，避免使用复杂的子查询和 JOIN 操作，使用索引来提高查询性能。使用 EXPLAIN 命令来查看查询的执行计划，找出性能瓶颈并进行优化。
• 数据库设计优化：合理设计数据库表结构，避免数据冗余和重复。使用表分区来将大表分解为较小的、更易于管理的片段，提高查询性能。
• 缓存优化： 缓存经常执行的查询结果。MySQL 的查询缓存已经废除，可以考虑使用其他缓存机制，如 Redis 或 Memcached，来进一步减少数据库的负担。
• 监控优化：部署监控工具（如 Prometheus + mysqld_exporter），实时监控数据库的性能指标，如 CPU 使用率、内存使用率、磁盘 I/O 延迟、慢查询等，以便及时发现和解决问题。

三、完整配置修改

1. EXT4文件系统系统配置

vim /etc/fstab

# /etc/fstab 增加挂载选项
UUID=xxx / ext4 defaults,data=writeback,nobarrier 0 0

>⚠️ O_DIRECT 强制要求： >1. 文件系统：EXT4必须启用data=writeback模式

• 避免默认data=ordered导致元数据不一致 >2. 写入屏障：SSD设备需禁用屏障nobarrier
• SSD自有电容保证断电安全
• 命令验证：sudo mount | grep ' / '

2. MySQL 系统配置

在Linux系统中修改MySQL配置文件的步骤如下：

找到MySQL配置文件：

MySQL配置文件通常是my.cnf或my.ini（在类Unix系统中通常是my.cnf）。可以通过以下命令查找：

mysql --help | grep -A 1 "Default options"

输出示例：

Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

这表示MySQL会按顺序从/etc/my.cnf、/etc/mysql/my.cnf和~/.my.cnf读取配置。

编辑配置文件：

编辑配置文件调整参数 vim /etc/my.cnf

在配置文件中找到[mysqld]部分，添加或修改相应的参数。

[mysqld]

# 缓冲体系
# 缓冲池设置
innodb_buffer_pool_size = 18G
# 匹配CPU和缓冲池配置
innodb_buffer_pool_instances = 8
# 清理线程
innodb_page_cleaners = 8

# 日志体系
# 日志文件大小
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_log_buffer_size = 64M

# I/O体系
# 文件刷新方式
innodb_flush_method = O_DIRECT
# I/O容量设置
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 16
innodb_write_io_threads = 16

# 并发控制
# 最大连接数
max_connections = 800
# 加载线程池插件
# plugin-load-add = thread_pool.so
# 启用线程池
thread_handling = pool-of-threads
# 线程池中的线程组数（默认为系统CPU核心数）
thread_pool_size = 16
# 线程池的最大线程数
thread_pool_max_threads = 100
thread_pool_stall_limit = 100


# 持久性优化
sync_binlog = 100
innodb_flush_log_at_trx_commit = 2
innodb_flush_log_at_timeout = 2
innodb_adaptive_flushing = ON

# SSD特化
innodb_numa_interleave = ON
innodb_change_buffer_max_size = 30
# AIO加速SSD响应
innodb_use_native_aio = ON
# 禁用HDD时代优化
innodb_flush_neighbors = 0

3. 操作系统优化

# 1. NUMA平衡策略
echo 1 > /proc/sys/kernel/numa_balancing

# 2. SSD调度器优化
sudo tee /sys/block/sda/queue/scheduler <<< 'none'

# 3. 网络加速（千兆+环境）
sudo ethtool -C eth0 rx-usecs 15

4. 重启MySQL服务：

sudo service mysql restart

5. 验证配置修改

检查配置是否生效,可以通过执行以下命令查看当前的配置参数值：

sudo mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

这将显示innodb_buffer_pool_size的当前值，验证是否按照你的设置进行了修改。

其他验证

/* 查看NUMA效果 */
SHOW STATUS LIKE 'Innodb_numa%';

/* 查看线程池效率 */
SELECT * FROM thread_pool_status\G

/* 查看I/O负载 */
SELECT * FROM sys.io_global_by_wait_by_latency;

本文由 systemofdown 创作，采用 知识共享署名 3.0 中国大陆许可协议 进行许可。 可自由转载、引用，但需署名作者且注明文章出处。