I have been trying to run pt-online-schema-change to drop 1 index and 2 columns from a table large table (roughly ~22 million~ rows) which has multiple foreign keys pointing to it by other tables. Most of the errors seem to stem from the inability to secure a lock on the table to either create or drop triggers (both issues have occurred). The query used to try and perform this patch was:
pt-online-schema-change --alter "DROP INDEX <index>, DROP COLUMN <column_1>, DROP COLUMN <column_2>;” --critical-load="Threads_running=100" --no-check-unique-key-change --alter-foreign-keys-method=drop_swap --recursion-method=none --execute D=<db>,t=<t>,u=<u>,p=<p>,h=<host>
The biggest problem encountered has been triggers taking an excessive amount of time to either create or drop has lead to the table locking and bringing down the database entirely. The exception from the most recent attempt was:
<db>.<t> was not altered. (in cleanup) 2020-08-18T22:58:38 Error copying rows from <db>.<t> to<db>.<t_new>: Threads_running=2146 exceeds its critical threshold 100.
I’m fairly new to using percona patches, but based on the documentation, the table modifications I’m trying to make should surely be able to be performed. Please let me know if you know a solution to troubleshooting a problem like this or if there are any obvious issues with the flags being used (or flags that should be being used). Any help would be greatly appreciated. Thank you.