Some data is collected in a single MySql table. After one week, this data becomes obsolete. The old data is deleted weekly from the table by scheduled command. But this leads to performance bottleneck during deletion as the table is locked. I have been looking in direction of partitioning - to have at least 2 tables: one table for the current week and another for the past week. So I can drop complete table from the past week. Is it a good choice? Are there other options?
CodePudding user response：
There are at least two options I can recommend:
Use table partitioning. Dropping a partition is like dropping a table, it's very fast regardless of the number of rows in the partition. But there are limitations on partitioning, so read the documentation carefully. The one that usually blocks usage of partitioning is how it requires the partitioning key is part of every unique key of the table: https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations-partitioning-keys-unique-keys.html
Use pt-archiver. This deletes rows in small groups, so it takes a brief time for each group. It may take longer than doing a single DELETE, but it's okay because it only locks one group at a time, not the whole table.