Sample Header Ad - 728x90

ALTER TABLE ... REORGANIZE PARTITION on huge table takes longer after upgrading to MySQL 8.0

1 vote
0 answers
57 views
After upgrading from MySQL 5.7 to MySQL 8.0.33, I’ve observed a dramatic slowdown in partition management operations, particularly: - ALTER TABLE ... REORGANIZE PARTITION - ALTER TABLE ... DROP PARTITION Before the upgrade, these commands finished in less than 1 second.
ALTER TABLE hugeTable 
REORGANIZE PARTITION pMax INTO (
  PARTITION p202506 VALUES LESS THAN (1748707200),
  PARTITION pMax VALUES LESS THAN MAXVALUE
);

ALTER TABLE hugeTable 
DROP PARTITION p202405;
Every month, we run a scheduled job that: - Adds the next month’s partition using REORGANIZE PARTITION - Removes the oldest partition using DROP PARTITION This pattern worked fine for years on MySQL 5.7. Now, on 8.0.33, both steps take much longer and affect performance, even though partitions being dropped are already empty. Is there any way to make these ALTER TABLE operations faster and without performance impact, like in MySQL 5.7? Note: I can’t use pt-online-schema-change, because it takes too long for a table this big. Environment: - OS: Ubuntu 18.04.6 LTS (Bionic Beaver) - MySQL Version: 8.0.33 - Table Size: ~2000 GB, ~40B rows - PARTITION BY RANGE (timestamp) Thanks in advance for any guidance or workarounds!
Asked by Billy Hsu (11 rep)
May 20, 2025, 10:22 AM
Last activity: May 23, 2025, 08:06 AM