# 编辑my.cnf配置文件
vi /etc/my.cnf

[mysqld]
# IO线程配置
innodb_read_io_threads=16
innodb_write_io_threads=16

# IO能力配置（根据存储设备性能调整）
innodb_io_capacity=4000
innodb_io_capacity_max=8000

# 缓冲池刷新策略
innodb_flush_method=O_DIRECT

# 双写缓冲区
innodb_doublewrite=ON

# 随机读预读
innodb_random_read_ahead=ON

# 自适应哈希索引
innodb_adaptive_hash_index=ON

# 保存并重启MySQL
systemctl restart mysqld

# 验证配置
mysql -u root -p -e “SHOW VARIABLES LIKE ‘innodb_read_io_threads’; SHOW VARIABLES LIKE ‘innodb_write_io_threads’; SHOW VARIABLES LIKE ‘innodb_io_capacity%’; SHOW VARIABLES LIKE ‘innodb_flush_method’;”
Enter password: Fgedu123!
+—————————+——-+
| Variable_name | Value |
+—————————+——-+
| innodb_read_io_threads | 16 |
+—————————+——-+
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| innodb_write_io_threads | 16 |
+—————————-+——-+
+————————–+——-+
| Variable_name | Value |
+————————–+——-+
| innodb_io_capacity | 4000 |
| innodb_io_capacity_max | 8000 |
+————————–+——-+
+————————+———–+
| Variable_name | Value |
+————————+———–+
| innodb_flush_method | O_DIRECT |
+————————+———–+

# 编辑my.cnf配置文件
vi /etc/my.cnf

[mysqld]
# 重做日志配置
innodb_log_file_size=2G
innodb_log_files_in_group=2
innodb_log_buffer_size=32M

# 事务提交日志刷新策略
# 1：每次提交都刷新（最安全）
# 2：每秒刷新一次（性能与安全平衡）
# 0：依赖操作系统刷新（性能最高，安全性最低）
innodb_flush_log_at_trx_commit=1

# 二进制日志同步策略
# 1：每次提交都同步（最安全）
# 0：依赖操作系统同步（性能最高）
sync_binlog=1

# 保存并重启MySQL
systemctl restart mysqld

# 验证配置
mysql -u root -p -e “SHOW VARIABLES LIKE ‘innodb_log%’; SHOW VARIABLES LIKE ‘innodb_flush_log_at_trx_commit’; SHOW VARIABLES LIKE ‘sync_binlog’;”
Enter password: Fgedu123!
+—————————+———-+
| Variable_name | Value |
+—————————+———-+
| innodb_log_buffer_size | 33554432 |
| innodb_log_file_size | 2147483648 |
| innodb_log_files_in_group | 2 |
+—————————+———-+
+——————————–+——-+
| Variable_name | Value |
+——————————–+——-+
| innodb_flush_log_at_trx_commit | 1 |
+——————————–+——-+
+—————+——-+
| Variable_name | Value |
+—————+——-+
| sync_binlog | 1 |
+—————+——-+

# 编辑my.cnf配置文件
vi /etc/my.cnf

[mysqld]
# 系统表空间配置
innodb_data_file_path=ibdata1:128M:autoextend

# 独立表空间（推荐）
innodb_file_per_table=ON

# 表空间加密（可选）
innodb_encrypt_tables=OFF

# 大页内存
innodb_use_native_aio=ON

# 保存并重启MySQL
systemctl restart mysqld

# 验证配置
mysql -u root -p -e “SHOW VARIABLES LIKE ‘innodb_data_file_path’; SHOW VARIABLES LIKE ‘innodb_file_per_table’; SHOW VARIABLES LIKE ‘innodb_use_native_aio’;”
Enter password: Fgedu123!
+———————–+————————+
| Variable_name | Value |
+———————–+————————+
| innodb_data_file_path | ibdata1:128M:autoextend |
+———————–+————————+
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| innodb_file_per_table | ON |
+———————–+——-+
+—————————+——-+
| Variable_name | Value |
+—————————+——-+
| innodb_use_native_aio | ON |
+—————————+——-+

# 1. 文件系统挂载选项优化
# 编辑/etc/fstab文件
vi /etc/fstab

# 添加或修改MySQL数据目录的挂载选项
/dev/sdb1 /var/lib/mysql xfs defaults,noatime,nodiratime,barrier=0,logbufs=8 0 2

# 重新挂载文件系统
mount -o remount /var/lib/mysql

# 验证挂载选项
mount | grep /var/lib/mysql
/dev/sdb1 on /var/lib/mysql type xfs (rw,noatime,nodiratime,barrier=0,logbufs=8)

# 2. IO调度器优化
# 查看当前IO调度器
cat /sys/block/sdb/queue/scheduler
[deadline] cfq none

# 设置IO调度器为deadline
echo deadline > /sys/block/sdb/queue/scheduler

