ALTERing a Huge MySQL Table

Table of Contents

The Problem

Future Solutions

Overview of Solution

Shortcut

Assumptions/Restrictions/Complications

Database Layer

The Alter

One Iteration (Copy one Clump)

Layer's INSERTs

Helper Table: Migrate

Tuning on the fly

Deactivation

Alternative - Log table

Alternative - Dual Master

Postlog

Brought to you by Rick James

The Problem


You have a "huge" table, and you need to ALTER it. By "huge" I mean that the ALTER would take an unacceptable amount of time.

Things you might want to do. and for which this is well suited:
    ⚈  Add/drop columns
    ⚈  Modify datatypes (INT -> BIGINT, SIGNED -> UNSIGNED)
    ⚈  Modify character set / collation of column(s)
    ⚈  Add/drop indexes
    ⚈  Add/drop/modify PARTITIONing
    ⚈  Normalize/Denormalize
    ⚈  Change Engine (MyISAM to InnoDB)

Future Solutions


Percona's
pt-online-schema-change
(aka pt-osc) can do an ALTER with very little downtime. It does, however, require adding a TRIGGER to the table.

See also this blog about FOREIGN KEYs: pt-osc and FKs

gh-ost
is a new and promising competitor to pt-online-schema-change; it uses the binlog.

The ALTER in version 5.6.7 can do a variety of ALTERs without blocking other operations. For a list of what 5.7 can and cannot do via ALGORITHM=INPLACE, see 5.7 ALTER TABLE

If you don't have the latest version, or you can't use Percona's solution, read on.

Overview of Solution

    1.  Build the 'Alter' script to copy rows into a new table (in clumps of rows)
    2.  Push code to add a Database Layer between client(s) and the database
    3.  Push code to augment the Database Layer to handle the effect of the Alter
    4.  Turn on the Alter
    5.  Push code to deactivate the Alter

The Layer may already be in place; it is 'best practice'. However, after seeing what is ahead (below), you may want to clean up the Layer some. One example is to change a BEGIN...COMMIT into a single API call (if practical). If this leads to a cleaner API, then that helps the API. Later, when you get to step 3, life will be simpler for other reasons.

Steps 2 and 3 are separate because I am assuming you also cannot afford screwups. Step 2 focuses on the API from Client to Layer; Step 3 focuses on tweaking the Layer for this Alter -- they are separable focuses, and is safer to think about only one at a time.

"Turning on the Alter" is essentially running the script (Step 1) to copy the table over. This may take hours or days.

The guiding principles...
    ⚈  If the existing table continues to be read/written by the identical code as before, all queries on it should continue to work correctly.
    ⚈  At all times, rows with id <= $left_off (a "highwater mark") will be correctly transformed, inserted, updated, etc.
    ⚈  At the end, when $left_off is at the end of the table, the transformation is complete.

Shortcut


Most of the rest of this discussion centers on the complex case of a table that is being modified -- any row could be UPDATEd, INSERTs could go anywhere into the table. And it assumes a single machine, or a single Master.

Some likely special cases are covered near the end ("Alternative...")

Assumptions/Restrictions/Complications


Assumptions
    ⚈  This discussion assumes you can walk through the original table using an explicit PRIMARY or UNIQUE key.
    ⚈  Single-column (not 'compound') key is used to walk through the table.
    ⚈  You have enough disk space to simultaneously hold both the original table and the new table(s).
    ⚈  There is enough 'wiggle room' in the performance of the databases so that the overhead of this process (100%?) can be handled.
    ⚈  INSERTs are single-row, UPDATEs are single-table, DELETEs are single-row
    ⚈  You can modify the Layer to change how INSERTs, etc, operate.
    ⚈  SELECTs need no modification. (Nice, eh?)
    ⚈  UPDATE statements do not modify the column being used to walk through the table.
    ⚈  INSERT..ON DUPLICATE KEY UPDATE.. is not used
    ⚈  INSERT IGNORE does not depend on a secondary UNIQUE key
    ⚈  There are no FOREIGN KEYs in the definition of this table, nor any in other tables referencing this table.
    ⚈  Only one table is involved
    ⚈  You have all the write operations (no ad hoc queries from users)
    ⚈  Self-joins

