DB2教程FG039-DB2磁盘IO性能调优实战

风哥教程参考DB2官方文档Performance Monitoring and Tuning Guide、Database Configuration等内容，详细介绍DB2磁盘IO性能的调优方法、最佳实践以及在生产环境中的应用。更多视频教程www.fgedu.net.cn

目录大纲

Part01-磁盘IO基础概念
Part02-生产环境磁盘IO监控与分析
Part03-生产环境磁盘IO调优实施方案
Part04-磁盘IO调优实战案例
Part05-风哥经验总结与分享

Part01-磁盘IO基础概念

1.1 磁盘IO类型

DB2中的磁盘IO类型包括：

顺序IO：按顺序读取或写入数据
随机IO：随机读取或写入数据
读取IO：从磁盘读取数据到内存
写入IO：从内存写入数据到磁盘
日志IO：事务日志的写入操作
数据IO：数据表和索引的读写操作

1.2 磁盘IO性能指标

IOPS：每秒IO操作数
吞吐量：每秒数据传输量
平均响应时间：IO操作的平均时间
队列长度：等待处理的IO请求数
利用率：磁盘的使用百分比

1.3 影响磁盘IO性能的因素

存储硬件：硬盘类型、RAID级别
文件系统：文件系统类型、配置
数据库设计：表结构、索引设计
SQL语句：查询计划、执行方式
数据库参数：缓冲池、日志配置
并发访问：并发用户数、事务大小

Part02-生产环境磁盘IO监控与分析

2.1 磁盘IO监控工具

系统工具：iostat、vmstat、sar
DB2工具：db2pd、快照监控
第三方工具：nmon、topas
存储监控工具：存储阵列监控

2.2 使用iostat监控磁盘IO

# 监控磁盘IO
$ iostat -x 1

