印刻数据库

外观

Oracle I/O 优化 ​

I/O 优化的概念 ​

I/O 优化是指通过合理的存储配置、参数调整和文件布局，减少数据库的 I/O 等待时间，提高 I/O 吞吐量，从而提升数据库性能。在 Oracle 数据库中，I/O 操作是最常见的性能瓶颈之一，尤其是在处理大量数据时。

I/O 相关的性能指标 ​

  • I/O 等待时间：进程等待 I/O 操作完成的时间
  • I/O 吞吐量：单位时间内完成的 I/O 操作量
  • I/O 操作数：每秒的 I/O 操作次数（IOPS）
  • 平均响应时间：I/O 操作的平均响应时间
  • I/O 队列长度：等待处理的 I/O 请求数量

I/O 瓶颈的表现 ​

  • 高 I/O 等待事件：如 db file sequential read、db file scattered read 等
  • 慢查询：查询执行时间长
  • 数据库响应慢：应用程序响应时间长
  • 高 CPU 使用率：CPU 等待 I/O 完成

存储配置优化 ​

1. 存储硬件选择 ​

硬盘类型 ​

  • HDD：机械硬盘，容量大，成本低，适合顺序 I/O
  • SSD：固态硬盘，速度快，成本高，适合随机 I/O
  • NVMe：非易失性内存 express，速度更快，适合高并发场景

存储架构 ​

  • DAS：直接附加存储，适合小型数据库
  • NAS：网络附加存储，适合文件共享
  • SAN：存储区域网络，适合大型数据库
  • 云存储：如 AWS EBS、Azure Blob Storage 等

2. RAID 配置 ​

RAID 级别选择 ​

RAID 级别特点适用场景
RAID 0条带化，无冗余，性能高临时表空间、测试环境
RAID 1镜像，冗余，读取性能高系统表空间、日志文件
RAID 5条带化+奇偶校验，有冗余一般数据文件
RAID 6条带化+双重奇偶校验，冗余更高重要数据文件
RAID 10镜像+条带化，性能和冗余兼顾高要求的生产环境

RAID 配置建议 ​

  • 系统表空间：RAID 10
  • 重做日志：RAID 1 或 RAID 10
  • 数据文件：根据重要性选择 RAID 5 或 RAID 10
  • 临时表空间：RAID 0 或 RAID 10
  • UNDO 表空间：RAID 10

3. 文件系统选择 ​

常见文件系统 ​

  • Linux：EXT4、XFS、Btrfs
  • Windows：NTFS
  • AIX：JFS2
  • Solaris：ZFS

文件系统优化 ​

  • 块大小：与数据库块大小匹配，如 8KB 或 16KB
  • 挂载选项：使用 noatime、nodiratime 等选项
  • 日志设置：调整文件系统日志设置
  • 缓冲区大小：调整文件系统缓冲区大小

4. ASM 配置 ​

ASM 优势 ​

  • 自动条带化：提高 I/O 性能
  • 自动镜像：提高数据可靠性
  • 动态重新平衡：自动平衡存储负载
  • 简化管理：统一管理存储

ASM 配置建议 ​

  • 磁盘组：根据用途创建不同的磁盘组
  • 条带大小：
    • 数据文件：1MB
    • 重做日志：128KB
    • 临时文件：1MB
  • 冗余级别：
    • 外部冗余：使用存储级 RAID
    • 正常冗余：双镜像
    • 高冗余：三镜像

I/O 调度优化 ​

1. 操作系统 I/O 调度器 ​

Linux I/O 调度器 ​

  • CFQ：完全公平队列，适合通用场景
  • Deadline：截止时间调度器，适合数据库
  • NOOP：电梯调度器，适合 SSD
  • BFQ：预算公平队列，适合多媒体

调度器选择建议 ​

  • HDD：Deadline 或 CFQ
  • SSD：NOOP 或 Deadline
  • 数据库：Deadline

配置方法 ​

bash
# 临时设置
echo deadline > /sys/block/sda/queue/scheduler

# 永久设置
# 在 /etc/default/grub 中添加
GRUB_CMDLINE_LINUX="elevator=deadline"
# 然后运行
update-grub
reboot

2. Oracle I/O 优化参数 ​

数据库参数 ​

  • db_file_multiblock_read_count：

    sql
    ALTER SYSTEM SET db_file_multiblock_read_count = 16 SCOPE=SPFILE;

  • disk_asynch_io：

    sql
    ALTER SYSTEM SET disk_asynch_io = TRUE SCOPE=SPFILE;

  • filesystemio_options：

    sql
    ALTER SYSTEM SET filesystemio_options = 'SETALL' SCOPE=SPFILE;

  • db_writer_processes：

    sql
    ALTER SYSTEM SET db_writer_processes = 4 SCOPE=SPFILE;

  • dbwr_io_slaves：

    sql
    ALTER SYSTEM SET dbwr_io_slaves = 4 SCOPE=SPFILE;

