Also, as Anjo taught me a couple years ago, "log file sync" is
particularly susceptible to long latencies if you simply have long CPU
runqueues.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic, Jul 23-25 Chicago
- Miracle Database Forum, Sep 20-22 Middlefart Denmark
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
-----Original Message-----
McDonald
Sent: Friday, July 12, 2002 5:33 AM
To: Multiple recipients of list ORACLE-L
Some suggestions
- Check your commit frequency - if you're committing
like mad then this is a great way to over stress redo
logging operations.
- Check the size of the average redo write. If
they're small, you might get some benefit by
allocation write cache in the T3 to the redo logs (if
thats possible) to batch up the writes
- Look at means at reducing the amount of redo log
your're generating eg transaction auditing etc
hth
connor
- chaos <chaospku_at_163.net> 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: 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).
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"Remember amateurs built the ark - Professionals built the Titanic"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Cary Millsap
INET: cary.millsap_at_hotsos.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).
Received on Sat Jul 13 2002 - 23:18:26 CDT
