參數調優核心原則
- 測量先行:
mysqladmin variables / SHOW VARIABLES / SHOW GLOBAL STATUS 建立基線
- 單一變更:每次只調一個參數,觀察負載測試/生產流量效果
- 增量調整:按 2x/0.5x 步長,避免震盪
- 持久化配置:
SET PERSIST var=val (8.0+) 或寫入 my.cnf + 重啟
- 關注反饋:延遲 P99、QPS、錯誤率、內存/CPU/IO 飽和度
核心變量分類詳解
一、連接與線程管理
| 變量 |
默認值 |
調優建議 |
影響 |
max_connections |
151 |
峰值 * 1.2,配合連接池 |
連接數上限,過大致內存/線程耗盡 |
thread_cache_size |
自動 |
100-200,避免頻繁創建/銷毀線程 |
線程復用,降低 CPU 上下文切換 |
back_log |
80 |
高併發突發時調大 (1000+) |
TCP 監聽隊列長度,防連接拒絕 |
wait_timeout |
28800 (8h) |
應用長連接調大 (86400),短連接可調小 |
非交互連接空閒超時 |
interactive_timeout |
28800 |
同 wait_timeout |
交互式客戶端 (mysql CLI) 空閒超時 |
max_connect_errors |
100 |
100000,防誤封禁 |
連續失敗次數達標封禁 Host,需 FLUSH HOSTS |
二、內存與緩衝區 (最關鍵)
| 變量 |
默認值 |
調優建議 |
作用域 |
innodb_buffer_pool_size |
128M |
物理內存 60-80%,專用 DB 服務器 70%+ |
Global |
innodb_buffer_pool_instances |
自動 (1GB/實例) |
8-16,減少全局鎖競爭 |
Global |
innodb_log_buffer_size |
16M |
64M-256M,大事務/大 Blob 寫入 |
Global |
key_buffer_size |
8M |
MyISAM 系統表用,128M 足夠 |
Global |
sort_buffer_size |
256K |
2-8M,**會話級**,慎調大 (max_conn × size = 總內存) |
Session |
read_buffer_size |
128K |
1-2M,全表掃描順序讀緩存 |
Session |
read_rnd_buffer_size |
256K |
2-4M,隨機讀 (排序後) 緩存 |
Session |
join_buffer_size |
256K |
4-8M,無索引 JOIN 時用,會話級 |
Session |
tmp_table_size / max_heap_table_size |
16M |
64-256M,內存臨時表上限,超限轉 MyISAM 磁盤表 |
Global/Session |
⚠️ 會話級內存陷阱
总会话内存 = max_connections × (sort_buffer + read_buffer + read_rnd_buffer + join_buffer + ...)
例如:2000 連接 × (4M+2M+4M+8M) = 36GB 額外內存!
原則:會話級緩存保守設置,大排序/大 JOIN 在會話級 SET SESSION sort_buffer_size=64M 臨時調大。
三、InnoDB 核心參數
| 變量 |
推薦值 |
核心影響 |
innodb_flush_log_at_trx_commit |
1 (核心業務) / 2 (日志/統計) |
1=提交時 fsync,零丟失;2=提交寫 OS Cache,每秒 fsync,掉電丟 1 秒 |
sync_binlog |
1 |
每次提交同步 Binlog 到磁盤,配合雙 1 實現崩潰安全 |
innodb_log_file_size |
1-4GB |
大 Redo Log 減少檢查點衝刷頻率,提升寫入吞吐;崩潰恢復時間隨之增加 |
innodb_flush_method |
O_DIRECT |
繞過 OS 文件系統緩存,避免雙重緩存浪費內存、增加延遲 |
innodb_io_capacity |
SSD: 2000-5000 / NVMe: 10000+ |
後台刷頁、合併插入緩衝、清理速率上限 |
innodb_read_io_threads / write_io_threads |
4-8 (每類) |
IO 線程數,高 IOPS 存儲可調大 |
innodb_thread_concurrency |
0 (無限制) |
0=由 OS 調度,高併發 CPU 核心多時可設 2×CPU 核心 |
innodb_file_per_table |
ON |
獨立表空間,支持 TRUNCATE TABLE 釋放空間、攜帶表移動 |
innodb_page_size |
16K (默認) |
實例創建時固定,大頁面適合大行/壓縮,小頁面適合高併發小行 |
四、複製與 Binlog
[mysqld]
binlog_format = ROW # ROW 格式最安全,支持並行複製
expire_logs_days = 7 # 自動清理,防磁盤滿
max_binlog_size = 100M # 單文件大小,輪換頻率
binlog_rows_query_log_events = ON # 記錄原始 SQL,便於審計/解析
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON # 級聯從庫必開
slave_parallel_type = LOGICAL_CLOCK # 基於組提交並行
slave_parallel_workers = 8 # 從庫並行重放線程數
master_info_repository = TABLE # 元數據存表,崩潰安全
relay_log_info_repository = TABLE
relay_log_recovery = ON # 中繼日志自動恢復
五、查詢緩存 (5.7 建議關閉,8.0 已移除)
query_cache_type = 0
query_cache_size = 0
# 原因:全局鎖競爭嚴重,寫入失效率高,8.0 移除改用 Query Rewrite Plugin / ProxySQL 緩存
配置模板:不同規格服務器
16C64G 專用 DB (SSD/NVMe)
[mysqld]
innodb_buffer_pool_size = 48G
innodb_buffer_pool_instances = 16
innodb_log_file_size = 4G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 5000
innodb_io_capacity_max = 10000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
max_connections = 3000
thread_cache_size = 200
back_log = 2000
wait_timeout = 86400
interactive_timeout = 86400
tmp_table_size = 512M
max_heap_table_size = 512M
sort_buffer_size = 4M
read_buffer_size = 2M
join_buffer_size = 4M
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
expire_logs_days = 7
slow_query_log = 1
long_query_time = 0.5
8C16G 混部服務器 (SSD)
[mysqld]
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
max_connections = 1000
thread_cache_size = 100
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 2M
read_buffer_size = 1M
join_buffer_size = 2M
binlog_format = ROW
gtid_mode = ON
expire_logs_days = 3
slow_query_log = 1
long_query_time = 1
動態調整與驗證
-- 1. 運行時動態調整 (無需重啟,8.0+ SET PERSIST 持久化)
SET GLOBAL innodb_buffer_pool_size = 53687091200; -- 50GB
SET PERSIST innodb_buffer_pool_size = 53687091200; -- 持久化到 mysqld-auto.cnf
SET GLOBAL max_connections = 2000;
SET PERSIST max_connections = 2000;
-- 2. 驗證生效
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
-- 3. 關鍵狀態指標監控
SHOW GLOBAL STATUS WHERE Variable_name IN (
'Threads_connected', 'Threads_running', 'Connections',
'Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests',
'Innodb_row_lock_waits', 'Innodb_row_lock_time_avg',
'Slow_queries', 'Questions', 'Queries',
'Created_tmp_disk_tables', 'Created_tmp_tables',
'Select_full_join', 'Select_scan',
'Innodb_log_waits', 'Innodb_buffer_pool_wait_free'
);
-- 緩存命中率 = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests (目標 > 99.9%)
-- 臨時表落盤率 = Created_tmp_disk_tables / Created_tmp_tables (目標 < 10%)
調優避坑指南
- 盲目調大 sort/join/read buffer → OOM Killer 殺進程
- innodb_flush_log_at_trx_commit=0/2 無業務評估 → 掉電丟數據
- query_cache 開啟 → 全局鎖瓶頸,寫入性能崩潰
- max_connections 設極大無連接池 → 線程風暴、上下文切換佔滿 CPU
- 忽略 innodb_log_file_size → 頻繁檢查點、寫入抖動
- 未啟用 GTID/ROW binlog → 複製不可靠、故障切換困難
- 不監控不告警 → 故障發現滯後,小問題變大故障
調優鐵律:配置服務於業務,沒有通用「最佳配置」,只有「當前負載下最優配置」。建立基線 → 壓測驗證 → 灰度發布 → 持續觀測。參數調整是手段,穩定達標 SLA 才是目的。