講解數據庫變慢的三種情況及具體處理方法

2024-07-21 02:43:26

字體：大中小

來源：轉載

供稿：網友

數據庫慢一般有三種情況：

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訪問磁盤。