Informix

Hi,

I am trying to make ALTER FRAGMENT ATTACH as seamless as possible, ideally to the point that given the right conditions the attach will just involve updating system tables and no data movement. Therefore it will take a matter of seconds no matter how large the tables involved are.

Here is a little test case:

-- Original table, in reality a table that has existed for some time with many millions of rows
create table original_table
(
id serial not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) in dbspace1;

create unique index original_table_u1 on original_table (id) using btree;
alter table original_table add constraint primary key (id) constraint original_table_pk;

-- Insert data
insert into original_table (oid, cid, called_date, status) values (1,1,current, 'A');
insert into original_table (oid, cid, called_date, status) values (2,2,current, 'B');
insert into original_table (oid, cid, called_date, status) values (3,3,current, 'C');
insert into original_table (oid, cid, called_date, status) values (4,4,current, 'D');
insert into original_table (oid, cid, called_date, status) values (5,5,current, 'E');
-- Insert more rows....

-- Empty new partitioned table
create table new_partitioned_table
(
id int not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) fragment by range(called_offer_id)
interval(115000000)
store in (dbspace1, dbspace2, dbspace3, dbspace4)
partition new_partitioned_table_p0 VALUES < 0 in dbspace1 statlevel fragment;

Then the attach:
ALTER FRAGMENT ON TABLE new_partitioned_table ATTACH original_table AS PARTITION new_partitioned_table_p1 VALUES < 115000000;

It all works fine but is not especially quick. Although there is little write activity, studying the partition profiles shows what appears to be a full scan of the original table during the alter fragment operation. I am not sure why this is necessary. The part number is then re-used as a partition of the new table.

In reality the situation gets more complicated and the command takes longer with indices on the new table, which also seem to involve more I/O than you might hope for. In the interests of the simplest case possible I am not asking about those right now.

Has anyone got any tips or experience to share?

Ben.

------------------------------
Benjamin Thompson
------------------------------

#Informix

Hi Ben,

I did this activity last month, exactly the same what you did and It was all over in the blink of an eye... I haven't seen any performance issue. The difference is i don't have indexes and i did that on 14.10.
And yes you are right, the partnum is reused as a partition of the new table.

What is your IDS version.?
How many records you have in your table?
How much approx time it took to add a fragment?


------------------------------
Gaurav Kumar
------------------------------

Original Message:
Sent: Mon August 17, 2020 01:05 PM
From: Benjamin Thompson
Subject: Alter fragment attach performance

Hi,

I am trying to make ALTER FRAGMENT ATTACH as seamless as possible, ideally to the point that given the right conditions the attach will just involve updating system tables and no data movement. Therefore it will take a matter of seconds no matter how large the tables involved are.

Here is a little test case:

-- Original table, in reality a table that has existed for some time with many millions of rows
create table original_table
(
id serial not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) in dbspace1;

create unique index original_table_u1 on original_table (id) using btree;
alter table original_table add constraint primary key (id) constraint original_table_pk;

-- Insert data
insert into original_table (oid, cid, called_date, status) values (1,1,current, 'A');
insert into original_table (oid, cid, called_date, status) values (2,2,current, 'B');
insert into original_table (oid, cid, called_date, status) values (3,3,current, 'C');
insert into original_table (oid, cid, called_date, status) values (4,4,current, 'D');
insert into original_table (oid, cid, called_date, status) values (5,5,current, 'E');
-- Insert more rows....

-- Empty new partitioned table
create table new_partitioned_table
(
id int not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) fragment by range(called_offer_id)
interval(115000000)
store in (dbspace1, dbspace2, dbspace3, dbspace4)
partition new_partitioned_table_p0 VALUES < 0 in dbspace1 statlevel fragment;

Then the attach:
ALTER FRAGMENT ON TABLE new_partitioned_table ATTACH original_table AS PARTITION new_partitioned_table_p1 VALUES < 115000000;

It all works fine but is not especially quick. Although there is little write activity, studying the partition profiles shows what appears to be a full scan of the original table during the alter fragment operation. I am not sure why this is necessary. The part number is then re-used as a partition of the new table.