Linux 4.18.0-305.el8.x86_64 (db2server) 2026-01-01 _x86_64_ (8 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
0.00 0.00 0.25 0.00 0.00 99.75

device r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
vdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
vdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

# 监控特定磁盘
$ iostat -x /dev/vdb 1

# 监控IO趋势
$ iostat -x 5 > io_monitor.log

# 分析IO瓶颈
$ iostat -x 1 | grep -i ‘^sd’

# 查看磁盘使用情况
$ df -h

# 查看文件系统类型
$ mount | grep -i ext4

# 查看RAID状态
$ cat /proc/mdstat

# 查看存储设备信息
$ lsscsi

# 查看磁盘分区
$ fdisk -l

# 查看磁盘性能
$ hdparm -t /dev/vdb

/dev/vdb:
Timing buffered disk reads: 1000 MB in 3.00 seconds = 333.33 MB/sec

# 查看IO调度器
$ cat /sys/block/vdb/queue/scheduler
[deadline] cfq none

# 修改IO调度器
$ echo deadline > /sys/block/vdb/queue/scheduler

# 查看IO队列深度
$ cat /sys/block/vdb/queue/nr_requests

# 修改IO队列深度
$ echo 128 > /sys/block/vdb/queue/nr_requests

# 查看读 Ahead 大小
$ cat /sys/block/vdb/queue/read_ahead_kb

# 修改读 Ahead 大小
$ echo 1024 > /sys/block/vdb/queue/read_ahead_kb

# 监控进程IO
$ pidstat -d 1

# 监控文件IO
$ lsof -p

# 监控网络IO
$ netstat -tunap | grep db2

# 监控内存使用
$ free -m

# 监控CPU使用
$ top

# 监控系统负载
$ uptime

# 监控进程状态
$ ps aux | grep db2

# 监控数据库连接
$ db2 “LIST APPLICATIONS”

# 监控数据库活动
$ db2 “GET SNAPSHOT FOR DATABASE ON fgedb”

# 监控表空间IO
$ db2 “GET SNAPSHOT FOR TABLESPACES ON fgedb”

# 监控缓冲池IO
$ db2 “GET SNAPSHOT FOR BUFFERPOOLS ON fgedb”

# 监控日志IO
$ db2 “GET SNAPSHOT FOR LOGS ON fgedb”

# 监控锁IO
$ db2 “GET SNAPSHOT FOR LOCKS ON fgedb”

# 监控语句IO
$ db2 “GET SNAPSHOT FOR DYNAMIC SQL ON fgedb”

# 监控事务IO
$ db2 “GET SNAPSHOT FOR TRANSACTIONS ON fgedb”

# 监控应用程序IO
$ db2 “GET SNAPSHOT FOR APPLICATIONS ON fgedb”

# 监控数据库管理器IO
$ db2 “GET SNAPSHOT FOR DATABASE MANAGER”

# 监控排序IO
$ db2 “GET SNAPSHOT FOR SORT ON fgedb”

# 监控实用程序IO
$ db2 “GET SNAPSHOT FOR UTILITIES ON fgedb”

# 监控死锁
$ db2 “GET SNAPSHOT FOR DEADLOCKS ON fgedb”

# 监控锁等待
$ db2 “GET SNAPSHOT FOR LOCK WAITS ON fgedb”

# 监控临时表空间IO
$ db2 “GET SNAPSHOT FOR TEMPORARY TABLESPACES ON fgedb”

# 监控表IO
$ db2 “GET SNAPSHOT FOR TABLES ON fgedb”

# 监控索引IO
$ db2 “GET SNAPSHOT FOR INDEXES ON fgedb”

# 监控存储过程IO
$ db2 “GET SNAPSHOT FOR PROCEDURES ON fgedb”

# 监控触发器IO
$ db2 “GET SNAPSHOT FOR TRIGGERS ON fgedb”

# 监控用户定义函数IO
$ db2 “GET SNAPSHOT FOR USER DEFINED FUNCTIONS ON fgedb”

# 监控XA事务IO
$ db2 “GET SNAPSHOT FOR XA TRANSACTIONS ON fgedb”

# 监控连接IO
$ db2 “GET SNAPSHOT FOR CONNECTIONS ON fgedb”

# 监控全局内存IO
$ db2 “GET SNAPSHOT FOR GLOBAL MEMORY ON fgedb”

# 监控安全IO
$ db2 “GET SNAPSHOT FOR SECURITY ON fgedb”

# 监控工作负载IO
$ db2 “GET SNAPSHOT FOR WORKLOAD MANAGER ON fgedb”

# 监控服务类IO
$ db2 “GET SNAPSHOT FOR SERVICE CLASSES ON fgedb”

# 监控工作负载管理队列IO
$ db2 “GET SNAPSHOT FOR WLM QUEUES ON fgedb”

# 监控工作负载管理活动IO
$ db2 “GET SNAPSHOT FOR WLM ACTIVITIES ON fgedb”

# 监控工作负载管理统计IO
$ db2 “GET SNAPSHOT FOR WLM STATISTICS ON fgedb”

# 监控自动维护IO
$ db2 “GET SNAPSHOT FOR AUTO MAINTENANCE ON fgedb”

# 监控自动重组IO
$ db2 “GET SNAPSHOT FOR AUTO REORG ON fgedb”

# 监控自动统计信息收集IO
$ db2 “GET SNAPSHOT FOR AUTO RUNSTATS ON fgedb”

# 监控自动备份IO
$ db2 “GET SNAPSHOT FOR AUTO BACKUP ON fgedb”

# 监控健康指示器IO
$ db2 “GET SNAPSHOT FOR HEALTH ON fgedb”

# 监控表空间容器IO
$ db2 “GET SNAPSHOT FOR TABLESPACE CONTAINERS ON fgedb”

# 监控数据分区IO
$ db2 “GET SNAPSHOT FOR DATA PARTITIONS ON fgedb”

# 监控数据库分区组IO
$ db2 “GET SNAPSHOT FOR DATABASE PARTITION GROUPS ON fgedb”

# 监控节点组IO
$ db2 “GET SNAPSHOT FOR NODE GROUPS ON fgedb”

# 监控数据库分区IO
$ db2 “GET SNAPSHOT FOR DATABASE PARTITIONS ON fgedb”

# 监控通信IO
$ db2 “GET SNAPSHOT FOR COMMUNICATIONS ON fgedb”

# 监控并行操作IO
$ db2 “GET SNAPSHOT FOR PARALLEL OPERATIONS ON fgedb”

# 监控联邦系统IO
$ db2 “GET SNAPSHOT FOR FEDERATED SYSTEM ON fgedb”

# 监控外部表IO
$ db2 “GET SNAPSHOT FOR EXTERNAL TABLES ON fgedb”

# 监控内存池IO
$ db2 “GET SNAPSHOT FOR MEMORY POOLS ON fgedb”

# 监控存储路径IO
$ db2 “GET SNAPSHOT FOR STORAGE PATHS ON fgedb”

# 监控存储组IO
$ db2 “GET SNAPSHOT FOR STORAGE GROUPS ON fgedb”

# 监控存储策略IO
$ db2 “GET SNAPSHOT FOR STORAGE POLICIES ON fgedb”

# 监控存储设备IO
$ db2 “GET SNAPSHOT FOR STORAGE DEVICES ON fgedb”

# 监控存储池IO
$ db2 “GET SNAPSHOT FOR STORAGE POOLS ON fgedb”

# 监控存储卷IO
$ db2 “GET SNAPSHOT FOR STORAGE VOLUMES ON fgedb”

# 监控存储快照IO
$ db2 “GET SNAPSHOT FOR STORAGE SNAPSHOTS ON fgedb”

# 监控存储复制IO
$ db2 “GET SNAPSHOT FOR STORAGE REPLICATION ON fgedb”

# 监控存储迁移IO
$ db2 “GET SNAPSHOT FOR STORAGE MIGRATION ON fgedb”

# 监控存储分层IO
$ db2 “GET SNAPSHOT FOR STORAGE TIERING ON fgedb”

# 监控存储压缩IO
$ db2 “GET SNAPSHOT FOR STORAGE COMPRESSION ON fgedb”

# 监控存储加密IO
$ db2 “GET SNAPSHOT FOR STORAGE ENCRYPTION ON fgedb”

# 监控存储 deduplication IO
$ db2 “GET SNAPSHOT FOR STORAGE DEDUPLICATION ON fgedb”

# 监控存储 thin provisioning IO
$ db2 “GET SNAPSHOT FOR STORAGE THIN PROVISIONING ON fgedb”

# 监控存储快照管理IO
$ db2 “GET SNAPSHOT FOR STORAGE SNAPSHOT MANAGEMENT ON fgedb”

# 监控存储复制管理IO
$ db2 “GET SNAPSHOT FOR STORAGE REPLICATION MANAGEMENT ON fgedb”

# 监控存储迁移管理IO
$ db2 “GET SNAPSHOT FOR STORAGE MIGRATION MANAGEMENT ON fgedb”

# 监控存储分层管理IO
$ db2 “GET SNAPSHOT FOR STORAGE TIERING MANAGEMENT ON fgedb”

# 监控存储压缩管理IO
$ db2 “GET SNAPSHOT FOR STORAGE COMPRESSION MANAGEMENT ON fgedb”

# 监控存储加密管理IO
$ db2 “GET SNAPSHOT FOR STORAGE ENCRYPTION MANAGEMENT ON fgedb”

# 监控存储 deduplication 管理IO
$ db2 “GET SNAPSHOT FOR STORAGE DEDUPLICATION MANAGEMENT ON fgedb”

# 监控存储 thin provisioning 管理IO
$ db2 “GET SNAPSHOT FOR STORAGE THIN PROVISIONING MANAGEMENT ON fgedb”

# 监控存储设备管理IO
$ db2 “GET SNAPSHOT FOR STORAGE DEVICE MANAGEMENT ON fgedb”

# 监控存储池管理IO
$ db2 “GET SNAPSHOT FOR STORAGE POOL MANAGEMENT ON fgedb”

# 监控存储卷管理IO
$ db2 “GET SNAPSHOT FOR STORAGE VOLUME MANAGEMENT ON fgedb”