On Wed, 25 Apr 2007 06:31:01 -0700, Ben wrote:
> 9.2.0.5 Ent Ed AIX5L
>
> So I read an article on table compression this morning and it sounds
> like good stuff.
The main use of compression is not saving space but cutting down on
the I/O. Basically, you will have many fewer blocks in the compressed
table then in an uncompressed one, which will save you some I/O at the
expense of some CPU. So, when is there a dire need to save some I/O?
- When you're having large range scans or full scans that you need
done quickly.
- When you want to make the table so small that full scan will cause
no physical reads. That is usually accomplished by COMPRESS and CACHE
attributes. In my opinion IOT are much better for small lookup tables
but sometimes compressed and cached table is just as good solution.
- When you have very write-intensive application and relatively slow
peripherals. I frequently see compressed tables used on Linux boxes
which have notoriously low I/O capacity. I work for a company that
is loading tons of data (~ 3,000,000 records a day) into several
tables for later processing. I tuned I/O well (6 4GB redo logs,
8MB log buffer, 2 DB writer processes, asynchronous I/O, 20 freelists,
25% of free space, 32 inittrans, reverse index for primary key and
the analog boxes feeding the database were still too fast. I also
went after the Linux kernel and fixed network parameters, Ext3
mount options and the Ethernet drivers. My compressing few critical
tables made a difference and caused the boxes to report backlog once
a week instead of once a day. It has cut down the number of I/O
requests for approximately 12% which was enough. Once we overgrow
that, the only thing to do will be to buy a box with more I/O
bandwidth.
- When your application has been designed by little bearded people with
funny hats, living in a swamp and you desperately need to cut down on
the number of blocks read, you compress everything in sight when all
other options are exhausted. I've seen many applications that I would
punish by slow and painful death, if it was legal. Table compression
can sometimes help you, but not as much as imagining the responsible
developer in a pool full of piranhas. Joy starts from within.
If your table is compressed you cannot add or drop columns. You have to
rebuild the entire table. If your schema is not stable and is prone to
frequent changes, don't do it.
--
http://www.mladen-gogala.com
Received on Fri Apr 27 2007 - 00:31:21 CDT