Search code examples
mysqlperformanceoptimizationinnodb

How can I speed-up the table reconstruction in MySQL when altering the schema?

I have a relatively big MySQL InnoDB table (compressed), and I sometimes need to alter its schema (increasing column size or adding a field).

It takes around 1 hour for a 500 MB table with millions of rows, but the server doesn't seem to be very busy (CPU @5%, not much RAM used, and 2.5 MB/s as I/O).

The table is not used in production so there are no concurrent requests at the same time. There is only a primary index (on the first 5 columns) and one foreign key constraint.

Do you have any suggestion on how to speed-up the table alteration process?

Solution

  • Changing storage engine (to newer generation engines like TokuDB) seems the way to go, until InnoDB is "fixed".