When you're working with large MySQL tables in production, making schema changes like adding or dropping indexes can be risky if not done carefully or manually using SQL statements.
Fortunately, Percona Toolkit’s pt-online-schema-change (pt-osc) provides a safer, non-blocking way to make changes — but even with this powerful tool, preparation is very essential.
Today, I am sharing here some of the most essential pre-checks you should perform before using pt-online-schema-change to modify indexes on a MySQL table.
Understand the table size to anticipate the duration and performance impact.
select table_schema as database_name,
table_name,
round( (data_length + index_length) / 1024 / 1024 / 1024, 2) as total_size_gb, round( (data_length) / 1024 / 1024 / 1024, 2) as data_size_gb,
round( (index_length) / 1024 / 1024 / 1024, 2) as index_size_gb
from information_schema.tables
where table_schema = 'DB_NAME'
and table_name = 'TABLE_NAME';
pt-osc needs a PRIMARY KEY or UNIQUE KEY to track and apply row changes. If no primary or unique key exists, the tool will refuse to run unless explicitly forced (--no-check-alter), which is not a recommended practice
SHOW CREATE TABLE TABLE_NAME;
Ensure all concerned replicas are up to date and healthy before running changes to the master. Schema changes can cause significant replication lag if not managed carefully.
SHOW SLAVE HOSTS;
SHOW SLAVE STATUS \G
Seconds_Behind_Master = 0
Slave_IO_Running = YES
Slave_SQL_Running = YES
You can use --check-slave-lag with pt-osc to monitor and throttle changes based on replica lag.
Before adding or dropping an index, ensure below things are done:
Indexes should improve read performance and not add unnecessary overhead. Before adding or dropping an index, run EXPLAIN (or EXPLAIN ANALYZE) on key queries:
SHOW INDEX FROM table_name;
SHOW CREATE TABLE table_name;
If you're adding or changing an index, test your most important queries with and without the index in a non-production environment:
EXPLAIN SELECT * FROM test WHERE id = 1234;
This will output the execution plan, showing how MySQL is accessing the table. If you see values like type: ALL, key: NULL in the output, it means a full table scan is happening and this table needs indexing.
EXPLAIN SELECT * FROM test WHERE id = 1234;
Now compare the output. You must see the difference as below
When pt-osc is executed, it creates a copy of the table. Make sure you have enough free disk space in:
You’ll need enough free space for a table copy. The available size in the data partition should be at least twice of the table size.For e.g, if the table size is 50G, make sure there is more than 100G free space available in the data partition when pt-osc is executed.
df -h
Foreign keys can block or prevent pt-osc from completing if not handled properly.If your table has foreign key constraints, pt-osc must:
- Detect them
- Rebuild the constraints pointing to the new table
- If improperly handled, the operation may fail or leave foreign key constraints broken.
SELECT table_name, column_name, constraint_name,referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name = 'table_name';
SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.referential_constraints
WHERE TABLE_NAME = 'table_name';
What do you do if foreign keys exist on your concerned table?
If foreign keys are existing, use the --alter-foreign-keys-method flag with one of the following options:
Since pt-osc creates triggers to keep the shadow table in sync with the original during the change, you must verify:
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING, ACTION_STATEMENT, CREATED
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'your_database_name'
AND EVENT_OBJECT_TABLE = 'table_name';
SHOW TRIGGERS LIKE 'table_name';
SHOW TRIGGERS FROM database_name WHERE Table = 'table_name';
pt-osc is very cautious when it detects that replication filters are used on the server. This is because:
So by default, it aborts execution if any such filters are found
SHOW VARIABLES LIKE '%replicate%';
Check below variables:
SHOW SLAVE STATUS\G
If any of the above values are non-empty, pt-osc will block the operation by default.
If you understand the risks, you can override this behavior by explicitly passing --no-check-replication-filters as an option in the pt-osc command. This will make pt-osc ignore the presence of replication filters and proceed anyway.
Make sure you can safely override this only if:
Always test your pt-osc command in a non-production environment before running it in a production environment
Use throttling to avoid overload, set the values as per your system values
For e.g:
--max-load Threads_running=XX
--critical-load Threads_running=XX
Add --max-lagoption in your pt-osc command if you are working on a server which has replicas added under it. With this option, pt-online-schema-change monitors the replica lag, and pauses the data copy if any replica exceeds the specified lag in seconds. Hence this prevents the tool from overloading replicas and causing excessive lag.
It checks lag via SHOW SLAVE STATUS If any replica has replication lag > X seconds, pt-osc pauses. This helps to maintain a near-real-time data consistency and avoids breaking or delaying downstream apps that rely on timely replication
Always take a full back up of your table before altering any production data
Double-check your full command using dry-run before doing actual execution. Always test your commands in a staging environment first, before running it directly on a production environment.
pt-online-schema-change \
--alter "ADD INDEX idx_example(created_at)" \
--dry-run \
--user=dbuser --password=dbpass --host=localhost \
--database=mydb --table=mytable
pt-online-schema-change is a fantastic tool, but it’s only as safe as your preparation. These few checks will help ensure that your schema change doesn't disrupt replication, application traffic or data integrity. It ensures your schema change is safe, non-disruptive, and production-ready.
Whether you're adding a much-needed index or cleaning up old ones, following these steps can save you hours of debugging and stress!
Looking to optimize your MySQL use?