Discuz 論壇 MySQL 優化配置 my.cnf 完整版：硬件、參數、監控三位一體

Discuz! 性能瓶頸 90% 在 MySQL。本文結合 2U 服務器（2×Xeon 2.4GHz、4GB DDR、SCSI 73GB）實戰配置，覆蓋硬件選型、my.cnf 參數、監控指標三維度。

硬件層面關鍵決策

my.cnf [mysqld] 完整優化配置（4GB 內存基準）

[mysqld]
port = 3306
server_id = 1
socket = /tmp/mysql.sock
skip-locking
skip-name-resolve          # 禁用 DNS 解析，授權只用 IP
back_log = 384
key_buffer_size = 256M
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 512
sort_buffer_size = 6M      # × max_connections 計算總佔用
read_buffer_size = 4M
join_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 64
query_cache_size = 64M
tmp_table_size = 256M
max_connections = 768
max_connect_errors = 10000000
wait_timeout = 10
thread_concurrency = 8     # 2 物理 CPU × 2 HT × 2
skip-networking            # 僅本地連接時開啟

# InnoDB 專項
innodb_additional_mem_pool_size = 4M
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 2M
innodb_thread_concurrency = 8

監控驅動微調：兩大黃金指標

1. Key_reads / Key_read_requests < 1/100 → 增大 key_buffer_size
2. Qcache_lowmem_prunes 大 → 增大 query_cache_size

社區補充：2GB 內存抗壓型最佳配置

table_cache = 1024
innodb_additional_mem_pool_size = 4M
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 2M
innodb_thread_concurrency = 8
key_buffer_size = 256M
tmp_table_size = 64M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
sort_buffer_size = 32M
max_connections = 1024
thread_cache_size = 120
query_cache_size = 64M

通用型折中配置

table_cache = 512
innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 4M
innodb_thread_concurrency = 8
key_buffer_size = 128M
tmp_table_size = 128M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
sort_buffer_size = 32M
max_connections = 1024

核心原則

• 無通用最優，只有業務最適：監控 → 分析 → 調整 → 驗證閉環
• 每連接緩存總和 < 物理內存 70%
• Discuz 專用：關閉不需要的功能（如 skip-name-resolve、skip-locking）
• 版本升級後必須重新基準測試