In reality the situation gets more complicated and the command takes longer with indices on the new table, which also seem to involve more I/O than you might hope for. In the interests of the simplest case possible I am not asking about those right now.

Has anyone got any tips or experience to share?

Ben.

------------------------------
Benjamin Thompson
------------------------------
#Informix

Hi, I am using 12.10.xC14.

In further testing I have found that table attach is instant if the resulting table uses a round-robin partitioning strategy.

In my example I am using interval partitioning and no indices at all on the resulting table. It takes about 1 min to attach 60 million rows if everything lines up as it should and there is no data movement. In a more realistic scenario with indices we are seeing much longer times.

I think that now I have a better understanding of what is happening to the table part, I can move onto adding indices to the mix.

Ben.

------------------------------
Benjamin Thompson
------------------------------

Original Message:
Sent: Tue August 18, 2020 12:29 AM
From: Gaurav Kumar
Subject: Alter fragment attach performance

Hi Ben,

I did this activity last month, exactly the same what you did and It was all over in the blink of an eye... I haven't seen any performance issue. The difference is i don't have indexes and i did that on 14.10.
And yes you are right, the partnum is reused as a partition of the new table.

What is your IDS version.?
How many records you have in your table?
How much approx time it took to add a fragment?


------------------------------
Gaurav Kumar

Original Message:
Sent: Mon August 17, 2020 01:05 PM
From: Benjamin Thompson
Subject: Alter fragment attach performance

Hi,

I am trying to make ALTER FRAGMENT ATTACH as seamless as possible, ideally to the point that given the right conditions the attach will just involve updating system tables and no data movement. Therefore it will take a matter of seconds no matter how large the tables involved are.

Here is a little test case:

-- Original table, in reality a table that has existed for some time with many millions of rows
create table original_table
(
id serial not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) in dbspace1;

create unique index original_table_u1 on original_table (id) using btree;
alter table original_table add constraint primary key (id) constraint original_table_pk;

-- Insert data
insert into original_table (oid, cid, called_date, status) values (1,1,current, 'A');
insert into original_table (oid, cid, called_date, status) values (2,2,current, 'B');
insert into original_table (oid, cid, called_date, status) values (3,3,current, 'C');
insert into original_table (oid, cid, called_date, status) values (4,4,current, 'D');
insert into original_table (oid, cid, called_date, status) values (5,5,current, 'E');
-- Insert more rows....

-- Empty new partitioned table
create table new_partitioned_table
(
id int not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) fragment by range(called_offer_id)
interval(115000000)
store in (dbspace1, dbspace2, dbspace3, dbspace4)
partition new_partitioned_table_p0 VALUES < 0 in dbspace1 statlevel fragment;

Then the attach:
ALTER FRAGMENT ON TABLE new_partitioned_table ATTACH original_table AS PARTITION new_partitioned_table_p1 VALUES < 115000000;

It all works fine but is not especially quick. Although there is little write activity, studying the partition profiles shows what appears to be a full scan of the original table during the alter fragment operation. I am not sure why this is necessary. The part number is then re-used as a partition of the new table.

In reality the situation gets more complicated and the command takes longer with indices on the new table, which also seem to involve more I/O than you might hope for. In the interests of the simplest case possible I am not asking about those right now.

Has anyone got any tips or experience to share?

Ben.

------------------------------
Benjamin Thompson
------------------------------
#Informix

Hello,

I did this several times and the main hint is to fragment all Indexes the same way as the table is fragmented.

When this is the case, the ADD FRAGMENT will be very fast.

------------------------------
Gerd Kaluzinski
------------------------------

Original Message:
Sent: Mon August 17, 2020 01:05 PM
From: Benjamin Thompson
Subject: Alter fragment attach performance

Hi,

I am trying to make ALTER FRAGMENT ATTACH as seamless as possible, ideally to the point that given the right conditions the attach will just involve updating system tables and no data movement. Therefore it will take a matter of seconds no matter how large the tables involved are.

