cancel
Showing results for 
Search instead for 
Did you mean: 

Impact of load database on server

0 Kudos

Hi There!!

I realize the backup server was created to alleviate the stress of dump/loads on the ASE server. I know dumps have a minimal or no effect on the server during the dump operation. Is the same true for loading the database? Can I expect a noticeable impact on the database or is it minimal?

Thanks,
Rob

Accepted Solutions (1)

Accepted Solutions (1)

sladebe
Active Participant

Well, for one thing, the database being loaded isn't available for use while being loaded, but that's probably not the impact you meant.

For other databases, the main effect will be competition for host level resources. Ie., the backupserver process executes I/O on it's own, separate from the main dataserver (pretty cool), but on heavily loaded systems a large amount of physical writes can really slow things down. Same for competition for network bandwidth (if the dump destination files are on NFS, for example). This applies to dumps and loads.

I work in Linux. In Linux-land, if you do a lot of buffered I/O ("dsync" in ASE lingo), you can eventually get the virtual memory system into a weird state. But that's only relevant for high levels of buffered I/O for measured over weeks, probably not the case here (direct_io is the slower alternative).

Lastly, new ASE has the new "cumulative" dump feature which lets you dump only the changed pages since the last full dump. If you have big databases with small amounts of data updates, this can be a huge win. You can do a full backup on the weekend, then do cumulative backups each night which might only take a few minutes. FYI, you have to set the "allow incremental dumps" [sic] option on a database to use this. The dumps are not incremental (all changes since the last incremental dump), they are differential (all changes since the last full dump)

Answers (2)

Answers (2)

former_member188958
Active Contributor

Also... the load has two phases - the actual loading of the page images from the dump archive (performed by the Backupserver / Sybmultbuf), followed by recovery rolling forward the transaction log (performed by the ASE).

On hosts where the ASE alone is configured to use pretty much all available cpu resources, then the dump or load occurs, the OS must allocate some cpu to the backupserver / sybmultbuf processes, making less available to the ASE, which will be seen as a performance impact. Most hosts are not configured that tightly.

0 Kudos

Hi Ben!!

Thanks for the swift response.

After examining your responses, I realized I forgot to include the output of @@version. Here it is:
Adaptive Server Enterprise/15.7.0/EBF 21677 SMP SP60 /P/Solaris AMD64/OS 5.10/ase157x/3329/64-bit/FBO/Thu Dec 12 05:02:04 2013

Would the network bandwidth come in to play if the disks are located on the same server ie backup server on the same server as ASE. I guess the only way to monitor the impact is by running sp_sysmon during the load database, correct?

The cumulative dump feature is that available in 15.7? Thanks for explaining the difference between cumulative and incremental.

Thanks,
Rob



sladebe
Active Participant
0 Kudos

Re: I forgot to include the output of @@version .. 15.7.0/EBF 21677 SMP SP60. cumulative dump feature is that available in 15.7?

It looks like cumulative dumps were added to ASE in 15.7 SP100:

https://help.sap.com/viewer/40fd57add48b438da0615d45ece12db7/15.7.0.0/en-US/9a7dc0d7718d4ee89ecf0d83...

So it looks like you don't have the feature in SP60 (just missed it). Sorry.

Re: Would the network bandwidth come in to play if the disks are located on the same server ie backup server

No, network bandwidth would not come into play for dumping to local disks.