Some background
When rehearsing a production deployment to EE 1.11 from 1.10, we encountered an issue where the SQL upgrade routines were adding columns to report, log, sales, customer tables for the Enterprise_RMA
module.
The log tables on this particular install had ~10M rows, had not been maintained in quite some time. Adding columns proved to take a very, very long process. The rehearsal upgrade was performed on an AWS 4XL, and still took ~4 hours to complete.
Upon analysis, and reproduction of the issue, we found that columns were being added one at a time, SHOW PROCESSLIST
reveals 30-45s for copy to tmp table
for each column. There are dozens of columns to add in some of these tables.
Our (workable, but not my favorite) solution:
Our workaround was to truncate log/report tables, and take the hit on the sales_flat_order
tables. We could have run the archiver to move some of the data out (~600k records or so) - but custom reports that demanded the presence of the sales_flat_order
table precluded our ability to do that.
Our final, actual, production deployment still hit up in the 1.5 hour range for the full database upgrade.
Finally, the question:
How can we speed up database migrations from the application layer? Can we batch or roll up all ALTER
statements into a handful of executions or is this just the nature of the beast? How do you handle this for your huge EE customers? Upgrade offline, swap in place?
Any and all feedback welcomed. Thanks in advance.