数据库服务器应该配置多大内存？不同业务场景的容量规划

采购数据库服务器时最常被问到的一句话是："这台机器配多大内存合适？"常见回答是"越大越好"或者"看预算"，这两种都无法落地。数据库是典型的内存敏感型服务：InnoDB 的 buffer pool 决定了有多少比例的请求不需要碰磁盘，PostgreSQL 的 shared_buffers 加系统 page cache 决定了同样的查询是一次 IO 还是零次 IO，而 Redis 干脆就是纯内存服务。本文给出可直接套用的公式和判断阈值，覆盖 MySQL、PostgreSQL、Redis 三大场景，并说明什么时候"加内存"是解药、什么时候它不是。

一、先算数据集大小，再谈内存

内存的黄金法则是：让热点数据集尽量驻留在内存里。所以第一步是把"热点数据集"量化。

1.1 MySQL：data + index

-- 各库数据量汇总（data_length 为数据、index_length 为索引）
SELECT table_schema,
       ROUND(SUM(data_length)/1024/1024, 1)     AS data_mb,
       ROUND(SUM(index_length)/1024/1024, 1)    AS index_mb,
       ROUND(SUM(data_length+index_length)/1024/1024, 1) AS total_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','sys','performance_schema','information_schema')
GROUP BY table_schema
ORDER BY total_mb DESC;

-- 单实例的索引/数据占比（索引比例过高说明索引冗余）
SELECT ROUND(SUM(index_length)/SUM(data_length+index_length)*100, 2) AS index_pct
FROM information_schema.tables WHERE engine = 'InnoDB';

-- 找出最大的 10 张表
SELECT table_schema, table_name, table_rows,
       ROUND((data_length+index_length)/1024/1024/1024, 2) AS size_gb
FROM information_schema.tables
WHERE engine = 'InnoDB'
ORDER BY (data_length+index_length) DESC LIMIT 10;

拿到 total_mb 后要乘以一个热点系数。多数 OLTP 系统的热点数据占总量的 20%-40%（近期订单、活跃用户），少数是全量热（字典表、配置表，但体积小）。因此：

理想 buffer pool 大小 ≈ 数据集总量 × 热点系数（0.3 - 1.0）+ 未来 12 个月增长量 × 1.3

1.2 PostgreSQL：库大小与索引大小

SELECT pg_size_pretty(pg_database_size('appdb')) AS db_size;
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS total,
       pg_size_pretty(pg_relation_size('orders'))       AS heap,
       pg_size_pretty(pg_indexes_size('orders'))        AS index_size;
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

1.3 Redis：直接看 used_memory

redis-cli INFO memory | grep -E 'used_memory:|used_memory_human:|used_memory_peak_human:'
redis-cli INFO keyspace

二、MySQL 的内存预算公式

MySQL 的内存消耗可以拆成"全局缓冲"与"会话级缓冲"两块：

实例总内存需求
  = innodb_buffer_pool_size                        【全局，最大头】
  + innodb_log_buffer_size                         【默认 16MB】
  + key_buffer_size                                【仅 MyISAM，默认 8MB，InnoDB 环境可留很小】
  + query_cache_size                               【MySQL 8.0 已移除；5.7 建议关闭】
  + max_connections × thread_stack                 【每线程栈，64 位 Linux 约 256KB-280KB】
  + 活跃连接数 × (sort_buffer_size + join_buffer_size + read_buffer_size + read_rnd_buffer_size)
  + 并发临时表数 × MIN(tmp_table_size, max_heap_table_size)
  + performance_schema 开销                        【默认有，百来 MB 起】
  + 2GB（操作系统、网络缓冲、page cache、文件系统元数据）

一个典型的估算例子：一台 32GB 内存的服务器，承载 1TB 数据、热点约 120GB 的订单系统。

• innodb_buffer_pool_size = 32GB × 70% ≈ 22GB
• max_connections = 500，thread_stack 256KB → 约 128MB
• 活跃连接按 50 算，会话缓冲（256K×4 种 × 50）≈ 51MB
• tmp_table_size 16MB × 并发 10 → 160MB（按最坏上限算）
• log buffer 16MB，key_buffer 8MB，performance_schema 约 200MB
• 合计约 22.6GB + OS 预留 2GB ≈ 25GB，32GB 内存有余量，配置成立

如果把 max_connections 提到 2000，会话缓冲与线程栈会多出 400MB 以上；若并发排序/连接数再上去，很容易突破 32GB 触发 OOM。这就是"调大连接数必须重算内存"的原因。

2.1 innodb_buffer_pool_size 的建议值与例外

通用建议是物理内存的 60%-75%：低于 60% 会造成内存浪费（磁盘还有 spare 能力），高于 80% 时 OS 的 page cache、网络缓冲区、临时表空间、连接缓冲都可能不够，风险陡增。例外场景：服务器上还跑着 Redis、应用中间件或备份进程时，必须降到 50% 以下。

2.2 判断内存够不够的三个指标

