Data Compression
User ran IDS table compression, saw a reported ~67% compression ratio, but the table's allocated size (via a sysmaster extent query) didn't shrink. Answer from John Miller/others: "compress" only shrinks rows in place, leaving free space within pages; you must also run repack and shrink, e.g. execute function task("table compress repack shrink","db","table"), and space won't drop below the first extent size (use ALTER TABLE MODIFY EXTENT SIZE). Use oncheck -pt/-pT to see free bytes. Blob/clob/byte/text and index pages aren't compressed; another user recovered much more space by dropping and rebuilding indexes. The original poster's specific table case wasn't explicitly confirmed resolved.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, When I compress the data in a table, After Compression, the result mentions that 67% Compression has been achieved, but the size of the table remains the same as before compression? Is there an issue with data compression that it says compression has been done, but have'nt? Is Data Compression compresses any particular type of data or can't compress any particular type of data. I have run compression on the data having byte data type for one field. Regads
Hi,
Where and how are you checking the table size.
I check the "oncheck -pr <dbname>:<tabname>" for the size of my table
The estimation was about 44% ie table would compress to about 490 Mb and after
the compression (along with repack and shrink) i got the table size as 545 Mb.
But i guess thats close if not accurate as compared to estimation!
Regards
Vikas
*******************************************************************************
Hi,
When I compress the data in a table, After Compression, the result mentions
that 67% Compression has been achieved, but the size of the table remains the
same as before compression?
Is there an issue with data compression that it says compression has been
done, but have'nt?
Is Data Compression compresses any particular type of data or can't compress
any particular type of data.
I have run compression on the data having byte data type for one field.
Oops, sorry for the typo
It is "oncheck -pt <dbname>:<tabname>"
*******************************************************************************
Hi,
Where and how are you checking the table size.
I check the "oncheck -pr <dbname>:<tabname>" for the size of my table
The estimation was about 44% ie table would compress to about 490 Mb and after
the compression (along with repack and shrink) i got the table size as 545 Mb.
But i guess thats close if not accurate as compared to estimation!
Regards
Vikas
*******************************************************************************
Hi,
When I compress the data in a table, After Compression, the result mentions
that 67% Compression has been achieved, but the size of the table remains the
same as before compression?
Is there an issue with data compression that it says compression has been
done, but have'nt?
Is Data Compression compresses any particular type of data or can't compress
any particular type of data.
I have run compression on the data having byte data type for one field.
Hi, I am using the following query. select s.tabname[1,25], substr(rowsize||"_"||nrows,1,12) rsize_nrows, count(*) nexts, sum(pe_size)*2 total_Kb from sysmaster:systabnames s, sysmaster:sysptnext x, <db_name>:systables t where s.partnum = pe_partnum and t.tabid > 99 and dbsname = '<db_name>' and t.partnum=s.partnum and t.tabname = '<table_name>' group by 1,2 order by count(*) desc; Even after doing compression, the size represents the same as before applying compression. Do you know which Algo is being used in Data Compression in IDS? And also, is it a loss-less compression activity ? Thanks
>
> Hi,
> When I compress the data in a table, After Compression, the result
mentions
> that 67% Compression has been achieved, but the size of the table remains
the
> same as before compression?
The compress operation just compresses each row, it doesn't reduce the size
of the table.>
You will have to perform "repack" and "shrink" operation to reduce the size
of the table.>
For e.g.
execute function sysadmin:task("table compress repack shrink", "table","database", "owner");
>
> Is there an issue with data compression that it says compression has been
> done, but have'nt?
>
> Is Data Compression compresses any particular type of data or can't
compress
> any particular type of data.
>
> I have run compression on the data having byte data type for one field.
>
> Regads
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Just to be clear here, if you run compress by itself, the size of the
table size will
not change. Hence if you execute a command like:
execute function task("table compress", "database","tablename");
That is expected behavior. What compression does is compress
each row in place (if it can). This mean if you have a single 2KB page
with
4 rows of 500 bytes. Compression will keep all of the rows on the same
page
and shrink them. So now you might have 4 rows on the exact same page,
each
of size 250 bytes. The page now has 1000 bytes free for other rows to
consume.
Now if you want to compress the table and tighten up all this free space
and return
the free space to the system. Then you need to add two additional commands
to
your command line. The first is the "repack" command. This will take the
rows
at the end of the table and move them to the free space in the beginning of
the table.
When this command completes all the free space will be at the end of the
table.
The last command to add to the compress command line is the "shrink"
command. This
will remove any free space at the end of the table and return it to the
main ids pool of
free space. It will not attempt to free space below your first extent
size. If you have
large first extents use the new alter table command to change your first
extent size so
shrink can free space up below your first extent size.
In short if you want to compress and make a table as small as you can (but
never smaller than
your first extent size) then run the following commands:
execute function task("table compress repack shrink","database","tablename");
One think to be aware of is that the "repack" and "shrink" commands can be
run on table
which are not compressed. If you have a table which you had inserted lots
of rows, then
purged say 80% of these rows and you want to return this purges space back
to IDS. The
consider using the "repack" and "shrink" commands. These run online and
will return the
extra space back to IDS.
execute function task("table repack shrink","dbname","tablename");
Your last question concerns the type of data compression will compress. It
will compress
all data stored in row. It will not compress blob,clob,byte,text data, nor
will it compress
data stored on index pages.
An additional side benefit is that the data stored in the logical log is
also compressed
so the logical log space consumed should also drop. So in you inserted a
500 byte row in
a logged database this row would consume approximately 500 bytes in the
logical logs, If this
table is compressed, then the compressed image of the row is stored in the
database. This
is a double win for those using Enterprise Replication, HDR or MACH.
My last comment is that while repack and shrink come with the base server,
compression
is an add on feature.
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 07/09/2009 03:59:32 AM:
> [image removed]
>
> Data Compression [16278]
>
> SHAHZAD SALAM KASI
>
> to:
>
> ids
>
> 07/09/2009 04:00 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Hi,
> When I compress the data in a table, After Compression, the result
mentions
> that 67% Compression has been achieved, but the size of the table remains
the
> same as before compression?
>
> Is there an issue with data compression that it says compression has been
> done, but have'nt?
>
> Is Data Compression compresses any particular type of data or can't
compress
> any particular type of data.
>
> I have run compression on the data having byte data type for one field.
>
> Regads
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
There are very few command which will show you the results of
just running the compression command by itself. Your query
below will only show results if you include the shrink and repack
options.
If you want to see results of just the compress command by itself
use the oncheck -pT dbs:tabname This will show you how many
free bytes are in the table. This should increase greatly after running
compression. An example of what you want to look for is below.
oncheck -pt sysmaster:systables
TBLspace Usage Report for sysmaster:informix.systables
Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 5
Bit-Map 1
Index 13
Data (Home) 21
Data (Remainder) 0 0 0 0 0
----------
Total Pages 40
Unused Space Summary
Unused data bytes in Home pages 10530
Unused data bytes in Remainder pages 0
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 07/09/2009 05:52:46 AM:
> [image removed]
>
> Re: Data Compression [16281]
>
> SHAHZAD SALAM KASI
>
> to:
>
> ids
>
> 07/09/2009 05:56 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Hi,
>
> I am using the following query.
>
> select s.tabname[1,25], substr(rowsize||"_"||nrows,1,12) rsize_nrows,
> count(*) nexts, sum(pe_size)*2 total_Kb
> from sysmaster:systabnames s, sysmaster:sysptnext x, <db_name>:systables
t
> where s.partnum = pe_partnum
> and t.tabid > 99
> and dbsname = '<db_name>'
> and t.partnum=s.partnum
> and t.tabname = '<table_name>'
> group by 1,2
> order by count(*) desc;
>
> Even after doing compression, the size represents the same as
beforeapplying
> compression.
>
> Do you know which Algo is being used in Data Compression in IDS?
> And also, is it a loss-less compression activity ?
>
> Thanks
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
I everybody, Im new with data compression, really I have a few days using in a
test environment these feature, I post in the oat group some problems with the
interface (too slow to get the list of tables from a database). Analyzing some
tables, after compress, pack, shrink, really don`t see too much space restored
to ids, i think which I need to drop and recreate the indexes, these is needed
or I`m wrong in my evaluation? sorry for my english. I have the oncheck -pt
before the compressions task.
Hi,
Thanks for the response. I would like to clear that i have run the following
command.
execute function task("table compress repack shrink", "tablename");
The same command has been used for multiple tables. In one table, it shows the
compression is done at 52% and when i checked the size of the table after
doing compression, its size is actually reduced to approximately 48%. So, its
been done brilliantly.
But, when the same activity has been performed on other table, the result
shows that 67% compression has been achieved. But after viewing the size of
the table after performing compression, its size remains the same as earlier.
So, what is the issue here ? If no compression is done, then why 67% figure is
being mentioned? It means, if we have to compress many tables, then we cant
rely on the compression activity, as it may or may not reduce the size of the
table.
Regards
Hi all, in my test environment, the space returned to IDS was great only after dropping and recreating the indexes! Before, we made changes to the first and next extent size.
There was a post about this from someone else yesterday answers in the forum
history. Quick & Dirty? You have to pack the table to put more rows on a
page since they are now compressed (compression doesn't move the rows just
shrinks each one leaving free space on each page). They you can compact the
table to release unused pages back to the common free page pool.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Thu, Jul 9, 2009 at 11:54 PM, GUSTAVO TOBARES <
gustavo.tobares@emegatone.com> wrote:
> I everybody, Im new with data compression, really I have a few days using
> in a
> test environment these feature, I post in the oat group some problems with
> the
> interface (too slow to get the list of tables from a database). Analyzing
> some
> tables, after compress, pack, shrink, really don`t see too much space
> restored
> to ids, i think which I need to drop and recreate the indexes, these is
> needed
> or I`m wrong in my evaluation? sorry for my english. I have the oncheck -pt
> before the compressions task.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e68dbd9767049c046e5b8794
Thanks for your comments! I drop and recreate the indexes on the tables "compressed" and then recover to much space than just the shrink command. I suggest to make this extra work to recover much more space to the dbspaces. Thanks!
Sorry, I forgot to mention which you must check the next extent size and the first extent size of the table, and modify them with "alter table x modify extent size yKB" and "alter table x modify next size yKB" for the first and next extent size. Thanks Again!