Compression/Uncompression Testing

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

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.

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. >