Fragmentation
User reported that adding new fragments to a fragmented index on a 5TB table was very slow and not behaving atomically, despite having no remainder clause. Kate Tomchik advised fragmenting the table (not just the index) so the primary key index gets created implicitly with the table, ensuring data and index storage happen together. She also recommended disabling non-fragmentation-strategy indexes during bulk data input.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
( Okay all, I have officially given up on trying to respond from work. Here
is what I tried to write. )
You are fragmenting the index with this statement, but you said it is the
primary key of your table. Can't you just fragment the table this way? The
index will get created implicitly and stored with the table, therefore for
this index new data will not cause two separate actions, one to store the
data and one to store the index.
And it is always a good idea to have a remainder clause even if you can't
foresee it ever being used.
Did you mention there is more than one index on this table? Any index that
is not part of the fragmentation strategy will take a very long time to
add. You should consider if you can remove this second index, but if not
you definitely need to disable that index during data inputs and rebuild
after the data is completed.
Thanks,
Kate Tomchik, IT Architect
-----Original Message-----
From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]
On Behalf Of Jarrod Teale
Sent: Monday, December 15, 2008 9:58 PM
To: informix-list@iiug.org
Subject: Attach fragment to index not atomic
Hi,
IDS11.10.UC2W2 on RHEL4
We have the index (and a few more like it) below. It is used for the primary
key of a table.
When I attach a new fragment to the system it reads every page of the index.
So adding 12 fragments, one for each month is a pain.
alter fragment on index pk_tagdata_73 add (dt_filter = 200901 ) instan_03a_idx_2009_01 before stan_03a_idx_2008_01
As you can see from the schema, there is no remainder clause, so I would
expect this operation to be atomic. The fragments for the table are (same
fragment scheme).
There is no data in the table for the fragments being added (the date filter
is for Jan 2009)
Any ideas why this is not running as an atomic operation? It's taking a
really long time over 5 TB data!
create unique index "sdev".pk_tagdata_73 on "sdev".tagdata_73
(group_id,dt_filter,sample_dt) using btree
fragment by expression
(dt_filter = 200801 ) in stan_03a_idx_2008_01 ,
(dt_filter = 200802 ) in stan_03a_idx_2008_02 ,
(dt_filter = 200803 ) in stan_03a_idx_2008_03 ,
(dt_filter = 200804 ) in stan_03a_idx_2008_04 ,
(dt_filter = 200805 ) in stan_03a_idx_2008_05 ,
(dt_filter = 200806 ) in stan_03a_idx_2008_06 ,
(dt_filter = 200807 ) in stan_03a_idx_2008_07 ,
(dt_filter = 200808 ) in stan_03a_idx_2008_08 ,
(dt_filter = 200809 ) in stan_03a_idx_2008_09 ,
(dt_filter = 200810 ) in stan_03a_idx_2008_10 ,
(dt_filter = 200811 ) in stan_03a_idx_2008_11 ,
(dt_filter = 200812 ) in stan_03a_idx_2008_12 ,
(dt_filter = 200701 ) in stan_03a_idx_2007_01 ,
(dt_filter = 200702 ) in stan_03a_idx_2007_02 ,
(dt_filter = 200703 ) in stan_03a_idx_2007_03 ,
(dt_filter = 200704 ) in stan_03a_idx_2007_04 ,
(dt_filter = 200705 ) in stan_03a_idx_2007_05 ,
(dt_filter = 200706 ) in stan_03a_idx_2007_06 ,
(dt_filter = 200707 ) in stan_03a_idx_2007_07 ,
(dt_filter = 200708 ) in stan_03a_idx_2007_08 ,
(dt_filter = 200709 ) in stan_03a_idx_2007_09 ,
(dt_filter = 200710 ) in stan_03a_idx_2007_10 ,
(dt_filter = 200711 ) in stan_03a_idx_2007_11 ,
(dt_filter = 200712 ) in stan_03a_idx_2007_12 ;
Thanks in advance for the help
Jarrod Teale
Team Lead - Manufacturing Execution Systems
Automation & Process Control Group
Thanks!
Kate Tomchik [ kate@iiug.org ] www.iiug.org
International Informix Users Group Board of Directors
I'm prepared for all emergencies but totally unprepared for everyday
life.
The table is fragmented, but I don't want the index interleaved in the
datapages in the same dbspaces.
We have over 1000 dbspaces (~2000 chunks) for these 14 tables, each
between 2 and 6 GB in size. I don't want the IO thrashing trying to find
indexes amungst data. That's why the data tables are in one set of
dbspaces, and the 2 indexes on the table are in separate dbspaces again.
Believe it or not, but all this data is not set up for DSS. It is
interacted through an OLTP style application layer - so fragment
elimination is paramount. Also the data arrives at a constant rate into
these tables, so the tables and indexes are always growing.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
kate
Sent: Wednesday, 17 December 2008 5:01 a.m.
To: ids@iiug.org
Subject: Fragmentation [14328]
( Okay all, I have officially given up on trying to respond from work.
Here is what I tried to write. )
You are fragmenting the index with this statement, but you said it is
the primary key of your table. Can't you just fragment the table this
way? The index will get created implicitly and stored with the table,
therefore for this index new data will not cause two separate actions,
one to store the data and one to store the index.
And it is always a good idea to have a remainder clause even if you
can't foresee it ever being used.
Did you mention there is more than one index on this table? Any index
that is not part of the fragmentation strategy will take a very long
time to add. You should consider if you can remove this second index,
but if not you definitely need to disable that index during data inputs
and rebuild after the data is completed.
Thanks,
Kate Tomchik, IT Architect
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]
On Behalf Of Jarrod Teale
Sent: Monday, December 15, 2008 9:58 PM
To: informix-list@iiug.org
Subject: Attach fragment to index not atomic
Hi,
IDS11.10.UC2W2 on RHEL4
We have the index (and a few more like it) below. It is used for the
primary
key of a table.
When I attach a new fragment to the system it reads every page of the
index.
So adding 12 fragments, one for each month is a pain.
alter fragment on index pk_tagdata_73 add (dt_filter = 200901 ) instan_03a_idx_2009_01 before stan_03a_idx_2008_01
As you can see from the schema, there is no remainder clause, so I would
expect this operation to be atomic. The fragments for the table are
(same
fragment scheme).
There is no data in the table for the fragments being added (the date
filter
is for Jan 2009)
Any ideas why this is not running as an atomic operation? It's taking a
really long time over 5 TB data!
create unique index "sdev".pk_tagdata_73 on "sdev".tagdata_73
(group_id,dt_filter,sample_dt) using btree
fragment by expression
(dt_filter = 200801 ) in stan_03a_idx_2008_01 ,
(dt_filter = 200802 ) in stan_03a_idx_2008_02 ,
(dt_filter = 200803 ) in stan_03a_idx_2008_03 ,
(dt_filter = 200804 ) in stan_03a_idx_2008_04 ,
(dt_filter = 200805 ) in stan_03a_idx_2008_05 ,
(dt_filter = 200806 ) in stan_03a_idx_2008_06 ,
(dt_filter = 200807 ) in stan_03a_idx_2008_07 ,
(dt_filter = 200808 ) in stan_03a_idx_2008_08 ,
(dt_filter = 200809 ) in stan_03a_idx_2008_09 ,
(dt_filter = 200810 ) in stan_03a_idx_2008_10 ,
(dt_filter = 200811 ) in stan_03a_idx_2008_11 ,
(dt_filter = 200812 ) in stan_03a_idx_2008_12 ,
(dt_filter = 200701 ) in stan_03a_idx_2007_01 ,
(dt_filter = 200702 ) in stan_03a_idx_2007_02 ,
(dt_filter = 200703 ) in stan_03a_idx_2007_03 ,
(dt_filter = 200704 ) in stan_03a_idx_2007_04 ,
(dt_filter = 200705 ) in stan_03a_idx_2007_05 ,
(dt_filter = 200706 ) in stan_03a_idx_2007_06 ,
(dt_filter = 200707 ) in stan_03a_idx_2007_07 ,
(dt_filter = 200708 ) in stan_03a_idx_2007_08 ,
(dt_filter = 200709 ) in stan_03a_idx_2007_09 ,
(dt_filter = 200710 ) in stan_03a_idx_2007_10 ,
(dt_filter = 200711 ) in stan_03a_idx_2007_11 ,
(dt_filter = 200712 ) in stan_03a_idx_2007_12 ;
Thanks in advance for the help
Jarrod Teale
Team Lead - Manufacturing Execution Systems
Automation & Process Control Group
Thanks!
Kate Tomchik [ kate@iiug.org ] www.iiug.org
International Informix Users Group Board of Directors
I'm prepared for all emergencies but totally unprepared for everyday
life.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
DISCLAIMER:
This email contains confidential information and may be legally privileged. If
you are not the intended recipient or have received this email in error,
please notify the sender immediately and destroy this email.
You may not use, disclose or copy this email or its attachments in any way.
Any opinions expressed in this email are those of the author and are not
necessarily those of the Fonterra Co-operative Group.
http://www.fonterra.com/
So you will always have these delays. We too have all online processes so I know what you are saying, but for this one situation you want the index in the tablespace so the drop of the old fragment and the creation of the new one is instaneous. If you have a full sized QA environment, you should verify how much slower the oltp runs with the embedded scenario. Not sure if some of the new features might help you in 11. We have had ours set up since v7 this way. Sent via BlackBerry by AT&T -----Original Message----- From: "Jarrod Teale" <Jarrod.Teale@fonterra.com> Date: Tue, 16 Dec 2008 14:21:25 To: <ids@iiug.org> Subject: RE: Fragmentation [14335] The table is fragmented, but I don't want the index interleaved in the datapages in the same dbspaces. We have over 1000 dbspaces (~2000 chunks) for these 14 tables, each between 2 and 6 GB in size. I don't want the IO thrashing trying to find indexes amungst data. That's why the data tables are in one set of dbspaces, and the 2 indexes on the table are in separate dbspaces again. Believe it or not, but all this data is not set up for DSS. It is interacted through an OLTP style application layer - so fragment elimination is paramount. Also the data arrives at a constant rate into these tables, so the tables and indexes are always growing. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of kate Sent: Wednesday, 17 December 2008 5:01 a.m. To: ids@iiug.org Subject: Fragmentation [14328] ( Okay all, I have officially given up on trying to respond from work. Here is what I tried to write. ) You are fragmenting the index with this statement, but you said it is the primary key of your table. Can't you just fragment the table this way? The index will get created implicitly and stored with the table, therefore for this index new data will not cause two separate actions, one to store the data and one to store the index. And it is always a good idea to have a remainder clause even if you can't foresee it ever being used. Did you mention there is more than one index on this table? Any index that is not part of the fragmentation strategy will take a very long time to add. You should consider if you can remove this second index, but if not you definitely need to disable that index during data inputs and rebuild after the data is completed. Thanks, Kate Tomchik, IT Architect -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Jarrod Teale Sent: Monday, December 15, 2008 9:58 PM To: informix-list@iiug.org Subject: Attach fragment to index not atomic Hi, IDS11.10.UC2W2 on RHEL4 We have the index (and a few more like it) below. It is used for the primary key of a table. When I attach a new fragment to the system it reads every page of the index. So adding 12 fragments, one for each month is a pain. alter fragment on index pk_tagdata_73 add (dt_filter = 200901 ) in stan_03a_idx_2009_01 before stan_03a_idx_2008_01 As you can see from the schema, there is no remainder clause, so I would expect this operation to be atomic. The fragments for the table are (same fragment scheme). There is no data in the table for the fragments being added (the date filter is for Jan 2009) Any ideas why this is not running as an atomic operation? It's taking a really long time over 5 TB data! create unique index "sdev".pk_tagdata_73 on "sdev".tagdata_73 (group_id,dt_filter,sample_dt) using btree fragment by expression (dt_filter = 200801 ) in stan_03a_idx_2008_01 , (dt_filter = 200802 ) in stan_03a_idx_2008_02 , (dt_filter = 200803 ) in stan_03a_idx_2008_03 , (dt_filter = 200804 ) in stan_03a_idx_2008_04 , (dt_filter = 200805 ) in stan_03a_idx_2008_05 , (dt_filter = 200806 ) in stan_03a_idx_2008_06 , (dt_filter = 200807 ) in stan_03a_idx_2008_07 , (dt_filter = 200808 ) in stan_03a_idx_2008_08 , (dt_filter = 200809 ) in stan_03a_idx_2008_09 , (dt_filter = 200810 ) in stan_03a_idx_2008_10 , (dt_filter = 200811 ) in stan_03a_idx_2008_11 , (dt_filter = 200812 ) in stan_03a_idx_2008_12 , (dt_filter = 200701 ) in stan_03a_idx_2007_01 , (dt_filter = 200702 ) in stan_03a_idx_2007_02 , (dt_filter = 200703 ) in stan_03a_idx_2007_03 , (dt_filter = 200704 ) in stan_03a_idx_2007_04 , (dt_filter = 200705 ) in stan_03a_idx_2007_05 , (dt_filter = 200706 ) in stan_03a_idx_2007_06 , (dt_filter = 200707 ) in stan_03a_idx_2007_07 , (dt_filter = 200708 ) in stan_03a_idx_2007_08 , (dt_filter = 200709 ) in stan_03a_idx_2007_09 , (dt_filter = 200710 ) in stan_03a_idx_2007_10 , (dt_filter = 200711 ) in stan_03a_idx_2007_11 , (dt_filter = 200712 ) in stan_03a_idx_2007_12 ; Thanks in advance for the help Jarrod Teale Team Lead - Manufacturing Execution Systems Automation & Process Control Group Thanks! Kate Tomchik [ kate@iiug.org ] www.iiug.org International Informix Users Group Board of Directors I'm prepared for all emergencies but totally unprepared for everyday life. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. DISCLAIMER: This email contains confidential information and may be legally pr
Okay, I will no longer respond from Black berry either :-) It is big
brother, not Outlook I suspect.
---------- Forwarded Message -----------
From: "kate@iiug.org" <kate@iiug.org>
To: ids@iiug.org
Sent: Tue, 16 Dec 2008 14:35:06 -0500 (EST)
Subject: Re: Fragmentation [14336]
So you will always have these delays. We too have all online processes so I
know what you are saying, but for this one situation you want the index in
the tablespace so the drop of the old fragment and the creation of the new
one is instaneous.
If you have a full sized QA environment, you should verify how much slower
the oltp runs with the embedded scenario. Not sure if some of the new
features might help you in 11. We have had ours set up since v7 this way.
Sent via BlackBerry by AT&T
-----Original Message-----
From: "Jarrod Teale" <Jarrod.Teale@fonterra.com>
Date: Tue, 16 Dec 2008 14:21:25
To: <ids@iiug.org>
Subject: RE: Fragmentation [14335]
The table is fragmented, but I don't want the index interleaved in the
datapages in the same dbspaces.
We have over 1000 dbspaces (~2000 chunks) for these 14 tables, each
between 2 and 6 GB in size. I don't want the IO thrashing trying to find
indexes amungst data. That's why the data tables are in one set of
dbspaces, and the 2 indexes on the table are in separate dbspaces again.
Believe it or not, but all this data is not set up for DSS. It is
interacted through an OLTP style application layer - so fragment
elimination is paramount. Also the data arrives at a constant rate into
these tables, so the tables and indexes are always growing.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
kate
Sent: Wednesday, 17 December 2008 5:01 a.m.
To: ids@iiug.org
Subject: Fragmentation [14328]
( Okay all, I have officially given up on trying to respond from work.
Here is what I tried to write. )
You are fragmenting the index with this statement, but you said it is
the primary key of your table. Can't you just fragment the table this
way? The index will get created implicitly and stored with the table,
therefore for this index new data will not cause two separate actions,
one to store the data and one to store the index.
And it is always a good idea to have a remainder clause even if you
can't foresee it ever being used.
Did you mention there is more than one index on this table? Any index
that is not part of the fragmentation strategy will take a very long
time to add. You should consider if you can remove this second index,
but if not you definitely need to disable that index during data inputs
and rebuild after the data is completed.
Thanks,
Kate Tomchik, IT Architect
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]
On Behalf Of Jarrod Teale
Sent: Monday, December 15, 2008 9:58 PM
To: informix-list@iiug.org
Subject: Attach fragment to index not atomic
Hi,
IDS11.10.UC2W2 on RHEL4
We have the index (and a few more like it) below. It is used for the
primary
key of a table.
When I attach a new fragment to the system it reads every page of the
index.
So adding 12 fragments, one for each month is a pain.
alter fragment on index pk_tagdata_73 add (dt_filter = 200901 ) instan_03a_idx_2009_01 before stan_03a_idx_2008_01
As you can see from the schema, there is no remainder clause, so I would
expect this operation to be atomic. The fragments for the table are
(same
fragment scheme).
There is no data in the table for the fragments being added (the date
filter
is for Jan 2009)
Any ideas why this is not running as an atomic operation? It's taking a
really long time over 5 TB data!
create unique index "sdev".pk_tagdata_73 on "sdev".tagdata_73
(group_id,dt_filter,sample_dt) using btree
fragment by expression
(dt_filter = 200801 ) in stan_03a_idx_2008_01 ,
(dt_filter = 200802 ) in stan_03a_idx_2008_02 ,
(dt_filter = 200803 ) in stan_03a_idx_2008_03 ,
(dt_filter = 200804 ) in stan_03a_idx_2008_04 ,
(dt_filter = 200805 ) in stan_03a_idx_2008_05 ,
(dt_filter = 200806 ) in stan_03a_idx_2008_06 ,
(dt_filter = 200807 ) in stan_03a_idx_2008_07 ,
(dt_filter = 200808 ) in stan_03a_idx_2008_08 ,
(dt_filter = 200809 ) in stan_03a_idx_2008_09 ,
(dt_filter = 200810 ) in stan_03a_idx_2008_10 ,
(dt_filter = 200811 ) in stan_03a_idx_2008_11 ,
(dt_filter = 200812 ) in stan_03a_idx_2008_12 ,
(dt_filter = 200701 ) in stan_03a_idx_2007_01 ,
(dt_filter = 200702 ) in stan_03a_idx_2007_02 ,
(dt_filter = 200703 ) in stan_03a_idx_2007_03 ,
(dt_filter = 200704 ) in stan_03a_idx_2007_04 ,
(dt_filter = 200705 ) in stan_03a_idx_2007_05 ,
(dt_filter = 200706 ) in stan_03a_idx_2007_06 ,
(dt_filter = 200707 ) in stan_03a_idx_2007_07 ,
(dt_filter = 200708 ) in stan_03a_idx_2007_08 ,
(dt_filter = 200709 ) in stan_03a_idx_2007_09 ,
(dt_filter = 200710 ) in stan_03a_idx_2007_10 ,
(dt_filter = 200711 ) in stan_03a_idx_2007_11 ,
(dt_filter = 200712 ) in stan_03a_idx_2007_12 ;
Thanks in advance for the help
Jarrod Teale
Team Lead - Manufacturing Execution Systems
Automation & Process Control Group
Thanks!
Kate Tomchik [ kate@iiug.org ] www.iiug.org
International Informix Users Group Board of Directors
I'm prepared for all emergencies but totally unprepared for everyday
life.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
DISCLAIMER:
This email contains confidential information and may be legally privileged.
If
you are not the intended recipient or have received this email in error,
please notify the sender immediately and destroy this email.
You may not use, disclose or copy this email or its attachments in any way.
Any opinions expressed in this email are those of the author and are not
necessarily those of the Fonterra Co-operative Group.
http://www.fonterra.com/
*****************************************************************************
**
Forum Note: Use "Reply" to post a response in the discussion forum.
------- End of Forwarded Message -------
Thanks!
Kate Tomchik [ kate@iiug.org ] www.iiug.org
International Informix Users Group Board of Directors
I'm prepared for all emergencies but totally unprepared for everyday
life.
My Blackberry can't post to the IIUG lists neither
Paul Watson
Tel: +1 913-745-5635
Mob: +1 913-387-7529
Web: www.oninit.com
Failure is not as frightening as regret.
If you want to improve, be content to be thought foolish and stupid.
It is better to be criticized by a wise person than to be praised by a fool.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of kate
Sent: Tuesday, December 16, 2008 1:48 PM
To: ids@iiug.org
Subject: Fw: Re: Fragmentation [14337]
Okay, I will no longer respond from Black berry either :-) It is big
brother, not Outlook I suspect.
---------- Forwarded Message -----------
From: "kate@iiug.org" <kate@iiug.org>
To: ids@iiug.org
Sent: Tue, 16 Dec 2008 14:35:06 -0500 (EST)
Subject: Re: Fragmentation [14336]
So you will always have these delays. We too have all online processes so I
know what you are saying, but for this one situation you want the index in
the tablespace so the drop of the old fragment and the creation of the new
one is instaneous.
If you have a full sized QA environment, you should verify how much slower
the oltp runs with the embedded scenario. Not sure if some of the new
features might help you in 11. We have had ours set up since v7 this way.
Sent via BlackBerry by AT&T
-----Original Message-----
From: "Jarrod Teale" <Jarrod.Teale@fonterra.com>
Date: Tue, 16 Dec 2008 14:21:25
To: <ids@iiug.org>
Subject: RE: Fragmentation [14335]
The table is fragmented, but I don't want the index interleaved in the
datapages in the same dbspaces.
We have over 1000 dbspaces (~2000 chunks) for these 14 tables, each
between 2 and 6 GB in size. I don't want the IO thrashing trying to find
indexes amungst data. That's why the data tables are in one set of
dbspaces, and the 2 indexes on the table are in separate dbspaces again.
Believe it or not, but all this data is not set up for DSS. It is
interacted through an OLTP style application layer - so fragment
elimination is paramount. Also the data arrives at a constant rate into
these tables, so the tables and indexes are always growing.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
kate
Sent: Wednesday, 17 December 2008 5:01 a.m.
To: ids@iiug.org
Subject: Fragmentation [14328]
( Okay all, I have officially given up on trying to respond from work.
Here is what I tried to write. )
You are fragmenting the index with this statement, but you said it is
the primary key of your table. Can't you just fragment the table this
way? The index will get created implicitly and stored with the table,
therefore for this index new data will not cause two separate actions,
one to store the data and one to store the index.
And it is always a good idea to have a remainder clause even if you
can't foresee it ever being used.
Did you mention there is more than one index on this table? Any index
that is not part of the fragmentation strategy will take a very long
time to add. You should consider if you can remove this second index,
but if not you definitely need to disable that index during data inputs
and rebuild after the data is completed.
Thanks,
Kate Tomchik, IT Architect
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]
On Behalf Of Jarrod Teale
Sent: Monday, December 15, 2008 9:58 PM
To: informix-list@iiug.org
Subject: Attach fragment to index not atomic
Hi,
IDS11.10.UC2W2 on RHEL4
We have the index (and a few more like it) below. It is used for the
primary
key of a table.
When I attach a new fragment to the system it reads every page of the
index.
So adding 12 fragments, one for each month is a pain.
alter fragment on index pk_tagdata_73 add (dt_filter = 200901 ) instan_03a_idx_2009_01 before stan_03a_idx_2008_01
As you can see from the schema, there is no remainder clause, so I would
expect this operation to be atomic. The fragments for the table are
(same
fragment scheme).
There is no data in the table for the fragments being added (the date
filter
is for Jan 2009)
Any ideas why this is not running as an atomic operation? It's taking a
really long time over 5 TB data!
create unique index "sdev".pk_tagdata_73 on "sdev".tagdata_73
(group_id,dt_filter,sample_dt) using btree
fragment by expression
(dt_filter = 200801 ) in stan_03a_idx_2008_01 ,
(dt_filter = 200802 ) in stan_03a_idx_2008_02 ,
(dt_filter = 200803 ) in stan_03a_idx_2008_03 ,
(dt_filter = 200804 ) in stan_03a_idx_2008_04 ,
(dt_filter = 200805 ) in stan_03a_idx_2008_05 ,
(dt_filter = 200806 ) in stan_03a_idx_2008_06 ,
(dt_filter = 200807 ) in stan_03a_idx_2008_07 ,
(dt_filter = 200808 ) in stan_03a_idx_2008_08 ,
(dt_filter = 200809 ) in stan_03a_idx_2008_09 ,
(dt_filter = 200810 ) in stan_03a_idx_2008_10 ,
(dt_filter = 200811 ) in stan_03a_idx_2008_11 ,
(dt_filter = 200812 ) in stan_03a_idx_2008_12 ,
(dt_filter = 200701 ) in stan_03a_idx_2007_01 ,
(dt_filter = 200702 ) in stan_03a_idx_2007_02 ,
(dt_filter = 200703 ) in stan_03a_idx_2007_03 ,
(dt_filter = 200704 ) in stan_03a_idx_2007_04 ,
(dt_filter = 200705 ) in stan_03a_idx_2007_05 ,
(dt_filter = 200706 ) in stan_03a_idx_2007_06 ,
(dt_filter = 200707 ) in stan_03a_idx_2007_07 ,
(dt_filter = 200708 ) in stan_03a_idx_2007_08 ,
(dt_filter = 200709 ) in stan_03a_idx_2007_09 ,
(dt_filter = 200710 ) in stan_03a_idx_2007_10 ,
(dt_filter = 200711 ) in stan_03a_idx_2007_11 ,
(dt_filter = 200712 ) in stan_03a_idx_2007_12 ;
Thanks in advance for the help
Jarrod Teale
Team Lead - Manufacturing Execution Systems
Automation & Process Control Group
Thanks!
Kate Tomchik [ kate@iiug.org ] www.iiug.org
International Informix Users Group Board of Directors
I'm prepared for all emergencies but totally unprepared for everyday
life.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
DISCLAIMER:
This email contains confidential information and may be legally privileged.
If
you are not the intended recipient or have received this email in error,
please notify the sender immediately and destroy this email.
You may not use, disclose or copy this email or its attachments in any way.
Any opinions expressed in this email are those of the author and are not
necessarily those of the Fonterra Co-operative Group.
http://www.fonterra.com/
****************************************************************************
*
**
Forum Note: Use "Reply" to post a response in the discussion forum.
------- End of Forwarded Message -------
Thanks!
Kate Tomchik [ kate@iiug.org ] www.iiug.org
International Informix Users Group Board of Directors
I'm prepared for all emergencies but totally unprepared for everyday
life.
*********
kate@iiug.org wrote:
> So you will always have these delays. We too have all online processes so I know what you are saying, but for this one situation you want the index in the tablespace so the drop of the old fragment and the creation of the new one is instaneous.
>
> If you have a full sized QA environment, you should verify how much slower the oltp runs with the embedded scenario. Not sure if some of the new features might help you in 11. We have had ours set up since v7 this way.
>
>
> Sent via BlackBerry by AT&T
>
> -----Original Message-----
> From: "Jarrod Teale" <Jarrod.Teale@fonterra.com>
>
> Date: Tue, 16 Dec 2008 14:21:25
> To: <ids@iiug.org>
> Subject: RE: Fragmentation [14335]
>
>
> The table is fragmented, but I don't want the index interleaved in the
> datapages in the same dbspaces.
> We have over 1000 dbspaces (~2000 chunks) for these 14 tables, each
> between 2 and 6 GB in size. I don't want the IO thrashing trying to find
> indexes amungst data. That's why the data tables are in one set of
> dbspaces, and the 2 indexes on the table are in separate dbspaces again.
>
> Believe it or not, but all this data is not set up for DSS. It is
> interacted through an OLTP style application layer - so fragment
> elimination is paramount. Also the data arrives at a constant rate into
> these tables, so the tables and indexes are always growing.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> kate
> Sent: Wednesday, 17 December 2008 5:01 a.m.
> To: ids@iiug.org
> Subject: Fragmentation [14328]
>
> ( Okay all, I have officially given up on trying to respond from work.
> Here is what I tried to write. )
>
> You are fragmenting the index with this statement, but you said it is
> the primary key of your table. Can't you just fragment the table this
> way? The index will get created implicitly and stored with the table,
> therefore for this index new data will not cause two separate actions,
> one to store the data and one to store the index.
>
> And it is always a good idea to have a remainder clause even if you
> can't foresee it ever being used.
>
> Did you mention there is more than one index on this table? Any index
> that is not part of the fragmentation strategy will take a very long
> time to add. You should consider if you can remove this second index,
> but if not you definitely need to disable that index during data inputs
> and rebuild after the data is completed.
>
> Thanks,
> Kate Tomchik, IT Architect
>
> -----Original Message-----
> From: informix-list-bounces@iiug.org
> [mailto:informix-list-bounces@iiug.org]
> On Behalf Of Jarrod Teale
> Sent: Monday, December 15, 2008 9:58 PM
> To: informix-list@iiug.org
> Subject: Attach fragment to index not atomic
>
> Hi,
> IDS11.10.UC2W2 on RHEL4
>
> We have the index (and a few more like it) below. It is used for the
> primary
> key of a table.
> When I attach a new fragment to the system it reads every page of the
> index.
> So adding 12 fragments, one for each month is a pain.
>
> alter fragment on index pk_tagdata_73 add (dt_filter = 200901 ) in > stan_03a_idx_2009_01 before stan_03a_idx_2008_01
>
> As you can see from the schema, there is no remainder clause, so I would
>
> expect this operation to be atomic. The fragments for the table are
> (same
> fragment scheme).
>
> There is no data in the table for the fragments being added (the date
> filter
> is for Jan 2009)
> Any ideas why this is not running as an atomic operation? It's taking a
> really long time over 5 TB data!
>
> create unique index "sdev".pk_tagdata_73 on "sdev".tagdata_73
>
> (group_id,dt_filter,sample_dt) using btree
> fragment by expression
>
> (dt_filter = 200801 ) in stan_03a_idx_2008_01 ,
>
> (dt_filter = 200802 ) in stan_03a_idx_2008_02 ,
>
> (dt_filter = 200803 ) in stan_03a_idx_2008_03 ,
>
> (dt_filter = 200804 ) in stan_03a_idx_2008_04 ,
>
> (dt_filter = 200805 ) in stan_03a_idx_2008_05 ,
>
> (dt_filter = 200806 ) in stan_03a_idx_2008_06 ,
>
> (dt_filter = 200807 ) in stan_03a_idx_2008_07 ,
>
> (dt_filter = 200808 ) in stan_03a_idx_2008_08 ,
>
> (dt_filter = 200809 ) in stan_03a_idx_2008_09 ,
>
> (dt_filter = 200810 ) in stan_03a_idx_2008_10 ,
>
> (dt_filter = 200811 ) in stan_03a_idx_2008_11 ,
>
> (dt_filter = 200812 ) in stan_03a_idx_2008_12 ,
>
> (dt_filter = 200701 ) in stan_03a_idx_2007_01 ,
>
> (dt_filter = 200702 ) in stan_03a_idx_2007_02 ,
>
> (dt_filter = 200703 ) in stan_03a_idx_2007_03 ,
>
> (dt_filter = 200704 ) in stan_03a_idx_2007_04 ,
>
> (dt_filter = 200705 ) in stan_03a_idx_2007_05 ,
>
> (dt_filter = 200706 ) in stan_03a_idx_2007_06 ,
>
> (dt_filter = 200707 ) in stan_03a_idx_2007_07 ,
>
> (dt_filter = 200708 ) in stan_03a_idx_2007_08 ,
>
> (dt_filter = 200709 ) in stan_03a_idx_2007_09 ,
>
> (dt_filter = 200710 ) in stan_03a_idx_2007_10 ,
>
> (dt_filter = 200711 ) in stan_03a_idx_2007_11 ,
>
> (dt_filter = 200712 ) in stan_03a_idx_2007_12 ;
>
> Thanks in advance for the help
>
> Jarrod Teale
>
> Team Lead - Manufacturing Execution Systems
>
> Automation & Process Control Group
>
> Thanks!
> Kate Tomchik [ kate@iiug.org ] www.iiug.org
> International Informix Users Group Board of Directors
>
> I'm prepared for all emergencies but totally unprepared for everyday
> life.
>
> ************************************************************************
> *******
> Forum
You caught me! any excuse to have dirty cyber talk I'm there... Thanks! Kate Tomchik [ kate@iiug.org ] www.iiug.org International Informix Users Group Board of Directors I'm prepared for all emergencies but totally unprepared for everyday life. ---------- Original Message ----------- From: "Obnoxio The Clown" <obnoxio@serendipita.com> To: ids@iiug.org Sent: Tue, 16 Dec 2008 15:29:23 -0500 (EST) Subject: Re: Fragmentation [14339] > kate@iiug.org wrote: > > U28geW91IHdpbGwgYWx3YXlzIGhhdmUgdGhlc2UgZGVsYXlzLiBXZSB0b28gaGF2ZSBhbGwgb25s > > snip... > > ICJSZXBseSIgdG8gcG9zdCBhIHJlc3BvbnNlIGluIHRoZSBkaXNjdXNzaW9uIGZvcnVtLiANCg0K > > DQo= > > I bet you say that to all the boys! > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > > ***************************************************************************** ** Forum Note: Use "Reply" to post a response in the discussion forum. ------- End of Original Message -------
The BEFORE part of the fragmentation clause was the killer.
Take that off and it runs in a second or two. Much better!
Thanks for the help - odd it affected the indexes and not the table.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Jarrod Teale
Sent: Wednesday, 17 December 2008 8:21 a.m.
To: ids@iiug.org
Subject: RE: Fragmentation [14335]
The table is fragmented, but I don't want the index interleaved in the
datapages in the same dbspaces.
We have over 1000 dbspaces (~2000 chunks) for these 14 tables, each
between 2 and 6 GB in size. I don't want the IO thrashing trying to find
indexes amungst data. That's why the data tables are in one set of
dbspaces, and the 2 indexes on the table are in separate dbspaces again.
Believe it or not, but all this data is not set up for DSS. It is
interacted through an OLTP style application layer - so fragment
elimination is paramount. Also the data arrives at a constant rate into
these tables, so the tables and indexes are always growing.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
kate
Sent: Wednesday, 17 December 2008 5:01 a.m.
To: ids@iiug.org
Subject: Fragmentation [14328]
( Okay all, I have officially given up on trying to respond from work.
Here is what I tried to write. )
You are fragmenting the index with this statement, but you said it is
the primary key of your table. Can't you just fragment the table this
way? The index will get created implicitly and stored with the table,
therefore for this index new data will not cause two separate actions,
one to store the data and one to store the index.
And it is always a good idea to have a remainder clause even if you
can't foresee it ever being used.
Did you mention there is more than one index on this table? Any index
that is not part of the fragmentation strategy will take a very long
time to add. You should consider if you can remove this second index,
but if not you definitely need to disable that index during data inputs
and rebuild after the data is completed.
Thanks,
Kate Tomchik, IT Architect
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]
On Behalf Of Jarrod Teale
Sent: Monday, December 15, 2008 9:58 PM
To: informix-list@iiug.org
Subject: Attach fragment to index not atomic
Hi,
IDS11.10.UC2W2 on RHEL4
We have the index (and a few more like it) below. It is used for the
primary key of a table.
When I attach a new fragment to the system it reads every page of the
index.
So adding 12 fragments, one for each month is a pain.
alter fragment on index pk_tagdata_73 add (dt_filter = 200901 ) instan_03a_idx_2009_01 before stan_03a_idx_2008_01
As you can see from the schema, there is no remainder clause, so I would
expect this operation to be atomic. The fragments for the table are
(same fragment scheme).
There is no data in the table for the fragments being added (the date
filter is for Jan 2009) Any ideas why this is not running as an atomic
operation? It's taking a really long time over 5 TB data!
create unique index "sdev".pk_tagdata_73 on "sdev".tagdata_73
(group_id,dt_filter,sample_dt) using btree fragment by expression
(dt_filter = 200801 ) in stan_03a_idx_2008_01 ,
(dt_filter = 200802 ) in stan_03a_idx_2008_02 ,
(dt_filter = 200803 ) in stan_03a_idx_2008_03 ,
(dt_filter = 200804 ) in stan_03a_idx_2008_04 ,
(dt_filter = 200805 ) in stan_03a_idx_2008_05 ,
(dt_filter = 200806 ) in stan_03a_idx_2008_06 ,
(dt_filter = 200807 ) in stan_03a_idx_2008_07 ,
(dt_filter = 200808 ) in stan_03a_idx_2008_08 ,
(dt_filter = 200809 ) in stan_03a_idx_2008_09 ,
(dt_filter = 200810 ) in stan_03a_idx_2008_10 ,
(dt_filter = 200811 ) in stan_03a_idx_2008_11 ,
(dt_filter = 200812 ) in stan_03a_idx_2008_12 ,
(dt_filter = 200701 ) in stan_03a_idx_2007_01 ,
(dt_filter = 200702 ) in stan_03a_idx_2007_02 ,
(dt_filter = 200703 ) in stan_03a_idx_2007_03 ,
(dt_filter = 200704 ) in stan_03a_idx_2007_04 ,
(dt_filter = 200705 ) in stan_03a_idx_2007_05 ,
(dt_filter = 200706 ) in stan_03a_idx_2007_06 ,
(dt_filter = 200707 ) in stan_03a_idx_2007_07 ,
(dt_filter = 200708 ) in stan_03a_idx_2007_08 ,
(dt_filter = 200709 ) in stan_03a_idx_2007_09 ,
(dt_filter = 200710 ) in stan_03a_idx_2007_10 ,
(dt_filter = 200711 ) in stan_03a_idx_2007_11 ,
(dt_filter = 200712 ) in stan_03a_idx_2007_12 ;
Thanks in advance for the help
Jarrod Teale
Team Lead - Manufacturing Execution Systems
Automation & Process Control Group
Thanks!
Kate Tomchik [ kate@iiug.org ] www.iiug.org International Informix Users
Group Board of Directors
I'm prepared for all emergencies but totally unprepared for everyday
life.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
DISCLAIMER:
This email contains confidential information and may be legally
privileged. If you are not the intended recipient or have received this
email in error, please notify the sender immediately and destroy this
email.
You may not use, disclose or copy this email or its attachments in any
way.
Any opinions expressed in this email are those of the author and are not
necessarily those of the Fonterra Co-operative Group.
http://www.fonterra.com/
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
DISCLAIMER:
This email contains confidential information and may be legally privileged. If
you are not the intended recipient or have received this email in error,
please notify the sender immediately and destroy this email.
You may not use, disclose or copy this email or its attachments in any way.
Any opinions expressed in this email are those of the author and are not
necessarily those of the Fonterra Co-operative Group.
http://www.fonterra.com/