適用場景
- 實體記憶體 4GB
- 純 InnoDB 工作負載
- 高併發佇列處理、連接數較少
- 需 ACID 合規
核心參數解析與建議值
連接與執行緒
| 參數 | 建議值 | 說明 |
|---|
| max_connections | 100 | 最大連接數，預留 1 給 SUPER 權限。依實際 QPS 調整，避免過大導致記憶體耗盡。 |
| thread_cache_size | 8 | 緩存執行緒減少建立銷毀開銷。 |
| thread_concurrency | 8 | 建議 CPU 核心數 × 2（僅 Solaris 有效，Linux 忽略）。 |
| back_log | 50 | 監聽佇列長度，高併發時調大。 |
記憶體緩衝區（關鍵）
| 參數 | 建議值 | 說明 |
|---|
| innodb_buffer_pool_size | 2G | 最重要參數，設為實體記憶體 50~80%。4GB 機器建議 2~3GB，留給 OS 與其他緩衝。 |
| innodb_log_buffer_size | 8M | 交易日誌緩衝，大交易可調大。 |
| innodb_log_file_size | 256M | 單日誌檔大小，總量建議為 buffer pool 25~100%。 |
| innodb_log_files_in_group | 3 | 日誌檔組數，2~3 為宜。 |
| key_buffer_size | 32M | MyISAM 索引緩衝，純 InnoDB 也需預留給內部臨時表。 |
| query_cache_size | 64M | 唯讀負載才開啟；寫入頻繁建議關閉（query_cache_type=0）。 |
| sort_buffer_size | 8M | 每執行緒排序緩衝，過大反而增加記憶體壓力。 |
| read_rnd_buffer_size | 16M | 排序後隨機讀取緩衝，提升 ORDER BY 效能。 |
| join_buffer_size | 8M | 全表掃描 JOIN 緩衝，優先優化索引避免全掃描。 |
| tmp_table_size / max_heap_table_size | 64M | 記憶體臨時表上限，超過轉磁碟。 |
| binlog_cache_size | 1M | 交易期間 binlog 緩衝。 |
InnoDB 進階調校
| 參數 | 建議值 | 說明 |
|---|
| innodb_flush_log_at_trx_commit | 1 | 完整 ACID，每次提交 fsync。可接受效能換取安全時設 2。 |
| innodb_thread_concurrency | 16 | InnoDB 內部並發執行緒上限，0 為不限制。 |
| innodb_max_dirty_pages_pct | 90 | 髒頁比例上限，觸發刷盤。 |
| innodb_lock_wait_timeout | 120 | 鎖等待超時秒數。 |
| innodb_file_io_threads | 4 | Linux 固定 4，Windows 可調大。 |
| innodb_data_file_path | ibdata1:10M:autoextend | 共享表空間自動擴展。 |
日誌與復原
log-bin=mysql-bin：啟用二進制日誌，支援主從複制與時間點復原。log_slow_queries + long_query_time=2 + log_long_format：慢查詢日誌，排查效能瓶頸。log_warnings：記錄警告，協助診斷異常斷線。
MyISAM 相關（僅供內部臨時表）
myisam_sort_buffer_size = 128M
bulk_insert_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
用戶端與工具
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
完整 my.cnf 範本（可直接複製修改）
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
# 連接設定
max_connections = 100
max_connect_errors = 10
back_log = 50
thread_cache_size = 8
thread_concurrency = 8
# 記憶體緩衝
key_buffer_size = 32M
max_allowed_packet = 16M
table_cache = 2048
sort_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
join_buffer_size = 8M
query_cache_size = 64M
query_cache_limit = 2M
tmp_table_size = 64M
max_heap_table_size = 64M
binlog_cache_size = 1M
# InnoDB 核心
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 16M
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1
innodb_thread_concurrency = 16
innodb_lock_wait_timeout = 120
innodb_file_io_threads = 4
innodb_data_file_path = ibdata1:10M:autoextend
innodb_max_dirty_pages_pct = 90
# 日誌
log-bin = mysql-bin
log_slow_queries
long_query_time = 2
log_long_format
log_warnings
# 複制
server-id = 1
# 其他
default_table_type = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
ft_min_word_len = 4
skip-federated
skip-bdb
# skip-innodb # 若不使用 InnoDB 才開啟
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
調校檢查清單
- 修改後必須 重啟 MySQL 生效。
- 觀察
SHOW GLOBAL STATUS 中 Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests 命中率是否 > 99%。
- 慢查詢日誌定期分析，配合
EXPLAIN 優化索引。
- 若機器記憶體擴充，同比例調大
innodb_buffer_pool_size。
此配置經實測於日 IP 2 萬、PV 20 萬的雙站點環境穩定運行，可作為 4GB 記憶體 InnoDB 專用伺服器的基準範本。
