萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 記憶體與 CPU 過高優化實戰:my.cnf 關鍵參數調校指南

問題現象

MySQL 啟動後佔用 800MB+ 記憶體,CPU 持續偏高,影響主機其他服務。

核心參數調整(以 2GB 記憶體主機為例)

參數預設/原值建議值說明
innodb_buffer_pool_size576M256M~512MInnoDB 核心緩衝池,佔記憶體最大頭。設為實體記憶體 60~70%(專用 DB 伺服器)或 30~40%(共用主機)
query_cache_size100M16M~32M查詢快取。過大反而增加維護開銷,建議小型啟用,大型關閉(query_cache_type=0)
tmp_table_size102M64M~128M記憶體臨時表大小,超過則轉磁碟 MyISAM 表
max_heap_table_size16M64M~128M需與 tmp_table_size 同步調整,限制 MEMORY 表大小
key_buffer_size256M32M~128MMyISAM 索引緩衝。若主要用 InnoDB 可調小
innodb_log_buffer_size1M4M~8M交易日誌緩衝,大量寫入時調大減少磁碟 I/O
innodb_flush_log_at_trx_commit10 或 21=最安全(每提交刷盤),0=每秒刷盤(效能最好,崩潰可能丟 1 秒),2=提交寫入 OS 快取
innodb_thread_concurrency8CPU 核心數 * 2InnoDB 並行執行緒數,0 為不限制
table_open_cache20001024~2048開啟表快取數,配合 open_files_limit
thread_cache_size60100~200執行緒快取,減少頻繁建立/銷毀開銷
sort_buffer_size256K2M~4M每連線排序緩衝,過大易 OOM,按需調整
read_buffer_size128K1M~2M循序掃描緩衝
read_rnd_buffer_size256K2M~4M隨機讀取緩衝
max_connections151200~500最大連線數,配合應用連線池設定

兩套預設配置範本(2GB 記憶體)

方案 A:少量站點、追求穩定(推薦)

[mysqld]
innodb_buffer_pool_size = 512M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
key_buffer_size = 64M
tmp_table_size = 128M
max_heap_table_size = 128M
table_open_cache = 1024
thread_cache_size = 100
query_cache_size = 32M
query_cache_type = 1
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
max_connections = 300

方案 B:多站點、高併發抗壓

[mysqld]
innodb_buffer_pool_size = 768M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_thread_concurrency = 0
key_buffer_size = 128M
tmp_table_size = 256M
max_heap_table_size = 256M
table_open_cache = 2048
thread_cache_size = 200
query_cache_size = 0
query_cache_type = 0
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
max_connections = 500

十大效能關鍵參數深度解析

  1. max_connections:防止 Too many connections,配合應用連線池設定
  2. read_buffer_size:全表掃描時的緩衝,大量 ORDER BY/GROUP BY 需調大
  3. key_buffer_size:MyISAM 索引快取命中率關鍵,Key_reads/Key_read_requests < 0.01 為佳
  4. back_log:連線排隊長度,突發連線時調大(如 500)
  5. interactive_timeout / wait_timeout:閒置連線回收,建議 60~300 秒,防止連線洩漏
  6. sort_buffer_size:排序緩衝,Sort_merge_passes 高時調大
  7. table_open_cache:Open_tables 接近此值時需調大
  8. thread_cache_size:Threads_created 快速增長時調大
  9. 字元集:啟動參數 --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci
  10. slow_query_log:啟用慢查詢日誌,long_query_time=1,定期分析優化

調優流程建議

  1. 修改 /etc/my.cnf(或 my.ini)
  2. 重啟 MySQL:systemctl restart mysqld
  3. 觀察狀態變數:SHOW GLOBAL STATUS LIKE 'key_read%'; 等
  4. 壓測驗證:sysbench 或實際流量觀察
  5. 微調迭代:每次只調 1~2 參數,記錄效果

參考配置檔

MySQL 安裝目錄提供 my-huge.ini、my-large.ini、my-medium.ini、my-small.ini 供不同記憶體規格參考。

copyright © 萬盛學電腦網 all rights reserved