linux 下filesystemio_options disk_asynch_io 導致的I/O效能下降
最近在做資料遷移，發現用資料庫泵要比用DML語言（insert）快很多，大概一個8G左右的表資料庫幾分鐘就可以匯入成功，而用DML語言需要2-3個小時才能完成。最初以為機器效能不行，想起來最進接觸的Linux HugePages相關知識。
透過後來學習發現在資料庫記憶體為AMM管理模式下，不支援Linux HugePages。
關於相關說明：HugePages and Oracle Database 11g Automatic Memory Management (AMM) on Linux (文件 ID 749851.1)
Linux HugePages說明：http://blog.csdn.net/zhouyh139/article/details/8662768#comments
檢視v$log表，發現絕大多數日誌為active狀態。遂將DML語言改為並行nologging形式：
insert /*+position(t1,4)*/ into t1 select /*+position(t2,4)*/ * from t2 nolongging;
發現還是不行，依舊很慢。無奈給資料庫做了一個statspack。發現問題：
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file async I/O submit 208 1,740 8367 42.6
free buffer waits 804 1,552 1930 38.0
rdbms ipc reply 150 282 1880 6.9
log file parallel write 1,776 219 124 5.4
db file scattered read 3,299 107 33 2.6
-------------------------------------------------------------
發現db file async I/O submit等待事件，且每個等待高達8367秒。同時還伴有free buffer waits等待事件，先查詢後者如下：
這個等待事件出現是因為dbwr沒有及時將dirty buffer寫到磁碟，造成server process得到不到free buffer。造成dbwr寫髒塊慢的原因主要由以下幾種情況：
-
1、i/o慢
檢查v$filestat，是否大量磁碟寫操作發生；透過iostat等作業系統命令檢視磁碟寫情況。
可以透過換更快速磁碟及I/O分擔的方法解決i/o慢的問題。
-
2、buffer cache設定過小造成dbwr重新整理緩慢
透過V$DB_CACHE_ADVICE獲得較合理的buffer cache大小並進行調整。
-
3、 buffer cache設定過大造成dbwr不能及時重新整理
增加dbwr程式。如果多個cpu，建議透過修改db_writer_processes增加dbwr程式；如果是單個cpu，建議透過修改dbwr_io_slaves增加dbwr slave程式。來提高dbwr重新整理速度。
透過分析鎖定問題是第一種I/O慢， 透過檢視IO狀態確定如此：
[root@localhost oracle]# iostat -k -x 1 10
avg-cpu: %user %nice %system %iowait %steal %idle
5.88 0.00 0.75 7.88 0.00 85.50
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 48.00 5315.00 155.00 104.00 18520.00 20668.00 302.61 3.13 12.03 3.67 95.00
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda5 48.00 5315.00 155.00 104.00 18520.00 20668.00 302.61 3.13 12.03 3.66 94.90
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdc1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 163.00 0.00 163.00 0.00 1304.00 16.00 1.00 6.13 6.12 99.70
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda5 0.00 163.00 0.00 163.00 0.00 1304.00 16.00 1.00 6.13 6.12 99.70
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdc1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
發現/dev/sda5即/U01這塊盤的IO一直是接近100%，但是我做了一個系統測試發現寫的能力又不是那麼弱.
[oracle@localhost ~]$ time dd if=/dev/zero of=/u01/testio.dmp bs=8k count=300000
300000+0 records in
300000+0 records out
2457600000 bytes (2.5 GB) copied, 7.13211 seconds, 345 MB/s
real 0m7.193s
user 0m0.124s
sys 0m4.328s
於是放狗去找，在metalink上找到一篇關於該問題的描述：
'db file async I/O submit' when FILESYSTEMIO_OPTIONS=NONE (文件 ID 1274737.1)。但該文章說在11.2.0.3中已經解決這個問題了。
但在11.2.0.3中已經將該BUG解決，我的版本為11.2.0.3.7的，不存在該問題。透過學習查詢彙總：
在11G中，預設非同步IO是開啟的：
SQL> show parameterdisk_asynch_io
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io boolean TRUE
但是實際filesystemio_options並沒有設定
SQL> show parameter filesystem
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string none
filesystemio_options有4中狀態：
· ASYNCH - Enabled asynchronous I/O where possible.
· DIRECTIO- Enabled direct I/O where possible.
· SETALL- Enabled both direct I/O and asynchronous I/O where possible.
· NONE - Disabled both direct I/O and asynchronous I/O.
也就是說目前資料庫預設filesystemio_options不是支援同步或非同步I/O的，所以我想用非同步IO就需要將資料庫開啟支援的狀態。
-
開啟資料庫非同步IO測試資料庫效能
在Oracle11gR2中AIO預設已經是開啟的了。可以透過ldd或者nm來檢查oracle是否已經啟用了AIO支援，有輸出代表已經啟用。
[oracle@localhost ~]$ /usr/bin/ldd $ORACLE_HOME/bin/oracle | grep libaio
libaio.so.1 => /usr/lib64/libaio.so.1 (0x00002b497fa42000)
[oracle@localhost ~]$ /usr/bin/nm $ORACLE_HOME/bin/oracle | grep io_getevent
w io_getevents@@LIBAIO_0.4
proc檔案系統包含了兩個虛擬檔案，它們可以用來對非同步 I/O的效能進行最佳化：
/proc/sys/fs/aio-nr檔案提供了系統範圍非同步 I/O請求現在的數目。
/proc/sys/fs/aio-max-nr檔案是所允許的併發請求的最大個數。最大個數通常是 64KB，這對於大部分應用程式來說都已經足夠了。
檢查非同步I/O是否在使用
根據[Note 370579.1]，可以透過檢視slabinfo統計資訊檢視作業系統中AIO是否執行，slab是Linux的記憶體分配器，AIO相關的記憶體結構已經分配，kiocb值的第二列和第三列非0即是已使用。與kernel 2.4.x不同，沒有顯示kiobuf，因為從kernel 2.5.43開始，kiobuf已經從核心中被移除。
kioctx：
AIO上下文在核心空間對應資料結構kioctx，它儲存非同步IO的所有資訊：
[root@localhost datadp]# grep kio /proc/slabinfo
kioctx 35 84 320 12 1 : tunables 54 27 8 : slabdata 7 7 0
kiocb 0 0 256 15 1 : tunables 120 60 8 : slabdata 0 0 0
--以上為我資料庫沒有設定filesystemio_options引數以前的一個狀態，當我設定該引數後檢視如下，再次測試將一個8G左右的表insert的時候：
- SQL> alter system set filesystemio_options=setall scope=spfile;
- System altered.
- SQL> shutdown immediate
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> startup
- SQL> sho parameter filesystemio_options
- NAME TYPE VALUE
- ------------------------------------ ----------- ----------------------------
- filesystemio_options string SETALL
[root@localhost datadp]# grep kio /proc/slabinfo
kioctx 26 60 320 12 1 : tunables 54 27 8 : slabdata 5 5 0
kiocb 165 165 256 15 1 : tunables 120 60 8 : slabdata 11 11 2
[root@localhost datadp]# grep kio /proc/slabinfo
kioctx 35 60 320 12 1 : tunables 54 27 8 : slabdata 5 5 0
kiocb 338 420 256 15 1 : tunables 120 60 8 : slabdata 28 28 188
如上可以發現系統有非同步I/O在使用了
檢查系統IO情況，發現雖然也是100%，但寫入新能從原來的2M提高到130M/S左右，此時I/O的效能大大的提高了。透過時間對比同樣是8000W的記錄修改後執行時間提高了7倍多。
avg-cpu: %user %nice %system %iowait %steal %idle
0.50 0.00 1.00 22.35 0.00 76.15
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 20038.00 2.00 353.00 8.00 103716.00 584.36 132.62 390.83 2.82 100.10
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda5 0.00 20038.00 2.00 353.00 8.00 103716.00 584.36 132.62 390.83 2.82 100.10
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdc 39.00 0.00 162.00 0.00 10380.00 0.00 128.15 1.13 7.04 2.01 32.50
sdc1 39.00 0.00 162.00 0.00 10380.00 0.00 128.15 1.13 7.04 2.01 32.50
avg-cpu: %user %nice %system %iowait %steal %idle
0.13 0.00 7.51 40.55 0.00 51.81
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 38869.00 2.00 522.00 8.00 156856.00 598.72 143.07 277.58 1.91 100.10
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda5 0.00 38869.00 2.00 522.00 8.00 156856.00 598.72 143.07 277.58 1.91 100.10
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdc1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
檢視statspack的報告：
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
log file switch (checkpoint incomplete) 15 802 53444 29.2
db file async I/O submit 27,298 560 21 20.4
log file parallel write 5,354 317 59 11.6
db file scattered read 5,567 267 48 9.7
db file parallel write 31,696 214 7 7.8
-------------------------------------------------------------
同樣發現雖然有db file async I/O submit 的等待事件，但可以發現平均每個等待只為21ms，大大降低了，說明很有效。
-
同樣在做實驗修改資料庫引數配置：
也就是說系統預設情況下，是走系統的caches來和資料庫檔案進行資料傳輸。但由於系統BUG，當請求非同步IO的時候出現了嚴重的等待，我們關閉非同步IO檢視DML效能情況。
- SQL> alter system set disk_asynch_io=false scope=spfile;
- System altered.
- SQL> alter system set filesystemio_options=none scope=spfile;
- System altered.
- SQL> startup force
清空快取：
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH GLOBAL CONTEXT;
SQL > insert /*+position(t1,4)*/ into t1 select /*+position(t2,4)*/ * from t2 nolongging;
檢視I/O狀況，發現此時系統I/0被佔滿
avg-cpu: %user %nice %system %iowait %steal %idle
0.00 0.00 0.12 12.38 0.00 87.50
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 422.00 2.00 186.00 8.00 2372.00 25.32 1.13 5.96 5.30 99.60
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda5 0.00 422.00 2.00 186.00 8.00 2372.00 25.32 1.13 5.96 5.30 99.60
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdc1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
可以發現sda5使用率100%，且只有2M多一些的寫入量。statspack報告中出現大量的db file parallel write等待事件，說明雖然沒有了非同步IO的等待事件，但I/O的效能卻急劇下降。
補充：
1、使用作業系統的caches
作業系統和裝置控制器提供了資料庫快取，這些不直接與資料庫的快取管理相沖突。儘管如此，這些架構可能消耗資源當有很少的或沒有效能利益。當資料庫檔案儲存在Linux或者UNIX檔案系統上時，這種情況就顯而易見了。預設情況下，所有的資料庫I/O都要透過檔案系統的快取。
在一些Linux和UNIX系統上，直接I/O是可用的對於檔案儲存來說。這個協議允許資料庫檔案存取在檔案系統範圍內，繞過檔案系統快取。直接的I/O節約了CPU資源斌並且允許檔案系統快取是獨立的在沒有資料庫活動的時候，例如program texts 和spool files.
儘管作業系統的的cache經常是冗餘的因為資料庫的cache buffers blocks。在一些情況下，資料庫不能用database buffer cache。在這些情況下，使用直接I/O或者裸裝置可能產生更嚴重的效能問題比使用系統的buffer。例如
· Reads or writes to the TEMP tablespace
· Data stored in NOCACHE LOBs
· Parallel Query slaves reading data
2、同步IO和非同步IO的區別：
在同步檔案IO中，執行緒啟動一個IO操作然後就立即進入等待狀態，直到IO操作完成後才醒來繼續執行。
而非同步檔案IO方式中，執行緒傳送一個IO請求到核心，然後繼續處理其他的事情，核心完成IO請求後，將會通知執行緒IO操作完成了。
如果IO請求需要大量時間執行的話，非同步檔案IO方式可以顯著提高效率，因為線上程等待的這段時間內，CPU將會排程其他執行緒進行執行，如果沒有其他執行緒需要執行的話，這段時間將會浪費掉（可能會排程作業系統的零頁執行緒）。如果IO請求操作很快，用非同步IO方式可能更低效，此時同步IO方式會較優。
同步IO在同一時刻只允許一個IO操作，也就是說對於同一個檔案控制程式碼的IO操作是序列化的，即使使用兩個執行緒也不能同時對同一個檔案控制程式碼同時發出讀寫操作。重疊IO允許一個或多個執行緒同時發出IO請求。
非同步IO在請求完成時，透過將檔案控制程式碼設為有訊號狀態來通知應用程式，或者應用程式透過GetOverlappedResult察看IO請求是否完成，也可以透過一個事件物件來通知應用程式。
3、相關引數介紹
在oracle裡為了提高io的速度，常用引數db_writer_processes和 dbwr_io_slaves
在資料庫裡事務數非常高，或db cache很大，一個DBWn process跟不上資料的load，我們可以調整這兩個引數，這兩個引數的作用就是增加io讀寫程式，啟用非同步io，加快io的速度
多個 slaves 可以並行寫資料檔案，而多個dbwr也可以並行寫資料檔案
一個 dbwr 多個 slaves是 dbwr蒐集dirty buffer而 slaves寫資料檔案
多個dbwr 可以並行地蒐集dirty buffer 並且並行地寫資料檔案
但是如果系統支援AIO一般不用設定多dbwr 或者 io slaves
db_writer_processes：在多cpu，多磁碟的環境，一般是每8個cpu一個dbwr程式
什麼時候配置這兩個引數
如果系統的io是瓶頸是，檢查os是否支援非同步io，如果支援非同步io，但目前沒有使用，那就啟用非同步io來緩解io瓶頸。如果os不支援非同步io，
或os已經啟用非同步io，但io還是瓶頸，那我們可以配置多個dbwr程式。配置這兩個引數是要注意，大都是如下
一個db_writer_processes，多個dbwr_io_slaves
多個db_writer_processes，dbwr_io_slaves不啟用
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20674423/viewspace-1445227/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Linux 下的I/O效能分析 iotopLinux
- 效能分析（7）- 未利用系統快取導致 I/O 緩慢案例快取
- AIX 下磁碟 I/O 效能分析[轉]AI
- 【AIX】AIX 下磁碟 I/O 效能分析AI
- 如何監測 Linux 的磁碟 I/O 效能Linux
- Linux下的5種I/O模型與3組I/O複用Linux模型
- MongoDB 分片叢集均衡器導致的效能下降MongoDB
- linux監測I/O效能－iostatLinuxiOS
- Linux 下使用 dd 命令進行硬碟 I/O 效能檢測Linux硬碟
- Linux下磁碟I/O測試Linux
- 【案例】BNL演算法導致效能下降一則演算法
- 在Linux下測試磁碟的I/OLinux
- 非同步IO,同步IO,Direct IO,FILESYSTEMIO_OPTIONS, DISK_ASYNCH_IO [final]非同步
- Linux下的一些I/O統計工具Linux
- 非索引列直方圖的丟失導致sql效能急劇下降索引直方圖SQL
- Standby OS i/o問題導致Primary 庫不能正常歸檔問題
- Linux下如何測試磁碟I/O: ( hdparm -t)Linux
- undo truncate 導致qps下降分析
- sql導致資料庫整體效能下降的診斷和解決的全過程SQL資料庫
- Linux I/O排程器Linux
- 深入 Linux I/O 重定向Linux
- 在Linux下開啟32位I/O和DMA(轉)Linux
- AIX系統磁碟I/O效能評估AI
- Veritas Quick I/O and Cached Quick I/OUI
- 如何更改Linux的I/O排程器Linux
- 【I/O scheduler】Linux的磁碟排程策略Linux
- Linux裡五種I/O模型Linux模型
- Linux libaio 非同步I/OLinuxAI非同步
- linux I/O 瓶頸監控Linux
- java的I/OJava
- 磁碟效能測試工具 flexible I/O testerFlex
- 計算機I/O與I/O模型計算機模型
- I/O埠和I/O記憶體記憶體
- Linux命令----分析系統I/O的瓶頸Linux
- HOW TO CHECK IF ASYNCHRONOUS I/O IS WORKING ON LINUXLinux
- 一條sql導致資料庫整體效能下降的診斷和解決的全過程(轉)SQL資料庫
- 服務端 I/O 效能：Node、PHP、Java、Go 的對比服務端PHPJavaGo
- SQL Server 2008 I/O效能監控SQLServer