CentOS7下WordPress站点MariaDB内存不足及my.cnf调优咨询

问题概述 #

在8GB内存的CentOS 7系统上运行WordPress站点时，MariaDB抛出内存不足错误：

[ERROR] mysqld: Out of memory (Needed 128917504 bytes)

当前环境信息 #

MariaDB版本：

[root@izk1aaur2shmpokx2h15i1z /]# mysql --version
mysql  Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1

当前my.cnf配置 #

/etc/my.cnf

# The MariaDB server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/

 [mysqldump]
 quick
 max_allowed_packet = 16M

 [mysql]
 no-auto-rehash
 # Remove the next comment character if you are not familiar with 
 SQL
 #safe-updates

 [myisamchk]
 key_buffer_size = 20M
 sort_buffer_size = 20M
 read_buffer = 2M
 write_buffer = 2M

 [mysqlhotcopy]
 interactive-timeout

重启后mariadb.log日志 #

/var/log/mariadb.log

230616 11:02:38 [Note] /usr/libexec/mysqld: Normal shutdown
230616 11:02:38 [Note] Event Scheduler: Purging the queue. 0 events
230616 11:02:38  InnoDB: Starting shutdown...
230616 11:02:39  InnoDB: Waiting for 9 pages to be flushed
230616 11:02:40  InnoDB: Shutdown completed; log sequence number 
5956557136
230616 11:02:40 [Note] /usr/libexec/mysqld: Shutdown complete

230616 11:02:40 mysqld_safe mysqld from pid file 
/var/run/mariadb/mariadb.pid ended
230616 11:02:40 mysqld_safe Starting mysqld daemon with databases 
from /var/lib/mysql
230616 11:02:40 [Note] /usr/libexec/mysqld (mysqld 5.5.68-MariaDB) 
starting as process 7417 ...
230616 11:02:40 InnoDB: The InnoDB memory heap is disabled
230616 11:02:40 InnoDB: Mutexes and rw_locks use GCC atomic 
builtins
230616 11:02:40 InnoDB: Compressed tables use zlib 1.2.7
230616 11:02:40 InnoDB: Using Linux native AIO
230616 11:02:40 InnoDB: Initializing buffer pool, size = 128.0M
230616 11:02:40 InnoDB: Completed initialization of buffer pool
230616 11:02:40 InnoDB: highest supported file format is Barracuda.
230616 11:02:40  InnoDB: Waiting for the background threads to 
start
230616 11:02:41 Percona XtraDB (http://www.percona.com) 5.5.61- 
MariaDB-38.13 started; log sequence number 5956557136
230616 11:02:42 [Note] Plugin 'FEEDBACK' is disabled.
230616 11:02:42 [Note] Server socket created on IP: '0.0.0.0'.
230616 11:02:42 [Note] Event Scheduler: Loaded 0 events
230616 11:02:42 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.68-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  
port: 3306  MariaDB Server

调优建议 #

核心内存参数调整（针对InnoDB） #

• 调整InnoDB缓冲池大小：从日志看当前默认innodb_buffer_pool_size为128M，8GB内存服务器可分配2-3GB给该参数（预留足够内存给系统、WordPress及其他进程）。在[mysqld]段添加：

  innodb_buffer_pool_size = 2G
  

  该参数是InnoDB最关键的内存配置，缓存表数据和索引，能大幅减少磁盘IO，避免内存不足。

连接相关内存参数优化 #

• 降低单连接内存分配：sort_buffer_size、read_buffer_size、read_rnd_buffer_size是每个连接都会分配的内存，并发连接多时会累积占用内存，建议调整为：

  sort_buffer_size = 256K
  read_buffer_size = 128K
  read_rnd_buffer_size = 256K
  

• 提升max_allowed_packet：当前1M过小，WordPress处理媒体文件或大查询时容易触发限制，调整为：

  max_allowed_packet = 64M
  

• 增大table_open_cache：当前64不足以支撑WordPress多表场景，调整为：

  table_open_cache = 256
  

MyISAM相关参数调整（若未使用可降低） #

WordPress默认使用InnoDB，若站点无MyISAM表，可降低MyISAM相关内存参数：

key_buffer_size = 8M
myisam_sort_buffer_size = 4M

其他优化建议 #

• 启用innodb_flush_log_at_trx_commit = 2：平衡数据安全性和性能，减少磁盘IO压力（若对数据一致性要求极高可保持默认1）。
• 添加innodb_log_file_size = 256M：增大InnoDB日志文件，减少checkpoint频率，提升性能。
• 重启MariaDB服务使配置生效：执行systemctl restart mariadb

内容的提问来源于stack exchange，提问作者Don2