Connor McDonald£¬
ÔÚ 2002-07-12 02:33:00 You wrote:
>Some suggestions
>
>a) Check your commit frequency - if you're committing
>like mad then this is a great way to over stress redo
>logging operations.
Yes, i check the statspack report and find there is about 20 commits/second in may,
and now there is 40 commits/second these days. I do not know whether this is too much.
THis is a busy oltp system,and the trend is that there will be more and more
transactions per second, painful
>
>b) 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
The average redo size per transaction is quite small, as i can see it from the
statspack,
~ Per Second Per Transaction
--------------- ---------------
Redo size: 82,547.28 2,247.16
per transaction is 2kbytes in size. I will try to modify the T3 cache policy. and see
if this helps.It can be modified when it is running, as the document said, but my sa
do not dare to modify it as i had requested.:(, maybe we have to try it.
oracle@main-db1$sar -d 2 2
SunOS main-db1 5.7 Generic_106541-14 sun4u 07/13/02
22:07:33 device %busy avque r+w/s blks/s avwait avserv
22:07:35 ssd0,e 100 20.7 199 3189 0.0 104.4
This is the most busy time in the day for the system, and this device ssd0,e
is for the redo log volumn, 3189blocks/200=16blocks/second, that means 8KBytes per
write.This is some small write, but still much greate than the 2k per transaction.
why? other wasted log because of the too large log_buffer_size?
(there should be no read for redo log, right, unless it is being archived, so all
write).
>
>c) Look at means at reducing the amount of redo log
>your're generating eg transaction auditing etc
This seems hard, we do not have much other information other than the transaction data.
Thanks so much for your help, and your site:)
Good luck!
chaos
[EMAIL PROTECTED]
zhu chao
DBA of Eachnet.com
86-021-32174588-667
>hth
>connor
>
> --- chaos <[EMAIL PROTECTED]> 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
>> [EMAIL PROTECTED]
>>
>> zhu chao
>> DBA of Eachnet.com
>> 86-021-32174588-667
>>
>>
>>
>> --
>> Please see the official ORACLE-L FAQ:
>> http://www.orafaq.com
>> --
>> Author: chaos
>> INET: [EMAIL PROTECTED]
>>
>> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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: chaos
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
