mysql|服务参数调优 – Wakamizu's Blog

注意：

每个配置的参数值因服务器条件和服务不同而有差异化，可以结合压力测试工具调整参数。

在优化的时候首先要明确数据库负责的业务是偏向OLTP（高频小事务，如日常业务操作）还是 OLAP（低频大数据量，如数据分析）。

OLTP 更关注实时、频繁的事务处理，适用于日常操作、订单处理、交易记录等场景。设计时强调低延迟、高并发和数据一致性。
OLAP 则面向决策支持和数据分析，关注对大数据量的存储、聚合与查询，适用于报表、趋势分析和历史数据统计。

然后结合top、iostat等工具综合磁盘I/O、内存分析，适当更改参数进行优化。

查询目前参数值

show variables like "参数名";

innodb_buffer_pool_size

定义了缓冲池的大小，缓冲池用于缓存数据和索引，从而减少磁盘I/O
默认单位为Byte，可以取M，G

推荐设置为系统物理内存的50%~80%，混合业务服务器可选择30%左右，但是要结合实际情况不要分配过多内存，以免影响其他程序的性能

innodb_log_file_size

指定redo日志文件的大小，redo日志文件用于记录数据库的更新操作，以便在发生故障时能够恢复数据

设置得太小，会导致redo日志文件频繁切换，触发数据库的检查点，innodb缓存脏页也会批量刷新，从而降低性能
设置得太大，出现意外崩溃后需要读取日志恢复已提交事务的时候会变慢

如果主要涉及读取数据，可以设置得更小一些。如果主要涉及写入数据，可以设置得更大一些

以下是一个例子：

可以在数据写入繁忙期间，执行下面的命令进行采样

show engine innodb status\G select sleep(60); show engine innodb status\G;

然后查看60s内的Log sequence number差值，然后扩展计算大约1小时内的差值

select (大-小)*60/1024/1024;

然后将其作为redo日志文件大小

innodb_log_buffer_size

定义redo日志缓冲区的大小，用于保存待写入磁盘的redo日志数据，当缓冲区快满时，InnoDB将必须将数据刷新到磁盘上，如果设置太小会频繁引起磁盘IO

通常建议将innodb_log_buffer_size设置为innodb_log_file_size的1/4~1/2左右

max_connections

定义同时连接到实例的最大客户端数量，如果新建立的连接达到限制会被服务器拒绝请求

由于会保留一个管理员登录连接，所以最大可连接数量=限制数+1

数值过小会经常出现太多连接数的错误
数值太大会因为提供很多的连接缓冲区，进而导致内存开销大

可以查看最大已响应的连接数

show status like 'max_used_connections';

然后根据使用率进行调整

innodb_flush_log_at_trx_commit

决定了InnoDB存储引擎在事务提交时如何处理日志缓冲区的内容

当值为1的时候，每次事务提交都会将日志缓冲区的数据写入到日志文件和刷新到磁盘上。
当值为2的时候，每次事务提交时，都将日志缓冲区的数据写入日志文件，但不会立即刷新到磁盘，刷新操作为每秒一次。这种模式提供了比1更好的性能，因为它减少了磁盘I/O操作。如果操作系统崩溃或断电，新数据有损失风险
当值为0的时候，日志缓冲区将每秒一次的写入日志文件，并且刷新到磁盘的操作也会同时进行。但是在事务提交时，不会主动触发写入磁盘的操作。如果mysql进程崩溃，新数据有损失风险

sync_binlog

控制二进制日志的同步策略

当值为0的时候，不会在每次事务提交后立即执行磁盘同步操作，而是让文件系统决定何时同步。这可以提高性能，如果系统崩溃，最近的事务可能会丢失
当值为1的时候，会在每次事务提交后立即将日志同步到磁盘。提供了最高的数据安全性，但会因为频繁操作磁盘而影响性能
当值为N（N>1且为int）的时候，会在N次事务提交后执行一次磁盘同步。这是一个折中的选择，可以在数据安全性和性能之间取得平衡。例如，设置为100意味着每100次事务提交后同步一次，这样可以提高性能，但如果在同步之前系统崩溃，最多可能丢失最近100次事务的日志

innodb_io_capacity/innodb_io_capacity_max

定义了InnoDB存储引擎可以用于后台任务（如刷新脏页【已修改但尚未写入磁盘的页面】和合并更改缓冲区到二级索引）的I/O操作的数量

默认值是200，这通常适用于7200 RPM的硬盘。如果是更高级的硬盘、RAID或固态硬盘，可以设置更高的值。例如，对于高端的总线连接SSD，可以设置为1000或更高

可以使用ioping -RD -w 20 /mnt测试数据，可以设置为iops结果的50%~80%

innodb_write_io_threads和innodb_read_io_threads

innodb_write_io_threads：指定写入数据的IO线程数。