Applicability
    ⚈  Engine -- The outline given here should work equally well for MyISAM or InnoDB.
    ⚈  Replication -- You can perform this on the read/write Master and have it propagate gracefully to the slaves.
    ⚈  Cluster (Galera, etc) -- It should work.

What to do if assumptions are not met?
    ⚈  No PRIMARY/UNIQUE key -- A non-unique key can be used, but this may lead to arbitrarily large locks on the table.
    ⚈  No explicit keys at all -- Punt. (LIMIT+OFFSET is not viable!)
    ⚈  Inadequate disk space -- Punt. Get space first.
    ⚈  If the system gets bogged down, two dynamic tunables can be tweaked to slow down the Alter and make it less invasive.
    ⚈  Multi-row INSERT -- design the Layer's API to make it easy to determine which rows are below $left_off.
    ⚈  Multi-table UPDATE -- This is a challenge -- especially 'self-joins' that interrogate later parts of the table to decide on changes to earlier parts.
    ⚈  DELETE .. IN(...) -- pass the IN list in an array for easy handling
    ⚈  DELETE .. WHERE (multi-row) -- no problem
    ⚈  UPDATE that changes the key -- You must recognize the situation and write some messy code. (It would be handy if the API helped make this obvious.)
    ⚈  INSERT..ON DUPLICATE KEY UPDATE.. -- Don't know.
    ⚈  INSERT IGNORE -- The problem is that it might get INSERTed before the conflicting record is inserted. No workaround available.
    ⚈  FOREIGN KEYS -- have not investigated
    ⚈  Extra tables for normalization -- not a big deal
    ⚈  ad hoc write queries -- must disallow for the duration of the Alter.
    ⚈  Compound index -- The code is more complex, but doable. See
Iterating through a compound key

Database Layer


It is "best practice" to have a "Layer" between your clients and your database. SQL statements are only known to the Layer, not to the clients. By having the Layer, you are segregating "business logic" from "database details". For the purposes here, the "database details" will be changing; it is better to have such code isolated.

The Layer would be called from the Client with calls like "Insert this stuff...". If the 'stuff' needs massaguing to be compatible with MySQL (eg, timestamps), then the Layer is the 'right' place to do the conversion. The Layer is also a good place to hide any "normalization" and "lookup" tables -- clients should no care whether a 'name' is stored in the table directly, or normalized into another table and only an id is put into the "Fact" table.

Keep the API clean and simple; hide any database complexity in the Layer. And, for this task, it will get complex.

For the task at hand, we will depend on the Layer to make it easy to migrate from one table to another, and to do that without any real knowledge of "business logic". This ignorance of the client side of things makes it easier and safer to write the code and have confidence that it is correct.

The Alter


This is a script that does the conversion by copying the data into a new table, which already has the 'new' schema elements (added columns, dropped indexes, etc).

    ⚈  CREATE TABLE -- the new table, with all new indexes/datatypes, etc.
    ⚈  Change the row in the helper table Migrate (running=1, etc) (below)
    ⚈  Loop (below)
    ⚈  Deactivate (running=0)
    ⚈  RENAME TABLE existing TO old, new TO existing;

Each iteration does:
    ⚈  Migrates a "clump" of, say, 100 rows from the old table to the new.
    ⚈  Any transforms (normalization, datatype conversion, etc) are done for those rows.
    ⚈  Locks out all 'write' operations while handling the clump. (More later)

The starting values for Migrate:
    ⚈  running = 1
    ⚈  clump_size = 100 (1000 if existing table is InnoDB with no secondary keys); <100 if lots of indexes)
    ⚈  delay = 1 (second)
    ⚈  left_off_* -- whatever key is less than first value (eg, 0 or '')

The Loop should watch for running out of rows. When that happens, it needs to do the Deactivate and RENAME before the Unlock.

