As mentioned in the previous post on saving storage by using compression Backup compression – save storage 85%(up to) without compromising duration, we shall go into depth of compression levels and their impact on duration as well as size at disk.
As you know, Sybase ASE supports compression level from 1 to 9, 1 is the least compression and 9 being the maximum compression. You may use compression level 0 however that is as good as no compression. Compression levels 100 and 101 shall also be covered.
Compression level 1
Let’s start with compression level 1, please note that we shall not change anything at stripe level. Keeping more than one variable for experiment is not very much recommended.
Compression 1 dump will be taken as below:
dump database db_name to
“/path/db_name_dump_compress_level01_1.bak” stripe on
“/path/db_name_dump_compress_level01_2.bak” stripe on
“/path/db_name_dump_compress_level01_3.bak” stripe on
“/path/db_name_dump_compress_level01_4.bak” with compression = “1”
Please note that, we are using same database of 30GB and you may refer the previous post for command to check the details Improve performance of DB Backup timing by more than 50%
Database dump starts with compression 1 and details from backup log is as below:
Dec 29 15:54:42 2020: Backup Server: 4.188.1.1: Database db_name: 191162 kilobytes (1%) DUMPED.
Dec 29 15:54:56 2020: Backup Server: 4.188.1.1: Database db_name: 362946 kilobytes (2%) DUMPED.
.
.
Dec 29 15:57:19 2020: Backup Server: 4.188.1.1: Database db_name: 17405778 kilobytes (96%) DUMPED.
Dec 29 15:57:22 2020: Backup Server: 3.43.1.1: Dump phase number 1 completed.
Dec 29 15:57:22 2020: Backup Server: 3.43.1.1: Dump phase number 2 completed.
Dec 29 15:57:22 2020: Backup Server: 3.43.1.1: Dump phase number 3 completed.
Dec 29 15:57:22 2020: Backup Server: 4.188.1.1: Database db_name: 17444912 kilobytes (100%) DUMPED.
The overall duration for compressed backup level 1 is 2 minute 40 seconds i.e. 160 seconds. Lets check the space consumed by this backup:
user@servername:/path>ls -ltr *compress_level01*
-rw-r—– 1 user grp 900384768 Dec 29 15:57 db_name_compress_level01_1.bak
-rw-r—– 1 user grp 901650432 Dec 29 15:57 db_name_compress_level01_2.bak
-rw-r—– 1 user grp 902334464 Dec 29 15:57 db_name_compress_level01_3.bak
-rw-r—– 1 user grp 903067648 Dec 29 15:57 db_name_compress_level01_4.bak
Space consumed by this backup is 3.35GB.
Compression level 3
Lets take the dump with compression level 3, command to take this backup would be:
dump database db_name to
“/path/db_name_dump_compress_level03_1.bak” stripe on
“/path/db_name_dump_compress_level03_2.bak” stripe on
“/path/db_name_dump_compress_level03_3.bak” stripe on
“/path/db_name_dump_compress_level03_4.bak” with compression = “3”
Database dump starts with compression 3 and details from backup log is as below:
Dec 29 16:00:43 2020: Backup Server: 4.188.1.1: Database db_name: 190906 kilobytes (1%) DUMPED.
Dec 29 16:00:44 2020: Backup Server: 4.188.1.1: Database db_name: 362946 kilobytes (2%) DUMPED.
.
.
Dec 29 16:02:44 2020: Backup Server: 3.43.1.1: Dump phase number 1 completed.
Dec 29 16:02:44 2020: Backup Server: 3.43.1.1: Dump phase number 2 completed.
Dec 29 16:02:44 2020: Backup Server: 3.43.1.1: Dump phase number 3 completed.
Dec 29 16:02:44 2020: Backup Server: 4.188.1.1: Database db_name: 17444912 kilobytes (100%) DUMPED.
Dec 29 16:02:44 2020: Backup Server: 3.42.1.1: DUMP is complete (database db_name).
The overall duration for compressed backup level 3 is 2 minute 1 second i.e. 121 seconds. Lets check the space consumed by this backup:
user@servername:/path>ls -ltr *compress_level03*
-rw-r—– 1 user grp 854538240 Dec 29 16:02 db_name_compress_level03_1.bak
-rw-r—– 1 user grp 855252992 Dec 29 16:02 db_name_compress_level03_2.bak
-rw-r—– 1 user grp 855724032 Dec 29 16:02 db_name_compress_level03_3.bak
-rw-r—– 1 user grp 856649728 Dec 29 16:02 db_name_compress_level03_4.bak
Space consumed by this backup(compression level 3) is 3.18GB.
Compression level 5
Lets take the dump with compression level 5, command to take this backup would be:
dump database db_name to
“/path/db_name_dump_compress_level05_1.bak” stripe on
“/path/db_name_dump_compress_level05_2.bak” stripe on
“/path/db_name_dump_compress_level05_3.bak” stripe on
“/path/db_name_dump_compress_level05_4.bak” with compression = “5”
Database dump starts with compression 5 and details from backup log is as below:
Dec 29 16:05:30 2020: Backup Server: 4.188.1.1: Database db_name: 190906 kilobytes (1%) DUMPED.
Dec 29 16:05:32 2020: Backup Server: 4.188.1.1: Database db_name: 362946 kilobytes (2%) DUMPED.
.
.
Dec 29 16:08:40 2020: Backup Server: 3.43.1.1: Dump phase number 1 completed.
Dec 29 16:08:40 2020: Backup Server: 3.43.1.1: Dump phase number 2 completed.
Dec 29 16:08:40 2020: Backup Server: 3.43.1.1: Dump phase number 3 completed.
Dec 29 16:08:40 2020: Backup Server: 4.188.1.1: Database db_name: 17444912 kilobytes (100%) DUMPED.
Dec 29 16:08:40 2020: Backup Server: 3.42.1.1: DUMP is complete (database db_name).
The overall duration for compressed backup level 5 is 3 minute 10 seconds i.e. 190 seconds. Lets check the space consumed by this backup now:
user@servername:/path>ls -ltr *compress_level05*
-rw-r—– 1 user grp 791789568 Dec 29 16:08 db_name_compress_level05_1.bak
-rw-r—– 1 user grp 792514560 Dec 29 16:08 db_name_compress_level05_2.bak
-rw-r—– 1 user grp 793401344 Dec 29 16:08 db_name_compress_level05_3.bak
-rw-r—– 1 user grp 793534464 Dec 29 16:08 db_name_compress_level05_4.bak
Space consumed by this backup(compression level 5) is 2.95GB.
Compression level 9
Lets take the dump with compression level 9, command to take this backup would be:
dump database db_name to
“/path/db_name_dump_compress_level09_1.bak” stripe on
“/path/db_name_dump_compress_level09_2.bak” stripe on
“/path/db_name_dump_compress_level09_3.bak” stripe on
“/path/db_name_dump_compress_level09_4.bak” with compression = “9”
Database dump starts with compression 9 and details from backup log is as below:
Dec 29 16:11:31 2020: Backup Server: 4.188.1.1: Database db_name: 190906 kilobytes (1%) DUMPED.
Dec 29 16:11:48 2020: Backup Server: 4.188.1.1: Database db_name: 362946 kilobytes (2%) DUMPED.
.
.
Dec 29 16:32:23 2020: Backup Server: 3.43.1.1: Dump phase number 1 completed.
Dec 29 16:32:24 2020: Backup Server: 3.43.1.1: Dump phase number 2 completed.
Dec 29 16:32:24 2020: Backup Server: 3.43.1.1: Dump phase number 3 completed.
Dec 29 16:32:24 2020: Backup Server: 4.188.1.1: Database db_name: 17444912 kilobytes (100%) DUMPED.
Dec 29 16:32:24 2020: Backup Server: 3.42.1.1: DUMP is complete (database db_name).
The overall duration for compressed backup level 9 is 20 minute 53 seconds i.e. 1253 seconds. Lets check the space consumed by this backup now:
user@servername:/path>ls -ltr compress_level09
-rw-r—– 1 user grp 743227392 Dec 29 16:32 db_name_compress_level09_1.bak
-rw-r—– 1 user grp 743202816 Dec 29 16:32 db_name_compress_level09_2.bak
-rw-r—– 1 user grp 744036352 Dec 29 16:32 db_name_compress_level09_3.bak
-rw-r—– 1 user grp 744626176 Dec 29 16:32 db_name_compress_level09_4.bak
Space consumed by this backup(compression level 9) is 2.77GB.
Compression level 100
Lets take the dump with compression level 100, command to take this backup would be:
dump database db_name to
“/path/db_name_dump_compress_level100_1.bak” stripe on
“/path/db_name_dump_compress_level100_2.bak” stripe on
“/path/db_name_dump_compress_level100_3.bak” stripe on
“/path/db_name_dump_compress_level100_4.bak” with compression = “100”
Database dump starts with compression 100 and details from backup log is as below:
Dec 29 16:42:39 2020: Backup Server: 4.188.1.1: Database db_name: 190904 kilobytes (1%) DUMPED.
Dec 29 16:42:40 2020: Backup Server: 4.188.1.1: Database db_name: 362946 kilobytes (2%) DUMPED.
.
.
Dec 29 16:46:16 2020: Backup Server: 3.43.1.1: Dump phase number 1 completed.
Dec 29 16:46:16 2020: Backup Server: 3.43.1.1: Dump phase number 2 completed.
Dec 29 16:46:16 2020: Backup Server: 3.43.1.1: Dump phase number 3 completed.
Dec 29 16:46:16 2020: Backup Server: 4.188.1.1: Database db_name: 17444912 kilobytes (100%) DUMPED.
Dec 29 16:46:16 2020: Backup Server: 3.42.1.1: DUMP is complete (database db_name).
The overall duration for compressed backup level 100 is 3 minute 37 seconds i.e. 217 seconds. Lets check the space consumed by this backup now:
user@servername:/path>ls -ltr *compress_level100*
-rw-r—– 1 user grp 1255311360 Dec 29 16:46 db_name_compress_level100_1.bak
-rw-r—– 1 user grp 1255897088 Dec 29 16:46 db_name_compress_level100_2.bak
-rw-r—– 1 user grp 1256321024 Dec 29 16:46 db_name_compress_level100_3.bak
-rw-r—– 1 user grp 1258786816 Dec 29 16:46 db_name_compress_level100_4.bak
Space consumed by this backup(compression level 100) is 4.68 GB.
Compression level 101
Lets take the dump with compression level 101, command to take this backup would be:
dump database db_name to
“/path/db_name_dump_compress_level101_1.bak” stripe on
“/path/db_name_dump_compress_level101_2.bak” stripe on
“/path/db_name_dump_compress_level101_3.bak” stripe on
“/path/db_name_dump_compress_level101_4.bak” with compression = “101”
Database dump starts with compression 101 and details from backup log is as below:
Dec 29 17:04:39 2020: Backup Server: 4.188.1.1: Database db_name: 190906 kilobytes (1%) DUMPED.
Dec 29 17:04:40 2020: Backup Server: 4.188.1.1: Database db_name: 362946 kilobytes (2%) DUMPED.
.
.
Dec 29 17:05:24 2020: Backup Server: 3.43.1.1: Dump phase number 1 completed.
Dec 29 17:05:24 2020: Backup Server: 3.43.1.1: Dump phase number 2 completed.
Dec 29 17:05:24 2020: Backup Server: 3.43.1.1: Dump phase number 3 completed.
Dec 29 17:05:24 2020: Backup Server: 4.188.1.1: Database db_name: 17444912 kilobytes (100%) DUMPED.
Dec 29 17:05:24 2020: Backup Server: 3.42.1.1: DUMP is complete (database db_name).
The overall duration for compressed backup level 101 is 45 seconds. Lets check the space consumed by this backup now:
user@servername:/path>ls -ltr *compress_level101*
-rw-r—– 1 user grp 1155657728 Dec 29 17:05 db_name_compress_level101_1.bak
-rw-r—– 1 user grp 1156552704 Dec 29 17:05 db_name_compress_level101_2.bak
-rw-r—– 1 user grp 1157781504 Dec 29 17:05 db_name_compress_level101_3.bak
-rw-r—– 1 user grp 1158936576 Dec 29 17:05 db_name_compress_level101_4.bak
Space consumed by this backup(compression level 101) is 4.31 GB.
I can understand that it has become really lengthy post. Lets conclude this with a table with size and duration for this database backup for these compression:
| Compression Level | Durations (Seconds) | Backup Size (GB) |
| 1 | 160 | 3.35 |
| 3 | 121 | 3.18 |
| 5 | 190 | 2.95 |
| 9 | 1253 | 2.77 |
| 100 | 217 | 4.68 |
| 101 | 45 | 4.31 |
I think, I have covered all the aspects of compression with regards to dump. You may take a call for your organization based on your needs.
I have covered this in my video here:
More posts will be there on covering load with stripe. Please leave your comment if you liked this post or have any feedback.