萬盛學電腦網

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

MySQL 5.6 低記憶體 VPS (512MB) 啟動 OOM 解決:精簡 my.cnf 核心參數調優

問題現象

512MB VPS 安裝 MySQL 5.6 後啟動被 OOM Killer 殺死,錯誤日誌顯示 Out of memory: Kill process ... (mysqld)。預設配置佔用 400MB+,小記憶體機器無法承受。

核心精簡參數(針對 512MB~1GB 記憶體)

[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_sizeInnoDB 核心快取,**單一最大記憶體消費者**,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 秒資料,可接受。

完整可用 my.cnf 範例(512MB VPS 實測穩定)

[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';"
copyright © 萬盛學電腦網 all rights reserved