Informix 性能调优案例讲解（转）

转自：http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0810niuxz/index.html

 

概述

在实际的生产运行环境中，笔者在国内很多客户现场都看到开发人员和系统管理人员遇到很多有关于 Informix 数据库引起的性能问题，进而被多次问起如何进行 Informix 数据库性能调优，笔者根据自己在工作中对 Informix 数据库的使用经验积累写下这篇文章。

 

性能优化原则

包括：

• 性能规划：深入了解应用与数据库的交互特征，确立良好的设计、开发、测试迭代过程，上线前消除模型上的性能瓶颈。
• 实例调优：建立性能基准，对比调节数据库、操作系统、存储、网络等的配置，主动监控、消除瓶颈。
• SQL 调优：书写高效 SQL，优化相关数据库对象，充分借助优化器，确定最佳执行计划。

 

性能优化流程

1. 首先执行下面的初始检查：
   • 获取直接用户的使用反馈，确定性能目标和范围。
   • 获取性能表现好与坏时的操作系统、数据库、应用统计信息。
   • 对数据库做一次全面健康检查。
2. 根据收集的信息，以及对应用特性的了解，构建性能概念模型，明确性能瓶颈所在，以及导致性能的根本原因。
   • 首先应该排除操作系统、硬件资源造成的瓶颈。
   • 然后针对数据库系统性能进行分析
   • 必要时，还需要检查应用日志，因为系统性能问题也可能由于应用非 SQL 部分造成瓶颈。
3. 提出一系列针对的优化措施，并根据它们对性能改善的重要程度排序，然后逐一加以实施。不要一次执行所有的优化措施，必须逐条尝试，逐步对比。
4. 通过获取直接用户的反馈验证调节是否已经产生预期的效果，否则，需要重新提炼性能概念模型，直到对应用特性了解进一步准确。
5. 重复上述，直到性能达到目标或由于客观约束无法进一步优化。

当从操作系统层面判断系统存在瓶颈并且是数据库引起的，那么可以从下面的流程图来解决

图 1. 性能诊断优化流程

(点击查看大图)

 

典型性能问题案例

案例 1：数据库应用突然变慢

问题特征

数据库应用突然变慢，查看系统信息，发现 CPU 空闲突然很低，IO 性能没有明显恶化。

处理步骤

首先，需要排除操作系统上其他应用程序的问题。通过 top(HP)/topas(AIX/Linux) 命令可以看到当前占用 CPU 资源最多的进程，确认是 oninit 进程。Solaris 上默认没有 top 命令，可以通过 /usr/ucb/ps –aux | more 的方式来查看，该输出是根据 CPU 占用情况来排序的。

数据库进程占用了大量 CPU 资源时，往往是在对大表在做全表扫描。通过 4.1 中的办法初步确认问题 SQL 后，如果是条件查询 SQL，如带 WHERE 条件的 SELECT /UPDATE /DELETE，还可以通过得到具体的 SQL 查询计划来确认是在进行全表扫描。此时需要对比 dbschema 得到的建表脚本，看是否建立了相应的索引，如果没有合适的索引，应该创建；如果应用没有合理应用已有索引，应该考虑修改应用 SQL。如果表上有合适的索引，应用 SQL 也没有问题，那么就有可能是由于表中数据已经变化较大而长时间未对表收集统计信息，造成数据库引擎选择了错误的查询计划。此时应该对该表收集统计信息后，通常可以收到良好的效果。

有时候问题 SQL 还会是 INSERT 语句，此时通常需要查看表的建表脚本，看看表上是否有过多的索引，是否该表上有不适当的外键指向另一个大表，也可以通过适当删除表中的记录来实现优化。

易出现时机

新应用 / 新模块投入运行

案例 2：检查点持续时间突然显著增加

问题特征

数据库应用突然变慢，查看系统信息，发现 CPU 空闲突然很低，IO 性能明显恶化。和问题 1 的显著不同在于，此时 IO 恶化现象非常明显。

Vmstat 显示 b(block) 很大，有很多等待 IO 的进程， sar 显示 wio 明显超过平时值。观察数据库日志，发现数据库检查点持续时间 (checkpoint duration time) 显著增加，平时在 3 秒以内就能完成，此时需要 10 秒甚至更长时间才能完成。

处理步骤

首先还是查看数据库日志和操作系统日志，排除数据库内部错误和操作系统 IO 错误。如果用的是阵列 (RAID)，最好再查看一下阵列的日志，出现这种情况最常见的原因是阵列出了问题，比如电池没电，cache 没有打开等等。