Here is a little test case:

-- Original table, in reality a table that has existed for some time with many millions of rows
create table original_table
(
id serial not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) in dbspace1;

create unique index original_table_u1 on original_table (id) using btree;
alter table original_table add constraint primary key (id) constraint original_table_pk;

-- Insert data
insert into original_table (oid, cid, called_date, status) values (1,1,current, 'A');
insert into original_table (oid, cid, called_date, status) values (2,2,current, 'B');
insert into original_table (oid, cid, called_date, status) values (3,3,current, 'C');
insert into original_table (oid, cid, called_date, status) values (4,4,current, 'D');
insert into original_table (oid, cid, called_date, status) values (5,5,current, 'E');
-- Insert more rows....

-- Empty new partitioned table
create table new_partitioned_table
(
id int not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) fragment by range(called_offer_id)
interval(115000000)
store in (dbspace1, dbspace2, dbspace3, dbspace4)
partition new_partitioned_table_p0 VALUES < 0 in dbspace1 statlevel fragment;

Then the attach:
ALTER FRAGMENT ON TABLE new_partitioned_table ATTACH original_table AS PARTITION new_partitioned_table_p1 VALUES < 115000000;

It all works fine but is not especially quick. Although there is little write activity, studying the partition profiles shows what appears to be a full scan of the original table during the alter fragment operation. I am not sure why this is necessary. The part number is then re-used as a partition of the new table.

In reality the situation gets more complicated and the command takes longer with indices on the new table, which also seem to involve more I/O than you might hope for. In the interests of the simplest case possible I am not asking about those right now.

Has anyone got any tips or experience to share?

Ben.

------------------------------
Benjamin Thompson
------------------------------

#Informix

And, for index fragmentation, there are two flavors of "same way as the table is fragmented":

1. using identical fragmentation clause
2. using no fragmentation clause at all  (i.e. implicitely demanding "follow table's fragmentation scheme")

Afaik, only the second flavor will provide the benefit of seamless attach/detach without any need for re-writing/re-creating indices, because every index fragment goes with exactly one data fragment.
And:  any implicite index created by a constraint definition that was part of the CREATE TABLE statement will not fall into that second category.

sysfragments:strategy column holding a 'T', for "follow table", is what you'd want to see for a given index's fragments.

I think the above is true for all types of fragmentation (round robin, expression, range, ...).


As for data scan when attaching a new fragment to an expression (or range) fragmented table, as per Ben's example, and besides index considerations:
initially such attach has to be treated as kind of a merge of the data arriving with the new fragment, and the engine cannot blindly trust that 'original table' data all fits the new fragment's fragmentation expression/range, hence probably that scan.
The example given fragments by a column 'called_offer_id' which doesn't really match any of the given columns.  Should that simply be 'id' instead? In which case an index would be present, on the original table, which one would would think could facilitate or even avoid the required scan...

------------------------------
Andreas Legner
------------------------------

Original Message:
Sent: Tue August 18, 2020 01:59 AM
From: Gerd Kaluzinski
Subject: Alter fragment attach performance

Hello,

I did this several times and the main hint is to fragment all Indexes the same way as the table is fragmented.

When this is the case, the ADD FRAGMENT will be very fast.

------------------------------
Gerd Kaluzinski

Original Message:
Sent: Mon August 17, 2020 01:05 PM
From: Benjamin Thompson
Subject: Alter fragment attach performance

Hi,

I am trying to make ALTER FRAGMENT ATTACH as seamless as possible, ideally to the point that given the right conditions the attach will just involve updating system tables and no data movement. Therefore it will take a matter of seconds no matter how large the tables involved are.

Here is a little test case:

-- Original table, in reality a table that has existed for some time with many millions of rows
create table original_table
(
id serial not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) in dbspace1;

create unique index original_table_u1 on original_table (id) using btree;
alter table original_table add constraint primary key (id) constraint original_table_pk;

