IDS Forum

RE: Fragmentation FIXED

Posted By: Jarrod Teale
Date: Tuesday, 16 December 2008, at 5:37 p.m.

In Response To: RE: Fragmentation (Jarrod Teale)

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 ) 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
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/

Messages In This Thread

• Fragmentation
  kate -- Tuesday, 16 December 2008, at 11:00 a.m.
  • RE: Fragmentation
    Jarrod Teale -- Tuesday, 16 December 2008, at 2:21 p.m.
    • Re: Fragmentation
      kate@iiug.org -- Tuesday, 16 December 2008, at 2:35 p.m.
      • Fw: Re: Fragmentation
        kate -- Tuesday, 16 December 2008, at 2:48 p.m.
        • RE: Re: Fragmentation
          Paul Watson -- Tuesday, 16 December 2008, at 2:55 p.m.
      • Re: Fragmentation
        Obnoxio The Clown -- Tuesday, 16 December 2008, at 3:29 p.m.
        • Re: Fragmentation
          kate -- Tuesday, 16 December 2008, at 3:35 p.m.
    • RE: Fragmentation FIXED
      Jarrod Teale -- Tuesday, 16 December 2008, at 5:37 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.