在Oracle中开启AIO异步IO后可以一定程度上提升数据库IO性能,但同时也引入了丢失commit数据的风险。具体可以参见小荷同学的文章,但是这里存在一个疑问就是redo重做记录是同步写出还是在启用AIO后同样使用异步IO的API,我们可以通过跟踪lgwr后台进程的system call系统调用来说明该问题: SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> show parameter disk_asynch_io NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ disk_asynch_io boolean TRUE SQL> show parameter filesystem NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ filesystemio_options string SETALL [oracle@rh2 ~]$ ps -ef|grep dbw0_G10R2|grep -v grep oracle 29168 1 0 19:02 ? 00:00:01 ora_dbw0_G10R2 [oracle@rh2 ~]$ strace -p 29168 ............................. io_submit(140140183375872, 34, {{0x7f74ee284e10, 0, 1, 0, 16}, {0x7f74ee290920, 0, 1, 0, 16}, {0x7f74ee286970, 0, 1, 0, 16}, {0x7f74ee290db0, 0, 1, 0, 16} = 34 io_getevents(140140183375872, 1, 1024, {{0x7f74ee284e10, 0x7f74ee284e10, 8192, 0}, 0x7f74ee289710, 8192, 0}}, {600, 0}) = 12 times(NULL) = 480509951 times(NULL) = 480509951 times(NULL) = 480509951 times(NULL) = 480509951 times(NULL) = 480509951 times(NULL) = 480509951 times(NULL) = 480509951 times(NULL) = 480509951 times(NULL) = 480509951 times(NULL) = 480509951 times(NULL) = 480509951 times(NULL) = 480509951 getrusage(RUSAGE_SELF, {ru_utime={0, 417936}, ru_stime={0, 823874}, ...}) = 0 getrusage(RUSAGE_SELF, {ru_utime={0, 417936}, ru_stime={0, 823874}, ...}) = 0 io_getevents(140140183375872, 1, 1024, {{0x7f74ee28c4b0, 0x7f74ee28c4b0, 8192, 0}, {0x7f74ee287290, 0x7f74ee287290, 8192, 0}, {0x7f74ee283988, 0x7f74ee283988, 8192, 0}, {0x7f74ee27fbf0, 0x7f74ee27fbf0, 8192, 0}, {0x7f74ee28a030, 0x7f74ee28a030, 8192, 0}, {0x7f74ee28fdb8, = 22 /* 此处dbwr使用io_submit函数将I/O请求入列, io_submit是典型的asynchronous I/O system calls, 可见Oracle针对数据文件写出已在使用异步IO */ [oracle@rh2 ~]$ ps -ef|grep lgwr_G10R2|grep -v grep oracle 29170 1 0 19:02 ? 00:00:01 ora_lgwr_G10R2 [oracle@rh2 ~]$ strace -p 29170 ............................. io_submit(139932588023808, 2, {{0x7f4497f423c8, 0, 1, 0, 20}, {0x7f4497f42590, 0, 1, 0, 21}}) = 2 io_getevents(139932588023808, 1, 1024, {{0x7f4497f423c8, 0x7f4497f423c8, 3584, 0}}, {600, 0}) = 1 times(NULL) = 480533371 io_getevents(139932588023808, 1, 1023, {{0x7f4497f42590, 0x7f4497f42590, 3584, 0}}, {600, 0}) = 1 以上io_submit system call说明当数据库启用AIO后lgwr同样使用异步IO写出重做记录到online logfile,换而言之当存储crash时的确可能出现redo记录丢失而造成的记录丢失情况。 实际控制lgwr是否使用异步IO的是一个隐藏参数_lgwr_async_io,该参数一般默认为false: SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ 2 FROM SYS.x$ksppi x, SYS.x$ksppcv y 3 WHERE x.inst_id = USERENV ('Instance') 4 AND y.inst_id = USERENV ('Instance') 5 AND x.indx = y.indx 6 AND x.ksppinm='_lgwr_async_io'; NAME -------------------------------------------------------------------------------- VALUE -------------------------------------------------------------------------------- DESCRIB -------------------------------------------------------------------------------- _lgwr_async_io FALSE LGWR Asynchronous IO enabling boolean flag 你可能要这样问:”既然该_lgwr_async_io参数默认为false,那么lgwr就应当使用同步IO而非async,这岂不矛盾?” 是的,理论上来说默认_lgwr_async_io参数为false,lgwr应当使用sync。但是在版本10.1.0.2到11.1.0.6存在一个“Bug:8357698 LGWR USES ASYNC IO INSPITE OF SETTING _LGWR_ASYNC_IO=FALSE”:
由于Bug:8357698的存在,导致filesystemio_options和disk_asynch_io 2个参数可以覆盖_lgwr_async_io的设置,这个问题一直到11.1.0.6以后才得到修复,而在11.2中_lgwr_async_io参数干脆被取消了: SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ 2 FROM SYS.x$ksppi x, SYS.x$ksppcv y 3 WHERE x.inst_id = USERENV ('Instance') 4 AND y.inst_id = USERENV ('Instance') 5 AND x.indx = y.indx 6 AND x.ksppinm='_lgwr_async_io'; no rows selected SQL> show parameter filesystem NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ filesystemio_options string setall SQL> show parameter async NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ disk_asynch_io boolean TRUE tape_asynch_io boolean TRUE io_submit(139755827974144, 2, {{0x7f1b6e992450, 0, 1, 0, 256}, {0x7f1b6e9921f8, 0, 1, 0, 259}}) = 2 io_getevents(139755827974144, 2, 128, {{0x7f1b6e992450, 0x7f1b6e992450, 1024, 0}, {0x7f1b6e9921f8, 0x7f1b6e9921f8, 1024, 0}}, {600, 0}) = 2 times({tms_utime=3, tms_stime=4, tms_cutime=0, tms_cstime=0}) = 480863691 semtimedop(2654212, 0x7fffadc3db80, 1, {3, 0}) = 0 times({tms_utime=3, tms_stime=4, tms_cutime=0, tms_cstime=0}) = 480863808 times({tms_utime=3, tms_stime=4, tms_cutime=0, tms_cstime=0}) = 480863808 io_submit(139755827974144, 2, {{0x7f1b6e9921f8, 0, 1, 0, 256}, {0x7f1b6e992450, 0, 1, 0, 259}}) = 2 io_getevents(139755827974144, 2, 128, {{0x7f1b6e9921f8, 0x7f1b6e9921f8, 512, 0}, {0x7f1b6e992450, 0x7f1b6e992450, 512, 0}}, {600, 0}) = 2 /* 在11.2中若启用了AIO,那么lgwr仍会沿用async IO */ 总结: 暂无相关文章 | No related posts. |
|