One Iteration (Copy one Clump)


    ⚈  Fetch the next row from Migrate
    ⚈  Find the key of the 100th row after where you 'left_off'.
    ⚈  Lock (exclusive)
    ⚈  INSERT INTO new SELECT * FROM existing WHERE id > $left_off AND id <= $hundredth_hence;
    ⚈  If no more rows, Deactivate and RENAME
    ⚈  Update row in Migrate
    ⚈  Unlock
    ⚈  Sleep 1 second (dynamically tunable)

Notes:
    ⚈  The "Find" step is outside the Lock/Unlock so as to minimize the time in lock state.
    ⚈  Risk: with the Find step outside, you could end up with more than 100 rows to move. (Probably not a big deal.)
    ⚈  Lock/Unlock -- an exclusive LOCK on the Migrate table. Note that all INSERTs/UPDATEs/etc must also Lock that table
    ⚈  The SELECT step should include any transforms needed, build normalization records, and whatever else is required.

Layer's INSERTs


The database Layer is vital for giving us a simple way to modify all INSERT/UPDATE/REPLACE/DELETE statements. All client write operations must be going through the Layer. (SELECTs should go through the Layer, but that does not matter for this discussion.)

I will discuss 'simple' write operations only.

Around every atomic operation, you need to add the following:
    ⚈  Lock (read-lock) on Migrate
    ⚈  Fetch the row from Migrate
    ⚈  If not running, then skip most of these steps. (Alter has not yet started, or has finished.)
    ⚈  Perform the SQL statement on old table
    ⚈  Modify statement to include AND id <= $left_off, and perform it on new table.
    ⚈  Unlock

For "transactions" in BEGIN..COMMIT, either make the whole transaction a single API call, or carefully coordinate the Lock/Unlock with BEGIN..COMMIT. BEGIN should map to the Lock and Fetch steps; COMMIT should map to the Unlock step.

REPLACE should be split into what it actually does -- a DELETE and an INSERT. Both of these sound be inside a single Lock/Unlock pair.

