Difference between revisions of "DBMigrationBestPractices"
| Line 1: | Line 1: | ||
== Overview == | == Overview == | ||
| − | Database migrations have historically been a source of considerable downtime when deploying new versions of Openstack services. | + | Database migrations have historically been a source of considerable downtime when deploying new versions of Openstack services. Often DB migrations take a long time, some take an hour or more to run. |
| + | |||
| + | The best strategy for handling DB migrations is to run as much of the DB migration as possible "online". This means while the service is running (usually old code, but sometimes new code). | ||
| + | |||
| + | However, some DB migrations do things that are particularly difficult to execute online leading to downtime. | ||
| + | |||
| + | DB migrations typically fall into two categories: schema migrations and data migrations. | ||
=== Schema Migrations === | === Schema Migrations === | ||
| − | Schema migrations are any schema changes that create, drop or rename tables, columns, indexes and foreign keys | + | Schema migrations are any schema changes that create, drop or rename tables, columns, indexes and foreign keys. |
==== MySQL Considerations ==== | ==== MySQL Considerations ==== | ||
Not all versions of MySQL are made alike. Older versions (such as 5.1) will block reads/writes for pretty much any schema changes. Newer versions (5.5 and 5.6) are much better and in many cases much faster and/or online (without blocking reads/writes). | Not all versions of MySQL are made alike. Older versions (such as 5.1) will block reads/writes for pretty much any schema changes. Newer versions (5.5 and 5.6) are much better and in many cases much faster and/or online (without blocking reads/writes). | ||
| + | |||
| + | It's important that the operator is running a version of MySQL that allows schema changes to be performed online without blocking running services. | ||
FIXME: Fill in matrix of versions and schema changes that block | FIXME: Fill in matrix of versions and schema changes that block | ||
| Line 15: | Line 23: | ||
=== Data Migrations === | === Data Migrations === | ||
| − | Data changes are any migrations that | + | Data changes are any migrations that change data already in tables. For example, encrypting data in a column or moving data from one table to a newly created table.migration. |
== Best Practices == | == Best Practices == | ||
| − | As a general rule, strive to make DB migrations run as much as possible | + | As a general rule, strive to make DB migrations run as much as possible online. This means that the DB migrations can run while old (or sometimes new) code is already running. This minimizes the amount of time services are down. Less downtime makes operators and users happy. |
=== Maintain Backwards Compatible Schemas If Possible === | === Maintain Backwards Compatible Schemas If Possible === | ||
Revision as of 16:13, 6 February 2014
Contents
Overview
Database migrations have historically been a source of considerable downtime when deploying new versions of Openstack services. Often DB migrations take a long time, some take an hour or more to run.
The best strategy for handling DB migrations is to run as much of the DB migration as possible "online". This means while the service is running (usually old code, but sometimes new code).
However, some DB migrations do things that are particularly difficult to execute online leading to downtime.
DB migrations typically fall into two categories: schema migrations and data migrations.
Schema Migrations
Schema migrations are any schema changes that create, drop or rename tables, columns, indexes and foreign keys.
MySQL Considerations
Not all versions of MySQL are made alike. Older versions (such as 5.1) will block reads/writes for pretty much any schema changes. Newer versions (5.5 and 5.6) are much better and in many cases much faster and/or online (without blocking reads/writes).
It's important that the operator is running a version of MySQL that allows schema changes to be performed online without blocking running services.
FIXME: Fill in matrix of versions and schema changes that block
Data Migrations
Data changes are any migrations that change data already in tables. For example, encrypting data in a column or moving data from one table to a newly created table.migration.
Best Practices
As a general rule, strive to make DB migrations run as much as possible online. This means that the DB migrations can run while old (or sometimes new) code is already running. This minimizes the amount of time services are down. Less downtime makes operators and users happy.
Maintain Backwards Compatible Schemas If Possible
If possible, maintaining backwards compatible schemas means migrations can occur online. Things like adding new columns or creating new tables is backwards compatible (mostly, be careful of 'SELECT *' or 'INSERT INTO foo VALUES ()') because old running code simply don't use the new columns or tables.
Avoid Renaming Columns
It's extremely difficult to rename columns safely with services running. Old code simply won't know the new column name and new code must go extremely far to transparently handle using either the old or new names. Renaming columns is usually not necessary for technical reasons, so avoiding it is recommended.
Avoid Changing the Type of Columns
For many of the same reasons avoiding renaming columns, we should avoid changing the type of columns. Creating a new column is recommended.
