Sybase ASE Performance Tuning

Sybase ASE backups and restores have two components that use the input and output buffer.

1. Reading and writing the input.

2. Copying and validating input data blocks to the output buffer.

The slowest of these components can decrease the efficiency of the backup or restore job. You must understand the input and output buffering in your hardware configuration to determine the slowest component. Once you have determined the slowest component, you can adjust SYBASE ASE performance tuning parameters to increase the efficiency of backup and restore jobs.

Sybase ASE Performance Tuning Parameters

The Database Administrator can tune Sybase backup server to maximize I/O, dump, and load throughput during backup and restore operations.

Sybase Backup Server Shared Memory

You can increase the Sybase server shared memory to speed up the database I/O depending on the hardware you are using.

The backup server passes page data to and from the ASE using a block of shared memory. If this block is too small, then this can limit performance. Use the -m backup server switch to increase the block until further increases have no effect on the performance. By default, sybase backup server is configured with 48 MB of shared memory that is less for a large database that has multiple devices and stripes.

For example:

1. Edit the RUN_SERVER_<BACKUPSERVER> file.

2. Add the shared memory to this file. Following is the syntax to add shared memory:

   –m<shared memory in MB>

   To add 3 GB of share memory, type -m3072.

3. Restart the backup server.

Increase Dump Block Size to Improve Dump Device I/O

You can increase the block size up to 1 MB based on when the backup server reaches optimum throughput. By default, the block size is set to 32 KB for the Sybase instance. If 32 KB of dump block size does not yield good performance, try increasing the size to 64 KB.

Stripe Dump by Using Multiple Streams

Striping is reading or writing a single dump to more than one file. Increasing stripes result in I/O increase. Stripes can be increased by increasing the stream count for data and log properties from the Sybase Subclient Properties dialog box.

It is a best practice to set the number of streams count to a higher number for a backup operation. More number of backup streams will create more stripes during dump and load. Due to this, the concurrent I/O will make throughput optimum.

For more information see, Configuring Streams for Backups.

Optimize Dump for Faster Load

Databases that have large transactions running during a dump, end up with a lot of recovery time that is needed for redo during load.

Use the following syntax to configure this behavior:

sp_configure "optimize dump for faster load", <percentage>

It might take a little longer to back up the databases if optimize dump is enabled on the Sybase server.

For more information about reducing load database time, go to the Sybase Infocenter website, Reducing Load Database Time.

Commvault Performance Tuning Parameters

You can improve the backup and restore operations using the parameters described below. You can use one or more parameters at a time.

Change the Block Size

By default, the value of block size is set at 32 KB. You can keep increasing the value of block size by multiple of 2 KB till you get a higher I/O during dump and load operations.

It is a best practice to set the block size as 64 KB.

For more information about block size, see Changing the Block Size.

Change Wait Time between the Sybase Server Shutdown and Restart Operations

By default, 60 seconds of wait time is added between each Sybase server shut down and restart operations. You can change this wait time by creating the additional setting sSybLoadWait.

For instructions about adding an additional setting from the CommCell Console, see Adding or Modifying Additional Settings from the CommCell Console.