Running database backup is one of the most critical part of DBA’s job. If you don’t have RTO and RPO defined and backup job configured to meet those objectives then the job could be at stake. When its daily job, we need to ensure that the backup(dump) process could finish as fast as possible.
When backup job is running, at that time system will experience slowness and if there is any long running transactions, would take even longer and hence it becomes even more important to ensure that the backup job is scheduled when there is very less or no activity on the server. Completing the backup job is very crucial and if we could enhance the process so that it takes lesser time would be a great value addition.
Let’s see few options that we could explore in order to reduce duration of backup time:
- First in the list comes in the form of stripe – Stripe helps in parallelizing the backup job. Earlier if you were running backup without any stripe option meaning the backup will be taken to one file, with stripe – we can run the same backup job in number of files having almost equal size. If we use 5 stripe, we will store backup file in 5 different files and they will use 5 threads/cores of the server. Performance could be enhanced to many fold by using stripe option. While choosing number of stripe, you need to consider number of available threads/cores available on the server. Number of stripe for backup should be lesser than allocated threads for Sybase ASE. I have covered taking database backup using stripe backup in my earlier post Improve performance of DB Backup timing by more than 50%. Command to take database backup using stripe is as below:
dump database db_name to
“/path/db_name_dump_stripe_1.bak” stripe on
“/path/db_name_dump_stripe_2.bak” stripe on
“/path/db_name_dump_stripe_3.bak” stripe on
“/path/db_name_dump_stripe_4.bak”
2. Second option comes in the form of compress – Compress helps in reducing backup size of file as well as reduces duration of the backup. Compression has degree from 1 to 9 – 1 meaning least compression and 9 meaning most compression however duration is also linked with that, you need to choose the right compression level which will fit into your environment and based on disk availability. There are two special compression level – 100 and 101. Both of them are great in terms of performance however they are not so great when it comes to size of file. We should use stripe along with compression for optimal performance of backup operation. I have covered all about compression in my earlier post at All About SAP Sybase ASE Dump Compression Command to take backup using compression is as below:
dump database db_name to
“compress::5::/path/db_name_dump_comp_1.bak” stripe on
“compress::5::/path/db_name_dump_comp_2.bak” stripe on
“compress::5::/path/db_name_dump_comp_3.bak” stripe on
“compress::5::/path/db_name_dump_comp_4.bak”
3. Third option to improve performance comes in the form of shared memory – This -m option is defined in the startup file of Sybase ASE backup service. In case, you are introducing this parameter now, you need to restart backup service for this to take effect. Shared memory is the max memory, backup service can use and is divided among number of used stripes. This number is measured in MegaBytes(MB).
Above three solution would help in speeding up the backup process and will complete in much lesser than time compared to earlier.
Please let me know if you liked this post or have any feedback.