| Late response time during RMAN hot backup [message #451215] |
Tue, 13 April 2010 03:24  |
Emrek
Messages: 3
Registered: April 2010
|
Junior Member |
|
|
Hello,
We are running Oracle 10g R2 on AIX 5.3. Our database is approximately 20G in size.
Our users are running applications which are highly sensitive to database response times. So any significant delay in database response time(say about more than 5 seconds) has serious impact on the user applications.
Everyday we take online backups using RMAN and we recently discovered that during the online backup process we are experiencing a serious performance problems at certain periods. For example a typical query which normally takes ~0.5 secods for database to respond takes about >30 seconds for several times during the process. to observe the problem more clearly we also wrote some test scripts which make random queries to the database to measure any delays in response times, and seen that we are getting late responses at several occasions(which normally never happen even when we run the script for a day without running RMAN online backup).
We have gone through IBM's whitepaper on "Tuning AIX for Oracle" and seen that there isn't any misconfiguration that would have bad impact on oracle performance on the AIX side.
Also we have gone through the following:
http://www.oracle.com/technology/deploy/availability/pdf/rman_performance_wp.pdf
http://www.oracle.com/technology/deploy/availability/pdf/br_optimization.pdf
http://download-west.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtunin.htm#sthref1057
We normally take our backups to tape drive. In order to test for any I/O bottlenecks we experimented by taking the backups to the disk drive. We have tried DURATION and MINIMIZE LOAD parameters, and also tried setting the RATE parameter to very low values(about 2M/s which normally reaches to about 100M/s) which resulted in total elapsed time for backups to increase from 30 mins to about 4-5 hours. And we came to find out that there is no improvement about the issue in any of these scenarios.
Also we ran the ADDM reports which claimed I/O bottleneck with about 20% impact on the system, so we tried decreasing I/O using the RATE parameter and then ADDM said "low I/O bottleneck".
Database responsiveness is a vital for our system. What measures can we take to avoid such slowness?
Thanks.
[Updated on: Tue, 13 April 2010 05:11] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Late response time during RMAN hot backup [message #451659 is a reply to message #451215] |
Fri, 16 April 2010 00:51  |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
Monitor your server's PageScan rate before and during the RMAN Backup -- use vmstat.
Check your average wait times for 'db file scattered read' and 'db file sequential read' waits. What is filesystemio_options set to ? (is it "asynch" or "directio" ?)
Hemant K Chitale
|
|
|
|
| Re: Late response time during RMAN hot backup [message #452093 is a reply to message #451215] |
Tue, 20 April 2010 00:48 |
Emrek
Messages: 3
Registered: April 2010
|
Junior Member |
|
|
Hello and thanks for the responses.
We have set SGA small to see if we have any trouble with RAM, but its set back to 6G now and the problem still exists.
We don't seem to have high pagescans, 'db file scattered read' and 'db file sequential read' waits are very small. filesystemio_options is set to asynch.
However we have found out something new. Watching iostat results during the database's slow phase we noticed 'tm_act' field for the disk which holds Oracle DBF files reaches 100% even though disk I/O utilization is far below maximum(and that can be controlled by setting the RATE parameter of RMAN). When this value falls below %100 the database is responding well again.
We tried setting High and Low watermarks for disk I/O on AIX as advised by AIX tuning docs but experimenting with different values(and also very low ones) we have come to see these won't solve the problem.
But we have verified that the problem only exists when tc_act is 100%.
Thanks again.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