-- Insert data
insert into original_table (oid, cid, called_date, status) values (1,1,current, 'A');
insert into original_table (oid, cid, called_date, status) values (2,2,current, 'B');
insert into original_table (oid, cid, called_date, status) values (3,3,current, 'C');
insert into original_table (oid, cid, called_date, status) values (4,4,current, 'D');
insert into original_table (oid, cid, called_date, status) values (5,5,current, 'E');
-- Insert more rows....

-- Empty new partitioned table
create table new_partitioned_table
(
id int not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) fragment by range(called_offer_id)
interval(115000000)
store in (dbspace1, dbspace2, dbspace3, dbspace4)
partition new_partitioned_table_p0 VALUES < 0 in dbspace1 statlevel fragment;

Then the attach:
ALTER FRAGMENT ON TABLE new_partitioned_table ATTACH original_table AS PARTITION new_partitioned_table_p1 VALUES < 115000000;

It all works fine but is not especially quick. Although there is little write activity, studying the partition profiles shows what appears to be a full scan of the original table during the alter fragment operation. I am not sure why this is necessary. The part number is then re-used as a partition of the new table.

In reality the situation gets more complicated and the command takes longer with indices on the new table, which also seem to involve more I/O than you might hope for. In the interests of the simplest case possible I am not asking about those right now.

Has anyone got any tips or experience to share?

Ben.

------------------------------
Benjamin Thompson
------------------------------

#Informix

Original Message:
Sent: 8/18/2020 5:32:00 AM
From: Andreas Legner
Subject: RE: Alter fragment attach performance

And, for index fragmentation, there are two flavors of "same way as the table is fragmented":

1. using identical fragmentation clause
2. using no fragmentation clause at all  (i.e. implicitely demanding "follow table's fragmentation scheme")

Afaik, only the second flavor will provide the benefit of seamless attach/detach without any need for re-writing/re-creating indices, because every index fragment goes with exactly one data fragment.
And:  any implicite index created by a constraint definition that was part of the CREATE TABLE statement will not fall into that second category.

sysfragments:strategy column holding a 'T', for "follow table", is what you'd want to see for a given index's fragments.

I think the above is true for all types of fragmentation (round robin, expression, range, ...).


As for data scan when attaching a new fragment to an expression (or range) fragmented table, as per Ben's example, and besides index considerations:
initially such attach has to be treated as kind of a merge of the data arriving with the new fragment, and the engine cannot blindly trust that 'original table' data all fits the new fragment's fragmentation expression/range, hence probably that scan.
The example given fragments by a column 'called_offer_id' which doesn't really match any of the given columns.  Should that simply be 'id' instead? In which case an index would be present, on the original table, which one would would think could facilitate or even avoid the required scan...

------------------------------
Andreas Legner
------------------------------

Original Message:
Sent: Tue August 18, 2020 01:59 AM
From: Gerd Kaluzinski
Subject: Alter fragment attach performance

Hello,

I did this several times and the main hint is to fragment all Indexes the same way as the table is fragmented.

When this is the case, the ADD FRAGMENT will be very fast.

------------------------------
Gerd Kaluzinski

Original Message:
Sent: Mon August 17, 2020 01:05 PM
From: Benjamin Thompson
Subject: Alter fragment attach performance

Hi,

I am trying to make ALTER FRAGMENT ATTACH as seamless as possible, ideally to the point that given the right conditions the attach will just involve updating system tables and no data movement. Therefore it will take a matter of seconds no matter how large the tables involved are.

Here is a little test case:

-- Original table, in reality a table that has existed for some time with many millions of rows
create table original_table
(
id serial not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) in dbspace1;

create unique index original_table_u1 on original_table (id) using btree;
alter table original_table add constraint primary key (id) constraint original_table_pk;

-- Insert data
insert into original_table (oid, cid, called_date, status) values (1,1,current, 'A');
insert into original_table (oid, cid, called_date, status) values (2,2,current, 'B');
insert into original_table (oid, cid, called_date, status) values (3,3,current, 'C');
insert into original_table (oid, cid, called_date, status) values (4,4,current, 'D');
insert into original_table (oid, cid, called_date, status) values (5,5,current, 'E');
-- Insert more rows....

