4GB内存云主机生产环境 MariaDB/MySQL my.cnf 配置优化指南

2018-05-15 · Ryan · 发表评论

前言

本文档提供一份针对 4GB 内存云主机生产环境的 MariaDB/MySQL 数据库 my.cnf 配置文件示例与优化说明。该配置旨在平衡内存使用、连接性能和稳定性，适用于中小型 Web 应用场景。请注意，数据库配置需根据实际负载、数据量和硬件情况进行调整，切勿直接照搬。

核心配置详解

以下配置分为几个关键部分，并对重要参数进行说明。

基础路径与连接设置

首先，必须根据你的实际安装路径设置以下目录。请勿直接复制示例中的占位符。

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
user        = mysql  # 或 mariadb，取决于安装时创建的系统用户
basedir     = /usr/local/mysql  # 请替换为你的 MySQL 安装目录
datadir     = /var/lib/mysql    # 请替换为你的数据目录
log_error   = /var/log/mysql/error.log  # 请替换为你的错误日志路径
pid-file    = /var/run/mysqld/mysqld.pid # 请替换为你的 PID 文件路径

内存与连接优化 (针对 4GB 内存)

这是针对 4GB 内存主机的核心调优部分，主要控制内存分配和连接处理能力。

back_log = 384
max_connections = 300  # 原500对4GB内存偏高，调整为300更稳妥
thread_cache_size = 128  # 原256偏高，调整为128
table_open_cache = 1024
table_definition_cache = 1024

# 关键内存缓冲区设置
key_buffer_size = 256M  # 原1344M严重超标，仅用于MyISAM表。若主要使用InnoDB，可设为64M-128M
innodb_buffer_pool_size = 1536M  # 原128M过低，这是InnoDB最重要的缓存，建议设为物理内存的50%-70%，此处设为1.5GB
innodb_log_file_size = 256M  # 原32M过小，建议设为innodb_buffer_pool_size的25%左右
innodb_log_buffer_size = 16M

# 每个连接/线程的缓冲区 (谨慎设置，总和 = 每个连接内存 * max_connections)
sort_buffer_size = 2M  # 原256K
read_buffer_size = 1M  # 原256K
read_rnd_buffer_size = 1M  # 原512K
join_buffer_size = 2M  # 原128K
tmp_table_size = 64M   # 原256M，与max_heap_table_size配合，避免过大消耗内存
max_heap_table_size = 64M

# 查询缓存 (MySQL 5.7及以后版本已弃用，MariaDB 10.1.7后默认禁用，若使用请评估)
# query_cache_type = 0
# query_cache_size = 0

InnoDB 存储引擎配置

如果使用 InnoDB 作为主要存储引擎，以下配置至关重要。

default_storage_engine = InnoDB
innodb_file_per_table = ON
innodb_flush_log_at_trx_commit = 1  # 默认值，保证ACID，安全性最高，性能略有损耗
innodb_lock_wait_timeout = 50
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_buffer_pool_instances = 2  # 对于1.5GB的pool size，设置为2或4

二进制日志与复制

用于数据恢复和主从复制。

server-id   = 1
log_bin = /var/log/mysql/mysql-bin.log  # 请指定完整路径
expire_logs_days = 7
binlog_format = ROW  # 推荐使用ROW格式，数据一致性更好

其他优化与安全设置

skip_name_resolve = ON  # 禁止域名解析，加快连接速度
max_allowed_packet = 64M
wait_timeout = 600  # 非交互式连接超时时间（秒）
interactive_timeout = 1800  # 交互式连接超时时间（秒）
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /var/log/mysql/slow.log

配置检查与生效

1. 验证配置语法：使用命令 mysqld --verbose --help | grep -A 1 -B 1 "Default options" 查看配置文件读取顺序，然后使用 mysqld --defaults-file=/path/to/my.cnf --validate-config 检查语法（MariaDB/MySQL 5.7+）。

2. 重启数据库服务：修改配置后，需重启 MariaDB/MySQL 服务使配置生效。

3. 监控与调整：配置生效后，务必通过 SHOW GLOBAL STATUS;、SHOW ENGINE INNODB STATUSG 等命令监控数据库运行状态，并根据实际情况（如连接数、缓存命中率、慢查询）进一步微调参数。

重要提醒

内存总量控制：所有内存相关参数（尤其是全局缓冲区和每个连接的缓冲区）的总和必须小于物理内存，并为操作系统和其他应用预留足够空间（通常1GB左右）。
版本差异：不同版本的 MariaDB/MySQL 可能存在参数差异或默认值变化，请以官方文档为准。
备份原配置：修改前务必备份原始的 my.cnf 文件。

前言