Table modifications are a routine task for database administrators. The blog post Using Percona Toolkit to Alter Database Tables Online: A Controlled Approach provides insights into the process of altering tables online in a controlled manner, ensuring uninterrupted access for application users and preventing application downtime. We will focus here on utilizing the powerful “pt-online-schema-change” tool from the Percona Toolkit, which allows structural modifications to tables without impeding read or write operations. This approach allows administrators to modify tables seamlessly while maintaining data integrity and minimizing disruption.
Online table alterations with pt-online-schema-change:
Percona Toolkit offers the “pt-online-schema-change” utility to alter table structures without causing disruptions. The tool follows a well-defined procedure to achieve this:
Creation of an empty copy:
pt-online-schema-change creates an empty copy of the target table that will be altered. The empty table is modified as required without affecting the original table or ongoing operations.
Row transfer and synchronization:
Once the empty table is ready, pt-online-schema-change copies rows from the original table to the modified table. During this process, any changes made to the original table’s data are synchronized with the new table.
Replacement of the original table:
After all, rows are successfully transferred, pt-online-schema-change replaces the original table with the modified one.
This step ensures a seamless transition from the old table to the new structure.
Benefits and considerations:
Performing table alterations online using pt-online-schema-change offers several advantages:
Minimal downtime: Applications can continue accessing the original table while the alteration occurs.
Data consistency: Synchronization mechanisms ensure that changes made during the copy process are accurately reflected in the new table.
Controlled approach: The step-by-step process allows administrators to monitor and validate each stage, reducing the risk of errors.
However, administrators should also consider the following:
Disk space requirements: Sufficient space is needed to accommodate the empty copy and any temporary data during the alteration.
Table with foreign keys: Using the tool when the table contains foreign keys carries inherent risks. It is crucial to consult the tool’s documentation to become acquainted with the available options and evaluate the associated risks before initiating any table modifications that have foreign keys.
–no-swap-tables and foreign keys: The option does not work if the table has foreign keys with child tables associated as expected.
If the “rebuild_constraints” method is chosen as the alter foreign keys method in pt-osc and the –no-swap-tables option is used, the tool may encounter an error similar to the following. In this scenario, there won’t be an “old_tbl” present since the tool is instructed not to perform the table swap.
1 2 | `db_name`.`table_name`: *** rows; can use rebuild_constraints Error updating foreign key constraints: I need a old_tbl argument at /usr/bin/pt-online-schema-change line 11119. |
The specific error indicates that the “old_tbl” is missing or cannot be found. This occurs because the “rebuild_constraints” method relies on the existence of the “old_tbl” during the alter process.
Verification before swap: If you choose to use the –no-swap-tables, –no-drop-old-table, –no-drop-new-table, and –no-drop-triggers options in pt-online-schema-change, it is important to follow a specific manual swapping process. Before proceeding with the manual swap, verifying the pt-online-schema-change log is highly recommended to ensure the alteration process has been completed successfully. Additionally, comparing the table size and recording counts is recommended to confirm they match accordingly.
Pre-flight checks
Foreign keys: When using the pt-online-schema-change tool from Percona Toolkit to perform online schema changes, it is essential to consider the foreign keys present in the database. The –alter-foreign-keys-method option in pt-online-schema-change allows you to specify how foreign keys should be handled during the alteration process.
1 2 3 | SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'Table_Name'; |
Checking foreign keys is important: The –alter-foreign-keys-method option in pt-online-schema-change allows you to specify the method that ensures safety and minimizes table metadata locks during the alteration process, considering the referred table size. By carefully choosing the appropriate methods(auto,rebuild_constraints,drop_swap), you can control how foreign keys are handled while ensuring the safety and integrity of the table.
Disk space requirements: More than double the table size must accommodate the table data copy and any temporary data during the alteration. Ensure all the servers in the replication topology or the cluster have enough disk space.
PRIMARY KEY or UNIQUE INDEX: In most cases, it is essential to have a PRIMARY KEY or UNIQUE INDEX defined on the table when using the pt-online-schema-change tool. This requirement is necessary because the tool creates a DELETE trigger to ensure the new table remains updated throughout the alteration process.
Dry run before execution: The –dry-run option in pt-online-schema-change provides a way to simulate the alteration process without changing the table structure. When used, it performs a dry run of the schema change, allowing you to preview the potential alterations and assess their impact before applying them to the actual table.
Once you have confirmed everything is in order to proceed with the alteration, replace the –dry-run option with –execute to initiate the schema change.
Dropping a secondary index: In recent MySQL versions, dropping an index is primarily a metadata change, which often does not require pt-online-schema-change. In most cases, you can directly alter the table with the algorithm=inplace and lock=none options to drop the index efficiently. These options allow for an online alteration that avoids locking the table, ensuring minimal disruption to concurrent operations. By leveraging these direct alter options, you can effectively remove the index while maintaining the availability and performance of your MySQL database.
Conclusion
Altering tables online in a controlled manner is crucial for database administrators aiming to minimize disruptions and maintain application availability. Percona Toolkit’s “pt-online-schema-change” tool provides a robust solution by enabling administrators to modify table structures without interfering with ongoing operations. By following the outlined steps and considering the benefits and considerations, administrators can confidently and safely perform table alterations while ensuring data integrity and minimizing downtime.
Note: For more detailed instructions on how to use the tool to do the online alter in a controlled approach, please refer to the blog post mentioned earlier.
