一次ORACLE IO效能診斷案例

Oracle 診斷案例 -I/O 子系統

系統軟硬體現狀

Oracle 資料庫執行環境

Unix 主機：               2 * IBM M85

CPU ：        4 個

系統記憶體：         6GB

磁碟陣列子系統：    IBM SSA RAID-5

Oracle 版本：      8.1.7.1.0 32-bit

OS 版本：     AIX 4.3.3-10

業務系統： ×××××業務管理系統

資料

kthr     memory             page              faults        cpu    

----- ----------- ------------------------ ------------ -----------

 r  b   avm   fre  re  pi  po  fr   sr  cy  in   sy  cs us sy id wa

 2  4 897649  6717   0   7  23 437  957   0 1011 1184 164 13  7 60 19

12  9 893274  9676   0  30   0   0    0   0 1787 70898 20625 41 23  2 34

 6 15 881613 18578   0 147   0   0    0   0 2035 62910 14427 37 31  1 31

 1 10 880631 17850   0  13   0   0    0   0 1940 38678 10031 27 17  4 51

16 10 886848  9508   0  36   0   0    0   0 2226 48349 7778 26 16  4 54

 2 14 892766   541   0  20   0   0    0   0 2331 88696 14576 43 18  1 38

15 19 895124    34   0  15 382 2007 4143   0 1878 59445 10411 26 16  2 56

 1 12 883561 12427   0  28 270 1288 2735   0 1910 45451 6520 20 14  1 65

22 11 882698 11794   0  29   0   0    0   0 1856 51539 5399 21 25  4 50

 9 19 879662 12982   0  17   0   0    0   0 1914 39960 7045 20 16  3 61

 1 12 878367 12601   0  33   0   0    0   0 2016 30913 5410 18 10  4 68

 2 14 881742  7084   0  51   0   0    0   0 2256 35721 7131 22  7  5 65

 2 11 888809     0   0  25 149 975 1981   0 1893 46405 6496 22 14  6 58

 1 11 877357  9894   0  27 212 631 1314   0 1636 28126 4256 16 26  2 55

 3  9 880033  5315   0  25   0   0    0   0 1714 44692 5093 20 24  3 53

 7 11 879638  3862   0  48   0   0    0   0 2044 62118 8235 27 28  3 42

 1  9 871947  9857   0  28   0   0    0   0 1954 49887 11556 30 22  5 44

 8 12 872208  7408   0  40   0   0    0   0 2132 38014 6991 22 14  4 59

 1 10 879075  3815   0  14 396 2269 4650   0 1775 38587 5830 16 14  6 63

 3 14 884434     4   0  24 235 1632 3575   0 1963 51640 7198 22 15  4 60

 

。。。 。。。

 

 

 

資料

Top 5 Wait Events

~~~~~~~~~~~~~~~~~                                             Wait     % Total

Event                                               Waits  Time (cs)   Wt Time

-------------------------------------------- ------------ ------------ -------

log file sync                                      95,588    1,675,398   58.21

db file sequential read                           402,294      917,696   31.88

db file parallel write                              7,780      114,321    3.97

db file scattered read                              8,560       70,945    2.46

buffer busy waits                                  12,303       40,058    1.39

          -------------------------------------------------------------

（詳細資訊參見《 2.16 如何理解 STATPACK 的報告》 .doc ）

 

 

業務分析

1. 主要業務發生在每天 8 點到 22 點之間

2. 每天的事務量是 60G 左右，要求較高的資料的完整性

3. 每天有較多的 DML （主要是 INSERT 和 UPDATE 操作，幾乎沒有 DELETE 操作和大量的查詢操作。 DML 操作主要是用來更新在 B/S 結構和 C/S 結構中的業務資訊， select 操作主要是提供大量的檢索功能，業務的操作模式是每個 DML 之後就會 commit ；

4. 目前的備份方式是冷備，目前的歸檔方式是歸檔到一個本地磁碟和 RAID 上各一份

5. 目前的 Web 服務，資料庫服務， C/S 結構的伺服器都跑在 RAID5 上

 

資料庫效能分析

問題 1 ：

