Re: Help me tuning the log file sync wait event

You say your log files are on RAID 5?

On a busy database?

Are your log files all on the same RAID 5 volume?

I'm going to take a SWAG and say that you seriously need to consider dedicating at least 3 RAID 1 volumes to nothing but log file usage.

There's a 400-500% write penalty on RAID 5. It's good for reads, not so much for writes.

Jared

On Thursday 11 July 2002 20:33, chaos wrote:
> 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: Jared Still
  INET: jkstill_at_cybcon.com

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).