INSERT .. ON DUPLICATE KEY UPDATE .., especially if including a SELECT can probably be broken into two steps as discussed in [staging_table#normalization][High Speed Ingestion]]

As noted earlier, some multi-row and multi-table write operations get more complicated.

Helper Table: Migrate


This table has only 1 row.
      CREATE TABLE Migrate (
         running TINYINT UNSIGNED NOT NULL DEFAULT '0',
         clump_size INT UNSIGNED NOT NULL DEFAULT '100',
         delay FLOAT NOT NULL DEFAULT '1',
         left_off_1 ... (INT, VARCHAR, etc, matching first field in KEY being used)
         left_off_2 ... (more field(s), if needed)
         clumps_moved INT UNSIGNED NOT NULL DEFAULT '0',
         rows_moved BIGINT UNSIGNED NOT NULL DEFAULT '0',
         lock_time DOUBLE NOT NULL DEFAULT '0',
         move_time DOUBLE NOT NULL DEFAULT '0',
         sleep_time DOUBLE NOT NULL DEFAULT '0',
         last_move TIMESTAMP NOT NULL
      ) ENGINE=MyISAM;

Discussion:
    ⚈  running is 1 while Alter is running. INSERTs, etc, check this to know whether to INSERT into the new table.
    ⚈  clump_size says how many rows to move per iteration; "100" is used as an example. This can be dynamically tuned.
    ⚈  delay is how long to sleep(), in seconds, between iterations; 1 second is a good first guess. This is tunable.
    ⚈  left_off_* contain the value(s) of the last key used by the last clump. (Initialize to 0 or '', or whatever is less than first key.)
    ⚈  clumps_moved and rows_moved tally the progress.
    ⚈  the *_time fields keep track of the seconds consumed by the phases of the loop.
    ⚈  last_move is a timestamp, overwritten at the end of each iteration. At the end of Alter, it is the finish time. (If the value is too long ago, but not finished, maybe it is broken.)
    ⚈  Engine=MyISAM was chosen to avoid any risk of involving this in BEGIN..COMMIT transactions.
    ⚈  Replicating the table is optional; it has no function on Slaves.
    ⚈  It was not intended to JOIN this table with any other; I don't know if "left_off_*" is useful.

The table must be CREATEd before the Layer has the code in it that looks at the table. The imporant value (at that point) is running=0.

Tuning on the fly


What to watch...
    ⚈  In Slaves, keep an eye on Seconds_behind_master (or a heartbeat if you have such)
    ⚈  (lock_time / clumps_moved) -- This should be close to zero; if it is high, then Client processes are not getting enough time.
    ⚈  (move_time / clumps_moved) -- The average time that it takes to move a clump. This impacts latency of Client write operations.
    ⚈  sleep_time versus move_time -- which is getting more time, the Clients, or the Alter.

The following two values in the Migrate table can be changed at will; changes take effect at the start of the next clump.

clump_size (nominally 100) effectively controls how long INSERTs and other writes are blocked at a time. Decrease during busy times; increase during lulls.

delay (nominally 1 second; declared FLOAT), controls the gap between clumps. It is intended to let other operations get in. Also, it somewhat controls the overloading of the replication stream. Increase delay if replication gets behind.

Deactivation


After the Alter is finished, the code automatically "deactivates" (by setting running=0) itself. No further writes are needed to the new table (which has been RENAMEd out of existence anyway).

However, the Layer is still checking Migrate, but this is no longer necessary. To clean up the code, you need another code push. This code push should be no more complicated than turning off a flag (a code flag, not the dynamic 'running' flag). The flag should have been built in when Alter is originally written. It will control whether to look at the Migrate table, or simply follow the "not running" path and skip the lock and unlock.

I recommend leaving these vestiges of the code in place; you are likely to do another Alter in the future. Meanwhile, the code will be sufficiently efficient. (A few "if false" statements are insignifant.)

Alternative - Log table


If the table to be Altered is a simple, append-only, "log", then the job can be done a lot simpler. There is no need for the Database Layer (though that might be best practice). The Migrate script can probably be run faster.

    ⚈  Migrate 1K-10K records per iteration, no extra locking needed.
    ⚈  Key being used must be the one for which new rows are sent to the 'end' of the table.
    ⚈  The final step that involves the RENAME gets tricky.

The only step that needs careful design (and I don't have the details ironed out...)

Plan A. When the Migrate script discovers that there are no rows left to move, it LOCKs the existing and new tables, does the RENAME, and UNLOCKs. (Could some INSERT slip in? Especially for InnoDB?)

Plan B. When the Migrate script discovers that there are no rows left to move, it does the RENAME, then checks for any extra rows that snuck in. If there are extra rows, it moves them. (What happens to any INSERTs that are trying to run when the RENAME occurs? Maybe they wait? Maybe they die? Does your code automatically retry in that case?)

Alternative - Dual Master


If you are set up with a pair of Masters replicating from each other (for quick recovery from a crash), then this is a much simpler approach. Or it is simpler for the developers -- it can be done entirely by the SEs.

The idea is to do some ALTER on offline server(s), failover, then do the rest of the ALTER(s).

Caveat: This only applies if the replication stream will not be confused by a Master having the 'old' schema, and the a Slave having the 'new' schema. (Note: RBR will probably be messed up by COLUMN changes, but not INDEX changes?) If any INSERT or UPDATE that would be confused, then you need to use the main approach so that you can have two flavors of statement and know which to apply to the existing versus the new tables.

Caution: Replication will need to be stopped or replication will be stuck, perhaps for as long as the ALTER takes. If the ALTER takes a week, you need enough disk space for a week's worth of binlogs and/or relay logs.

Caution: A really big table could take days to ALTER. There will be multiple (different machines), non-overlaping, ALTERs. Plan accordingly.

Plan A -- Allow the ALTER to replicate, but carefully staged: The idea is to run the ALTER on the Backup Master, and do the failover just when it is ready to start on the Live Master. You can spot that time by knowing when the ALTER finishes on the Backup Master. If the ALTER turns out to be "wrong", fail back to the original Live Master and lick your wounds. Now you have the equivalent of the Backup Master having crashed.

Plan B -- Manually do one machine at a time. This requires SET SESSION SQL_LOG_BIN=0; in front of the ALTER each time.
    1.  One at a time, take a Slave out of rotation and do the ALTER on it. Do multiple Slaves at a time, but not more than you can afford to have offline (Out Of Rotation) simultaneously.
    2.  Ditto for the Backup Master. Note: Any slaves connected to this server will become 'stale', so should be offline.
    3.  Failover to the Backup Master
    4.  ALTER what had been the Live Master. Again note that its slaves should be offline for the length of the ALTER.

Caveat: The Backup Master will be unavailable for failover during the ALTER. However, you can abort the ALTER if you need to failover.

Caveat: Any machine offline should probably have replication turned on (though stuck) so that the I/O thread is receiving updates. This should avoid having binlogs purged out from under you. However, it means that the receiving machine will have a pileup in the relay logs -- be sure to have enough disk space for such.

Postlog


Original writing -- Feb, 2011;   Refreshed: Sep, 2017
-- Rick James

MySQL Documents by Rick James

HowTo Techniques for Optimizing Tough Tasks:

Partition Maintenance (DROP+REORG) for time series (includes list of PARTITION uses)

Big DELETEs - how to optimize -- and other chunking advice, plus a use for PARTITIONing
    Chunking lengthy DELETE/UPDATE/etc.

Data Warehouse techniques:
    Data Warehouse Overview   Summary Tables   High speed ingestion   Bulk Normalization  

Schema and code design for large Sensor database

Entity-Attribute-Value (EAV) -- a common, poorly performing, design pattern; plus an alternative

5 methods for 'Find Nearest'

Lat/Lng search to Find the nearest 10 pizza parlors
    Lat/Long representation choices

Z-Order 'find nearest'

Pagination, not with OFFSET, LIMIT

Techniques on efficiently finding a random row (On beyond ORDER BY RAND())

GUID/UUID Performance (type 1 only)

IP Range Table Performance -- or other disjoint ranges

Rollup Unique User Counts

Alter of a Huge table -- Mostly obviated by 5.6

Efficient List of Latest 10 news articles

Build and execute a Pivot SELECT (showing rows as columns)

(Groupwise Max): Efficiently find largest row(s) for each group

Other Tips, Tuning, Debugging, Optimizations, etc...

Rick's RoTs (Rules of Thumb -- lots of tips)

Datatypes and building a good schema

Memory Allocation (caching, etc)

Character Set and Collation problem solver
    Trouble with UTF-8   If you want case folding, but accent sensitivity, please file a request at https://bugs.mysql.com .
    Python tips,   PHP tips,   other language tips
    utf8 Collations   utf8mb4 Collations on 8.0

Converting from MyISAM to InnoDB -- includes differences between them

Compound INDEXes plus other insights into the mysteries of INDEXing

Cookbook for Creating Indexes
    Many-to-many mapping table   Handler counts   wp_postmeta   UNION+OFFSET

MySQL Limits -- built-in hard limits
    767-byte INDEX limit

Galera, tips on converting to (Percona XtraDB Cluster, MariaDB 10, or manually installed)

5.7's Query Rewrite -- perhaps 5.7's best perf gain, at least for this forum's users

Analyze MySQL Performance
    Analyze VARIABLEs and GLOBAL STATUS     Analyze SlowLog

My slides from conferences
MiniFest 2021 - Rick James & Daniel Black - Answering on Stack Overflow(+comments) - MariaDB Frontlines
Percona Live 4/2017 - Rick's RoTs (Rules of Thumb) - MySQL/MariaDB
Percona Live 4/2017 - Index Cookbook - MySQL/MariaDB
Percona Live 9/2015 - PARTITIONing - MySQL/MariaDB

Contact me via LinkedIn; be sure to include a brief teaser in the Invite request:   View Rick James's profile on LinkedIn

Did my articles help you out? Like what you see? Consider donating:

☕️ Buy me a Banana Latte and bagel ($10) There is no obligation but it would put a utf8mb4 smiley 🙂 on my face, instead of the Mojibake "🙂"