并行执行参数 ​

  • parallel_execution_message_size：

    sql
    ALTER SYSTEM SET parallel_execution_message_size = 16384 SCOPE=SPFILE;

  • parallel_io_cap_enabled：

    sql
    ALTER SYSTEM SET parallel_io_cap_enabled = TRUE SCOPE=SPFILE;

文件布局优化 ​

1. 数据文件布局 ​

分离不同类型的数据文件 ​

  • 系统表空间：单独存储
  • 用户表空间：根据应用分离
  • 临时表空间：使用高速存储
  • UNDO 表空间：单独存储
  • 索引表空间：与数据文件分离

多数据文件配置 ​

  • 为大型表空间创建多个数据文件
  • 将数据文件分布在不同的物理磁盘上
  • 确保数据文件大小相近

2. 日志文件布局 ​

重做日志优化 ​

  • 多个重做日志组：至少 3 个
  • 每个组多个成员：分布在不同的磁盘上
  • 合适的大小：根据事务量调整，一般 1GB-2GB
  • 避免日志切换频繁：监控日志切换频率

归档日志优化 ​

  • 单独存储：与数据文件分离
  • 快速存储：使用高速存储
  • 合理归档：配置自动归档
  • 定期清理：避免归档日志积压

3. 临时文件布局 ​

  • 使用高速存储：如 SSD
  • 多个临时文件：分布在不同的磁盘上
  • 合适的大小：根据排序需求调整
  • 自动扩展：配置自动扩展

数据库参数优化 ​

1. 内存参数 ​

  • SGA 大小：足够大，减少 I/O

    sql
    ALTER SYSTEM SET sga_target = 16G SCOPE=SPFILE;

  • PGA 大小：足够大，减少临时表空间 I/O

    sql
    ALTER SYSTEM SET pga_aggregate_target = 4G SCOPE=SPFILE;

  • buffer cache：足够大，减少物理 I/O

    sql
    ALTER SYSTEM SET db_cache_size = 8G SCOPE=SPFILE;

  • shared pool：足够大，减少硬解析

    sql
    ALTER SYSTEM SET shared_pool_size = 2G SCOPE=SPFILE;

2. 写入参数 ​

  • log_buffer：足够大，减少重做日志写入

    sql
    ALTER SYSTEM SET log_buffer = 16M SCOPE=SPFILE;

  • commit_write：异步提交

    sql
    ALTER SYSTEM SET commit_write = 'BATCH,ASYNC' SCOPE=SPFILE;

  • fast_start_mttr_target：调整恢复时间目标

    sql
    ALTER SYSTEM SET fast_start_mttr_target = 300 SCOPE=SPFILE;

3. 读取参数 ​

  • optimizer_mode：选择合适的优化器模式

    sql
    ALTER SYSTEM SET optimizer_mode = 'ALL_ROWS' SCOPE=SPFILE;

  • optimizer_index_caching：索引缓存百分比

    sql
    ALTER SYSTEM SET optimizer_index_caching = 90 SCOPE=SPFILE;

  • optimizer_index_cost_adj：索引成本调整

    sql
    ALTER SYSTEM SET optimizer_index_cost_adj = 10 SCOPE=SPFILE;

SQL 语句优化 ​

1. 减少 I/O 的 SQL 技巧 ​

  • 使用索引：避免全表扫描
  • 合理的 WHERE 子句：减少返回的数据量
  • **避免 SELECT ***：只选择需要的列
  • 使用绑定变量：减少硬解析
  • 合理的 JOIN 操作：选择合适的 JOIN 类型

2. 批量操作 ​

  • 批量插入：使用 INSERT ALL 或 FORALL
  • 批量更新：使用 MERGE 语句
  • 批量删除：使用分区删除或批量提交

3. 并行查询 ​

  • 启用并行查询：

    sql
    ALTER SESSION ENABLE PARALLEL DML;

  • 设置并行度：

    sql
    ALTER TABLE employees PARALLEL 4;

  • 使用并行提示：

    sql
    SELECT /*+ PARALLEL(4) */ * FROM employees;

I/O 监控与诊断 ​

1. 监控工具 ​

Oracle 内置工具 ​

  • AWR 报告：分析 I/O 等待事件
  • ASH 报告：分析活动会话的 I/O 等待
  • STATSPACK：收集性能统计信息
  • Enterprise Manager：图形化监控

操作系统工具 ​

  • iostat：监控磁盘 I/O 性能

    bash
    iostat -x 1

  • vmstat：监控虚拟内存和 I/O

    bash
    vmstat 1

  • sar：监控系统活动

    bash
    sar -d 1

  • iotop：监控进程 I/O

    bash
    iotop

