mysql8.x配置my.cnf优化

Posted on Views:

1.优化目标

项目 假设值
服务器内存 16 GB
存储类型 SSD
业务类型 高并发读写混合型（OLTP）
MySQL版本 8.0.34
系统 Linux (RockyLinux 9等)

2.优化版 my.cnf (推荐生产配置)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
[mysqld]
# 基础
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
default_storage_engine = InnoDB
skip_name_resolve = 1

# 连接
max_connections = 1000
max_connect_errors = 10000
wait_timeout = 1800
interactive_timeout = 1800
thread_cache_size = 64

# 查询缓存（MySQL 8 已弃用 Query Cache，勿配置）

# 日志
log_error = /var/log/mysql/mysql-error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 0

# 二进制日志（启用可恢复与主从）
server-id = 1
log_bin = /data/mysql/binlog/mysql-bin
binlog_format = ROW
sync_binlog = 1
expire_logs_days = 7

# InnoDB 设置（重点）
innodb_buffer_pool_size = 8G            # ≈ 物理内存的50%~70%
innodb_buffer_pool_instances = 8        # 每GB一个实例（最多8）
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1      # 数据安全优先
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_neighbors = 0              # SSD推荐关闭
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_lock_wait_timeout = 20
innodb_open_files = 4096
innodb_max_dirty_pages_pct = 80
innodb_adaptive_hash_index = 1
innodb_doublewrite = 1

# 临时表与排序
tmp_table_size = 128M
max_heap_table_size = 128M
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M

# 其他优化
table_open_cache = 4096
open_files_limit = 65535
performance_schema = ON

# 防止大事务
innodb_log_buffer_size = 64M
max_allowed_packet = 128M

# 慢SQL分析推荐
log_timestamps = SYSTEM

3.主要优化点解析

项目 原因与说明
innodb_buffer_pool_size 最关键参数，占内存的 50–70%，缓存数据页与索引页。原2G太小。
innodb_flush_log_at_trx_commit=1 确保事务安全，生产环境应保持 1 （强一致）。
innodb_flush_method=O_DIRECT 避免双缓存（文件系统与InnoDB缓存）。SSD推荐。
innodb_flush_neighbors=0 SSD场景关闭，减少无谓IO。
innodb_io_capacity 提示InnoDB 后台刷新速率。SSD应≥2000。
innodb_log_file_size=1G 较合适，过大会延迟恢复，过小频繁flush。
innodb_log_files_in_group=2 默认 2 个日志文件，平衡性能与安全。
innodb_read/write_io_threads 根据CPU核数（8核→8线程）。
max_connections=1000 防止连接拒绝但仍控制资源。
skip_name_resolve=1 禁止DNS解析，提高连接速度。
tmp_table_size / max_heap_table_size 提高复杂查询内存临时表空间，减少磁盘落地。
sort_buffer_size / join_buffer_size 合理分配排序与连接缓存，避免过大造成内存爆炸。
binlog_format=ROW 更安全可靠的复制格式，推荐。
sync_binlog=1 保证主从一致与数据安全。
innodb_doublewrite=1 防止部分页写入损坏。生产务必开启。

4.性能优先而非数据一致性优先

可调整以下：

1
2
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0

吞吐量可提升 30–40%，但断电可能丢事务。

5.日常优化策略

  1. 定期重建索引与分析表

    1
    2
    OPTIMIZE TABLE table_name;
    ANALYZE TABLE table_name;

  2. 监控关键指标

    • SHOW ENGINE INNODB STATUS;
    • SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
    • SHOW VARIABLES LIKE 'innodb_io_capacity';

  3. 监控慢查询日志 → 用 pt-query-digest 做分析。

  4. 若内存 <16 G，可比例缩减各参数：

    • buffer_pool_size ≈ 内存 × 0.6
    • log_file_size ≈ buffer_pool_size × 0.1