percona-online-schema-change recommendation

Question

I am currently setting up percona-online-schema-change to do a ALTER on a table with 300m+ rows.

I've verified that percona-online-schema-change can make the required ALTER.

The source table receives updates/inserts in large batches at erratic intervals.

During testing, with defaults values for percona-online-schema-change, the update fails due to the process not being able to obtain a lock on the new table.

My theory is that due to the erratic nature of the updates, percona-online-schema-change is not able to obtain sufficient accuracy for its chunk-size calculations, and is trying to do too much on the new table.

Therefore, I was going to set some hard values to try and obtain some more consistent behaviour.

Specifically, I was going to set a hard --check-size value and disable over-sized chunk checking.

I was also going to increase the number of retries for copy-rows from the default of 10 to 30, and increase the wait between tries from 0.25 to 10.0.

Finally, I was going to increase the innodb_lock_wait_timeout value from 1 to 5.

I know that these measures will slow down the overall operations considerably, but its more important to me that it completes in the first pass that it completes quickly.

Does this seem like a reasonable and safe approach to the update?

Answer 1

You seem to be asking the right questions and that seems like a reasonable course of action.

Don't be surprised if this runs for a few weeks.

Answer 2

We ran an update with extra parameters and it completed.

In the end, we didn't alter the chunk-size but instead altered the default settings for the copy-rows process, to increase the number of retries and the interval between retries.

We also increased the innodb_lock_wait_timeout from the default 1 second to 5 seconds

--set-vars innodb_lock_wait_timeout=5 --statistics --tries copy_rows:30:10

We ran it with the --statistics parameter which showed up that locks were encountered 41 times during the process, which is minimal, so there was no material change in the amount of time it took to complete the process.

Obviously that isn't going to apply in every situation, so don't take these settings as gospel.

To me, the default settings for copy_rows do seem to be a little tight, so in the interests of getting your update to pass, it probably no harm in to least increasing the interval between the default 10 update tries. The default interval is 0.25 seconds, which seems very low.