-- Empty new partitioned table
create table new_partitioned_table
(
id int not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) fragment by range(called_offer_id)
interval(115000000)
store in (dbspace1, dbspace2, dbspace3, dbspace4)
partition new_partitioned_table_p0 VALUES < 0 in dbspace1 statlevel fragment;

Then the attach:
ALTER FRAGMENT ON TABLE new_partitioned_table ATTACH original_table AS PARTITION new_partitioned_table_p1 VALUES < 115000000;

It all works fine but is not especially quick. Although there is little write activity, studying the partition profiles shows what appears to be a full scan of the original table during the alter fragment operation. I am not sure why this is necessary. The part number is then re-used as a partition of the new table.

In reality the situation gets more complicated and the command takes longer with indices on the new table, which also seem to involve more I/O than you might hope for. In the interests of the simplest case possible I am not asking about those right now.

Has anyone got any tips or experience to share?

Ben.

------------------------------
Benjamin Thompson
------------------------------

#Informix

Thanks for the replies. Yes "called_offer_id" should be "id". I try to anonymise posts in public forums, hence this discrepancy.

I think you are right: the engine cannot blindly trust that 'original table' data all fits the new fragment's fragmentation expression/range. Furthermore if it doesn't, when attaching to an interval table it will create system generated fragments to hold the remaining data. This isn't what I expected to happen at all: an attach splitting an unfragmented table across more than one fragment on the surviving table. I expected the operation would fail. I found what I think is a bug for sure, which is that the statistics generated for the fragments on the surviving table show all the rows went into the fragment I specified (which is impossible) and none into the system generated fragment. Doing an "update statistics low... force" corrects this.

It's unclear from the online documentation what the correct behaviour is. There is an article about doing online attaches (https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_1940.htm), which is the only place interval fragmentation is discussed in this context, but the document looks to have been badly edited (a bit like my table definition) and doesn't entirely make sense. It takes about using check constraints which I will do some experimentation with.

Ben.

------------------------------
Benjamin Thompson
------------------------------

Original Message:
Sent: Tue August 18, 2020 05:32 AM
From: Andreas Legner
Subject: Alter fragment attach performance

And, for index fragmentation, there are two flavors of "same way as the table is fragmented":

1. using identical fragmentation clause
2. using no fragmentation clause at all  (i.e. implicitely demanding "follow table's fragmentation scheme")

Afaik, only the second flavor will provide the benefit of seamless attach/detach without any need for re-writing/re-creating indices, because every index fragment goes with exactly one data fragment.
And:  any implicite index created by a constraint definition that was part of the CREATE TABLE statement will not fall into that second category.

sysfragments:strategy column holding a 'T', for "follow table", is what you'd want to see for a given index's fragments.

I think the above is true for all types of fragmentation (round robin, expression, range, ...).


As for data scan when attaching a new fragment to an expression (or range) fragmented table, as per Ben's example, and besides index considerations:
initially such attach has to be treated as kind of a merge of the data arriving with the new fragment, and the engine cannot blindly trust that 'original table' data all fits the new fragment's fragmentation expression/range, hence probably that scan.
The example given fragments by a column 'called_offer_id' which doesn't really match any of the given columns.  Should that simply be 'id' instead? In which case an index would be present, on the original table, which one would would think could facilitate or even avoid the required scan...

------------------------------
Andreas Legner

Original Message:
Sent: Tue August 18, 2020 01:59 AM
From: Gerd Kaluzinski
Subject: Alter fragment attach performance

Hello,

I did this several times and the main hint is to fragment all Indexes the same way as the table is fragmented.

When this is the case, the ADD FRAGMENT will be very fast.

------------------------------
Gerd Kaluzinski

Original Message:
Sent: Mon August 17, 2020 01:05 PM
From: Benjamin Thompson
Subject: Alter fragment attach performance

Hi,

