I am upgrading some extremely large MySQL DBs from 5.0 to Percona 5.1. These contain hundreds of databases, hundreds of GB of data, and many tens of thousands of tables.
The upgrade went fine, new binaries are in place and everything is working including replication with the still-5.0 master.
However, during the mysql_upgrade script (or if I run mysqlcheck -A --check-upgrade directly) I get the needs repair message for perhaps 10% of my tables as described in Peters post here:
Following his instructions to run “alter table X engine=InnoDB” does fix these up but the problem is I have 10k tables needing this done and the process is extremely slow.
I’ve enabled expand_fast_index_creation and moved the tmpdir directory to an array of SSDs (following advice from Alexey at http://www.mysqlperformanceblog.com/2011/11/06/improved-inno db-fast-index-creation/) and that combination did make things faster but no where near fast enough - at the current pace it will take weeks to complete. I am running 15 at a time by piping the list of tables that need this through xargs. I have bin logs disabled and the MySQL data directory itself is also on SSDs.
Is there anything else I can do to make this faster? Is this process necessary and what are the consequences of skipping it?
The ugpraded DB appears to work perfectly, including the tables that mysqlcheck says need to have this slow process performed so I don’t understand why this is necessary.
Any help is greatly appreciated. I am doing this migration on an extremely hefty box so assume I have all options available in terms of RAM/CPU/Disk.