NoDowntimeDBMigrations

Problem Description
In the past, some migrations have shown to take a significant amount of time to run on large installations, causing unacceptable amounts of downtime during code upgrades.

As Openstack installations continue getting larger and larger, these long downtimes will continue getting worse and worse. If an Openstack provider guarantees 99.99% uptime, that leaves only 4 minutes total per month for all downtime (planned or unplanned). Some database migrations in the past have taken hours to run on large installations.

Schema Changes
MySQL will often grab a table lock during the entire take it takes to execute a schema change. This will block all reads and writes during the period the table lock is held. The length of time the lock is held often depends on the number of rows in the table.

MySQL will take a table lock depending on the version of the daemon, what underlying storage engine is used and what type of schema change is being performed.

PostgreSQL is much better but will also grab a table lock in some cases (eg adding a column with a default value).

Schema and Data Format Expectations
Code in Openstack currently expects a fixed schema and data format. All schema changes and changes to data format are required to be completed after Openstack has been stopped and before the new version is running.

Proposed End Solution
Embrace the Expand/Contract pattern for database migrations. This pattern splits database migrations into three parts:


 * 1) Expand schema (adding new columns/tables/indexes)
 * 2) Migrate data
 * 3) Contract schema (removing unused columns/tables/indexes)

Code would migrate data on load when the service is running. Optionally, a background task can migrate all data at whatever speed specified. When all data that needs to be migrated has been migrated, the contraction can be run which will remove unused columns.

This decouples the database schema from the code allowing the two to be updated independently and allowing the service to continue running transparently while the data is migrated.

The goal is to get total downtime per code upgrade measured in seconds.

Implementation Path
Getting to the proposed end solution will require non-trivial changes and significant work. Smaller implementation steps should be taken to reduce the time it takes to get improvements implemented as well as increase the likelihood of changes being merged. The goal is to incrementally reduce the amount of downtime with each step.

Online Schema Changes
Certain schema changes can be done that retain compatibility with running code. Creating new tables, adding columns or adding indexes can generally be done while old code is running. Dropping old tables, removing columns or removing indexes can generally be done while new code is running.

However, underlying database limitations might cause queries to block an unacceptable amount of time. Some tools have been developed in the past to help with online schema changes:
 * pt-online-schema-change
 * Facebook Online Schema Change
 * openark kit

These tools may also have restrictions on when they can be safely used. For instance, tables that are the target of foreign key constraints pose problems. This is because of underlying MySQL constraints that are not compatible with the tools. There are workarounds (in the case of pt-online-schema-change) but they have significant drawbacks.

Complicating any schema changes is the need to support all of the databases Openstack currently supports: MySQL/MariaDB, PostgreSQL and SQLite. All of the existing solutions appear to be specific to MySQL/MariaDB. SQLite brings it's own set of problems by supporting basically no schema changes (it only supports renaming tables and adding columns).

The existing sqlalchemy-migrate and alembic tools used by Openstack perform all schema changes directly. Patching these tools to do online schema changes might be difficult.

Since schema changes can take a long time, tools need to make managing the workflow of the online and offline parts easier.