Commit Wait Class 대기시간감소방안 엑셈컨설팅본부 /DB 컨설팅팀박준연 개요 Wait Class 중 Commit 카테고리에해당하는 Wait Event 에의한대기현상으로 DB 시스템의성능저하현상이발생하는것은종종경험할수있다. 그중대표적인 Wait Event 는 Log File Sync 이다. 실제로대부분의 DB 시스템의 Top 5 Wait Event 를조사해보면, Log File Sync 가이에속해있는경우가대부분이다. 하지만 Log File Sync 를포함하여 Log File Parallel Write 등의 Commit Wait Class 에해당하 는 Wait Event 가발생하는것자체가문제가되진않는다. 이는, Oracle 은 Commit 이나 Rollback 명령이요청되면이를 LGWR 에게요청을전달하며, LGWR 은 Redo Buffer 에서가장마지막에기록이이루어진이후시점부터 Commit 또는 Rollback 지점까지의모든 Redo Entry 를 Redo Log File 에기록하는메커니즘을가지고있다. 따라서, 언급한 Wait Event 들은이와같은과정에서필연적으로발생하는 Transaction 의부가적인현상일뿐그자체로문제가된다고볼수없다. 다만, 해당 Wait Event 를대기하는시간이과도하게긴경우, 예를들어과도한 Commit 이발생하는경우등에따라서는이대기시간을감소시켜전반적으로 DB 시스템의최적화된성능을보장할수도있다. 이문서는 Commit_wait, Commit_Logging 파라미터값의수정을통해 Log File Sync 의대기시간을경감할수있는방안과이에대한주의사항에대한정보를알아보는것을목적으로한다. 186 2013 기술백서 White Paper
Commit_Wait / Commit_Logging 두파라미터를통해 Log File Sync 대기시간을감소시킬수있다는것은놀라움과의아함을동시에느낄수있다. 단지파라미터의수정을통해당연히대기해야하는시간을감축한다는것은분명성능을개선해야하는입장에서는놀라운일이될것이다. 반면, 그에따르는비용도감수해야할것이다. 이에대한자세한내용은마지막부분에언급하 는것으로하고, 두파라미터에대한설명과수정가능한값들에대한의미를알아보자. Commit_Wait Commit_Wait 파라미터는 Server Process 가 Redo Data 를 Redo Log File 에기록을완 료할때까지대기할것인지말것인지를결정하는파라미터이다. Wait : Wait 값은위파라미터의기본값이다. 이는 LGWR 의작업이완료되는순간까지 Server Process 가대기함을의미한다. Nowait : 말그대로 Server Process 는해당작업에대해대기하지않음을의미한다. 이경우, 시스템의 ACID 의특성중지속성이침해될수있으므로일반적으로는 Nowait 으로설정하는것을권고하지는않는다. Force_Wait : Wait 설정값과유사한의미를갖는다. 다만, 시스템레벨에서설정한경우, 세션레벨에서이를무시할수있으며, 그반대의경우도가능하다. 즉, 낮은수준에서의 Wait 설정이라고이해하면된다. Commit_Logging Commit_Logging 파라미터는 LGWR 가 Redo Data 를일괄로기록할지의여부를결정하 는파라미터이다. Immediate : 이값이기본값이며, 각 Commit 마다 LGWR 가쓰기작업을진행함을의미한다. Batch : 이는 LGWR 가일괄로 Redo Data 를기록함을의미한다. 작은단위의 Transaction 의경우이방법으로기록하면보다효율적인방법이될것이다. Part 1 ORACLE 187
위에서설명한바와같이이두파라미터의설정값의변경만으로도 Log File Sync 를포함한 Commit Class 의대기이벤트들의대기시간이획기적으로감소할수있다는가능성이있음을알수있다. 사실이기능이처음소개된것은 10G 때부터인데, 10G 에서는파라미터가 Commit_Write 라는파라미터만존재한다. 설정값을 Wait, Immediate 나 Nowait, Batch 등으로설정한다. 11G 에와서파라미터가위 와같이두가지로분리되어적용할수있도록변경되었다. Parameter 값변경에따른성능테스트 DECLARE l_dummy INTEGER; BEGIN FOR i IN 1..1000 LOOP INSERT INTO t VALUES (i, rpad('*',100,'*')); COMMIT; SELECT count(*) INTO l_dummy FROM dual; END LOOP; END; 본격적으로두파라미터의값의변경에따른위의 PL/SQL Block 과같이건건이 Commit 을수 행하는트랜잭션의성능차이가어느정도인지테스트를통해알아보도록하자. 참고 : 아래결과는 STRACE 를이용하여서버프로세스와 LGWR 의 System Call 횟수를보여주는 것으로별도첨부된 Script 를참조할것. WAIT / IMMEDIATE ***** Server Process ***** 188 2013 기술백서 White Paper
100.00 0.069561 69 1005 1 semtimedop 100.00 0.069561 1005 1 total ***** Log Writer ***** 100.00 0.013919 14 1016 io_submit 100.00 0.013919 1016 total NOWAIT / IMMEDIATE ***** Server Process ***** 100.00 0.002195 439 5 1 semtimedop 100.00 0.002195 5 1 total ***** Log Writer ***** 100.00 0.010073 10 1015 io_submit 100.00 0.010073 1015 total NOWAIT / BATCH ***** Server Process ***** 100.00 0.002132 533 4 1 semtimedop Part 1 ORACLE 189
100.00 0.002132 4 1 total ***** Log Writer ***** 100.00 0.000533 36 15 io_submit 100.00 0.000533 15 total 두파라미터의값이 Default 값인경우 (Wait/Immediate), Server Process 와 LGWR 의 System Call 횟수는 Commit 횟수와비슷한것으로나타났다. Nowait/Immediate 로설정한경우에는 Server Process 의 Call 횟수는급감하였지만 LGWR 의경우는여전히 Commit 횟수와비슷한수치를유지하고있다. 반면에 Nowait/Batch 로설정한경우, 두프로세스모두시스템 Call 횟수가눈에띄게감소한 것으로나타났다. 이것이원인이되어 Log File Sync 등의 Wait Event 대기시간이감소하여전 반적인성능향상의결과로나타난다. 주의사항및결론 앞서소개한빈번한 Commit 에의한대기현상을감소하는방안은분명매력적인방법이다. 하지만이역시공짜는아니다. ACID(Atomicity, Consistency, Isolation, Durability) 라는 Transaction 의특성중 Durability( 지속성 ) 가위배되는상황이발생할수있기때문이다. 파라미터값의변경의의미는단순히 Commit 시마다 LGWR 가 Redo Log File 에기록하지않고일정량을한번에기록하여대기시간을감소시키려는목적인데, 예기치않은 Instant Failure 가발생한다거나 DB Shutdown 상황이발생하면 Transaction 의완전한복구는사실상불가능하다. 190 2013 기술백서 White Paper
따라서, 금융시스템과같은중요한시스템에해당파라미터의변경적용은고려사항이아니다. 즉, DB 시스템의성격과업무의특성을고려하여선별적인적용이필요함을의미한다. 예를들어, Data Migration 시에세션단위로파라미터변경값을적용하여작업을수행한다던 지, 빈번한 Commit 이발생하는특정업무 ( 단, Transaction 의지속성에큰영향을받지않는 Data 에한함.) 에선별적으로적용하는등의적절한대처가필요하다. 상황에따라영리하게적용할수있다면 Commit Class 에해당하는 Wait Event 들의대기시간 을감소시켜성능을개선할수있는확실한카드로사용될수있다고믿는다. 별도첨부 Script Script 1. Commi.sql SET DEFINE ON VERIFY OFF FEEDBACK OFF PAGESIZE 0 TERMOUT OFF ECHO OFF ALTER SESSION SET commit_wait = &1; ALTER SESSION SET commit_logging = &2; CREATE TABLE t (n NUMBER, pad VARCHAR2(1000)); SPOOL servprc.pid SELECT p.spid FROM v$process p, v$session s WHERE p.addr = s.paddr AND s.sid = sys_context('userenv','sid'); SPOOL OFF execute dbms_lock.sleep(2) DECLARE l_dummy INTEGER; BEGIN FOR i IN 1..1000 LOOP INSERT INTO t VALUES (i, rpad('*',100,'*')); COMMIT; SELECT count(*) INTO l_dummy FROM dual; END LOOP; END; / DROP TABLE t PURGE; EXIT Part 1 ORACLE 191
Script 2. Commit.sh #/bin/bash user=$1 password=$2 commit_wait=$3 commit_logging=$4 lgwr_pid=`pgrep -f lgwr_$oracle_sid` strace -p $lgwr_pid -c -e io_submit -o lgwr.log & strace_pid=`pgrep -f strace` sqlplus -s $user/$password @commit.sql $commit_wait $commit_logging & sleep 1 strace -p `head -1 servprc.pid` -c -e semtimedop -o servprc.log rm servprc.pid kill -1 $strace_pid "***** Server Process *****" cat servprc.log "***** Log Writer *****" cat lgwr.log 192 2013 기술백서 White Paper