I am trying to make ALTER FRAGMENT ATTACH as seamless as possible, ideally to the point that given the right conditions the attach will just involve updating system tables and no data movement. Therefore it will take a matter of seconds no matter how large the tables involved are.

Here is a little test case:

-- Original table, in reality a table that has existed for some time with many millions of rows
create table original_table
(
id serial not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) in dbspace1;

create unique index original_table_u1 on original_table (id) using btree;
alter table original_table add constraint primary key (id) constraint original_table_pk;

-- Insert data
insert into original_table (oid, cid, called_date, status) values (1,1,current, 'A');
insert into original_table (oid, cid, called_date, status) values (2,2,current, 'B');
insert into original_table (oid, cid, called_date, status) values (3,3,current, 'C');
insert into original_table (oid, cid, called_date, status) values (4,4,current, 'D');
insert into original_table (oid, cid, called_date, status) values (5,5,current, 'E');
-- Insert more rows....

-- Empty new partitioned table
create table new_partitioned_table
(
id int not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) fragment by range(called_offer_id)
interval(115000000)
store in (dbspace1, dbspace2, dbspace3, dbspace4)
partition new_partitioned_table_p0 VALUES < 0 in dbspace1 statlevel fragment;

Then the attach:
ALTER FRAGMENT ON TABLE new_partitioned_table ATTACH original_table AS PARTITION new_partitioned_table_p1 VALUES < 115000000;

It all works fine but is not especially quick. Although there is little write activity, studying the partition profiles shows what appears to be a full scan of the original table during the alter fragment operation. I am not sure why this is necessary. The part number is then re-used as a partition of the new table.

In reality the situation gets more complicated and the command takes longer with indices on the new table, which also seem to involve more I/O than you might hope for. In the interests of the simplest case possible I am not asking about those right now.

Has anyone got any tips or experience to share?

Ben.

------------------------------
Benjamin Thompson
------------------------------

#Informix

Original Message:
Sent: 8/18/2020 5:32:00 AM
From: Andreas Legner
Subject: RE: Alter fragment attach performance

And, for index fragmentation, there are two flavors of "same way as the table is fragmented":

1. using identical fragmentation clause
2. using no fragmentation clause at all  (i.e. implicitely demanding "follow table's fragmentation scheme")

Afaik, only the second flavor will provide the benefit of seamless attach/detach without any need for re-writing/re-creating indices, because every index fragment goes with exactly one data fragment.
And:  any implicite index created by a constraint definition that was part of the CREATE TABLE statement will not fall into that second category.

sysfragments:strategy column holding a 'T', for "follow table", is what you'd want to see for a given index's fragments.

I think the above is true for all types of fragmentation (round robin, expression, range, ...).


As for data scan when attaching a new fragment to an expression (or range) fragmented table, as per Ben's example, and besides index considerations:
initially such attach has to be treated as kind of a merge of the data arriving with the new fragment, and the engine cannot blindly trust that 'original table' data all fits the new fragment's fragmentation expression/range, hence probably that scan.
The example given fragments by a column 'called_offer_id' which doesn't really match any of the given columns.  Should that simply be 'id' instead? In which case an index would be present, on the original table, which one would would think could facilitate or even avoid the required scan...

------------------------------
Andreas Legner
------------------------------

Original Message:
Sent: Tue August 18, 2020 01:59 AM
From: Gerd Kaluzinski
Subject: Alter fragment attach performance

Hello,

I did this several times and the main hint is to fragment all Indexes the same way as the table is fragmented.

When this is the case, the ADD FRAGMENT will be very fast.

------------------------------
Gerd Kaluzinski

Original Message:
Sent: Mon August 17, 2020 01:05 PM
From: Benjamin Thompson
Subject: Alter fragment attach performance

Hi,

I am trying to make ALTER FRAGMENT ATTACH as seamless as possible, ideally to the point that given the right conditions the attach will just involve updating system tables and no data movement. Therefore it will take a matter of seconds no matter how large the tables involved are.

Here is a little test case:

-- Original table, in reality a table that has existed for some time with many millions of rows
create table original_table
(
id serial not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) in dbspace1;