由於系統存在大量的 transaction, 造成了 redo log 對磁碟的巨大 io 壓力，造成了系統大量的 IO 等待。所以，當前資料庫的主要效能瓶頸是磁碟子系統的效能問題，系統的 IO Wait 嚴重偏高，尤其是 log file sync 事件， 20 分鐘內發生 95,588 次，平均每秒發生 80 個左右，佔系統所有等待時間的將近 60% 。由於 log file sync 的產生是由於應用程式過度的 Commit 造成的，所以最好的方法是修改應用程式，採用 Batch Commit 技術，來提高系統的效能。如果由於第三方軟體的原因，導致應用程式無法進行修改，我們也可以提出一個變通的方案，即最佳化使用者磁碟子系統，提高使用者磁碟的效能，以達到減少系統的 IO 資源等待，提高系統效能的目標。

 

 

問題 2 ：

還有一個系統的主要問題所在是 Oracle 的配置問題。當前系統有 6GB 的實體記憶體，但是 Oracle SGA 只是開了 600M 。這是對資源的巨大浪費。如果這個伺服器只是執行 Oracle      Database ， 那麼 Oracle SGA 可以調節到 3.5-4GB 。 ( 當前系統使用的是 32Bit Oracle ，建議升級到 64Bit Oracle) 。

 

問題 3 ：

1)         另外的幾個小問題是系統地 rollback segment 數目偏少。

2)         系統地 log_buffer 偏大。

3)         系統把 redo log file 作了兩個 member, 是高可用性的一個比較好的策略，不過當前的配置是在同一個磁碟上面，似乎意義不大。 Log file switch 過於頻繁， logfile 大小偏小。

 

修改建議

1)         如果系統沒有配置 hacmp 作為 HA 可用性解決方案，建議把 redolog 從 hdisk2,hdisk3 所在的磁碟移動到 hdisk0,hdisk1 上面（採用 raw device ）。這樣，可以大大降低系統地 IO 壓力，同時保證 redo log 的安全性。

2)         當前磁碟子系統，主要 IO 集中在 hdisk2, hdisk3 這兩個邏輯磁碟上面。為了降低系統地 IO 壓力， 建議把一部分 datafile 移到 hdisk4 磁碟上。

3)         如果有可能調節引用程式，建議使用 batch commit 技術，把下面的語句修改一下：同時注意，

 

  Buffer Gets    Executions  Gets per Exec  % Total  Hash Value

--------------- ------------ -------------- ------- ------------

      2,440,675    1,218,841            2.0    18.1   1115576732

update nlc50.z07 set z07_rec_key = :r1:i1,z07_sequence = :r2:i2,

z07_level = :r3:i3,z07_source = :r4:i4,z07_history = :r5:i5 wher

e z07_rec_key = :v1

同時注意：

Executions   Rows Processed    Rows per Exec   Hash Value

------------ ---------------- ---------------- ------------

   1,218,841              355              0.0   1115576732

update nlc50.z07 set z07_rec_key = :r1:i1,z07_sequence = :r2:i2,

z07_level = :r3:i3,z07_source = :r4:i4,z07_history = :r5:i5 wher

e z07_rec_key = :v1

程式的寫法有問題。執行那麼多次，實際上被處理的行的資料很少，就是實際上很多都是無用功。卻找成了系統的那麼多的 commit ， 從而趙成了 IO 子系統的很大的壓力。

 

4)         調節下面的 SQL 的索引策略：

  Physical Reads  Executions  Reads per Exec % Total  Hash Value

--------------- ------------ -------------- ------- ------------

         83,852            5       16,770.4    19.9   3357555463

select max(z30_rec_key_2) from nlc50.z30 where z30_rec_key_2 lik

e :v1

 

 

SQL ordered by Executions for DB: ALEPH0  Instance: aleph0  Snaps: 1 -2

-> End Executions Threshold:     100

 

     497,348           17,371              0.0    695152841

select z98_rec_key,z98_type,z98_direction,z98_compress_type,z98_

total_map_length,z98_map_length,z98_data from nlc09.z98 where z9

8_rec_key >= :v1

 

     340,815           93,393              0.3   3969206952

select z98_rec_key,z98_type,z98_direction,z98_compress_type,z98_

total_map_length,z98_map_length,z98_data from nlc01.z98 where z9

8_rec_key >= :v1

 

     141,423          137,021              1.0   3698977705

select * from nlc01.z101 where z101_rec_key >= :v1

 

 

調整後效能的改善

         系統 IO Wait% 降低到 10% 左右，系統 CPU 空閒可以從現在的小於 8% 增加到 30 ％以上。對於使用者來講，系統主要業務響應時間明顯變快。