-- 1) buffer pool 命中率（OLTP 应 > 99%）
SELECT ROUND((1 - r.cnt / q.cnt) * 100, 3) AS hit_pct FROM
 (SELECT VARIABLE_VALUE AS cnt FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') r,
 (SELECT VARIABLE_VALUE AS cnt FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests') q;

-- 2) 脏页刷写等待（大于 0 说明 buffer pool 偏小或 IO 跟不上）
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';

-- 3) 临时表与排序是否落到磁盘
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';

命中率低于 98% 且 Innodb_buffer_pool_reads 持续增长，说明热数据放不下，应该加内存或优化 SQL（减少扫描行数）。Innodb_buffer_pool_wait_free > 0 说明需要等脏页刷完才能读入新页，属于明显的 IO + 内存双重压力。Created_tmp_disk_tables / Created_tmp_tables 的比值超过 10% 时，优先考虑的是改 SQL 而不是加 tmp_table_size。

2.3 Buffer Pool 命中率：公式、判读与处置

这是判断"内存够不够"最重要的一条公式，建议做成监控项长期保留：

命中率 = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests

分子 Innodb_buffer_pool_reads        ：无法从缓冲池满足、真正落盘读取的次数
分母 Innodb_buffer_pool_read_requests：逻辑读（向缓冲池发起请求）的总次数

判读口径与对应动作：

• ≥ 99%：健康。典型 OLTP 在运行一段时间后应稳定在这个区间。
• 98% - 99%：观察区。先看磁盘读 IOPS 是否也高；如果 Innodb_buffer_pool_reads 折算后是每秒几千次以上，就需要扩容或裁减扫描量。
• < 98%：警戒。先做 SQL 侧减法（加索引、减少 SELECT *、消除全表扫描），把读写到的数据量降下来；做不到再加内存。
• < 95%：基本可以确定数据集远超内存，加服务器内存的同时要考虑归档历史数据与垂直拆分。
• 刚启动就偏低：不要慌，实例重启初期缓冲池是空的，需要跑一段时间预热；大实例可以开启预热机制减少抖动。

开启预热（MySQL 5.6+，会在关闭/开启时保存和恢复热点页信息）
[mysqld]
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON

手工立刻触发一次预热/落盘
SET GLOBAL innodb_buffer_pool_dump_now = ON;
SET GLOBAL innodb_buffer_pool_load_now  = ON;
SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';

一个必须注意的误区：单纯提高 innodb_buffer_pool_size 不一定提升命中率。如果低命中率是"一条全表扫描 SQL 反复把冷数据刷进缓冲池"造成的，加内存只是把污染的量放大，命中率会在短暂改善后回到原处。正确的顺序是：先看慢查询日志把扫描行数最多的 SQL 找出来优化，再评估扩容。

三、PostgreSQL 与 Redis 的内存规划

3.1 PostgreSQL

PostgreSQL 依赖"自身的 shared_buffers + 操作系统 page cache"的双层缓存，所以它给自己的缓冲区通常不给太多：

• shared_buffers：Linux 上建议物理内存的 25%，一般不超过 8GB（超过之后收益递减，部分负载甚至因管理开销变大而变慢）。
• effective_cache_size：设为物理内存的 50%-75%，这是一个"告诉优化器有多少页可能已在缓存中"的提示值，并不真实申请内存。
• work_mem：默认 4MB。极端重要的一点——它按每次排序/哈希操作分配，一个 SQL 内可能多次申请，乘以并发连接数就是最坏情况。可以按公式估算：work_mem × 每查询平均排序数 × 活跃连接数，不要让它超过剩余内存的 25%。
• maintenance_work_mem：默认 64MB，VACUUM / CREATE INDEX 使用，建议 256MB-1GB，但同一时刻通常只有一个维护操作，风险比 work_mem 小。

-- 检查是否有异常占用
SELECT * FROM pg_stat_activity WHERE state = 'active';
SHOW shared_buffers; SHOW work_mem; SHOW maintenance_work_mem; SHOW effective_cache_size;

3.2 Redis

Redis 最特殊，需要给 fork 的写时复制预留空间：

实例 maxmemory 上限 ≈ 物理内存 × 45% - 65%

典型画像：
  8GB  机器 → maxmemory 4GB
  16GB 机器 → maxmemory 8GB
  32GB 机器 → maxmemory 16GB
  64GB 机器 → maxmemory 32GB

写密集且有 AOF 的实例取下限（45%），纯读缓存可以取上限（65%）。若一台机器上部署多个 Redis 实例，除了自身 maxmemory，还要预留 repl-backlog-size（默认 1MB，主从切换时可能撑到几十 MB）、客户端输出缓冲区（replica 默认硬限制 256MB）。

四、操作系统侧：别让内核参数毁掉你的规划

# 1) swap 倾向：数据库服务器建议设为 1（MySQL/Redis/PG 通用建议），0 在某些版本内核下有副作用
echo 'vm.swappiness = 1' >> /etc/sysctl.conf
sysctl -p