innodb_read_io_threads：指定I读取数据的IO线程数。

在进行优化时，可以根据CPU核数来更改相应的参数值。
如果CPU是2颗8核的，那么可以设置：innodb_read_io_threads = 8和innodb_write_io_threads = 8。
如果数据库的读操作比写操作多，那么可以设置：innodb_read_io_threads = 10和innodb_write_io_threads = 6

wait_timeout

定义客户端与服务器空闲连接的时间，超过则自动断开，默认值为8小时。

max_connect_errors

是 MySQL 中的一个参数，默认为100，用于防止恶意攻击者通过无限制的连接尝试来攻击 MySQL 服务器。该参数指定了在 MySQL 连接失败的情况下允许的最大错误次数，超过此次数后，MySQL 会将其拒绝连接，并记录日志

table_open_cache_instances

用于提高数据库的可伸缩性和性能，它通过将打开的表缓存划分为多个较小的缓存实例来减少会话间的争用，每个会话只需要锁定一个实例就可以访问 DML 语句。

取值范围是1~64，一般选择16，

增加table_open_cache_instances的值可以提高系统的并发处理能力，但也会增加内存使用量。

innodb_file_per_table

innodb_file_per_table 是 MySQL 中的一个配置参数，用于控制 InnoDB 存储引擎是否为每个表创建独立的表空间文件（.ibd 文件）。默认情况下，所有表的数据存储在一个共享的表空间文件（ibdata1）中，但启用 innodb_file_per_table 后，每个表的数据和索引将存储在单独的文件中

启用 innodb_file_per_table 有以下几个优点：

减少I/O争用：每个表的数据和索引存储在单独的文件中，有助于减少I/O争用，提高性能
更好的磁盘空间管理：可以更方便地进行表的数据管理和优化磁盘空间使用
提高文件系统操作性能：例如在执行 DROP TABLE 或 TRUNCATE TABLE 操作时，性能开销较低

简单测试（4G 2vCPU混合业务虚拟服务器）

可以临时开启慢日志，观察超时的指令

mysql> SET slow_query_log = ON;
mysql> SET long_query_time = 1;
mysql> SHOW VARIABLES LIKE 'slow_query_log';

第一次读写混合测试

sysbench   --test=oltp_read_write   --mysql-host=localhost   --mysql-port=3306   --mysql-user=root   --mysql-password=wakamizu   --mysql-db=test   --threads=64 run

SQL statistics:
    queries performed:
        read:                            69230
        write:                           19524
        other:                           9795
        total:                           98549
    transactions:                        4850   (482.36 per sec.)
    queries:                             98549  (9801.25 per sec.)
    ignored errors:                      95     (9.45 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0524s
    total number of events:              4850

Latency (ms):
         min:                                    5.08
         avg:                                  132.26
         max:                                  758.46
         95th percentile:                      292.60
         sum:                               641445.94

Threads fairness:
    events (avg/stddev):           75.7812/5.38
    execution time (avg/stddev):   10.0226/0.02

优化参数

vim /etc/mysql/mysql.conf.d/mysqld.conf

innodb_buffer_pool_size =1G
innodb_log_file_size=2224M
innodb_log_buffer_size=1112M
max_connections=200
innodb_flush_log_at_trx_commit=2
sync_binlog = 0

systemctl restart mysql

第二次测试

SQL statistics:
    queries performed:
        read:                            77966
        write:                           22169
        other:                           11098
        total:                           111233
    transactions:                        5529   (549.20 per sec.)
    queries:                             111233 (11048.82 per sec.)
    ignored errors:                      40     (3.97 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0656s
    total number of events:              5529

Latency (ms):
         min:                                    2.57
         avg:                                  116.16
         max:                                  469.08
         95th percentile:                      200.47
         sum:                               642226.68

Threads fairness:
    events (avg/stddev):           86.3906/3.41
    execution time (avg/stddev):   10.0348/0.02

两次压测数据综合对比

指标名称	第一次测试数据	第二次测试数据	变化幅度
TPS（事务/秒）	482.36	549.20	+13.8%
QPS（查询/秒）	9801.25	11048.82	+12.7%
平均延迟（ms）	132.26	116.16	-12.2%
95% 分位延迟（ms）	292.60	200.47	-31.5%
最大延迟（ms）	758.46	469.08	-38.1%
忽略错误数	95	40	-57.9%

吞吐量大幅增加：
- TPS 提升 13.8% ，QPS 提升 12.7%，表明系统处理能力显著增强。
延迟显著下降：
- 平均延迟下降 12.2% ，95% 分位延迟下降 31.5%，最大延迟下降 38.1%，响应时间更稳定。
稳定性增强：
- 忽略错误数从 95 次降至 40 次（-57.9%），事务冲突或资源竞争明显减少。
线程公平性提升：
- 事件分布标准差从 5.38 降至 3.41，线程负载更均衡。