Can pt-online-schema-change be run separately to just handle the table swap?

Polyglot Projects Percona Toolkit
1

Hi All,

Question: Would I be able to run pt-online-schema-change (or another percona tool) that does everything but the table creation and data sync?

I’m a beginner with percona and pt-online-schema-change and just want some more information about the table swap and what happens between that and and when the data finishes syncing in the temporary table. Our team is trying to use this tool to add an index on a large table, but we don’t want to do it all in one go since we aren’t too familiar with this toolkit. Right now, we’re using pt-online-schema-change to create a temporary table that has the index and is constantly synced with the original with the created triggers. A couple of days later, after we analyze the table and make sure the new one looks correct, we plan to do a swap on our part that just does a rename of both tables to swap them.

Below is the command we use to generate that temp table. Running this gives us a temp table with the needed triggers that populate the table with all the same data as the original. Neither the triggers nor the temp table will be deleted.

pt-online-schema-change 
--alter "ADD INDEX ..." 
--no-swap-tables 
--no-drop-new-table 
--new-table-name "..." 
--no-drop-triggers 
--execute

That’s all well and good, but we thought it may be best to let the tool do its thing since we don’t know all the ins and outs that come with it. I also noticed that the tool comes with a pause feature (–pause-file) and saw can pt-online-schema-change pause after copy but before table rename. However, I don’t think a pause will work for our case since we want to delay the time between creating/syncing the new table and swapping the two by a couple days.

With all that being said, would it still be possible to rerun this tool (or another percona tool) separately that will handle everything pt-online-schema-change would past the new table generation and data copy?

If not, could someone share what all pt-online-schema-change does once the two tables are ready to be swapped?

Thanks in advance.

2

pt-osc has a --dry-run feature. Try that to see exactly all the steps the tool will take.

Why do you want to do this? What benefit is there to waiting to swap? Renaming tables in MySQL is an atomic operation. You should allow pt-osc to do this aspect of the ALTER in line with the rest of the process.

After pt-osc syncs the data from old → new, the tables are renamed together in one atomic operation:

RENAME tableA TO _tableA_old, tableA_New TO tableA;

Then the old table is dropped:

DROP TABLE _tableA_old;

Check out the --dry-run to see all of the steps.

2 Likes
3

Why do you want to do this? What benefit is there to waiting to swap? Renaming tables in MySQL is an atomic operation. You should allow pt-osc to do this aspect of the ALTER in line with the rest of the process.

This is a new tool for our team and we’re migrating from using our own current process of large schema change to using this tool instead. In part of the switch, we’re taking small steps to ensure we know and are confident of everything this tool does and of the temp table being created. The benefit is that we know exactly what table we’re swapping out for the main one by doing it ourselves, including being able to do whatever validation we need beforehand. Given what you mentioned, I would really like to do the full thing, but we still need to follow a formal process.

We confirmed with the dry run that it only swaps the tables and drops the original (and the triggers). We’ll move forward with our own monitoring and swap on the table, but likely switch to using the full tool on our next iteration.

Thanks for the help and information!

1 Like