MySQL教程 / 第 210 节

第21章：硬件与操作系统优化

硬件是性能的基础，操作系统是性能的保障

21.1 硬件选型

21.1.1 CPU选择

CPU核心数：

✅ 多核心优于高频率
✅ 推荐：8核心以上
✅ MySQL可以利用多核心并行处理

CPU架构：

✅ x86_64架构
✅ Intel Xeon或AMD EPYC
✅ 支持AES-NI（加密加速）

性能指标：

# 查看CPU信息
lscpu
cat /proc/cpuinfo

# 查看CPU核心数
grep -c processor /proc/cpuinfo

# 查看CPU型号
grep "model name" /proc/cpuinfo | head -1

# CPU性能测试
sysbench cpu --cpu-max-prime=20000 run

建议：

小型：4核心
中型：8-16核心
大型：16-32核心

21.1.2 内存选择

内存大小：

✅ 越大越好
✅ 推荐：物理内存 > 数据库大小 * 1.5
✅ Buffer Pool = 物理内存 * 60%-70%

内存类型：

✅ DDR4或DDR5
✅ ECC内存（纠错内存）
✅ 高频率（2666MHz+）

性能指标：

# 查看内存信息
free -h
cat /proc/meminfo

# 查看内存详细信息
dmidecode -t memory

# 内存性能测试
sysbench memory --memory-total-size=10G run

建议：

小型：8-16GB
中型：32-64GB
大型：128GB+

21.1.3 磁盘选择 ⭐⭐⭐⭐⭐

磁盘类型对比：

类型	IOPS	延迟	价格	推荐度
HDD	100-200	10-20ms	低	❌ 不推荐
SATA SSD	10K-50K	0.1-1ms	中	⭐⭐⭐
NVMe SSD	100K-1M	0.01-0.1ms	高	⭐⭐⭐⭐⭐

SSD的优势：

✅ IOPS高（100倍于HDD）
✅ 延迟低（1/100于HDD）
✅ 随机读写性能好
✅ 适合数据库

RAID配置：

RAID 0：性能最好，无冗余（不推荐）
RAID 1：镜像，冗余好，性能一般
RAID 5：性能和冗余平衡（写性能差）
RAID 10：性能和冗余都好（推荐）⭐⭐⭐⭐⭐

磁盘性能测试：

# 顺序读写测试
dd if=/dev/zero of=/tmp/test bs=1M count=1024
dd if=/tmp/test of=/dev/null bs=1M

# 随机读写测试（推荐）
sysbench fileio --file-total-size=10G prepare
sysbench fileio --file-total-size=10G \
  --file-test-mode=rndrw \
  --time=60 \
  --max-requests=0 run
sysbench fileio --file-total-size=10G cleanup

# 使用fio测试
fio -filename=/tmp/test -direct=1 -iodepth 1 -thread \
  -rw=randrw -ioengine=psync -bs=16k -size=10G \
  -numjobs=10 -runtime=60 -group_reporting -name=mytest

建议：

数据文件：NVMe SSD + RAID 10
日志文件：NVMe SSD
备份文件：HDD（成本低）

21.1.4 网络选择

网卡：

✅ 千兆网卡（1Gbps）：小型
✅ 万兆网卡（10Gbps）：中大型
✅ 双网卡绑定（提高带宽和可用性）

网络性能测试：

# 测试网络带宽
iperf3 -s  # 服务端
iperf3 -c server_ip  # 客户端

# 测试网络延迟
ping server_ip

# 测试MySQL网络性能
sysbench oltp_read_write \
  --mysql-host=remote_host \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=test \
  --tables=10 \
  --table-size=100000 \
  prepare

21.2 操作系统选择

21.2.1 Linux发行版选择

推荐发行版：

✅ CentOS/RHEL：企业级，稳定（推荐）⭐⭐⭐⭐⭐
✅ Ubuntu Server：易用，社区活跃
✅ Debian：稳定
❌ Windows：不推荐（性能差）

版本选择：

CentOS 7/8 或 RHEL 7/8
Ubuntu 20.04 LTS 或 22.04 LTS

查看系统信息：

# 查看发行版
cat /etc/os-release
lsb_release -a

# 查看内核版本
uname -r

# 查看系统位数
uname -m

21.3 操作系统优化 ⭐⭐⭐⭐⭐

21.3.1 文件系统选择

文件系统对比：

文件系统	性能	稳定性	推荐度
ext3	一般	好	❌
ext4	好	好	⭐⭐⭐⭐
XFS	很好	很好	⭐⭐⭐⭐⭐
Btrfs	好	一般	⭐⭐⭐

