解决数据库慢的方法论

解决数据库慢的方法论

数据库的performance是一个长期的监控过程，不能头疼医头，脚疼医脚。

数据库慢一般有三种情况
1。逐渐变慢
2。突然变慢
3。不定时变慢


第一种情况 “逐渐变慢”，要建立一个长期的监控机制。比如，写个shell脚本每天的忙时（通常9~10 etc.)定时收集os,network,db的信息， 每个星期出report对收集到的信息进行分析。 这些数据的积累，可以决定后期的优化决策，并且可以是DBA说服manager采用自己决策的重要数据。DBA的价值，就在每个星期的report中体现。

第二种情况 “突然变慢”，也是最容易解决的。先从业务的角度看是DB的使用跟以前有何不同，然后做进一步判断。硬件/网络故障通常也会引起DB性能的突然下降。

第一步:  察看DB/OS/NETWORK的系统log, 排除硬件/网络问题

第二步：察看数据库的等待事件，根据等待事件来判断可能出问题的环节。如果, 没有等待事件, 可以排除数据库的问题. 如果有等待时间, 根据不同的等待事件, 来找引起这些事件的根源.
比如latch free等跟SQL parse有关系的等待事件，OS的表现是CPU 的占用率高
db file scattered read等跟SQL disk read有关系的等待时间， OS的表现是iostat可以看到磁盘读写量增加

第三步: 察看os的信息, CPU/IO/MEMORY等.
a.  Cpu 的占用率
CPU占用率与数据库性能不成反比. CPU占用率高, 不能说明数据库性能慢.  通常情况, 一个优化很好, 而且业务量确实很大的数据库, CPU的占用率都会高, 而且会平均分布在每个进程上. 反过来, CPU的占用率都会高也不代表数据库性能就好, 要结合数据库的等待事件来判断CPU占用率高是否合理.
如果某个进程的cpu占用高, 肯定是这个进程有问题. 如果,不是oracle的进程, 可以让application察看是否程序有死循环等漏洞. 如果,是oracle的进程, 可以根据pid查找oracle数据字典看看这个进程的发起程序, 正在执行的sql语句, 以及等待事件. 然后, 不同情况使用不同的方法来解决.

b. IO
排除硬件的IO问题, 数据库突然变慢, 一般来说, 都是一个或几个SQL语句引起的.
如果IO很频繁, 可以通过优化disk reads高的TOP SQL来解决. 当然这也是解决IO问题的最笨也是最有效的办法.
OS以及存储的配置也是影响IO的一个重要的原因.
比如, 最常见的HP-unix下异步IO的问题, 如果DBA GROUP没有MLOCK的权限, ORACLE是不使用AIO的. 偏偏OS与DB的两方的admin如果配合不够好地话, 这个配置就很容易给漏掉了.

c. Memory
第二种情况与memory的关系比较小, 只要SGA区配置合理没有变化, 一般来说, 只要不是Application Memory leak, 不会引起突然变慢的现象.

第三种情况 “不定时变慢”, 是最难解决的. 现场出现的问题原因也是五花八门千奇百怪, 最重要的是, 出现慢的现象时, 以最快的速度抓取到最多的信息以供分析. 先写好抓取数据的shell 脚本, 并在现象发生时及时按下回车键





一个例子

数据库突然变慢

背景: 一个新应用上线后, 数据库突然变慢

第一步, 调查新应用

据开发人员讲新应用访问的都是新建立的表, 表的数据量很小, 没有复杂的SQL查询.
查询 v$sqlarea 分别按照disk_reads / buffer_gets / executions 排序, TOP SQL 中没有新应用的SQL. 排除新应用数据库访问照成的性能问题.

第二步, 察看数据库log/ OS log

数据库log中可以看到大量的ORA-7445错误, 以及大量的dump文件. 分析dump文件(时间久了,没有dump文件可参考, 具体细节没法描述下来. ), 发现是新应用通过dblink访问remote DB时生成的dump文件, 应用开发人说没法修改, Oracle也没有相应的patch解决.

OS log中没有错误信息