# 3. 调整IO读写比例
echo 50 > /sys/block/sdb/queue/iosched/read_expire
echo 3000 > /sys/block/sdb/queue/iosched/write_expire

# 4. 禁用访问时间记录
# 查看当前状态
mount | grep /var/lib/mysql
# 确保包含noatime和nodiratime选项

学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 IO性能监控实战

# 1. 系统级IO监控
# 使用iostat监控磁盘IO
iostat -x 1
Linux 4.18.0-348.el8.x86_64 (mysql-server) 03/15/2024 _x86_64_ (16 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
2.31 0.00 1.25 0.42 0.00 96.01

Device r/s w/s rkB/s wkB/s rrqm/s wrqm/s %rrqm %wrqm r_await w_await aqu-sz rareq-sz wareq-sz svctm %util
sda 0.12 0.16 2.40 2.56 0.00 0.00 0.00 0.00 1.67 1.94 0.00 20.00 16.00 0.50 0.01
sdb 12.34 15.67 493.60 626.80 0.12 2.34 0.97 13.06 2.12 1.87 0.05 40.00 40.00 0.42 1.19

# 使用pidstat监控MySQL进程IO
pidstat -d -p $(pgrep -f mysqld) 1
Linux 4.18.0-348.el8.x86_64 (mysql-server) 03/15/2024 _x86_64_ (16 CPU)

14:32:45 UID PID kB_rd/s kB_wr/s kB_ccwr/s iodelay Command
14:32:46 996 1234 123.4 56.7 0.0 0 mysqld
14:32:47 996 1234 145.6 78.9 0.0 0 mysqld

# 2. MySQL内部IO监控
# 查看InnoDB IO状态
mysql -u root -p -e “SHOW GLOBAL STATUS LIKE ‘Innodb_data%’; SHOW GLOBAL STATUS LIKE ‘Innodb_pages%’; SHOW GLOBAL STATUS LIKE ‘Innodb_os_log%’;”
Enter password: Fgedu123!

# 查看InnoDB引擎状态
mysql -u root -p -e “SHOW ENGINE INNODB STATUS\G”
Enter password: Fgedu123!

# 3. Performance Schema监控
# 查看IO等待事件
mysql -u root -p -e “SELECT event_name, sum_timer_wait/1000000000 as total_time_ms, count_star FROM performance_schema.events_waits_summary_global_by_event_name WHERE event_name LIKE ‘%io%’ ORDER BY sum_timer_wait DESC LIMIT 10;”
Enter password: Fgedu123!

# 查看文件IO统计
mysql -u root -p -e “SELECT file_name, sum_number_of_bytes_read, sum_number_of_bytes_write FROM performance_schema.file_summary_by_instance ORDER BY sum_number_of_bytes_read DESC LIMIT 10;”
Enter password: Fgedu123!

# 案例：IO等待时间长，导致查询响应慢

# 1. 诊断问题
# 查看系统IO等待
mpstat 1
Linux 4.18.0-348.el8.x86_64 (mysql-server) 03/15/2024 _x86_64_ (16 CPU)

14:32:45 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
14:32:46 all 5.23 0.00 3.12 75.34 0.00 0.12 0.00 0.00 0.00 16.19

# 查看MySQL慢查询
mysqldumpslow -s t /var/log/mysql/slow.log
Reading mysql slow query log from /var/log/mysql/slow.log
Count: 50 Time=20.50s (1025s) Lock=0.00s (0s) Rows=1000000.0 (50000000), root[root]@localhost
SELECT * FROM large_table WHERE created_at < '2024-01-01' # 2. 实施优化 # 方案1：升级存储设备（从HDD到SSD） # 更换存储设备后测试IO性能 fio --name=random-read --ioengine=libaio --rw=randread --bs=4k --size=10G --numjobs=4 --runtime=60 --group_reporting # 方案2：优化SQL查询，添加索引 mysql -u root -p -e "ALTER TABLE large_table ADD INDEX idx_created_at (created_at);" Enter password: Fgedu123! # 方案3：增加缓冲池大小，减少IO vi /etc/my.cnf innodb_buffer_pool_size=24G # 从16G增加到24G systemctl restart mysqld # 3. 验证优化效果 # 再次查看系统IO等待 mpstat 1 # 查看查询性能提升 mysql -u root -p -e "EXPLAIN SELECT * FROM large_table WHERE created_at < '2024-01-01';" Enter password: Fgedu123!

# 案例：写入操作性能差，事务提交慢

# 1. 诊断问题
# 查看InnoDB日志状态
mysql -u root -p -e “SHOW GLOBAL STATUS LIKE ‘Innodb_os_log%’;”
Enter password: Fgedu123!
+—————————+——-+
| Variable_name | Value |
+—————————+——-+
| Innodb_os_log_written | 1234567890 |
| Innodb_os_log_fsyncs | 56789 |
| Innodb_os_log_pending_writes | 10 |
| Innodb_os_log_pending_fsyncs | 5 |
+—————————+——-+

# 2. 实施优化
# 方案1：调整日志配置
vi /etc/my.cnf
innodb_log_file_size=4G # 从2G增加到4G
innodb_log_files_in_group=2
innodb_log_buffer_size=64M # 从32M增加到64M

# 方案2：优化事务提交策略（平衡性能和安全性）
# 从最安全的1改为性能更好的2
innodb_flush_log_at_trx_commit=2
sync_binlog=100

# 保存并重启MySQL
# 注意：修改innodb_log_file_size需要先删除旧日志文件
systemctl stop mysqld
rm -f /var/lib/mysql/ib_logfile*
systemctl start mysqld

# 3. 验证优化效果
# 查看写入性能提升
mysql -u root -p -e “SHOW GLOBAL STATUS LIKE ‘Innodb_os_log_fsyncs’;”
Enter password: Fgedu123!

# 使用sysbench测试写入性能
sysbench –test=oltp_write_only –db-driver=mysql –mysql-host=127.0.0.1 –mysql-user=root –mysql-password=Fgedu123! –mysql-db=testdb –tables=10 –table-size=1000000 –threads=16 –time=60 run

风哥提示：更多学习视频公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 IO配置最佳实践

# 1. IO等待高
## 可能原因
– 存储设备性能不足
– 查询复杂，全表扫描多
– 缓冲池大小不足，导致频繁IO
– 锁竞争导致线程等待

## 解决方案
– 升级到SSD或NVMe SSD
– 优化SQL语句，添加合适索引
– 增加innodb_buffer_pool_size
– 优化锁机制，减少锁竞争
– 考虑读写分离或分片

# 2. 写入性能差
## 可能原因
– 日志配置不合理
– 写入频繁，事务小而多
– 存储设备写入性能不足
– 锁竞争严重

## 解决方案
– 调整innodb_log_file_size和innodb_log_buffer_size
– 优化事务提交策略
– 使用批量插入减少IO次数
– 升级存储设备
– 优化锁设计，减少锁竞争

# 3. 临时表IO高
## 可能原因
– 查询复杂，需要大量排序或分组
– tmp_table_size和max_heap_table_size设置过小
– 内存不足，导致临时表溢出到磁盘

## 解决方案
– 优化查询，减少排序和分组操作
– 增加tmp_table_size和max_heap_table_size
– 增加服务器内存
– 考虑使用内存表或Redis缓存结果

# 4. 表空间碎片
## 可能原因
– 频繁的删除和更新操作
– 表结构设计不合理
– 存储引擎特性导致

## 解决方案
– 定期使用OPTIMIZE TABLE优化表
– 重建表：ALTER TABLE table_name ENGINE=InnoDB
– 合理设计表结构，避免频繁更新大字段
– 使用合适的存储引擎

# 5. IOPS达到上限
## 可能原因
– 存储设备IOPS不足
– 并发请求过多
– 查询效率低下，导致不必要的IO

## 解决方案
– 升级存储设备或增加存储设备数量
– 优化查询，减少IO操作
– 实现读写分离，分散IO负载
– 使用缓存减少数据库访问

# 1. IO性能测试工具
# 使用sysbench测试文件IO
sysbench –test=fileio –file-total-size=10G –file-test-mode=rndrw –max-time=300 –max-requests=0 run

# 使用fio测试随机读写性能
fio –name=random-rw –ioengine=libaio –rw=randrw –bs=4k –size=10G –numjobs=4 –runtime=60 –group_reporting

# 使用fio测试顺序读写性能
fio –name=sequential-rw –ioengine=libaio –rw=rw –bs=64k –size=10G –numjobs=4 –runtime=60 –group_reporting

# 2. MySQL IO性能测试
# 使用sysbench测试OLTP性能
sysbench –test=oltp_read_write –db-driver=mysql –mysql-host=127.0.0.1 –mysql-user=root –mysql-password=Fgedu123! –mysql-db=testdb –tables=10 –table-size=1000000 –threads=16 –time=60 run

# 使用mysqlslap测试并发性能
mysqlslap –concurrency=100 –iterations=10 –create-schema=testdb –query=”SELECT * FROM users WHERE id BETWEEN 1 AND 100″ –number-of-queries=10000

# 3. 调优流程
# 监控 → 分析 → 优化 → 测试 → 验证 → 监控

# 监控：使用iostat、pidstat、MySQL状态变量监控IO
# 分析：找出IO瓶颈和热点
# 优化：根据分析结果调整配置或硬件
# 测试：使用基准测试工具验证优化效果
# 验证：在生产环境中验证性能提升
# 监控：持续监控IO性能，及时发现问题

风哥提示：更多视频教程www.fgedu.net.cn

from MySQL:www.itpux.com