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