Compression performance
A DBA on IDS 11.50.FC6/AIX 6.1 asked how to speed up compression/repack of a 2.4-billion-row, 210GB table in 13 fragments, which had already run 25+ hours. Suggestions: archive/split the historical data; from IBM's John Miller, instead of a single "table compress repack shrink" task, generate per-fragment "fragment compress repack shrink" calls using partnums from sysmaster:systabnames and run the 13 in parallel, monitoring with onstat -g dsk or sysstoragemgr. Another poster noted indexes slow repack/uncompress considerably, so dropping and rebuilding them with PDQ helps. The poster thanked Miller, saying he hadn't been working fragment by fragment; no final timing results were reported.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.50.fc6 Aix 6.1 I have a table which contains approximately 2.4 billion rows. It's currently fragmented over 13 fragments. It's approximately 210gb. I am doing some testing with the compression feature. I started the compression of this table about 25 hours ago. My question is, are there certain environment or onconfig parameters that can be set to help with the performance of the compression and repack statements? Thanks in advance for any help .... Peter Logan Senior Database Administrator Phone: 616/878-8309
Peter, I am sure you have considered this already but its worth asking. Are you sure you need 2.5 billion rows online and available all the time? is this table not a candidate for archiving or splitting up? Also does the table contains chars where a varchar could be better used? Regards Andy G. > To: ids@iiug.org > From: Peter_Logan@spartanstores.com > Subject: Compression performance [20411] > Date: Thu, 17 Jun 2010 10:57:36 -0400 > > IDS 11.50.fc6 > Aix 6.1 > > I have a table which contains approximately 2.4 billion rows. It's > currently fragmented over 13 fragments. It's approximately 210gb. I am > doing some testing with the compression feature. I started the > compression of this table about 25 hours ago. My question is, are there > certain environment or onconfig parameters that can be set to help with > the performance of the compression and repack statements? > > Thanks in advance for any help .... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ http://clk.atdmt.com/UKM/go/197222280/direct/01/ We want to hear all your funny, exciting and crazy Hotmail stories. Tell us now
It's actually in an instance of historical data ... So, it's rarely accessed other than to add new rows ... Yes, it contains varchars .... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Andrew Grantham" <agrantha@hotmail.com> To: ids@iiug.org Date: 06/17/2010 11:39 AM Subject: RE: Compression performance [20412] Sent by: ids-bounces@iiug.org Peter, I am sure you have considered this already but its worth asking. Are you sure you need 2.5 billion rows online and available all the time? is this table not a candidate for archiving or splitting up? Also does the table contains chars where a varchar could be better used? Regards Andy G. > To: ids@iiug.org > From: Peter_Logan@spartanstores.com > Subject: Compression performance [20411] > Date: Thu, 17 Jun 2010 10:57:36 -0400 > > IDS 11.50.fc6 > Aix 6.1 > > I have a table which contains approximately 2.4 billion rows. It's > currently fragmented over 13 fragments. It's approximately 210gb. I am > doing some testing with the compression feature. I started the > compression of this table about 25 hours ago. My question is, are there > certain environment or onconfig parameters that can be set to help with > the performance of the compression and repack statements? > > Thanks in advance for any help .... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ http://clk.atdmt.com/UKM/go/197222280/direct/01/ We want to hear all your funny, exciting and crazy Hotmail stories. Tell us now ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
In that case can it be broken down into years for example if its historical data? Or months even? > To: ids@iiug.org > From: Peter_Logan@spartanstores.com > Subject: RE: Compression performance [20414] > Date: Thu, 17 Jun 2010 11:42:26 -0400 > > It's actually in an instance of historical data ... So, it's rarely > accessed other than to add new rows ... > > Yes, it contains varchars .... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > From: > "Andrew Grantham" <agrantha@hotmail.com> > To: > ids@iiug.org > Date: > 06/17/2010 11:39 AM > Subject: > RE: Compression performance [20412] > Sent by: > ids-bounces@iiug.org > > Peter, > > I am sure you have considered this already but its worth asking. Are you > sure > you need 2.5 billion rows online and available all the time? is this table > not > a candidate for archiving or splitting up? Also does the table contains > chars > where a varchar could be better used? > > Regards > > Andy G. > > > To: ids@iiug.org > > From: Peter_Logan@spartanstores.com > > Subject: Compression performance [20411] > > Date: Thu, 17 Jun 2010 10:57:36 -0400 > > > > IDS 11.50.fc6 > > Aix 6.1 > > > > I have a table which contains approximately 2.4 billion rows. It's > > currently fragmented over 13 fragments. It's approximately 210gb. I am > > doing some testing with the compression feature. I started the > > compression of this table about 25 hours ago. My question is, are there > > certain environment or onconfig parameters that can be set to help with > > the performance of the compression and repack statements? > > > > Thanks in advance for any help .... > > > > Peter Logan > > Senior Database Administrator > > Phone: 616/878-8309 > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > _________________________________________________________________ > http://clk.atdmt.com/UKM/go/197222280/direct/01/ > We want to hear all your funny, exciting and crazy Hotmail stories. Tell > us > now > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ http://clk.atdmt.com/UKM/go/195013117/direct/01/ We want to hear all your funny, exciting and crazy Hotmail stories. Tell us now
It would be nice to have the command you are running to
compress/repack/shrink this
table. The table should remain online the entire time for all your
applications and
users.
I am going to assume that you ran the following command:
execute function sysadmin:task("table compress repack shrink",
"tab1", "dbs1")
Since you have such a large table which is fragmented and want to get it
done faster you can run the
following which will create commands, which you then can execute in
parallel.
select "execute function sysadmin:task('fragment compress repack shrink', "
|| partnum || ");"
from sysmaster:systabnames
where tabname = "tab1" and dbsname = "dbs1";
This will generate 13 commands which you can run in parallel.
In addition you can use onstat -g dsk or sysmaster:sysstoragemgr table to
view
the progress and how fast it is running. In general the longer it take
more compression
and compaction you are getting.
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 06/17/2010 07:57:36 AM:
> [image removed]
>
> Compression performance [20411]
>
> Peter_Logan@spartanstores.com
>
> to:
>
> ids
>
> 06/17/2010 07:58 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> IDS 11.50.fc6
> Aix 6.1
>
> I have a table which contains approximately 2.4 billion rows. It's
> currently fragmented over 13 fragments. It's approximately 210gb. I am
> doing some testing with the compression feature. I started the
> compression of this table about 25 hours ago. My question is, are there
> certain environment or onconfig parameters that can be set to help with
> the performance of the compression and repack statements?
>
> Thanks in advance for any help ....
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hi, Do you have indexes on your table that you want to compress and repack? 1/ Compression: If you do have indexes on your table, the COMPRESS+ REPACK will take longer. If you can, drop your indexes, compress and repack, then recreate your indexes using PDQ to go faster. If your want to compress only without REPACK, there is no need to drop your indexes. Of course, doing the compression in parallel for each fragment will help quite a bit. 2/ Uncompression: If you have indexes, the uncompression takes for sure a lot longer. Just to give you an idea, uncompressing a 30 million row table with 4 indexes took 3 hours with indexes in place and 40 minutes without indexes + 10 minutes to create the indexes back. Khaled Bentebal ConsultiX Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr ----- Original Message ----- From: <Peter_Logan@spartanstores.com> To: <ids@iiug.org> Sent: Thursday, June 17, 2010 4:57 PM Subject: Compression performance [20411] > IDS 11.50.fc6 > Aix 6.1 > > I have a table which contains approximately 2.4 billion rows. It's > currently fragmented over 13 fragments. It's approximately 210gb. I am > doing some testing with the compression feature. I started the > compression of this table about 25 hours ago. My question is, are there > certain environment or onconfig parameters that can be set to help with > the performance of the compression and repack statements? > > Thanks in advance for any help .... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
Thanks John for the suggestion ... I wasn't doing it by fragment ...
Peter
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From:
"John Miller iii" <miller3@us.ibm.com>
To:
ids@iiug.org
Date:
06/17/2010 08:40 PM
Subject:
Re: Compression performance [20423]
Sent by:
ids-bounces@iiug.org
It would be nice to have the command you are running to
compress/repack/shrink this
table. The table should remain online the entire time for all your
applications and
users.
I am going to assume that you ran the following command:
execute function sysadmin:task("table compress repack shrink",
"tab1", "dbs1")
Since you have such a large table which is fragmented and want to get it
done faster you can run the
following which will create commands, which you then can execute in
parallel.
select "execute function sysadmin:task('fragment compress repack shrink',
"
|| partnum || ");"
from sysmaster:systabnames
where tabname = "tab1" and dbsname = "dbs1";
This will generate 13 commands which you can run in parallel.
In addition you can use onstat -g dsk or sysmaster:sysstoragemgr table to
view
the progress and how fast it is running. In general the longer it take
more compression
and compaction you are getting.
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 06/17/2010 07:57:36 AM:
> [image removed]
>
> Compression performance [20411]
>
> Peter_Logan@spartanstores.com
>
> to:
>
> ids
>
> 06/17/2010 07:58 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> IDS 11.50.fc6
> Aix 6.1
>
> I have a table which contains approximately 2.4 billion rows. It's
> currently fragmented over 13 fragments. It's approximately 210gb. I am
> doing some testing with the compression feature. I started the
> compression of this table about 25 hours ago. My question is, are there
> certain environment or onconfig parameters that can be set to help with
> the performance of the compression and repack statements?
>
> Thanks in advance for any help ....
>
> 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.