排除了操作系统和数据库内部错误，就需要了解一下是否有新的应用在进行大批量的数据操作，如 INSERT/DELETE/UPDATE，是否能将这些操作放在系统相对空闲的时候进行。对于大批量的数据导入操作，在 INFORMIX9.4 中提供了 RAW 类型的表，由于不记录逻辑日志，插入速度会快很多，导入完成后，再将表修改为正常模式；对普通表应该先导入数据，再创建索引，注意主键、外键默认都会创建索引，应该在数据导入后在创建。

不恰当的应用 SQL 也会导致 IO 量非常大，可以用案例 1 中的办法来找到问题 SQL，根据实际情况进行处理。

易出现时机

• 新应用 / 新模块投入运行
• 阵列、存储的硬件问题

案例 3：检查点持续时间逐渐缓慢增加

问题特征

数据库稳定运行一段时间后，性能开始下降，检查点持续时间 (checkpoint duration time) 开始逐渐增加，系统 CPU 空闲降低，WIO 有所增加。这些情况往往出现在新的应用上线后一段时间，由于在开发和测试环境中数据量小，性能问题不会暴露，当生产环境数据量增长到一定程度后，性能问题就会出现。

针对这种情况，需要确认定期在对数据库，尤其是对数据库中的大表，在定期做收集统计数据的工作 (update statistics)，避免数据量的增大造成系统性能急剧下降。

利用 4.2 中描述的办法找到被顺序扫描多次的大表及其上的问题 SQL，进行分析，采取相应办法尝试减少其上的顺序扫描：

• 创建相应索引；
• 修改应用 SQL；
• 及时删除表中不必要的数据。

 

常见调优技巧

找到 CPU 占用最高的 SQL

1. 在 sysmaster 库中执行

select sqx_estcost,
sqx_sqlstatement
from syssqexplain
order by sqx_estcost desc

注意：此时看到的仅仅是当前正在执行的 SQL

需要多看几次

2. onstat 命令

onstat -g act 得到当前正在执行的 SQL

Running threads:
tid tcb rstcb prty status vp-class name
75 a327318 a14d6b4 2 cond wait(sm_read) 1cpu sqlexec
76 a327b40 a14d280 2 yield lockwait 1cpu sqlexec

根据 rstcb 列（不要包括前面的 C0000 等，仅要后面部分）

onstat -u | grep a14d6b4

从第三列 sessid 得到 session id

onstat -g sql <sessid> 即可得到当时正在执行的 SQL

一般多找几个 threads 后，就基本可以确定问题 SQL

3. 示例

onstat –g act 
Threads:
  tid tcb rstcb prty status vp-class name
141904 84176538 8030eab8 2 running 1cpu sqlexec
  
onstat –u | grep 8030eab8
Userthreads
address flags sessid user tty wait tout locks nreads nwrites
8030eab8 Y--P--- 131047 informix - 84022480 0 1 11671 14722
  
onstat –g sql 131047
  
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers
131047 DELETE (all) testdb DR Wait 10 0 0 9.03
  
Current SQL statement :
  delete from my_tab
  
Last parsed SQL statement :
  delete from my_tab

得到 SQL 后，利用 set explain on 分析其查询路径，

看是否未利用索引，在对大表进行全表扫描

根据需要创建相应索引

找到全表扫描较多的表及其 SQL

1. 得到全表扫描较多的表

  cat <<! > check.sql  


  -- 系统顺序扫描较多时，被多次顺序扫描的大表，如果有，应该考虑增加索引  
  select first 5 substr(t.tabname,0,20) tabname,substr(dbsname,0,10) dbname,  
  nrows*rowsize*p.seqscans costs,  
  substr(p.seqscans,0,8) seqscans,substr(nrows,0,8) nrows  
  from sysmaster:sysptprof p , systables t,sysmaster:sysprofile s  
  where p.tabname = t.tabname  
  and p.seqscans > s.value/50  
  and s.name = 'seqscans'  
  --and s.value > 2000000  
  and nrows > 2000  
  order by 3 desc  
  !  


  dbaccess sysmaster <<!  
  unload to /tmp/db delimiter ';'   
  select 'dbaccess '||trim(name)||' check.sql' from sysdatabases  
  where name not like 'sys%'  
  !  


  ksh /tmp/db

2. 利用 onstat –g ses 0 –r 5/ onstat –g stm 的输出信息，根据表名，找到可能的 SQL 语句。由于以上获取 SQL 的办法是有局限的，如果无法获取，建议通过查看应用日志或联系开发人员查看源代码的方式来找到。

3. 利用 set explain on 分析其查询路径，确认是在对表进行全表扫描，根据需要创建相应索引。