create unique index original_table_u1 on original_table (id) using btree;
alter table original_table add constraint primary key (id) constraint original_table_pk;

-- Insert data
insert into original_table (oid, cid, called_date, status) values (1,1,current, 'A');
insert into original_table (oid, cid, called_date, status) values (2,2,current, 'B');
insert into original_table (oid, cid, called_date, status) values (3,3,current, 'C');
insert into original_table (oid, cid, called_date, status) values (4,4,current, 'D');
insert into original_table (oid, cid, called_date, status) values (5,5,current, 'E');
-- Insert more rows....

-- Empty new partitioned table
create table new_partitioned_table
(
id int not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) fragment by range(called_offer_id)
interval(115000000)
store in (dbspace1, dbspace2, dbspace3, dbspace4)
partition new_partitioned_table_p0 VALUES < 0 in dbspace1 statlevel fragment;

Then the attach:
ALTER FRAGMENT ON TABLE new_partitioned_table ATTACH original_table AS PARTITION new_partitioned_table_p1 VALUES < 115000000;

It all works fine but is not especially quick. Although there is little write activity, studying the partition profiles shows what appears to be a full scan of the original table during the alter fragment operation. I am not sure why this is necessary. The part number is then re-used as a partition of the new table.

In reality the situation gets more complicated and the command takes longer with indices on the new table, which also seem to involve more I/O than you might hope for. In the interests of the simplest case possible I am not asking about those right now.

Has anyone got any tips or experience to share?

Ben.

------------------------------
Benjamin Thompson
------------------------------

#Informix

Hi Benjamin,

It has occurred more than once that I do a simple ALTER TABLE DROP FRAGMENT statement that ends up taking a VERY long time and coming perilously close to a long transaction rollback.  A few knot's on the head like that and you start to wonder whazzup.

Well, in my case it turned out that there was at least one index that did not have the same fragmentation scheme as the table.  Hence, index entries for the rows in the dropped fragment had to deleted from their trees as if the rows were being deleted; not a simple fragment drop.  Might your situation be the reverse, with new index entries being added as for row inserts, not as a new index fragment?

Just a thought, FWIW.

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
------------------------------

Original Message:
Sent: Mon August 17, 2020 01:05 PM
From: Benjamin Thompson
Subject: Alter fragment attach performance

Hi,

I am trying to make ALTER FRAGMENT ATTACH as seamless as possible, ideally to the point that given the right conditions the attach will just involve updating system tables and no data movement. Therefore it will take a matter of seconds no matter how large the tables involved are.

Here is a little test case:

-- Original table, in reality a table that has existed for some time with many millions of rows
create table original_table
(
id serial not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) in dbspace1;

create unique index original_table_u1 on original_table (id) using btree;
alter table original_table add constraint primary key (id) constraint original_table_pk;

-- Insert data
insert into original_table (oid, cid, called_date, status) values (1,1,current, 'A');
insert into original_table (oid, cid, called_date, status) values (2,2,current, 'B');
insert into original_table (oid, cid, called_date, status) values (3,3,current, 'C');
insert into original_table (oid, cid, called_date, status) values (4,4,current, 'D');
insert into original_table (oid, cid, called_date, status) values (5,5,current, 'E');
-- Insert more rows....

-- Empty new partitioned table
create table new_partitioned_table
(
id int not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) fragment by range(called_offer_id)
interval(115000000)
store in (dbspace1, dbspace2, dbspace3, dbspace4)
partition new_partitioned_table_p0 VALUES < 0 in dbspace1 statlevel fragment;

Then the attach:
ALTER FRAGMENT ON TABLE new_partitioned_table ATTACH original_table AS PARTITION new_partitioned_table_p1 VALUES < 115000000;

It all works fine but is not especially quick. Although there is little write activity, studying the partition profiles shows what appears to be a full scan of the original table during the alter fragment operation. I am not sure why this is necessary. The part number is then re-used as a partition of the new table.