推荐：XFS

✅ 高性能
✅ 支持大文件
✅ 日志文件系统
✅ CentOS 7默认文件系统

挂载选项优化：

# /etc/fstab
/dev/sdb1 /var/lib/mysql xfs noatime,nodiratime,nobarrier 0 0

# 选项说明：
# noatime：不更新访问时间（提高性能）
# nodiratime：不更新目录访问时间
# nobarrier：禁用写屏障（SSD或有RAID卡缓存时）

# 重新挂载
mount -o remount /var/lib/mysql

# 查看挂载选项
mount | grep mysql

21.3.2 I/O调度器优化

I/O调度器类型：

CFQ（Completely Fair Queuing）：默认，适合HDD
Deadline：适合数据库
NOOP：适合SSD/NVMe（推荐）⭐⭐⭐⭐⭐

查看和修改I/O调度器：

# 查看当前I/O调度器
cat /sys/block/sda/queue/scheduler

# 临时修改
echo noop > /sys/block/sda/queue/scheduler

# 永久修改（/etc/default/grub）
GRUB_CMDLINE_LINUX="elevator=noop"

# 更新grub
grub2-mkconfig -o /boot/grub2/grub.cfg

# 重启生效
reboot

建议：

SSD/NVMe：noop
HDD：deadline

21.3.3 内核参数优化

优化内核参数（/etc/sysctl.conf）：

# 网络优化
net.core.somaxconn = 65535  # 最大连接队列
net.core.netdev_max_backlog = 65535  # 网卡接收队列
net.ipv4.tcp_max_syn_backlog = 65535  # SYN队列
net.ipv4.tcp_fin_timeout = 10  # FIN超时时间
net.ipv4.tcp_tw_reuse = 1  # 重用TIME_WAIT连接
net.ipv4.tcp_tw_recycle = 0  # 不回收TIME_WAIT（避免NAT问题）
net.ipv4.ip_local_port_range = 1024 65535  # 端口范围

# 内存优化
vm.swappiness = 0  # 禁用swap（重要）⭐⭐⭐⭐⭐
vm.dirty_ratio = 10  # 脏页比例
vm.dirty_background_ratio = 5  # 后台刷新脏页比例

# 文件系统优化
fs.file-max = 6553560  # 最大文件句柄数
fs.aio-max-nr = 1048576  # 异步I/O最大数

# 应用配置
sysctl -p  # 立即生效

# 查看配置
sysctl -a | grep swappiness

重点：禁用swap

# 为什么要禁用swap？
# MySQL使用swap会导致性能急剧下降

# 临时禁用
swapoff -a

# 永久禁用（注释/etc/fstab中的swap行）
# /dev/mapper/centos-swap swap swap defaults 0 0

# 查看swap使用情况
free -h
swapon -s

21.3.4 文件句柄限制

修改文件句柄限制（/etc/security/limits.conf）：

# 添加以下内容
mysql soft nofile 65535
mysql hard nofile 65535
mysql soft nproc 65535
mysql hard nproc 65535

# 或者
* soft nofile 65535
* hard nofile 65535
* soft nproc 65535
* hard nproc 65535

# 查看当前限制
ulimit -n
ulimit -u

# 查看MySQL进程的限制
cat /proc/$(pidof mysqld)/limits

修改systemd服务限制（/etc/systemd/system/mysqld.service.d/limits.conf）：

[Service]
LimitNOFILE=65535
LimitNPROC=65535

# 重新加载
systemctl daemon-reload
systemctl restart mysqld

21.3.5 透明大页（THP）

禁用透明大页：

# 为什么要禁用？
# THP会导致MySQL性能下降和内存碎片

# 查看THP状态
cat /sys/kernel/mm/transparent_hugepage/enabled

# 临时禁用
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

# 永久禁用（/etc/rc.local）
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
chmod +x /etc/rc.d/rc.local

# 或者修改grub（/etc/default/grub）
GRUB_CMDLINE_LINUX="transparent_hugepage=never"
grub2-mkconfig -o /boot/grub2/grub.cfg
reboot

21.3.6 NUMA优化

什么是NUMA？

Non-Uniform Memory Access
多CPU系统中，每个CPU有自己的本地内存
访问本地内存快，访问远程内存慢

查看NUMA信息：

# 查看NUMA节点
numactl --hardware

# 查看MySQL进程的NUMA分布
numastat -p $(pidof mysqld)

NUMA优化：

# 方法1：禁用NUMA（推荐）
# /etc/default/grub
GRUB_CMDLINE_LINUX="numa=off"
grub2-mkconfig -o /boot/grub2/grub.cfg
reboot

