Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Alter fragment attach performance

  • 1.  Alter fragment attach performance

    Posted Mon August 17, 2020 01:06 PM
    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
    ------------------------------


  • 2.  RE: Alter fragment attach performance

    Posted Tue August 18, 2020 12:30 AM
    Edited by Gaurav Kumar Tue August 18, 2020 12:31 AM
    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
    ------------------------------



  • 3.  RE: Alter fragment attach performance

    Posted Tue August 18, 2020 12:32 PM
    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
    ------------------------------



  • 4.  RE: Alter fragment attach performance

    Posted Tue August 18, 2020 01:59 AM

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



  • 5.  RE: Alter fragment attach performance

    Posted Tue August 18, 2020 05:32 AM
    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
    ------------------------------



  • 6.  RE: Alter fragment attach performance

    Posted Tue August 18, 2020 05:59 AM
    ... und wieder was gelernt ... DANKE !

    Mit freundlichen Grüßen / Kind regards


    Gerd Kaluzinski

    Certified IT-Specialist INFORMIX und DB2 UDB
    IBM Software Group, Information Management / SWG-Services-Info.Mgmnt.
    -------------------------------------------------------------------------------------------------------------------------------------------
    IBM Deutschland
    Hollerithstr. 1
    81829 München
    Phone: +49-175-228 1983
    E-Mail: gerd.kaluzinski@de.ibm.com
    -------------------------------------------------------------------------------------------------------------------------------------------
    IBM Deutschland GmbH / Vorsitzender des Aufsichtsrats: Sebastian Krause
    Geschäftsführung: Gregor Pillen (Vorsitzender), Agnes Heftberger, Norbert Janzen, Markus Koerner, Christian Noll, Nicole Reimer
    Sitz der Gesellschaft: Ehningen / Registergericht: Amtsgericht Stuttgart, HRB 14562 / WEEE-Reg.-Nr. DE 99369940






  • 7.  RE: Alter fragment attach performance

    Posted Tue August 18, 2020 10:43 AM
    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
    ------------------------------



  • 8.  RE: Alter fragment attach performance

    Posted Thu August 27, 2020 09:16 AM
    Hallo Andreas,

    wenn ich zwei Server in einer RSS habe, dann kann ich am Primary mittels "onparams -p"
    das PLOG ändern. Sollte das nicht am RSS automatisch mit geändert werden ? Is hier gerade nicht so ...

    Mit freundlichen Grüßen / Kind regards


    Gerd Kaluzinski

    Certified IT-Specialist INFORMIX und DB2 UDB
    IBM Software Group, Information Management / SWG-Services-Info.Mgmnt.
    -------------------------------------------------------------------------------------------------------------------------------------------
    IBM Deutschland
    Hollerithstr. 1
    81829 München
    Phone: +49-175-228 1983
    E-Mail: gerd.kaluzinski@de.ibm.com
    -------------------------------------------------------------------------------------------------------------------------------------------
    IBM Deutschland GmbH / Vorsitzender des Aufsichtsrats: Sebastian Krause
    Geschäftsführung: Gregor Pillen (Vorsitzender), Agnes Heftberger, Norbert Janzen, Markus Koerner, Christian Noll, Nicole Reimer
    Sitz der Gesellschaft: Ehningen / Registergericht: Amtsgericht Stuttgart, HRB 14562 / WEEE-Reg.-Nr. DE 99369940






  • 9.  RE: Alter fragment attach performance

    Posted Tue August 18, 2020 04:37 PM
    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
    ------------------------------



  • 10.  RE: Alter fragment attach performance

    Posted Wed August 26, 2020 05:03 AM
    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
    ------------------------------