Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Help me tuning the log file sync wait event

Help me tuning the log file sync wait event

From: chaos <chaospku_at_163.net>
Date: Thu, 11 Jul 2002 19:33:18 -0800
Message-ID: <F001.00495F56.20020711193318@fatcity.com>


hi, dbas:

        One of the database i manage face a serious wait event, log file sync. This is a big and busy oltp system, and using disk array of Sun T3 with raid-5. We are using Veritas QuickIO for datafile and redo log files. The pressure on the database is growing fast, and more and more the redo log becomes the bottleneck of the database. Here is some data showing the fact:

2002/05/03
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total

Event                                               Waits  Time (cs)   Wt Time
-------------------------------------------- ------------ ------------ -------
log file sync                                      82,244       92,442   33.68
db file sequential read                           298,301       80,719   29.41
log file parallel write                            81,849       36,989   13.48
db file parallel write                              5,427       33,615   12.25
control file parallel write                         4,673        6,104    2.22
 

2002/05/07
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total

Event                                               Waits  Time (cs)   Wt Time
-------------------------------------------- ------------ ------------ -------
log file sync                                   6,352,383   15,785,313   40.09
db file sequential read                        26,862,699   12,538,922   31.85
log file parallel write                         5,971,229    3,990,066   10.13
db file parallel write                            290,479    3,164,391    8.04
db file scattered read                          1,749,137      814,981    2.07

2002/05/21
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total

Event                                               Waits  Time (cs)   Wt Time
-------------------------------------------- ------------ ------------ -------
log file sync                                   2,207,609    6,688,751   59.47
log file parallel write                         2,044,977    1,385,379   12.32
db file parallel write                            103,155    1,203,077   10.70
db file sequential read                         8,772,908    1,088,922    9.68
log buffer space                                    3,284      222,604    1.98

2002/05/28
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total

Event                                               Waits  Time (cs)   Wt Time
-------------------------------------------- ------------ ------------ -------
log file sync                                   2,247,585   20,529,779   63.71
db file parallel write                            441,052    4,377,899   13.59
log file parallel write                         1,724,089    3,806,535   11.81
db file sequential read                         8,854,525    2,044,020    6.34
enqueue                                            78,759      592,411    1.84
     
2002/07/04     
Top 5 Wait Events

~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time -------------------------------------------- ------------ ------------ ------- log file sync 3,838,694 13,158,371 63.28 db file sequential read 2,189,863 2,401,275 11.55 log file parallel write 3,401,035 2,098,018 10.09 db file parallel write 97,086 1,503,608 7.23 enqueue 71,251 432,706 2.08

2002/07/11
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total

Event                                               Waits  Time (cs)   Wt Time
-------------------------------------------- ------------ ------------ -------
log file sync                                     453,862    9,679,513   68.11
db file sequential read                           500,157    1,660,982   11.69
db file parallel write                             67,235    1,137,017    8.00
log file parallel write                           163,180    1,075,201    7.57
log file sequential read                           32,012      254,327    1.79




As you can see, log file sync is a big bottleneck, and i have to solve this problem. I think the best solution maybe convert to dedicated raid1 or single disk with oracle multiplex, but this need to rebuild the whole disk array , need too much downtime, and with some risk. I tried to disable CachedQuick IO on the redo log file, but did not see performance gains. Veritas Said quick io is as fast as RAW, but i did not tested it before, i wonder whether move to raw for redo log will helps? It is difficult to modify the application to reduce the frequency of commit, for every transaction finishes, it have to commit.

Another strange question is that: from the iostat result and sar -d result, there is not much write to the disk array, maybe 2Mbytes/second, and it causes so much wait( average 10%time wait during normal workload). Maybe some misconfiguration in the disk array? Thanks for everyone reading and answering, please give me some suggestions

Good luck!

            chaos
            chaospku_at_163.net

zhu chao
DBA of Eachnet.com
86-021-32174588-667

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: chaos
  INET: chaospku_at_163.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Jul 11 2002 - 22:33:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US