# 方法2：使用numactl启动MySQL
numactl --interleave=all mysqld_safe &

# 方法3：在my.cnf中配置
[mysqld_safe]
numa-interleave = 1

21.4 性能监控工具

21.4.1 系统监控工具

top/htop：

# top：查看系统资源使用情况
top

# 关注指标：
# %CPU：CPU使用率
# %MEM：内存使用率
# load average：系统负载

# htop：更友好的top
htop

# 安装htop
yum install htop  # CentOS
apt install htop  # Ubuntu

iostat：

# 查看I/O统计
iostat -x 1

# 关注指标：
# %util：磁盘使用率（>80%说明I/O瓶颈）
# await：平均等待时间（SSD<1ms，HDD<10ms）
# r/s, w/s：每秒读写次数

# 安装
yum install sysstat

vmstat：

# 查看系统统计
vmstat 1

# 关注指标：
# si, so：swap in/out（应该为0）
# bi, bo：block in/out
# us, sy, id, wa：CPU使用率

sar：

# 系统活动报告
sar -u 1 10  # CPU使用率
sar -r 1 10  # 内存使用率
sar -d 1 10  # 磁盘I/O
sar -n DEV 1 10  # 网络流量

# 查看历史数据
sar -f /var/log/sa/sa10  # 查看10号的数据

dstat：

# 综合监控工具
dstat -cdngy

# 安装
yum install dstat

21.4.2 MySQL专用监控工具

mysqladmin：

# 查看状态
mysqladmin -u root -p status

# 查看变量
mysqladmin -u root -p variables

# 查看进程列表
mysqladmin -u root -p processlist

# 持续监控
mysqladmin -u root -p -i 1 status

mytop：

# 类似top的MySQL监控工具
mytop -u root -p password

# 安装
yum install mytop

innotop：

# InnoDB监控工具
innotop -u root -p password

# 安装
yum install innotop

pt-query-digest：

# 慢查询分析（前面已介绍）
pt-query-digest /var/lib/mysql/slow.log

Percona Monitoring and Management (PMM)：

# 企业级监控方案
# 包含：
# - 性能监控
# - 查询分析
# - 图形化界面
# - 告警功能

# 安装PMM Server（Docker）
docker run -d -p 80:80 \
  --name pmm-server \
  percona/pmm-server:2

# 安装PMM Client
yum install pmm2-client

# 配置
pmm-admin config --server-url=http://pmm-server:80

# 添加MySQL监控
pmm-admin add mysql --username=root --password=password

21.5 性能基准测试

21.5.1 sysbench基准测试

安装sysbench：

# CentOS
yum install sysbench

# Ubuntu
apt install sysbench

# 从源码安装（最新版本）
git clone https://github.com/akopytov/sysbench.git
cd sysbench
./autogen.sh
./configure
make
make install

CPU测试：

sysbench cpu --cpu-max-prime=20000 run

内存测试：

sysbench memory --memory-total-size=10G run

磁盘测试：

# 准备测试文件
sysbench fileio --file-total-size=10G prepare

# 随机读写测试
sysbench fileio --file-total-size=10G \
  --file-test-mode=rndrw \
  --time=60 \
  --max-requests=0 run

# 清理
sysbench fileio --file-total-size=10G cleanup

MySQL OLTP测试：

# 准备测试数据
sysbench oltp_read_write \
  --mysql-host=localhost \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=test \
  --tables=10 \
  --table-size=100000 \
  prepare

# 运行测试
sysbench oltp_read_write \
  --mysql-host=localhost \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=test \
  --tables=10 \
  --table-size=100000 \
  --threads=16 \
  --time=60 \
  --report-interval=10 \
  run

# 清理
sysbench oltp_read_write \
  --mysql-host=localhost \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=test \
  --tables=10 \
  cleanup

只读测试：

sysbench oltp_read_only \
  --mysql-host=localhost \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=test \
  --tables=10 \
  --table-size=100000 \
  --threads=16 \
  --time=60 \
  run

只写测试：

sysbench oltp_write_only \
  --mysql-host=localhost \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=test \
  --tables=10 \
  --table-size=100000 \
  --threads=16 \
  --time=60 \
  run

21.5.2 mysqlslap基准测试

# MySQL自带的压测工具

# 自动生成测试数据
mysqlslap --auto-generate-sql \
  --auto-generate-sql-load-type=mixed \
  --auto-generate-sql-add-autoincrement \
  --number-of-queries=1000 \
  --concurrency=50 \
  --user=root \
  --password=password

