pt-online-schema-change issues around securing a table lock when creating or dropping triggers

MySQL & MariaDB Other MySQL® Questions
1

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.

2

This is normal, all DDL like “create trigger” must acquire a metadata lock on a table.  If there are long running queries using the table, the metadata lock request has to wait and it blocks the following queries.   The presence of foreign keys is challenging for pt-osc, especially if the table being altered is referenced by constraints on other tables.  Read the section on foreign keys in https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html.
The tool also bails out if there are too many thread running, you got 2146, a high number.  If you prefer pt-osc to continue, try:

--critical-load=999999 --max-load=200

The above parameters push the “bail-out” threshold very high and the tool will throttle itself if there are more than 200 threads running.  Adjust these to what makes sense for your environment.