Performance issue backup

[RKR]

Hello All,

OpenEdge 10.2A03. Windows 2003 SP2.

Every evening we make an offline Progress backup of a database of approx 8 Gb. The backup is written to another disk on the same computer. Untill a few weeks ago the time to make this backup was about 10 minutes. I have verified that the backup was correct by restoring it. About two weeks ago the time to create the backup suddenly increased from 10 minutes to 1 hour and 45 minutes. The DB size has not increased significantly. Also the space required on the backup media is more then enough.

Is there anyone who has experienced the same problems. I have restarted the server but the problem has not been solved. We did not install any Progress patches so I think the problem is server related. I hope someone can give me some ideas where to look further.

Thanks

Ruud

 

[RealHeavyDude]

Anti-virus would be a usual suspect. But it does not explain why it would take so much time to backup an 8 GB database.

What else is running on the server when the backup is taken?

Heavy Regards, RealHeavyDude.

 

[TomBascom]

Are there any clues in the .lg file? There are several phases to the backup process -- is one of them suddenly taking longer than it used to?

If, for instance, the redo phase has become quite long try truncating the bi file. Or backing up with -norecover.

 

[Rob Fitzpatrick]

What is the size of the BI file now compared to when the backup took 10 minutes?

If there is no issue with the database then you may have some other load on the server that is slowing you down. Use the tools at your disposal (e.g. perfmon, Process Explorer, xperf, kernrate, etc.) to see what else is running at the time of the backup. Look for unexpected heavy disk use, file transfers, interrupt activity, processes with high CPU utilization, etc.

Alternatively, run the backup at some other time several hours different from your current time and see if the duration returns to the expected level. If it does, that would also indicate that the delay is external to Progress and the database.

You could also look at the health of your storage (disks & file system); see if any errors are being logged, and see if diagnostics turn up anything. And check the Windows event logs during the time of the backup. See if there are any new errors or warnings since the backup duration was 10 minutes.

Also, is there any reason why you shut down your database daily instead of taking online backups?

 

[cj_brandt]

what does windows perfmon show on the disk queue for the drives being read from and written to ?

 

[RKR]

Hi all,

Thanks for all the replies.

The BI file is not the issue. Before the backup starts the BI file is always truncated. The reason for the offline backup is that the database is not used by anyone between 20:00 PM and 08:00 AM so we do not see a reason to perform an online backup. Maybe someone can tell me about other advantages other then avoiding downtime to do an online backup instead of an offline backup.

The event logs of windows do not show any messages that can provide a clue.

The backup phase that actually takes longer is the writing of the data files.

I will try to schedule the backup at another time and monitor what is happening on the system at the time of the backup.

 

[RealHeavyDude]

There is one drawback stopping a database that springs to my mind immediately: The optimized buffer pool will be destroyed.

When you start a database the buffer pool is empty. Over time the buffer pool gets optimized so that it should hold the mostly used records (database blocks) in memory increasing the chances that user queries are satisfied by the buffer pool instead of going to disk. You can check the difference in performance submitting the same query twice immediately after the database has been started - you will notice that the second attempt will perform significantly better ( given that the query can be resolved using an index and the buffer pool is big enough ). Stopping the database every night will cause the users experience worse performance until the buffer pool is optimized again.

Whether that is an issue or not is up to you to decide.

Usually I prefer a mixed strategy: Online backups during the week and an offline backup on the weekend which can be combined with other maintenance tasks for which the database must be offline. Whether that makes sense for you, again is up to you to decide.

Heavy Regards, RealHeavyDude.

 

[Rob Fitzpatrick]

I agree with RHD. It doesn't sound like there is a clear reason for shutting down the DB every day and truncating the BI, and doing so just slows application performance as the cache is warmed up. Also there is potential for extra disk I/O from having to re-grow the BI to its usual operating size, if that tends to exceed the default size of four clusters.

 

[RKR]

RHD and Rob, thanks for this information about online backup. I'll keep it in mind although performance of the system during working hours is not an issue and I have never heard anyone complain that the system performance badly in the morning . I'll let you know if I have found anything that is causing the problem with the backup.

Thanks all for the help so far.

 

[LynnReas]

By chance the server running in a virtual environment?
99% of the time when I hear of a backup increasing by more then 30 minutes in just a single day its running on on ESX or ESXi.
So what happens: If the total Virtual RAM exceeds physical RAM, then VMWare changes the guests memory reservation without your knowledge.
To see if its the case edit the virtual machine and click on Resource tab, then click on Memory. It should have a check in the unlimited box. If not the slider will indicate how much physical Ram it is allowed. I have seen Win 2K8R2 VM's with 16GB of Ram assigned with a memory limit of 2GB. So the VM gets 2GB of Physical Ram and the other 14GB is swapped to disk. Same holds true of W2K3, Linux and other VM OS. The Virtual Machine has no knowledge of this occurring as VMWare is still presenting the same amount of RAM, just that its using its disk subsystem as the memory for the VM. Changing the slider to MAX and checking unlimited again does make things better. However a shutdown and restart of the VM truly fixes the memory issue. Below is showing what you never want to see. 3GB of the VM memory is going to Disk. When the VM is also writing to the Page File / Swap and then VMWare is also doing the same thing is double trouble, and the VM is basically useless.
Surprising how often I find this.

Lynn