Best practices

I'm using a table of approximately 1TB in a MySQL database. This table also has a monthly partition. We store the last two months of data in this table and regularly truncate the data from the previous two months. However, when we do this without stopping the server, CPU usage increases significantly, and the server becomes unreachable until the truncate is complete. What needs to be done to delete this without stopping the system and without encountering any problems?

server: amazon ec2 c6gn.12xlarge 96GB RAM 48vCPU

Improve this question

3 Replies 3

I Am Staple
• 
ALTER TABLE your_table DROP PARTITION your partition name;

This could delete the partition without locking table

Improve this answer
user1592429
•

I'm already using Drop Partition. I think there is no way to do this online with 5.7. But i found
ALTER TABLE your_table DROP PARTITION partition_name, ALGORITHM=INPLACE, LOCK=NONE;
for mysql 8.

Improve this answer
Bill Karwin
•

Correct — MySQL 5.7 does not support the ALGORITHM or LOCK options for DROP PARTITION.

It should run DROP PARTITION quickly (without doing a table-copy) if you use RANGE or LIST partitioning, which I assume you are, since that would be the typical way to partition by month (but you haven't shared your table definition, so this is an assumption).

Reference: https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html#online-ddl-partitioning

DROP PARTITION ... Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Does not copy existing data for tables partitioned by RANGE or LIST.

You should not be using MySQL 5.7 anyway, since it has been end-of-life since 2023. In fact, MySQL 8.0 is nearing its end-of-life, so you should really be using at least 8.4.

Improve this answer

Your Reply

By clicking “Post Your Reply”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.