IDS Forum

Re: Compression/Uncompression Testing

Posted By: John Miller iii
Date: Thursday, 27 August 2009, at 9:00 p.m.

In Response To: Re: Compression/Uncompression Testing (Kamini Jagtiani)

Peter:

A few thing I would like to make you aware of:

1. Running the command "table compress" or "fragment compress" will yield

rows that are compress. These rows are generally compressed in
place.

This means that if you have 10 rows on a page and then you compress
this

page you will still have 10 compressed rows on that page with more
free

space on each page.

2. I would suggest that you run or add the "repack" command to fully
realize all the benefits.

This will move all rows to the beginning of the table, utilizing the
free space on the page.

Example:

If you have a table which has 500 pages with each page hold 20
rows

then you execute:

EXECUTE FUNCTION task ("table compress " "<tabname>")

You will still have a table of 500 pages and there will still
be 20 row on each page.

EXECUTE FUNCTION task ("table repack" "<tabname>")

You will now still have a table of 500 pages, but now the first
250 pages holds

40 rows on each page and the last 250 pages are empty.

Lastly, if you want to remove that unused space at the end of a
table you should run a shrink.

EXECUTE FUNCTION task ("table shrink" "<tabname>")

You will now have a table of 250 pages holding 40 rows on each
page. (Note the free

250 pages was give back to the main IDS storage pool).

In short to get the full benefit from compression you will have to run
three commands:

1. compress

2. repack

3. shrink
They can be run together or independently.

EXECUTE FUNCTION task ("table compress repack shrink" "<tabname>")

OR

EXECUTE FUNCTION task ("table compress " "<tabname>")

EXECUTE FUNCTION task ("table repack" "<tabname>")>

EXECUTE FUNCTION task ("table shrink" "<tabname>")>

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 08/27/2009 05:06:56 PM:

> [image removed]
>
> Re: Compression/Uncompression Testing [16800]
>
> Kamini Jagtiani
>
> to:
>
> ids
>
> 08/27/2009 05:07 PM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Hi Peter,
> No,afaik there is no real tuning in terms of tuning onconfig parameters.
>
> There are some general things you could do:
>
> 1.If you are running compress on a fragmented table and you want to
> compress/uncompress all fragments , then instead of running
> EXECUTE FUNCTION task ("table compress " "<tabname>")--------------->(1)
>
> you could fire off separate commands
> EXECUTE FUNCTION task ("fragment compress" "partnum1");--------------->
(2)
> EXECUTE FUNCTION task ("fragment compress " "partnum2");------
>
> Option 2 could get you some parallelism...
>
> 2.You could change it to raw table before starting the compress and
repack
> operation.That will speed up the operation
>
> 3.You could also try repack_offline option, which will do the repack
> operation in an exclusive way, so that it does not have to wait on locks.

>
> Thanks,
> Kamini
>
> "Peter_Logan@spartanstores.com" <Peter_Logan@spartanstores.com>
> Sent by: ids-bounces@iiug.org
> 08/27/2009 12:37 PM
> Please respond to
> ids@iiug.org
>
> To
> ids@iiug.org
> cc
>
> Subject
> Compression/Uncompression Testing [16794]
>
> I am testing the compression and uncompression working on building a cost

> justification of the feature ... Doing this on an Aix v6 P6 ...
> v11.50.FC5 of the engine ... I have compressed ... repacked .. and
> shrinked the tables and got very good results ... My question has to do
> with the performance of the functions ... are there any tuning
> considerations that one should make while doing the compression and
> uncompression. The processes seem to be extremely slow ... Maybe that is
> just the nature of the process, but if anyone has any pointers, please
let
>
> me know.
>
> Peter
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
>
>
*******************************************************************************

>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************

> Forum Note: Use "Reply" to post a response in the discussion forum.
>

Messages In This Thread

• Compression/Uncompression Testing
  Peter_Logan@spartanstores.com -- Thursday, 27 August 2009, at 3:37 p.m.
  • Re: Compression/Uncompression Testing
    Kamini Jagtiani -- Thursday, 27 August 2009, at 8:06 p.m.
    • Re: Compression/Uncompression Testing
      John Miller iii -- Thursday, 27 August 2009, at 9:00 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.