【MySQL深入详解】第08篇：CPU与内存选型——MySQL服务器的硬件配置

xyghehehehe
0 阅读8分钟

开篇引入

"老板让我采购一台MySQL服务器，你帮看看配置？" "32核CPU够不够用？" "256G内存配多大磁盘？" "SSD用哪种好，PCIe还是SATA？"

这些问题几乎每个DBA都会遇到。《高性能MySQL》第4章专门讲了硬件和操作系统优化，今天我们聊最核心的两个：CPU和内存。

MySQL的性能瓶颈在哪

MySQL的性能瓶颈通常来自三个方面：

1. CPU瓶颈：查询太复杂，大量排序、聚合计算，CPU打满。

2. I/O瓶颈：数据太多，内存装不下，大量磁盘读写。

3. 内存瓶颈：配置不当，内存溢出，OOM。

实际生产中，I/O瓶颈最常见（尤其是用机械硬盘时），CPU瓶颈次之，纯粹的内存瓶颈相对少见。

CPU选型：核数 vs 主频

这是最容易被搞混的问题。

两个目标，两种选择

低延迟场景（追求单个查询快）：选择高主频的CPU

  • 每个查询只用到一个CPU核心
  • 主频越高，单核性能越强
  • 适合OLTP小事务

高吞吐场景（追求并发处理能力）：选择多核的CPU

  • MySQL可以并行处理多个查询
  • 更多核心 = 更高并发
  • 适合复杂分析查询

Intel vs AMD

Intel Xeon Scalable（至强可扩展）
├── Bronze: 入门级，8核以下
├── Silver: 主流，12-24核
├── Gold: 高性能，24核以上
└── Platinum: 顶级，28核以上

AMD EPYC（霄龙）
├── 7002系列：最高64核
├── 7003系列：Zen3架构，更强单核性能
└── 性价比通常更高

怎么看MySQL用不用CPU

-- 查看当前查询的CPU使用情况
SHOW PROCESSLIST;

-- 查看连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看正在运行的查询数（关键指标）
SHOW STATUS LIKE 'Threads_running';

-- 如果Threads_running接近CPU核数，说明CPU是瓶颈

实战：定位CPU瓶颈

-- 查看最耗CPU的查询
SELECT 
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    SUM_CPU_TIME/1000000000000 AS cpu_seconds,
    AVG_CPU_TIME/1000000000000 AS avg_cpu_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_CPU_TIME DESC
LIMIT 10;

⚠️ 注意：MySQL 8.0才支持SUM_CPU_TIME，旧版本看不到。

内存选型：容量配置的艺术

为什么内存重要

MySQL用内存做缓存。缓存命中时，从内存读数据；缓存未命中时，要从磁盘读。

磁盘I/O vs 内存访问：速度相差100-1000倍。

内存访问：纳秒级（~100ns）
SSD访问：微秒级（~100μs）
HDD访问：毫秒级（~10ms）

配置大内存不是为了"把数据都装进去"，而是为了减少磁盘I/O。

InnoDB Buffer Pool

Buffer Pool是InnoDB的核心内存区域，用来缓存表数据和索引。

-- 查看Buffer Pool使用情况
SHOW ENGINE INNODB STATUS\G

-- 或者查询performance_schema
SELECT 
    POOL_ID,
    FREE_BUFFERS,
    DATABASE_PAGES,
    MODIFIED_PAGES,
    PAGES_DATA,
    PAGES_DIRTY,
    PAGES_FLUSH,
    NUMBER_PAGES_READ,
    NUMBER_PAGES_WRITTEN
FROM performance_schema.INNODB_BUFFER_PAGE_LAZY;

推荐配置：

[mysqld]
# 生产环境：设置为可用内存的70-80%
innodb_buffer_pool_size = 128G

# 分成多个实例，减少锁竞争（建议不要超过实例数）
innodb_buffer_pool_instances = 8

# 预加载Buffer Pool（加速重启后的启动）
innodb_buffer_pool_load_at_startup = ON

工作集（Working Set）

不是所有数据都需要放内存。MySQL真正需要的，是工作集——经常被访问的那部分数据。

-- 查看哪些表被访问最多
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_READ,
    COUNT_WRITE,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY COUNT_READ DESC
LIMIT 20;

假设你的数据库有1TB数据，但80%的访问集中在20%的表上，那把热点数据放内存就够了，不需要买能装下整个数据库的内存。

内存配置检查清单

# 查看可用内存
free -h

# 查看MySQL进程内存使用
ps aux | grep mysqld

# 查看OOM killer日志（如果有内存问题）
dmesg | grep -i oom

常见配置错误：

[mysqld]
# 错误：Buffer Pool设置太大，超过物理内存
innodb_buffer_pool_size = 256G  # 实际只有128G内存

# 错误：多个内存配置项加起来超过物理内存
innodb_buffer_pool_size = 64G
key_buffer_size = 32G
query_cache_size = 16G
tmp_table_size = 16G
# 总共128G，超过物理内存！

推荐配置原则

1. Buffer Pool = 物理内存 × 70%