第三步, 察看statspack report

从wait events中看到,Top event是“buffer busy waits” “db file parallel write” 等于IO相关的等待事件.
从buffer busy waits 的统计信息来看, 是等待data block.
还有些physical reads等信息与从前比没有太多的异常.
Tablespace 的IO reads/writes也没有异常, 但是wait明显增加.
初步确定是IO问题.

第四步, 察看OS的信息

1. top 命令（输出为实验室数据，仅作格式参考）
load averages:  0.05,  0.10,  0.09                                                                           10:18:32
307 processes: 304 sleeping, 1 zombie, 1 stopped, 1 on cpu
CPU states: 96.0% idle,  0.3% user,  2.6% kernel,  1.1% iowait,  0.0% swap
Memory: 4096M real, 2660M free, 1396M swap in use, 3013M swap free

   PID USERNAME THR PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
11928 a21562     1   0    0 3008K 2496K cpu/1    0:02  1.12% top
14965 mpgj76     4  59    0   10M 3696K sleep    3:09  0.18% view_server

当时现场数据显示：iowait 值与以前相比大很多， 没有异常进程

2. sar –d （输出为实验室数据，仅作格式参考）

SunOS sc19 5.7 Generic_106541-42 sun4u    03/20/08

00:00:00   device        %busy   avque   r+w/s  blks/s  avwait  avserv
           sd410            17     0.4      50    1628     0.1     7.1
           sd410,a           0     0.0       0       0     0.0     0.0
           sd410,b           0     0.0       0       0     0.0     0.0
           sd410,c           0     0.0       0       0     0.0     0.0
           sd410,g          17     0.4      50    1628     0.1     7.1

当时现场数据显示，放数据文件的设备 avwait, avque, blks/s值偏大


第五步, 察看数据库的等待事件

一个大业务量的数据库如果性能不好的话, 一般来说都会有大量的等待事件, 上百个等待事件很常见, 我通常会按照EVENT进行group.

Select count(*), event from v$session_wait where event not in ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client') group by event order by 1 desc;

输出结果显示最多的等待事件是buffer busy waits。

进一步分析，找出等待的原因
Select count(*), p1, p2, p3 from v$session_wait where event = ‘buffer busy waits’ group by p1,p2,p3;

在buffer busy waits等待事件中
P1 = file#
P2 = block#
P3 = id ( 此id对应为等待的原因)

按照p1,p2,p3 group是为了明确buffer busy waits的等待集中在哪些对象上。
Metalink对buffer busy waits等待事件的描述有如下一段话：

“If P3 shows that the "buffer busy wait" is waiting for a block read to complete then the blocking session is likely to be waiting on an IO wait (eg: "db file sequential read" or "db file scattered read" for the same file# and block#.”

输出结果显示，等待分布在多个不同的对象上，等待原因为 “waiting for a block read to complete”，进一步分析为IO的问题。

如果，buffer busy waits等待集中在某个对象上，说明有hot block, 通过重新rebuild这个对象增加freelist来解决,RAC环境增加freelist group.

通过以下SQL可以找到具体的object.

Select owner, segment_name, segment_type from dba_extents where file_id=P1 and P2 between block_id and block_id+blocks;

P1,P2是上面v$session_wait查出的具体的值

第六步, 明确原因，找出解决步骤

分析：
1。磁盘的IO流量增加
2。磁盘的IO等待增加
3。DB的IO流量没有增加
4。DB的IO等待增加
由1，2，3，4可以推出，有数据库以外的IO访问磁盘。
察看磁盘配置，该VG只存放了数据库数据文件和数据库系统文件。排除数据文件，产生IO的是数据库系统文件。
数据库系统文件一般来说不会产生IO, 有IO读写的地方只有log和dump文件。
结论：ora-7445产生的大量core dump文件堵塞IO

解决办法：
1，消除ora-7445. (应用不改的情况下，无法解决)
2, 把dump目录指向别的VG
3, 让oracle尽量少的去写core dump文件
  background_core_dump = partial
  shadow_core_dump = partial