Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle backup consumig excessive CPU time

SOLVED
Go to solution
Randy Navarro_1
Occasional Visitor

Oracle backup consumig excessive CPU time

We run a nightly hot backup and we're seeing CPU available free time drop dramatically from 75% to 10% or even hit zero.

The backup includes copying tablespace datafiles to a backup area on our RAID. Then the data is written to tape. It is the first part of writing to the backup area that loads the CPU excessively. Once the write to tape step begin, we see CPU loading reduced and returned to normal upon completion.

We've tried using 'nice' for process prioritization, but that hasn't helped much.

System Info:
HP-UX 10.20
HP K420
Oracle 7.3.2.2
17 REPLIES
Luciano Carvalho
Respected Contributor
Solution

Re: Oracle backup consumig excessive CPU time

hi,

I think the problem could be the disks with high utilization during backup. Have you monitored the disks with "sar -d" or glance?
If the utilization are very high, will be necessary to divide the lod between many disks, maybe using disk stripe.

regards
Paula J Frazer-Campbell
Honored Contributor

Re: Oracle backup consumig excessive CPU time

Randy

Instead of a copy for each backup why not mirror the backup area to the data and then split it away at backup time and merge back in when finished.

Ps the database may need to be stopped/synced during the split away to ensure file integrity.





Paula
If you can spell SysAdmin then you is one - anon
Michael Steele_2
Honored Contributor

Re: Oracle backup consumig excessive CPU time

What backup utility? What RAID utility? What lun configuration on the RAID and what level of RAID?

Check for bottlenecks during this window and attach:

sar -u 5 5
sar -d 5 5
vmstat 5 5
sar -v 5 5
swapinfo -tam
Support Fatherhood - Stop Family Law
Randy Navarro_1
Occasional Visitor

Re: Oracle backup consumig excessive CPU time

Paula,

Could you elaborate on breaking a mirrored pair of drives? Can the primary mirrored drive still stay active while we work with the second drive to backup data? And, what would be the performance cost in reestablishing the mirrored pair?

Thanks all for your assistance.
Paula J Frazer-Campbell
Honored Contributor

Re: Oracle backup consumig excessive CPU time

Randy

When breaking a mirror it depends upon your data being stored, as if active there is a possiblity of an incomplete record write.

Most databases have a lock command which will hold the database during the mirror split (this should take less than a second.)

Then as soon the split has occured then it can be unlocked - most users would be unaware that a split had occured.

On my database I have a triple mirror which I split away one to backup whilst live.

It is a very safe way on a database that must be available 25/7/365.

The best way is to script the split and restore into your backup routine echoing time and date into a log file :-

echo " Start time" > /tmp/mirror.log
date > /tmp/mirror.log
"database hold command"
lvsplit "lvol"
fbackup command
lvmerge "lvol"
echo " End time" > /tmp/mirror.log
date > /tmp/mirror.log.


Test it fully on a sandbox (Play) server.



Paula
If you can spell SysAdmin then you is one - anon
malay boy
Trusted Contributor

Re: Oracle backup consumig excessive CPU time

Hi,
I agree with Paula.That's how we backup our database which are mirrored.This are the step that we take :

1) stop the database
2) split the mirror
3) bring up database without mirror
4) backup the mirror
5) after finish backup,mirror back the database.


regards
mB
There are three person in my team-Me ,myself and I.
twang
Honored Contributor

Re: Oracle backup consumig excessive CPU time

I would suggest break the mirror first, then back to the backup and try again, you should also open a session to monitor the OS process using "top" and see what consuming the CPU time.
Michael Steele_2
Honored Contributor

Re: Oracle backup consumig excessive CPU time

How do you perform an LVM mirror split on a RAID disk array?

Could you attach or check your lvdisplay please. Is the value of mirror 0, 1, 2?

Are there BCV's involved on this disk array? Hot backups with a BCV don't include an LVM mirror split.

How are splitting mirrors going to remove a CPU bottleneck which is defined as 0% CPU idle + higher then usual process level + more than one job waiting in the run queue?

I apologize if I've missed something but I think we've taken a wrong turn somewhere.

And I renew my request to evaluate your system with the 'sar' commands attached above.
Support Fatherhood - Stop Family Law
Paula J Frazer-Campbell
Honored Contributor

Re: Oracle backup consumig excessive CPU time

Hi Michael


My logic is that trying to backup a live database will cause problems as the disks will be very busy with the backup routine and the users both trying to access files, so it you split away a seperate area that only the backup routine is using then the load generated by this heavy disk usage will be reduced.

