512MB VPS 安裝 MySQL 5.6 後啟動被 OOM Killer 殺死,錯誤日誌顯示 Out of memory: Kill process ... (mysqld)。預設配置佔用 400MB+,小記憶體機器無法承受。
[mysqld]
# 核心精簡
performance_schema_max_table_instances = 200
table_definition_cache = 200
table_open_cache = 128
# 連線與緩衝(每連線記憶體 × max_connections 必須可控)
max_connections = 1000
thread_cache_size = 8
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size = 2M
# InnoDB 核心(佔用大頭)
innodb_buffer_pool_size = 64M # 關鍵:設為實體記憶體 10~15%
innodb_log_file_size = 32M
innodb_log_buffer_size = 2M
innodb_flush_log_at_trx_commit = 2 # 性能與安全折衷
innodb_file_per_table = 1
# 關閉非必要功能
performance_schema = 0
query_cache_size = 0
query_cache_type = 0
# Binlog
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 3
max_binlog_size = 100M
# 其他
skip-name-resolve
default-storage-engine = InnoDB
transaction_isolation = REPEATABLE-READ
wait_timeout = 28800
interactive_timeout = 28800
| 參數 | 調整理由 |
|---|---|
| innodb_buffer_pool_size | InnoDB 核心快取,**單一最大記憶體消費者**,512MB 機建議 64M~128M,留 300MB+ 給 OS、連線緩衝、其他進程。 |
| table_open_cache / table_definition_cache | 減少表快取物件,降低元資料記憶體佔用。 |
| performance_schema = 0 | 完全關閉效能架構,省下 50MB+ 記憶體(5.6 預設開啟)。 |
| query_cache_size = 0 | 高寫入場景查詢快取反而拖慢效能,直接關閉。 |
| sort/read/join_buffer_size | 每連線獨享,**務必 × max_connections 估算總上限**,設小防 OOM。 |
| innodb_flush_log_at_trx_commit = 2 | 秒級刷盤,效能大幅提升,僅極端斷電丟 1 秒資料,可接受。 |
[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
user = mysql
bind-address = 0.0.0.0
server-id = 1
skip-name-resolve
back_log = 300
max_connections = 500
max_connect_errors = 1000
open_files_limit = 10000
table_open_cache = 128
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 16M
tmp_table_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 8
query_cache_size = 0
query_cache_type = 0
default-storage-engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 200
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 2
innodb_read_io_threads = 2
innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
bulk_insert_buffer_size = 4M
myisam_sort_buffer_size = 8M
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 3
max_binlog_size = 100M
log_error = /data/mysql/mysql-error.log
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /data/mysql/mysql-slow.log
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
# 啟動後檢查
ps aux | grep mysqld
# RES (常駐記憶體) 應穩定在 150~250MB 區間
# 監控關鍵狀態
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages%';"
mysql -e "SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';"