Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 3 of 3 total
Thread Effect of Compression Level on database size..
Mon, Nov 30 2020 1:59 PMPermanent Link

Ian Branch

Avatar

Hi Team,
Database with 40+ tables, most have CLOBS, BLOBS or both.
On a very few I had already set the field's Compression Level to Optimal.
So I thought I would set CLOBs & BLOBs from None to Optimal.  Optimal has to be good.  Right?
To my surprise the overall Database size rose from 844MB to 1,033MB.  Not what I expected.
What is happening here?  
Were my expectations wrong?  
What effect on Performance might this now have?

Regards & TIA,
Ian
Mon, Nov 30 2020 5:02 PMPermanent Link

Raul

Team Elevate Team Elevate

On 11/30/2020 1:59 PM, Ian Branch wrote:
> Database with 40+ tables, most have CLOBS, BLOBS or both.
> On a very few I had already set the field's Compression Level to Optimal.
> So I thought I would set CLOBs & BLOBs from None to Optimal.  Optimal has to be good.  Right?
> To my surprise the overall Database size rose from 844MB to 1,033MB.  Not what I expected.
> What is happening here?

Can you confirm the actual table sizes increased by 20% ? (and it's not
backup files or such. also optimize tables just to make sure it was not
temporary).

If that is the case then possibly your data is not compressible.

AFAIk compression is just as described - data is compressed when stored
and uncompressed when accessed.

The different levels basically represent ZLib compression levels where 1
is fastest speed and 9 is best compression).

Benefit of compression is that potentially DB has to do lot less disk
I/O (i.e. instead of writing 2MB to disk it has to write only 1MB when
compressed). Downside is that extra cycles of CPU are now needed when do
compress/decompress.

Related in EB D is remote compression - used to compress data being
sent/received to again trade CPU cycles against smaller data packets
that need to be sent over possibly slow connection(s).


> Were my expectations wrong?

No but compression benefit really depends on data being compressed.

Worst case for example is something encrypted - by definition it is
supposed to look random so it usually is same or larger than
uncompressed data.  Similar example is when storing things like modern
zip files since those are likely already same or better compressed than
what zlib can manage (it's optimized for line speed).

On the other end are things like web pages or emails or other text data
which likely has tons of white space and repetition or such.

> What effect on Performance might this now have?

In general there is the extra overhead of doing compression/decompression.

However in your case if it's truly larger now then there is also
potential for some extra disk i/o now (since more data needs to be
read/written to disk)

Raul
Mon, Nov 30 2020 8:15 PMPermanent Link

Ian Branch

Avatar

Hi Raul,
Thanks for your feedback.  Appreciated.
Fortunately I have a full set of Pre & Post Compression/Optimized files.
Pre   - 110 files - 1,512,262,280 bytes.
Post - 110 files - 1,451,627,320 bytes.
So Yes, the Post raw files are smaller, and yet the Post Backup file is larger.  As  you suggest, it is probably the ability of EDB's backup function.

Regards,
Ian
Image