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?
Best Answer
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.