# 2) 脏页回写：避免一次性大量回写造成 IO 抖动
echo 'vm.dirty_background_ratio = 5'  >> /etc/sysctl.conf
echo 'vm.dirty_ratio = 20'            >> /etc/sysctl.conf

# 3) Redis 必须设置，否则 fork 可能因地址空间检查失败
echo 'vm.overcommit_memory = 1'       >> /etc/sysctl.conf

# 4) MySQL 建议关闭双缓冲的唯一方式是用 O_DIRECT
# my.cnf: innodb_flush_method = O_DIRECT

# 5) 检查是否发生过 OOM
dmesg -T | grep -i -E 'oom|killed process'
grep -i 'killed process' /var/log/messages

NUMA 架构的服务器还需要额外注意：默认的 NUMA 策略可能导致 MySQL 把大量内存分配在某一个节点的本地内存上，而这个节点内存耗尽后就开始 swap（即使整机还有大量空闲）。常见做法是：

numactl --interleave=all mysqld_safe ...     # 或用 systemd 的 ExecStart 前缀
numactl --hardware
grep -i numa /var/log/mysqld.log

五、两个特殊场景的处理

5.1 场景一：数据量远大于内存怎么办

很多团队会因为"数据 2TB、买不起 2TB 内存"而焦虑。实际上不需要让全部数据进内存，只要让热点数据进去即可。判断和控制手段：

• 用索引把"扫描 500 万行"变成"先通过索引定位、再回表几十行"，被读取的页数量会下降一到两个数量级。
• 分区或归档历史数据：把一年前的订单按月做 PARTITION BY RANGE 或迁到归档库，让活跃数据集缩小到原来的 1/5。
• 避免把大字段（TEXT/BLOB/JSON 大文档）放进频繁参与扫描的表，MySQL 在读取包含长字段的页时代价更高；可拆到附表。
• 用 innodb_io_capacity 与合适的 slow query 阈值观察 IO 压力是否落在预期范围内。

5.2 场景二：一台机器多个实例的分配原则

测试环境或小型业务常常要在同一台机器上跑 MySQL + Redis，或多个 MySQL 实例。此时总内存预算应按"先分配所有非 MySQL 组件，剩下的再按 60%-75% 给每个实例的 buffer pool"来做。举例：32GB 机器上跑一个 Redis（maxmemory 6GB）、一个 MySQL、操作系统预留 4GB，则可给 MySQL 的自由空间约 22GB，innodb_buffer_pool_size 保守设为 14GB，而不是按 32GB × 70% = 22GB 来算——后者一定会出问题。多实例场景还应该用 cgroup 或 systemd 的 MemoryMax 给每个实例设定硬上限，避免相互挤兑：

# 给 MySQL 实例设定内存硬上限（systemd override）
# /etc/systemd/system/mysqld.service.d/memory.conf
[Service]
MemoryMax=24G
MemoryHigh=20G

# Redis 实例同样如此
systemctl set-property redis.service MemoryMax=8G

六、什么时候加内存没用

七、容量规划落地步骤

1. 量化当前数据集：用 information_schema / pg_total_relation_size / INFO memory 取总量与索引占比。
2. 估算热点比例：结合慢查询日志与业务特性，取 0.3-1.0 的热点系数。
3. 叠加未来 12 个月增长：按月增长率推算，建议预留 1.3 倍余量。
4. 套用预算公式：MySQL 按 60%-75% 给 buffer pool；PostgreSQL 按 25% 给 shared_buffers；Redis 按 45%-65% 给 maxmemory。
5. 扣除非数据类开销：连接缓冲、临时表、performance_schema、OS 的 2GB 预留。
6. 核对运行中的实际情况：命中率、wait_free、tmp_disk_tables、mem_fragmentation_ratio。
7. 检查 OS 参数：swappiness、dirty_ratio、overcommit_memory、NUMA 策略。
8. 对 Redis 单独核对 fork 开销：latest_fork_usec 与 AOF/RDB 重写期间的内存增量。
9. 用压测工具验证：sysbench（MySQL）、pgbench（PostgreSQL）、redis-benchmark（Redis），观察 QPS 与 P99 延迟。
10. 写入容量基线文档，并设定扩容阈值：内存使用率超过 85%、命中率低于 98%、或磁盘读 IOPS 持续高位，即触发扩容评估。

小结

给数据库服务器配内存不是"越大越好"，而是"让热点数据装得下、同时给操作系统和其他组件留够空间"。MySQL 的核心参数是 innodb_buffer_pool_size，取物理内存的 60%-75%，命中率要盯到 99% 以上；PostgreSQL 的核心参数是 shared_buffers（约 25%，通常不超过 8GB）与容易失控的 work_mem；Redis 的核心则是给 fork 的写时复制留出余量，maxmemory 一般不超过物理内存的 65%。避坑清单：不要只加内存不改索引（全表扫描会把任何规格的内存都搅热）；不要把 swappiness 设成 0 就以为万事大吉；不要忽略 NUMA 造成的"有内存却在 swap"；不要给 Redis 设置超过物理内存 80% 的 maxmemory。