alter table truncate partition

  • Filter
  • Time
  • Show
Clear All
new posts

  • alter table truncate partition

    We have a partitioned by day table. We use hash partitioning but generally it doesn't matter - range behaves the same way as described below. We are trying to organize rolling partition truncation for the old days.
    Problem is "alter table truncate partition" requires "table metadata lock" which causes major locks, connection timeouts, dropped connections, etc. for heavily used table (there are no transactions against given partition for truncation). Anybody can have an experiment where you start one session with:
    start transaction;
    select * from a partition(p0);

    And then run other session with
    alter table a truncate partition p15;

    Then open another one with
    select * from a partition(p5);

    Your second one will be blocked by first one with "Waiting for table metadata lock". Same is your third one.

    In 5.6 documentation you have: ALTER TABLE ... TRUNCATE PARTITION now prunes locks; only the partitions to be emptied are locked.
    ALTER TABLE statements still take metadata locks on the table level.

    Has anyone had experience with this? Any possible solution?
    We use Percona 5.6.15

    Thank you