What’s the best of adding more partitions to my already partitioned tables. I am trying to use pt-online -schema change to alter these tables and add more partitions to the partitions scheme I have. I found out that using pt-online-schema -change it still involves copying the data over and it’s taking time. Reorganize kinda work fine but still want know if that’s best approach
@Dba1,
Did you try a simple ALTER TABLE .. ADD PARTITION (< new partition definition >);
Simply adding more partitions might be an online operation in MySQL 8, but test that first on a copy.
https://dev.mysql.com/doc/refman/8.0/en/alter-table-partition-operations.html
Yes, pt-osc will always copy the data, no matter what the change is.
Thanks @matthewb I did reorganize partition it went faster. Thank you.
There is an issue I don’t know if you have solution to this
When altering table using pt-osc , this i am trying to add a new partitions on a table with a foreign key constraints i got the below error
DBD::Mysql::db do failed: cannot delete or update a parent row : a foreign key constraint fails for statements. Here I’m trying to use the pt-online schema change and I have tried all the available options, using this tool. Any suggestions