Starting with Oracle 11g Oracle added several compression algorithms to compress data. They can be used for compressing tables, LOBs , compressed data pump exports or even RMAN backups. Unfortunately for some compression algorithms you need to purchase the “Advanced Compression Option”. The following table lists the available RMAN compression options, the most likely compression algorithm being used and states if an additional license is required:
RMAN Compression Type Compression Algorithm used Adv. Compression License required? Backup set size CPU Load
BASIC BZIP2 (100k record size?) No small medium to high
NONE none No largest; approx. db size extremely small
LOW LZO YES somewhat smaller than using NONE low
MEDIUM ZLIB YES medium medium
HIGH BZIP2 (900k record size?) YES smallest highest
This article is intended to take a look at the different compression methods available in Oracle 11g and to compare them.
Test environment
The environment being used was a freshly created 11g Release 2 database with some smaller tables in it. The total sum of all segments equals to 4.88 GB. All database data files excluding the temporary ones are 7.3 GB total. Excluding temporary and undo data files total size equates to 5.9 GB.
The server was running on VMWARE with one dedicated CPU core per host and 4 GB memory.
Configure RMAN
To configure RMAN to use compression at all you can use:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
or
CONFIGURE DEVICE TYPE TAPE BACKUP TYPE TO COMPRESSED BACKUPSET;
To configure the different backup algorithms:
CONFIGURE COMPRESSION ALGORITHM 'BASIC'; CONFIGURE COMPRESSION ALGORITHM 'NONE'; CONFIGURE COMPRESSION ALGORITHM 'LOW'; CONFIGURE COMPRESSION ALGORITHM 'MEDIUM'; CONFIGURE COMPRESSION ALGORITHM 'HIGH';
Test results
The following table displays the test results for the chosen test environment:
NONE (=uncompressed) BASIC LOW MEDIUM HIGH
Size 4080192 KB 356924 KB 430200 KB 373288 KB 238200 KB
CPU Load < 5% per Channel 70 to 80% per Channel 20% per Channel 50% per Channel 100% per Channel
Test 1 281 sec 291 sec 213 sec 203 sec 2992 sec
Test 2 334 sec 267 sec 187 sec 189 sec long, very long
Test 3 272 sec 278 sec 189 sec 212 sec long, very long
Test 4 324 sec 299 sec 157 sec 188 sec long, very long
Test 5 260 sec 257 sec 157 sec 192 sec long, very long
Minumum 260 257 157 188 n/a
Maximum 334 299 213 212 n/a
Avg. 294 278 180 196 n/a
As you can see from the table HIGH compression does an incredibly high load on the machine and take extremely long but produces the smallest backup set size. I suppose the long backup time might be explained by the fact that the environment is running under VMWARE which adds additional overhead and slows down CPU. Surprisingly BASIC compression (which is available without advanced compression license) does a good job as well and produces the second smallest backup set but takes nearly as long as doing uncompressed backups. But in other environment with faster CPUs this will change!
In the test environment used either LOW or MEDIUM compression seems to be the best choice. Due to the fact MEDIUM produces a approx. 15% smaller backup set but taking only a few seconds more time to complete i would rank MEDIUM on 1st and LOW on second.
Conclusion
RMAN offers backup compression since version 10. But with 11g Oracle added several new compression algorithms which lets you adjust the IO- and CPU-consumption and the resulting backup set size by choosing between five algorithms: NONE, DEFAULT, LOW, MEDIUM, HIGH. The stronger the compression the smaller the backup size but the more CPU-intensive the backup is. You basically change CPU-cycles for IOPs. If you do not have the advanced compression license BASIC compression will produce reasonable compression rates at moderate Load. If you have the licence you have a lot more options to suit your needs.
Regardless of the results in THIS test case YOUR results will be different due to: different data which leads to different compression ratios, different storage architecture for data files and backup destination (local disks, SAN, NAS, Tape), different CPU resources (faster/slower CPUs), rman parallelism). The test performed here are intended to give you some basic idea how backup time and backup size will change depending on the compression chosen.
!! Because of many factors influencing backup speed and backup size you have to test backup and backup compression in your environment yourself. There is no general rule of thumb. !!
If you want to test and optimize your rman backup you basically have three major switches to play with:
- compression algorithmn,
- rman parallelism and
- data transfer mechanism (SAN or Ethernet [this includes: iSCSI, NFS, CIFS, Backup to tape over Ethernet])
Thank you for this useful post. But I had difficulty navigating through your site because I kept getting 502 bad gateway error. Just thought to let you know.
Strange thing. Youre the first person to say that. I will take a look into the log files. Thanks!
Hi
I would be interested to know how you tied the compression algorithms (LZO, BZIP & ZLIB) to the 11g RMAN compression settings (LOW/MED/HIGH)? I know the BASIC one is known. Care to share?
Cheers
F.
Thank you so much for this precious information.
Hi
Wanted to know what your experience is for the average compression ratio for rman compression.
Thank you for you help.
If you only store textual data in the database it is up to 1:8 (basic / high compression). For low compression it is apporx 1:3
Care to share your scripts. I would like to do the same thing for our environment.
Nothing to share here. Full backup of a given database with modified compression algorithmn.
Very good and useful information on comparing the different options. Good to know that BASIC option does not cost (No need buy Advanced Compression license).
Thanks a lot!
Good Article Ronny, Was helpfull. Thank you.
Hi Ronny,
nice to read your test. I had no problem with the high compression rate. The main point is. if you can get a return of investment.
I want to make you aware that your full article was copied by someone and given as his own work.
I have no problem if information are shared but copying 1:1 and telling it is the own work is inacceptable.
I thought you are interested to know this.
http://neeraj-dba.blogspot.com/2011/12/configure-rman-backupset-compression.html
All the best
Tom
Hi Thomas,
thanks you for your comment.
About the plagiatism… there is probably nothing i can do. But these people exist everywhere…
HI,
Can you please let me if there in any reference in oracle documentation for setting compression to NONE
CONFIGURE COMPRESSION ALGORITHM ‘NONE’;
I am getting below error message:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 08/21/2015 11:40:38
RMAN-06806: compression algorithm ‘NONE’ of release DEFAULT not found
Try: CONFIGURE COMPRESSION ALGORITHM CLEAR
Hi Ronny,
I facing the same problem at one of my customer, while analyzing the high CPU load, I am sure this will help me to explain my customer that why CPUs are highly utilized at the time of backup. I must say you shared a good knowledge on RMAN compression algorithm.
Muhammad