BTW the log commads should use >> and not >

Paula
If you can spell SysAdmin then you is one - anon
Michael Steele_2
Honored Contributor

Re: Oracle backup consumig excessive CPU time

Raid 5 devices aren't mirrored and CU indicates "...RAID..." device. (* JBOD's are mirrored. *)

Anyway. Time to move on. Can't make presumptions.

(* Why don't you come to NYC for your get together Paula? Airfares just as cheap heading west over the Atlantic. Great gambling over here at Foxwoods and Mohegan Sun. *)
Support Fatherhood - Stop Family Law
Paula J Frazer-Campbell
Honored Contributor

Re: Oracle backup consumig excessive CPU time

Michael

CU did not say which raid level and as you know raid 0+1,1 and 10 are mirrored.

;^)


NYC would be great, I do not gamble as I know how easy it is to cheat -

Why not try and organize something - perhaps just before thanks giving?

I would be interested in attending.


Paula
If you can spell SysAdmin then you is one - anon
Randy Navarro_1
Occasional Visitor

Re: Oracle backup consumig excessive CPU time

We're running RAID-1/0. Also attached is a partial sar output.

You'll see the backup starts at 2300. At 35 seconds after CPU load begins increasing. And, at 23:01:40 CPU ocassionally reaches 0% idle time.
Michael Steele_2
Honored Contributor

Re: Oracle backup consumig excessive CPU time

Thanks. I???ve reviewed your sar report and I'm concerned with your %wio numbers. They seem to indicate a tape device bottleneck.

Can you also attach 'swapinfo -tam' and 'vmstat 5 5'. You may also be paging out. Note the value of 'po' in vmstat. Is it non zero?

%wio is the percentage of time that processes have spent waiting for I/O and anything above 15 is bad.

You've got numbers repeatedly approaching 100% wait time.

You have several instances of jobs waiting 80% of the time or higher, with 96% the highest at time index 23:08:10.

Either your pipe into your device is too small or you need bigger tape devices.

Regarding the LVM split. In my opinion you will see no performance advantage since the problem is at the other end of you I/O and not at the beginning where an LVM split would provide performance enhancements.

What is your I/O connection into your tape drives? Can you double them?
Support Fatherhood - Stop Family Law
Paula J Frazer-Campbell
Honored Contributor

Re: Oracle backup consumig excessive CPU time

Hi

From:-

23:01:40 1 2 97 0
23:01:45 1 2 97 0
23:01:50 1 1 92 5

Notice that the usr and sys are very low and wio is high, this is a bottleneck from either the disks or to the backup drive.


Paula
If you can spell SysAdmin then you is one - anon
Randy Navarro_1
Occasional Visitor

Re: Oracle backup consumig excessive CPU time

Mike,

Attached is the system data from last night's backup that includes the vmstat and swapinfo. The file was huge so we pared it down to the first 7mins or so. That is representative of the CPU loading problem.

The backup was scheduled for 2300 last night and we captured data starting at 22:59, so you can see the "before" and "after" situation.

Also, file viewability is best in Wordpad.

Thanks,

Randy
Bill Douglass
Esteemed Contributor

Re: Oracle backup consumig excessive CPU time

What type of RAID setup do you have? Based on the wio times, and the fact that you're doing a simple cp of the dbf files, I wonder if

1) You're using s/w RAID, and it is causing a lot of waiting

2) You have a problem on your I/O bus.

Do you have any error messages in syslog.log for the period of the back-ups?
Michael Steele_2
Honored Contributor

Re: Oracle backup consumig excessive CPU time

You're not paging and you have enough swap, this is all about your tape device and the I/O pipe into it.

There is a possibility of a disk bottleneck, please attach:

sar -d 5 5

...during backup.

(* Just a few samplings please. *)

Is this a network or SAN I/O pipe into your tape devices? Please indicate with speeds. For example, 100BaseT, 1 gb / second, etc.

Also, please check LOGTOOL for I/O HW failures. This is a historical log and doesn't have to used during the backup.

STM > TOOLS > UTILITY > RUN > LOGTOOL > FILE > VIEW > RAW SUMMARY.

Note the first and last dates of transactions and calculate the difference. If the difference is short, like 4 hours, then this is important to note. Now read down the report of hardware addresses and observe the integer numbers in parenthesis. Anything over 150 in this 4 hour period should be called into HP for replacement.


Support Fatherhood - Stop Family Law