Preventing performance surprises with Laravel migrations

Yesterday, I took you on a historical journey with how MySQL handled column insertion over the years, but today I'm going to talk about how to avoid any potential surprises when running migrations.

There are two basic reasons that the INSTANT algorithm would not be used:

1. If you're doing an operation that isn't supported by that algorithm. For example, changing nullability of a column.
2. The operation is supported, but your table is at the maximum number of row versions, so a full table rebuild is required.

For the first situation, you could read the docs which explain in great detail what is and isn't supported. Another option is to let Laravel generate the ALTER TABLE queries using php artisan migrate --pretend. You can then take those queries, modify them by appending ALGORITHM=INSTANT and manually test them in your local database. If the algorithm isn't supported, you'll know it.

For the second situation, you can query the INFORMATION_SCHEMA database to see how many row versions each affected table has:

SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES;

If any tables you're about to migrate have a row version count approaching the limit of 64, you could consider holding off on deploying that migration until you can schedule some database maintenance and run OPTIMIZE TABLE first.

And if you have a system dashboard in place where you monitor infrastructure health (like Pulse as an example), you could add a check for row version count there to keep an eye on it.

Hope this helps,

Joel

P.S. Would you like two pros to build out your next Laravel app?