**WARNING:** This still requires getting a table metadata lock at the start so it can be blocked by long transactions. You will need to kill those for this to continue. Sometimes this means using native MySQL is just as good as Percona.
**WARNING:** Altering a busy DB table with lots of incoming foreign keys can cause hours of downtime due to all incoming foreign PKs locking their tables during rebuild. Options are: 1) Drop these FKs. 2) Use drop\_swap option (dangerous) 3) take down application & DB during update
Tool name: [pt-online-schema-change](https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html "https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html") Use this tool if a standard alter MySQL command fails (eg. due to duplicate key) The below `pt-online-schema-change` sample is a good starting place, but you should become familiar with all the options. This avoids the potentially dangerous `drop_swap` option for foreign keys. While it can be a lot faster, it can also cause damage. When possible, avoid foreign keys. Sample Usage: 1. First collect the **ALTER** commands you plan to perform, excluding the DB and table name. This should be MySQL's native alter format. You can use Workbench to help build your alter query. 1. eg: `ADD COLUMN qty_test INT(11) NOT NULL DEFAULT 0` 2. **DO NOT** include tick marks around the field names. If you must because of special characters in field/index names, ensure the alter command is surrounded by single quotes, not double 3. If you have a `CREATE INDEX` command, reformat it to look like this: 1. `ADD [UNIQUE] INDEX idx_name (col_1, col_2, ...) USING BTREE` 4. If you have multiple commands, separate them with a comma 2. Build your command like so: 1. pt-online-schema-change --alter "**ALTER\_COMMAND**" --alter-foreign-keys-method rebuild\_constraints 'h=**MYSQL\_HOST**,P=3306,u=**DB\_USER**,p=**DB\_PASS**,D=**SCHEMA**,t=**TABLE\_NAME**' --max-load Threads\_running=300 --critical-load Threads\_running=450 --set-vars innodb\_lock\_wait\_timeout=10,foreign\_key\_checks=0 2. Fill in your **MYSQL\_HOST** and **SCHEMA** name 3. Fill in your credentials in **DB\_USER** and **DB\_PASS** 1. NOTE: You must user a user with the following privileges: DROP, REPLICATION SLAVE (If replication is enabled) 4. In **ALTER\_COMMAND** - put your alter command to be run 5. in **TABLE\_NAME** put the table to alter 6. You must add either **--dry-run** or **--execute** at the end. 7. Other options are available as well: 1. **--max-load Threads\_running=300 --critical-load Threads\_running=450** 1. This allows the script to run under busier conditions and pause if taking too many resources instead of exiting early. Max Load specifies when the script pauses, Critical load specifies when the script terminates. 2. You can leave these off, or tune them to your performance needs 2. **--set-vars innodb\_lock\_wait\_timeout=10** 1. We prevent lock wait timeout errors by increasing the innodb lock wait timeout from the default of 1 second [Percona Toolkit Documentation](https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html) 1. A Dry Run is recommended first! 2. This may take a while, to prevent it from halting early if your ssh session ends use the following: `nohup COMMAND &` **Side effects:** Foreign keys will be renamed. Usually by adding an underscore at the beginning ## **Find Duplicate & Unneeded Indexes** This tool will find and output a list of DB indexes that are unnecessary. Review each one manually and determine if it really should be removed. There can be a lot of output, so it's recommended to pipe this into a file like the example below does. `pt-duplicate-key-checker --host MYSQL_HOST --user DB_USER --password DB_PASS --databases SCHEMA > duplicate_indexes.txt`