In reality the situation gets more complicated and the command takes longer with indices on the new table, which also seem to involve more I/O than you might hope for. In the interests of the simplest case possible I am not asking about those right now.

Has anyone got any tips or experience to share?

Ben.

------------------------------
Benjamin Thompson
------------------------------

#Informix

I did eventually get a kind of solution to this which is probably worthy of a blog post in the near future.

The table scan is unavoidable and seems to be required by what may happen next. This scan appears to be making sure the data will fit in the new interval you have specified. This could be optimised to use an index or take advantage of an exactly matching check constraint, which I added on the table being attached but it does not. (I find the lack of optimisation in operations like this a bit frustrating sometimes.)

It seems that if your data contains values under the transition value for the new range partitioned table (transition value is a concept discussed in the manual) and your attach clause also specifies the upper limit of the new fragment below this value the attach can be done with no data movement. Otherwise your data is re-organised and new range fragments added in accordance with the range partitioned table's storage schema. I didn't find any way of avoiding the indices being rebuilt even with indices on the range partitioned table inline with the table, but if I were to do a blog post I would spend more time looking at this aspect.

I also noticed the combined table has no distributions despite the index rebuilds and needs UPDATE STATISTICS MEDIUM/HIGH running as appropriate.

I haven't really explored every possible possibility so I think the main things I can say about attaching a fragment to a table partitioned by range are:
* a full table scan will take place on the data being attached no matter what indices or constraints are present on the data.
* the above means an instantaneous attach is not achievable.
* attaching with no data movement is possible, giving a small time advantage over simply doing ALTER FRAGMENT ON TABLE... INIT.. which is an alternative here.
* but indices will probably be rebuilt plus time waiting for index page logging if you have this turned on.
* afterwards UPDATE STATISTICS MEDIUM/HIGH need to be run.
* little of the above is in the manual.

There is potential for an RFE here but admittedly this type of operation is not one we do very often.

Ben.

------------------------------
Benjamin Thompson
------------------------------

Original Message:
Sent: Mon August 17, 2020 01:05 PM
From: Benjamin Thompson
Subject: Alter fragment attach performance

Hi,

I am trying to make ALTER FRAGMENT ATTACH as seamless as possible, ideally to the point that given the right conditions the attach will just involve updating system tables and no data movement. Therefore it will take a matter of seconds no matter how large the tables involved are.

Here is a little test case:

-- Original table, in reality a table that has existed for some time with many millions of rows
create table original_table
(
id serial not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) in dbspace1;

create unique index original_table_u1 on original_table (id) using btree;
alter table original_table add constraint primary key (id) constraint original_table_pk;

-- Insert data
insert into original_table (oid, cid, called_date, status) values (1,1,current, 'A');
insert into original_table (oid, cid, called_date, status) values (2,2,current, 'B');
insert into original_table (oid, cid, called_date, status) values (3,3,current, 'C');
insert into original_table (oid, cid, called_date, status) values (4,4,current, 'D');
insert into original_table (oid, cid, called_date, status) values (5,5,current, 'E');
-- Insert more rows....

-- Empty new partitioned table
create table new_partitioned_table
(
id int not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) fragment by range(called_offer_id)
interval(115000000)
store in (dbspace1, dbspace2, dbspace3, dbspace4)
partition new_partitioned_table_p0 VALUES < 0 in dbspace1 statlevel fragment;

Then the attach:
ALTER FRAGMENT ON TABLE new_partitioned_table ATTACH original_table AS PARTITION new_partitioned_table_p1 VALUES < 115000000;

It all works fine but is not especially quick. Although there is little write activity, studying the partition profiles shows what appears to be a full scan of the original table during the alter fragment operation. I am not sure why this is necessary. The part number is then re-used as a partition of the new table.

In reality the situation gets more complicated and the command takes longer with indices on the new table, which also seem to involve more I/O than you might hope for. In the interests of the simplest case possible I am not asking about those right now.

Has anyone got any tips or experience to share?

Ben.

------------------------------
Benjamin Thompson
------------------------------

#Informix