2. 诊断 I/O 瓶颈 ​

识别 I/O 密集的 SQL ​

  • AWR 报告：查看 TOP SQL
  • V$SQL：分析 SQL 执行统计信息
    sql
    SELECT sql_id, elapsed_time, disk_reads, buffer_gets
FROM v$sql
ORDER BY disk_reads DESC
FETCH FIRST 10 ROWS ONLY;

识别 I/O 密集的数据文件 ​

  • V$FILESTAT：分析数据文件 I/O
    sql
    SELECT file#, name, phyrds, phywrts, readtim, writetim
FROM v$filestat f, v$datafile d
WHERE f.file# = d.file#
ORDER BY (readtim + writetim) DESC;

识别 I/O 等待事件 ​

  • V$SESSION_WAIT：分析会话等待事件
    sql
    SELECT event, count(*)
FROM v$session_wait
WHERE event LIKE 'db file%'
GROUP BY event
ORDER BY count(*) DESC;

I/O 优化的最佳实践 ​

1. 预规划 ​

  • 存储评估：评估存储需求和性能要求
  • I/O 模式分析：分析应用的 I/O 模式
  • 容量规划：合理规划存储容量
  • 性能基准：建立性能基准

2. 实施阶段 ​

  • 分层存储：使用不同性能的存储设备
  • 热数据识别：将热数据存储在高速存储上
  • 数据压缩：减少存储需求和 I/O
  • 分区表：使用分区表提高查询性能

3. 维护阶段 ​

  • 定期监控：定期监控 I/O 性能
  • 存储碎片整理：定期整理存储碎片
  • 统计信息更新：定期更新统计信息
  • 索引维护：定期重建碎片化索引
  • 存储扩展：根据需要扩展存储

4. 常见优化场景 ​

数据仓库 ​

  • 使用分区表：按时间或其他维度分区
  • 并行查询：启用并行查询
  • 批量加载：使用 SQL*Loader 或外部表
  • 聚合表：创建聚合表减少计算

OLTP 系统 ​

  • 使用索引：优化索引设计
  • 减少事务大小：缩短事务时间
  • 使用绑定变量：减少硬解析
  • 连接池：使用连接池减少连接开销

常见问题（FAQ） ​

Q1: 如何判断数据库是否存在 I/O 瓶颈？ ​

A1: 判断数据库是否存在 I/O 瓶颈的方法：

  • 检查等待事件：查看 AWR 报告中的 I/O 等待事件
  • 监控 I/O 性能：使用 iostat 等工具监控磁盘 I/O
  • 分析 SQL 语句：查看是否有大量磁盘读取的 SQL
  • 检查响应时间：应用程序响应时间是否变长

Q2: 如何选择合适的 RAID 级别？ ​

A2: 选择 RAID 级别的方法：

  • 考虑性能需求：随机 I/O 密集型选择 RAID 10
  • 考虑可靠性：重要数据选择 RAID 10 或 RAID 6
  • 考虑成本：根据预算选择合适的 RAID 级别
  • 考虑容量：RAID 5 和 RAID 6 容量利用率更高

Q3: SSD 和 HDD 如何结合使用？ ​

A3: SSD 和 HDD 结合使用的方法：

  • 热数据：将热数据存储在 SSD 上，如索引、临时表空间
  • 冷数据：将冷数据存储在 HDD 上，如历史数据
  • 分层存储：使用自动分层存储技术
  • 缓存：使用 SSD 作为 HDD 的缓存

Q4: 如何优化临时表空间的 I/O？ ​

A4: 优化临时表空间 I/O 的方法：

  • 使用 SSD：将临时表空间存储在 SSD 上
  • 多个临时文件：创建多个临时文件，分布在不同的磁盘上
  • 合适的大小：根据排序需求调整临时表空间大小
  • 监控使用情况：定期监控临时表空间使用情况

Q5: 如何优化重做日志的 I/O？ ​

A5: 优化重做日志 I/O 的方法：

  • 使用 RAID 1 或 RAID 10：提高写入性能和可靠性
  • 多个日志组：至少 3 个重做日志组
  • 合适的大小：根据事务量调整，一般 1GB-2GB
  • 分布在不同磁盘：将不同日志组的成员分布在不同的磁盘上
  • 避免日志切换频繁：监控日志切换频率，调整大小

Q6: 如何使用 ASM 提高 I/O 性能？ ​

A6: 使用 ASM 提高 I/O 性能的方法：

  • 合理的磁盘组：根据用途创建不同的磁盘组
  • 合适的条带大小：根据 I/O 模式调整条带大小
  • 适当的冗余：根据数据重要性选择冗余级别
  • 均衡负载：确保磁盘组中的磁盘负载均衡
  • 使用快速存储：将重要的磁盘组存储在高速存储上