可用内存  合理Buffer Pool
128G      90G
256G      180G
512G      350G

保留30%给：操作系统缓存、连接、临时表、其他MySQL内存区域。

2. 其他内存配置项

[mysqld]
# MyISAM索引缓存（如果还用MyISAM）
key_buffer_size = 32M

# 临时表最大内存
tmp_table_size = 256M
max_heap_table_size = 256M

# 连接内存
max_connections = 2000
# 预估：max_connections × sort_buffer_size × 2
# 2000 × 2M × 2 = 8G

SSD vs HDD：性能差距有多大

2015年后，SSD基本成了MySQL服务器的标配。

性能对比

                随机读取IOPS    顺序读取吞吐
HDD (7200RPM)   ~100            ~150 MB/s
SATA SSD        ~50,000         ~550 MB/s
NVMe SSD        ~500,000+       ~3,500 MB/s

SSD的优势：

  • 随机I/O性能提升100-1000倍
  • 支持更多并发连接
  • 消除I/O等待导致的查询延迟尖刺

SSD选购要点

1. 看DWPD（每日全盘写入次数）

DWPD = 1: 消费级，适合开发测试
DWPD = 3: 企业级，适合中等写入负载
DWPD = 10+: 高耐久型，适合高写入场景

MySQL的写入主要是：binlog、redo log、数据页刷新。选择DWPD≥3的企业级SSD。

2. 看容量和性能关系

SSD越满，性能越差（垃圾回收影响）。

160GB SSD满载 ← 性能可能只有320GB SSD满载的一半

建议：购买SSD容量 = 预计使用量的2倍

3. RAID配置建议

数据盘：RAID 10（性能+冗余）
日志盘：RAID 10 或 RAID 1（顺序写入为主）
备选：独立NVMe SSD作为日志盘

写入放大（Write Amplification）

SSD的特殊现象：实际写入量 > 应用写入量。

举例：写入4KB数据
1. SSD需要擦除512KB块
2. 把块内其他数据读出来
3. 合并新数据
4. 写回整个块
结果：实际写入512KB
写入放大率 = 128倍

优化建议：

  • 不要把SSD写满，保留30%空闲空间
  • 开启TRIM支持
  • 合理配置InnoDB刷新策略

典型服务器配置方案

入门级（开发测试）

CPU: 8核 (Intel i7/Xeon Bronze)
内存: 32G DDR4
存储: 1TB SATA SSD
用途: 个人学习、小项目
预算: ~1万

小规模生产

CPU: 16核 (Xeon Silver)
内存: 128G DDR4
存储: 2TB NVMe SSD (RAID 1)
用途: 10-50并发，中小业务
预算: ~5万

中规模生产

CPU: 32-64核 (Xeon Gold/EPYC)
内存: 256-512G DDR4 ECC
存储: 4TB NVMe SSD (RAID 10)
用途: 100-500并发，中型业务
预算: ~15-30万

大规模生产

CPU: 128+核 (EPYC 7742/Intel Xeon Platinum)
内存: 1TB+ DDR4 ECC
存储: 多盘NVMe SSD + 高速存储
用途: 1000+并发，大型业务
预算: 50万+

性能监控与容量规划

判断当前瓶颈

-- CPU瓶颈特征
-- Threads_running 高，且持续上升
SHOW STATUS LIKE 'Threads_running';

-- I/O瓶颈特征
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';
-- 如果这个值持续 > 0，说明Buffer Pool需要从磁盘读取

-- 内存瓶颈特征
SHOW GLOBAL STATUS LIKE 'Aborted_connects';
SHOW VARIABLES LIKE 'max_connections';

容量规划公式

方法1：基于TPS估算

# 假设单线程sysbench测试结果
TPS = 1000

# 需要达到10000 TPS
# 需要并发线程数 = 10000 / 1000 = 10
# 如果单核处理1000 TPS，需要10核CPU

方法2：基于内存估算

数据总量 = 500GB
热点数据比例 = 20%（100GB）
预留Buffer Pool = 100GB × 1.2 = 120GB

结论：至少配置128GB内存

小结

  1. CPU选型看场景：低延迟选高主频，高吞吐选多核
  2. 内存是缓存，不是存储：配置够放下工作集即可
  3. Buffer Pool占大头：通常设置为物理内存的70-80%
  4. SSD是标配：NVMe SSD性能最强，但也要考虑DWPD和容量
  5. 不要只看单指标：CPU、内存、I/O需要平衡配置
  6. 持续监控：定期检查瓶颈，调整配置

硬件选型没有标准答案，关键是匹配业务特点。TPCC 10000的OLTP系统和TB级数据分析系统，硬件配置思路完全不同。

上一篇：【第07篇】基准测试方法论——如何科学评估MySQL性能 下一篇：【第09篇】文件系统与磁盘IO——让MySQL数据写入飞起来

延伸阅读

  • 《高性能MySQL》第4章 操作系统和硬件优化
  • InnoDB Buffer Pool配置文档
  • Intel/AMD CPU选型指南
  • SSD评测网站：StorageReview、Tom's Hardware
avatar
文章
阅读
粉丝