alter fragment on fragment table bring longtx
ZhiWei Cui saw a long transaction with many CHALLOC log records when dropping a partition of an expression-fragmented table with a fragmented index. Art Kagel suggested DETACH first then drop, and RANGE partitioning; David Williams suggested sane extent sizes; Henri Cujass also advised DETACH. ZhiWei Cui planned to test; no result was reported.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
|
view attached (2)
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.PNGIn the production environment, I found that the contents of the logical log are as follows
：
in logical log
，
there is more and more CHALLOC
，
and this is a longTRX
DML.PNG
Why?
Shouldn't the alter statement be a DDL statement?
------------------------------
ZhiWei Cui
GBASE
------------------------------
#Informix
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 ------------------------------
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:tabnameI 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
------------------------------
Hi, Try sizing extent size and next extent size to a more reasonable value as well. David. ------------------------------ David Williams ------------------------------
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 ------------------------------
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 ------------------------------
TKS I will try 。 ------------------------------ ZhiWei Cui GBASE ------------------------------