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