# 使用自定义SQL
mysqlslap --create-schema=test \
  --query="SELECT * FROM users WHERE id = 1" \
  --number-of-queries=1000 \
  --concurrency=50 \
  --user=root \
  --password=password

# 测试并发性能
mysqlslap --auto-generate-sql \
  --concurrency=10,50,100,200 \
  --number-of-queries=1000 \
  --iterations=5 \
  --user=root \
  --password=password

21.6 优化检查清单

21.6.1 硬件检查清单

✅ CPU：8核心以上
✅ 内存：物理内存 > 数据库大小 * 1.5
✅ 磁盘：NVMe SSD + RAID 10
✅ 网络：万兆网卡（中大型）
✅ RAID卡：带缓存和电池

21.6.2 操作系统检查清单

✅ 操作系统：CentOS/RHEL 7/8
✅ 文件系统：XFS
✅ 挂载选项：noatime,nodiratime,nobarrier
✅ I/O调度器：noop（SSD）
✅ swap：禁用（vm.swappiness=0）
✅ 透明大页：禁用
✅ NUMA：禁用或interleave
✅ 文件句柄：65535
✅ 内核参数：优化网络和内存参数

21.6.3 MySQL配置检查清单

✅ innodb_buffer_pool_size：物理内存的60%-70%
✅ innodb_flush_log_at_trx_commit：1
✅ sync_binlog：1
✅ innodb_flush_method：O_DIRECT
✅ innodb_file_per_table：1
✅ innodb_io_capacity：根据磁盘类型设置
✅ max_connections：根据需求设置
✅ thread_cache_size：64-128
✅ table_open_cache：4096-8192
✅ slow_query_log：开启
✅ skip-name-resolve：开启

21.7 性能优化案例

21.7.1 案例1：I/O瓶颈优化

问题：

查询慢
iostat显示%util接近100%
await很高

分析：

# 查看I/O情况
iostat -x 1

# 结果：
# %util: 98%
# await: 50ms
# 说明I/O是瓶颈

解决方案：

升级到SSD
增加Buffer Pool大小
优化慢查询
使用RAID 10

效果：

%util降到30%
await降到1ms
查询速度提升10倍

21.7.2 案例2：内存不足优化

问题：

频繁使用swap
查询慢
系统负载高

分析：

# 查看内存使用
free -h

# 结果：
# Swap: 2G used
# 说明内存不足

解决方案：

增加物理内存
减小Buffer Pool大小
禁用swap
优化查询，减少内存使用

效果：

swap使用为0
查询速度提升5倍

21.7.3 案例3：CPU瓶颈优化

问题：

CPU使用率100%
查询慢
大量慢查询

分析：

# 查看CPU使用
top

# 查看慢查询
pt-query-digest /var/lib/mysql/slow.log

解决方案：

优化慢查询（添加索引）
升级CPU
读写分离
分库分表

效果：

CPU使用率降到30%
慢查询减少90%

21.8 本章总结

本章学习内容：

✅ 硬件选型（CPU、内存、磁盘、网络）⭐⭐⭐⭐⭐
✅ 操作系统选择
✅ 操作系统优化（文件系统、I/O调度器、内核参数）⭐⭐⭐⭐⭐
✅ 性能监控工具
✅ 性能基准测试
✅ 优化检查清单
✅ 性能优化案例

重点掌握：

磁盘选择：NVMe SSD + RAID 10
禁用swap（vm.swappiness=0）
禁用透明大页
I/O调度器：noop（SSD）
文件系统：XFS + noatime
文件句柄：65535

硬件选型：

CPU：8核心以上
内存：物理内存 > 数据库大小 * 1.5
磁盘：NVMe SSD + RAID 10
网络：万兆网卡

操作系统优化：

禁用swap
禁用透明大页
I/O调度器：noop
文件系统：XFS
挂载选项：noatime,nodiratime

性能监控：

iostat：I/O监控
vmstat：系统监控
sar：历史数据
sysbench：基准测试

面试重点：

为什么要禁用swap
为什么要禁用透明大页
SSD应该使用什么I/O调度器
如何进行性能基准测试
如何诊断I/O瓶颈

下一章预告： 主从复制

练习题

为什么推荐使用SSD而不是HDD？
RAID 10有什么优势？
为什么要禁用swap？
什么是透明大页？为什么要禁用？
SSD应该使用什么I/O调度器？
如何查看磁盘I/O使用情况？
如何使用sysbench进行MySQL基准测试？
文件句柄限制应该设置为多少？
什么是NUMA？如何优化？
如何诊断系统性能瓶颈？

继续学习： 第22章：主从复制