Informix

 View Only
  • 1.  alter fragment on fragment table bring longtx

    Posted 15 days ago

    I found that a long transaction occurred in the database because of the alter table


    create table "informix".t1
    (
    id serial not null ,
    name varchar(20),
    tx_date char(8)
    )
    fragment by expression
    partition p1 ((tx_date >= '20210101' ) AND (tx_date < '20210201'
    ) ) in datadbs1,
    partition p2 ((tx_date >= '20210201' ) AND (tx_date < '20210301'
    ) ) in datadbs1,
    partition p3 ((tx_date >= '20210301' ) AND (tx_date < '20210401'
    ) ) in datadbs1,
    partition p4 ((tx_date >= '20210401' ) AND (tx_date < '20210501'
    ) ) in datadbs1,
    partition p5 ((tx_date >= '20210501' ) AND (tx_date < '20210601'
    ) ) in datadbs1
    extent size 16 next size 16 lock mode row;
    revoke all on "informix".t1 from "public" as "informix";
    create index "informix".idx_t1 on "informix".t1 (tx_date,id) using btree ;

    dbaccess ttdb -
    > alter fragment on table t1 drop partition p6;

    onlog -n 10 -t 7340114
    DDL.PNG

    In the production environment, I found that the contents of the logical log are as follows

    in logical log
    there is more and more  CHALLOCand this is a longTRX
    DML.PNG

    Why?
    Shouldn't the alter statement be a DDL statement?



    ------------------------------
    ZhiWei Cui
    GBASE
    ------------------------------


  • 2.  RE: alter fragment on fragment table bring longtx

    Posted 15 days ago
    Not sure why the DROP PARTITION behaved the way it did. What was the definition of partition p6? The CHALLOC log records indicate that data was being moved around. At the very least, lots of index nodes might have to be rewritten and probably, because the index isn't partitioned, the entire index may have been recreated.

    One suggestion is to DETACH the partition next time then drop it afterwards.
    I would also note that this expression based partition scheme would work better and be more efficient as a RANGE partition and might even take advantage of rolling windows to automatically add and drop partitions. If nothing else, the overhead of finding the proper partition for a new row is far less for a RANGE partition than an expression partition plan especially when the partition definition for the newest data is the last expression.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: alter fragment on fragment table bring longtx

    Posted 15 days ago
    create index "informix".idx_t1 on "informix".t1 (tx_date,id) using btree ;  
    this is default fragment index  and experssion  with data fragment.
    oncheck -pt dbname:tabname 
    I find index is fragmented.

    so,when I drop partition,the index partition will be droped。 Instead of rebalancing the B + tree。

    I will   test with DETACH the partition。

    It's strange that drop partition is so fast most of the time, but DML sometimes causes long transactions.



    ------------------------------
    ZhiWei Cui
    GBASE
    ------------------------------



  • 4.  RE: alter fragment on fragment table bring longtx

    Posted 12 days ago

    Hi,

    Try sizing extent size and next extent size to a more reasonable value as well.

    David.

    ------------------------------
    David Williams
    ------------------------------



  • 5.  RE: alter fragment on fragment table bring longtx

    Posted 12 days ago
    I'll test it,set extents of different sizes
    I will also test whether the partition data has migrated through onheck -pt dbname: tabname after detach

    thank you。

    ------------------------------
    ZhiWei Cui
    GBASE
    ------------------------------



  • 6.  RE: alter fragment on fragment table bring longtx

    Posted 9 days ago
    Hi,

    the DETACH fragment operation is what you need to do. Your index will be attached to the data fragment and should not delay the DETACH in this special case.
    You can reprocess the "partition p6" data in the new table you will get after the DETACH .
    Informix can handle this for you if you use your "monthly" data schema in a RANGE partition way.

    Best Reagrds
    Henri

    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion 2021
    ------------------------------



  • 7.  RE: alter fragment on fragment table bring longtx

    Posted 9 days ago
    TKS

    I will try 。

    ------------------------------
    ZhiWei Cui
    GBASE
    ------------------------------