Re: RMAN backup slows database performance to a crawl
Date: Fri, 11 Jul 2008 09:50:36 -0400
are you writing your RMAN backups to the same set of disks that you are reading from ? make sure your backups are on different sets of disks. if your on a SAN, ask your administrator to map you out a mount point that maps through the same to a different raid group preferably on a different HBA. alot of SAN guys don't want to put in the effort. Its really just a GUI and will not take alot of time.
On Fri, Jul 11, 2008 at 9:18 AM, Finn Jorgensen <finn.oracledba_at_gmail.com> wrote:
> Believe me, I've tried to find a time when the backup can run when
> nothing else is going on, but even the incrementals take 4-5 hours.
> I've added a block change tracking file today, so after this weekend's
> full backup that should dramatically reduce the amount of time the
> inc's take.
> Jared, the channels is set to 12, but since the database is mostly
> this one bigfile tablespace effectively I'm only using 1 channel 99%
> of the time. I will read your doc though.
> I'm pretty sure I've not saturated the IO subsystem. With only 1
> channel going I'm getting ~400MB/min for this database. I have other
> databases on the same storage using the same backup storage too that
> use 8-12 channels and get 1800-2000MB/min.
> My plan is to split this large tablespace into several smaller
> tablespaces and use more channels along with the BCT file which will
> reduce the time the backup takes so I can avoid running it while the
> app runs. However, I think that's a bit of a bandaid. The app
> shouldn't slow like that during backups.
> Here's another couple of nuggets from the AWR report if that rings a
> bell with anybody :
> Top 5 Timed Events Avg %Total
> ~~~~~~~~~~~~~~~~~~ wait Call
> Event Waits Time (s) (ms) Time Wait
> ------------------------------ ------------ ----------- ------ ------
> RMAN backup & recovery I/O 1,652,234 11,557 7 14.7
> System I/O
> buffer busy waits 10,628 10,213 961 13.0
> Also, the filesystem is Veritas, which I know does not support Asynch
> IO natively, however filesystemio_options is set to async. I know
> there are many opinions on this parameter out there, but does anyone
> have any thoughts on if this is the best value or if I should change
> that to setall?
> Thanks again,
> On Thu, Jul 10, 2008 at 5:30 PM, David Barbour <david.barbour1_at_gmail.com>
> > I have a similar problem, but it's a difference between incrementals and
> > full backups. When the full backups run, I've limited the size of the
> > backup set and I don't have a performance issue. When the incremental
> > backups run however, the application basically comes to a screaming halt.
> > We're on AIX 5.3L, Oracle 220.127.116.11 (soon to be upgraded to 10g - thank
> > with cio mounted filesystems. Finally got the Sys Admins to mount our QA
> > box without CIO and I'm going to test the incremental backups using both
> > setall and asynch in the filsystem_io parameter.
> > On Thu, Jul 10, 2008 at 5:14 PM, Jared Still <jkstill_at_gmail.com> wrote:
> >> On Thu, Jul 10, 2008 at 1:16 PM, Finn Jorgensen <
> >> wrote:
> >>> Checking an AWR report I noticed that the "Av Rd(ms)" column of the
> >>> tablespace IO stat for that tablespace is at 671ms (Yikes!) when the
> >>> RMAN job is running and a much more normal 3.6ms when it's not.
> >> How many channels are being used?
> >> You may want to reduce the # of channels if > 1.
> >> You can use the 'SET LIMIT' command to slow down RMAN.
> >> (it's mostly undocumented)
> >> See "Making RMAN Perform' at http://jaredstill.com/articles.html
> >> There's a couple pages on RATE and SET LIMIT
> >> --
> >> Jared Still
> >> Certifiable Oracle DBA and Part Time Perl Evangelist