LoginSignup
86
82

More than 3 years have passed since last update.

@doriven

MySQL オンラインDDL(ロックなしによるサービス無停止でDDLを流す方法) パターン集

Last updated at Posted at 2019-03-12

職場でオンラインDDLのパターンをより勉強したほうがよいと感じたのでインプットしたことをQiitaにアウトプットしてみた。

以下のリポジトリで実践も合わせて行った。

記事の中で出てくるコードなどはこちらで参照することが可能。
https://github.com/d0riven/learning_online_ddl_pattern

今回はMySQLというDBMSに限定してパターンを紹介しているが、他のDBMSでも使えるパターンはいくつかあるはずなのでPostgreSQLやOracleを利用している人が読んでもためになるとは思う。

想定読者

  • MySQLを普段から触っている人
  • 可用性の高さを求められるサービスを運用・開発している人
  • 古くからあるシステムのリファクタリングのためにデータベースの足回りから手をつけていきたい人
  • オンラインDDLのやり方を知らない人・知っていても色々なパターンがあることを知らない人

オンラインDDLとは？

タイトルにも書いてあるがテーブルのロックを取らないのでDDL(CREATE,ALTER,DROP,TRUNCATE文)を本番環境のDBに流すときにもそのDBに対して変更系のSQLを流し込むサービスがロック待ちで停止することがなくなる。

この記事ではMySQL上でオンラインDDLを実現するためのいくつかのパターンを紹介する。

MySQL公式の標準機能を用いるパターン

INPLACE(MySQL5.6以上)

いくつかの条件はあるが、ロックを取得せずにALTER TABLEを流すことが可能。

公式のドキュメントを参考にするのが確実だが、以下のパターン以外ならオンラインDDLが可能。

  • カラムのデータ型の変更
  • 主キーの削除(削除と合わせて主キーの追加 = 主キーの入れ替えはしない)
  • テーブルの文字セットの変更

Pros・Cons

  • Pros
    • MySQLの標準機能なので特に準備がいらない
  • Cons
    • 5.5以前に作成されたテーブルで使えない
    • カラムの追加・削除などのよくあるDDLはテーブルコピーが裏で走るのでDBへの負荷が高い

INPLACEが当該テーブルで使えるかの確認方法

以下のようにDDLに ALGORITHM=INPLACE, LOCK=NONE を追加することでオンラインDDLが適用できない場合はエラーメッセージを出してくれる。

オンラインDDLでALTERを適用したい、という意図を持ってクエリを実行するときは付けるようにしたほうが無難。

ALTER TABLE dummy ADD COLUMN added_col INT NULL, ALGORITHM=INPLACE, LOCK=NONE;

実演

golangのコード上で以下のDDLを流しつつ、オンラインDDLの当該テーブル dummy に対してINSERT文を実行してロックがかかっていないことを確認した。
一瞬でDDLが適用されてしまうと検証にならないので、事前に対象テーブルに対して 100万行近いデータをインサートしている。(以降のパターンでも同様)

気になる人は詳細は https://github.com/d0riven/learning_online_ddl_pattern/tree/master/standard/inplace を見てほしい。

package main

import (
"fmt"
"os"
"strconv"
"time"

_ "github.com/go-sql-driver/mysql"
"github.com/google/uuid"
"github.com/jmoiron/sqlx"
"github.com/pkg/errors"
)

func main() {
    user := os.Getenv("USER")
    pass := os.Getenv("PASS")
    host := os.Getenv("HOST")
    port := os.Getenv("PORT")
    dbName := os.Getenv("DB_NAME")
    intervalMsRaw := os.Getenv("INSERT_INTERVAL_MS")

    if user == "" || pass == "" || host == "" || port == "" || dbName == "" || intervalMsRaw == "" {
        panic(fmt.Errorf("invalid environment variables. expected MySQL's [USER, PASS, HOST, PORT, DB_NAME] and INSERT_INTERVAL_MS: %+v\n", map[string]string{
            "user" : user,
            "pass" : "##masked##",
            "host" : dbName,
            "port" : port,
            "dbName" : dbName,
            "intervalMsRaw" : intervalMsRaw,
        }))
    }

    db, err := sqlx.Connect("mysql", fmt.Sprintf(
        "%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Asia%%2FTokyo",
        user, pass, host, port, dbName))
    if err != nil {
        panic(errors.Wrap(err, "connection failed"))
    }

    v, err := strconv.Atoi(intervalMsRaw)
    if err != nil {
        panic(errors.Wrapf(err, "unexpected insert interval argument: %s", intervalMsRaw))
    }
    insertIntervalMs := v

    done := make(chan struct{})
    t := time.Now()

    go func(done chan struct{}) {
        addIndexSql := `ALTER TABLE dummy ADD INDEX contents_idx(contents), ALGORITHM=INPLACE, LOCK=NONE`
        fmt.Printf("start add index sql = `%s`\n", addIndexSql)
        db.MustExec(addIndexSql)
        fmt.Printf("\ncomplete add index.\n")

        dropIndexSql := `DROP INDEX contents_idx ON dummy LOCK=NONE`
        fmt.Printf("start drop index sql = `%s`\n", dropIndexSql)
        db.MustExec(dropIndexSql)
        fmt.Printf("\ncomplete drop index.\n")

        addColumnSql := `ALTER TABLE dummy ADD COLUMN added_col INT NULL, ALGORITHM=INPLACE, LOCK=NONE`
        fmt.Printf("start add column sql = `%s`\n", addColumnSql)
        db.MustExec(addColumnSql)
        fmt.Printf("\ncomplete add column.\n")

        dropColumnSql := `ALTER TABLE dummy DROP COLUMN added_col, ALGORITHM=INPLACE, LOCK=NONE`
        fmt.Printf("start drop column sql = `%s`\n", dropColumnSql)
        db.MustExec(dropColumnSql)
        fmt.Printf("\ncomplete drop column.\n")
        close(done)
    }(done)

    go func(done chan struct{}) {
        fmt.Println("Start DDL with INSERT ( '>' is INSERTED COUNTER )")
        for {
            time.Sleep(time.Duration(insertIntervalMs) * time.Millisecond)
            select {
            case _, ok := <-done:
                if !ok {
                    return
                }
            default:
        go insert(db)
            }
        }
    }(done)
    <-done
    fmt.Printf("spend time %d second\n", time.Now().Sub(t)/time.Second)
}

func insert(db *sqlx.DB) {
  fmt.Print(">")
  db.MustExec(`INSERT INTO dummy (contents) VALUES (?)`, uuid.New().String())
}

以下、実行結果を見てみるとDDLを流しているにも関わらずINSERTが出来ていることがわかる。

⟩ make test_online_ddl
: ====================================================
Start DDL with INSERT ( '>' is INSERTED COUNTER )
start add index sql = `ALTER TABLE dummy ADD INDEX contents_idx(contents), ALGORITHM=INPLACE, LOCK=NONE`
>>>>>>>>>>>>
complete add index.
start drop index sql = `DROP INDEX contents_idx ON dummy LOCK=NONE`

complete drop index.
start add column sql = `ALTER TABLE dummy ADD COLUMN added_col INT NULL, ALGORITHM=INPLACE, LOCK=NONE`
>>>>>>>>>>>
complete add column.
start drop column sql = `ALTER TABLE dummy DROP COLUMN added_col, ALGORITHM=INPLACE, LOCK=NONE`
>>>>>>>>>>
complete drop column.
spend time 33 second
: ====================================================

INSTANT(MySQL8.0.12以上)を使う

MySQL8.0.12からINSTANTが使えるようになっている。

これはテーブルのメタデータのみ更新を行うので高速に実行することが可能。

ただし、INPLACEと比較して以下のように適用できるDDLのパターンが更に絞られている点に注意。

  • インデックスオプションの変更
  • テーブル名の変更
  • SET/DROP DEFAULT
  • MODFY COLUMN (手元でいくつか実行してみたがこれも結構制約がありそう)
    • NULL => NOT NULLはDEFAULT句をつけても不可能
    • 異なる型には変更できない (INT => BIGINTとか)
  • 仮想行の追加、削除
  • カラムの追加
    • テーブルの最後の列以外に追加できない（ALTER TABLE ADD COLUMN ... AFTER|BEFORE ... みたいなことはできない)
    • 一時テーブルや全文検索インデックスを含むテーブルには適用できない
    • etc

Pros・Cons

  • Pros
    • INPLACEと比べて高速
    • ADD COLUMNでもコピーが走らないので低負荷
    • INPLACEでは不可能だったMODIFY COLUMNもオンラインDDL可能
  • Cons
    • MySQL8.0.12以上でないと実行できない
    • 使えるケースが限られている

INSTANTが当該テーブルで使えるかの確認方法

ALGORITHM=INSTANT を末尾につければ良い。

ちなみにINPLACEのように LOCK=NONE とかやると怒られるのでつける必要はない。1 

ALTER TABLE dummy ADD COLUMN added_col INT NULL, ALGORITHM=INSTANT

実演

基本DDLを流す部分以外は一緒なので //... で省略した。

//...

func main() {
  //...

    go func(done chan struct{}) {
        addColumnSql := `ALTER TABLE dummy ADD COLUMN added_col INT NULL, ALGORITHM=INSTANT`
        fmt.Printf("start add column sql = `%s`\n", addColumnSql)
        db.MustExec(addColumnSql)
        fmt.Printf("\ncomplete add column.\n")

        addColumnWithDefaultSql := `ALTER TABLE dummy ADD COLUMN added_col_with_default INT NULL DEFAULT 1, ALGORITHM=INSTANT`
        fmt.Printf("start add column sql = `%s`\n", addColumnWithDefaultSql)
        db.MustExec(addColumnWithDefaultSql)
        fmt.Printf("\ncomplete add column with default.\n")

        addColumnSqlBeforeModify := `ALTER TABLE dummy ADD COLUMN modify_col ENUM('a', 'b', 'c'), ALGORITHM=INSTANT`
        fmt.Printf("start add column to modify sql = `%s`\n", addColumnSqlBeforeModify)
        db.MustExec(addColumnSqlBeforeModify)
        fmt.Printf("\ncomplete add column to modify.\n")

        modifyColumnWithDefaultSql := `ALTER TABLE dummy MODIFY COLUMN modify_col ENUM('a', 'b', 'c', 'd', 'e'), ALGORITHM=INSTANT`
        fmt.Printf("start modify column sql = `%s`\n", modifyColumnWithDefaultSql)
        db.MustExec(modifyColumnWithDefaultSql)
        fmt.Printf("\ncomplete modify column with default.\n")

        close(done)
    }(done)

  //...
}

INPLACEと比較しても相当高速にDDLを実行していることがわかる。

⟩ make test_online_ddl
go build main.go
: ====================================================
Start DDL with INSERT ( '>' is INSERTED COUNTER )
start add column sql = `ALTER TABLE dummy ADD COLUMN added_col INT NULL, ALGORITHM=INSTANT`
>
complete add column.
start add column sql = `ALTER TABLE dummy ADD COLUMN added_col_with_default INT NULL DEFAULT 1, ALGORITHM=INSTANT`
>
complete add column with default.
start add column to modify sql = `ALTER TABLE dummy ADD COLUMN modify_col ENUM('a', 'b', 'c'), ALGORITHM=INSTANT`
>>
complete add column to modify.
start modify column sql = `ALTER TABLE dummy MODIFY COLUMN modify_col ENUM('a', 'b', 'c', 'd', 'e'), ALGORITHM=INSTANT`
>
complete modify column with default.
spend time 0.30 second
: ====================================================

蛇足

INSTANTと似た機能がAWS RDS Auroraでも提供されている。

FastDDLという機能でINSTANTよりも更に限定的だが、こちらはAuroraのバージョンさえ満たしていれば利用可能。

  • 高速 DDL は、デフォルト値を持たない、null が許容される列を、既存の表の末尾に追加する場合にのみ使用できます。
  • 高速 DDL は、パーティション分割されたテーブルをサポートしません。
  • 高速 DDL は、REDUNDANT 列形式を使用する DB テーブルをサポートしません。
  • DDL オペレーションの最大有効レコードサイズが大きすぎる場合、高速 DDL は使用されません。レコードサイズがページサイズの半分を占める場合は大きすぎます。レコードの最大サイズは、すべての列の最大サイズを追加して計算されます。サイズを変更可能な列の場合は、InnoDB 標準に基づき、extern byte は計算に含まれません

レプリケーションを使う

MySQLのレプリケーション機能を利用してオンラインDDLを使うパターン。

手順

参考サイトの一覧に含まれているZero Downtime Maintenances on MySQL RDSをもとに解説していく。

  1. DDL対象のテーブルを保持しているDB(M1)のスナップショット用レプリカ(R1)を立てる
  2. スナップショット用レプリカ(R1)からスナップショットを作成
  3. スナップショットからインスタンスを作成し、M2インスタンスを作成。
    • この後で行う挿入先の切り替え処理のときに 主キーの衝突が起きないようにauto_increment_increment, auto_increment_offsetの値を変更しておく必要がある
  4. M1 => M2 へのレプリカ設定を行う
  5. アプリケーションサーバのトラフィックをM2へ向ける

Pros・Cons

  • Pros
    • MySQL5.5以前でもオンラインDDLが利用が可能
    • INPLACEでデータがコピーされて負荷が高めのケースにも負荷を抑えることが可能
  • Cons
    • INPLACEやINSTANTに比べて手順が煩雑で難易度が高め(ミスするとレプリケーションがコケて一時的にデータ欠損が発生する)
    • AUTO_ICMREMENTによる採番で歯抜けが発生するようになるので、歯抜けが一時的にでも許容されない場合は使えない
    • レプリカラグが大きく、それを許容できないような環境では使えない

実演

DDL完了後に挿入先の切り替えをする処理を行っている点に注意

レプリカ設定

⟩ tail master/conf.d/repl.conf
[mysqld]
server_id=1
log_bin=/var/log/mysql/bin.log

gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=on

auto_increment_increment=2
auto_increment_offset=1

⟩ tail slave/conf.d/repl.conf
[mysqld]
server_id=2
log_bin=/var/log/mysql/bin.log

gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=on

auto_increment_increment=2
auto_increment_offset=2

コード

// ...

var insertDB *sqlx.DB

func main() {
  // ...

    go func(done chan struct{}) {
        addColumnSql := `ALTER TABLE dummy ADD COLUMN added_col INT NULL, ALGORITHM=COPY, LOCK=SHARED`
        fmt.Printf("start add column sql = `%s`\n", addColumnSql)
        slaveDB.MustExec(addColumnSql)
        fmt.Printf("\ncomplete add column.\n")

        // DDLが完了したら接続先を新しいテーブルへ向ける
        // 実際の業務などでデプロイなどで接続情報を更新したタイミングでこれと同じような挙動をするはず
        switchConnection(slaveDB)

        // 切り替え後もしばらく挿入処理が入った前提で10秒間sleepをかけてみる
        time.Sleep(10 * time.Second)

        close(done)
    }(done)

    go func(done chan struct{}) {
        insertDB = masterDB
        fmt.Println("Start DDL with INSERT ( '>' is INSERTED COUNTER )")
        for {
            time.Sleep(time.Duration(insertIntervalMs) * time.Millisecond)
            select {
            case _, ok := <-done:
                if !ok {
                    return
                }
            default:
                go insert(insertDB)
            }
        }
    }(done)
}

func insert(db *sqlx.DB) {
    fmt.Print(">")
    db.MustExec(`INSERT INTO dummy (contents) VALUES (?)`, uuid.New().String())
}

func switchConnection(db *sqlx.DB) {
    insertDB = db
}

実行結果

⟩ make test_online_ddl
go build main.go
: ====================================================
Start DDL with INSERT ( '>' is INSERTED COUNTER )
start add column sql = `ALTER TABLE dummy ADD COLUMN added_col INT NULL, ALGORITHM=COPY, LOCK=SHARED`
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
complete add column.
>>>>>>>>>>>>>>>>>>>>
spend time 31.00 second
: ====================================================

データの状況

masterのデータが歯抜けになっていることがわかる

master

mysql> select * from dummy order by id desc limit 10;
+---------+--------------------------------------+
| id      | contents                             |
+---------+--------------------------------------+
| 2621467 | b3139813-06e9-4c86-a29a-7d005b85858b |
| 2621465 | 03d56a4e-345b-4459-8125-bf089fc3443d |
| 2621463 | 48f68054-b3c0-46e4-bef5-8b15b5503b8d |
| 2621461 | 66cd6423-4114-4083-8e6a-61d81e315eb0 |
| 2621459 | 7f519697-baa3-46a0-8676-2f4d7a28c41f |
| 2621457 | 2aad003e-882e-415f-a51f-3a4fb673829e |
| 2621455 | bb807058-904d-4e42-bbbb-fa4e9595fb4e |
| 2621453 | 2e00b834-8d24-44f6-b20f-3c6305a62968 |
| 2621451 | 2abd6570-31e5-4e74-ba79-857b48df6835 |
| 2621449 | 9d956b87-e435-43af-a262-cbc1fe95f5b4 |
+---------+--------------------------------------+
10 rows in set (0.00 sec)

slave(DDL対象のDB)

mysql> mysql> select * from dummy order by id desc limit 10;
+---------+--------------------------------------+-----------+
| id      | contents                             | added_col |
+---------+--------------------------------------+-----------+
| 2621468 | 120f812e-4600-4d77-9c8f-42252c75ef46 |      NULL |
| 2621467 | b3139813-06e9-4c86-a29a-7d005b85858b |      NULL |
| 2621466 | b43d6e9e-03ba-44df-990c-ed2814b06cbc |      NULL |
| 2621465 | 03d56a4e-345b-4459-8125-bf089fc3443d |      NULL |
| 2621464 | 362241e0-da6b-4a7e-bccc-60442e6025ec |      NULL |
| 2621463 | 48f68054-b3c0-46e4-bef5-8b15b5503b8d |      NULL |
| 2621462 | 57a8d16b-9fed-4a6d-9337-0f91817d2c97 |      NULL |
| 2621461 | 66cd6423-4114-4083-8e6a-61d81e315eb0 |      NULL |
| 2621460 | 127280b4-3407-4ae7-b782-85502f8e6839 |      NULL |
| 2621459 | 7f519697-baa3-46a0-8676-2f4d7a28c41f |      NULL |
+---------+--------------------------------------+-----------+
10 rows in set (0.01 sec)

Copy・Rename戦略

スキーマ適応後の形と同じ別テーブルを用意して、そのテーブルに対してデータをコピーしてテーブルのデータが完全に一致したタイミングでRENAME TABLEを使って切り替えるパターン

ツール(pt-online-schema-change, gh-ost)を利用する

ツールの使い方の説明になってしまうので今回はここでは触れないが、機会があれば別な記事にでも書きたい。

自前実装

実際にどういう挙動になっているのかを理解するために多少実装をサボったが自前実装してみた。

MySQLでサービス停止のないALTER TABLEの検討を参考にこちらを実装しているので、どういう挙動をするかの解説はそちらを見ると図解付きでわかりやすく解説してくれている。

手順

  1. 新しい構造のテーブル(以降、新テーブルと呼称)を作成
  2. 本来DDL適用される対象になる古い構造のテーブル(以降、旧テーブルと呼称)にUPDATE, DELETE, INSERTで新テーブルにデータコピーするTRIGGERを作成
    • これによって旧テーブルへの追加・変更・削除はすべて新テーブルに反映されるようになる
  3. 旧テーブルが以前から持っている行を新テーブルにコピーしていく
  4. 旧テーブルで削除されたものが、同時に3のコピーによって復活してしまう行があるのでそれらの行を削除する
  5. 1~4を通してデータが完全に一致したタイミングでRENAME TABLEを行い、アプリケーションサーバの参照先を新テーブルに向ける

Pros・Cons

  • Pros
    • どのMySQLバージョンでも適用可能
    • 他でロックを取るようなパターンでもロックを取らない
    • 負荷が高まった場合でも任意のタイミングで一時停止が可能
  • Cons
    • データコピーが入り、かつMySQLのDDLのアルゴリズムを使用している訳でもないのでDBの負荷は相応に高くなる
    • 手順が複雑（これはオンラインDDLのツールを使えば楽になる）
    • コピー部分はアプリケーションが管理しているので、データの完全性を担保するのが大変（バックアップと確認は行ったほうが良い）

実演

//...

func main() {
  //...

    go func(done chan struct{}) {
        createTableSql := `CREATE TABLE dummy_new LIKE dummy`
        addColumnSql := `ALTER TABLE dummy_new ADD COLUMN added_col INT`
        fmt.Printf("start create table. sql = `%s`\n", strings.Join([]string{createTableSql, addColumnSql}, ";"))
        db.MustExec(createTableSql)
        db.MustExec(addColumnSql)
        fmt.Printf("\ncomplete create table.\n")

        insertTriggerSql := `
CREATE TRIGGER insert_new AFTER INSERT ON dummy FOR EACH ROW
BEGIN
  REPLACE INTO dummy_new (id, contents) VALUES (NEW.id, NEW.contents);
END`
        fmt.Printf("start create insert trigger. sql = `%s`\n", insertTriggerSql)
        db.MustExec(insertTriggerSql)
        fmt.Printf("\ncomplete create insert trigger.\n")

        updateTriggerSql := `
CREATE TRIGGER update_new AFTER UPDATE ON dummy FOR EACH ROW
BEGIN
  DELETE FROM dummy_new WHERE id = OLD.id;
  REPLACE INTO dummy_new (id, contents) VALUES (NEW.id, NEW.contents);
END`
        fmt.Printf("start create update trigger. sql = `%s`\n", updateTriggerSql)
        db.MustExec(updateTriggerSql)
        fmt.Printf("\ncomplete create update trigger.\n")

        deleteTriggerSql := `
CREATE TRIGGER delete_new AFTER DELETE ON dummy FOR EACH ROW
BEGIN
  DELETE FROM dummy_new WHERE id = OLD.id;
END`
        fmt.Printf("start create delete trigger. sql = `%s`\n", deleteTriggerSql)
        db.MustExec(deleteTriggerSql)
        fmt.Printf("\ncomplete create delete trigger.\n")

        fmt.Printf("start copy old rows to new.\n")
        err := copyOldRows(db)
        if err != nil {
            panic(errors.Wrap(err, "failed to copy old rows"))
        }
        fmt.Printf("\ncomplete copy old rows.\n")

        // 古い行のコピー(INSERT IGNORE INTO)だけやれば良いように見えてしまうのであえてスリープを入れてTRIGGERの存在意義を出す
        time.Sleep(5 * time.Second)

        renameTableSql := `RENAME TABLE dummy TO dummy_old, dummy_new TO dummy`
        fmt.Printf("start rename table. sql = `%s`\n", renameTableSql)
        db.MustExec(renameTableSql)
        fmt.Printf("\ncomplete rename table.\n")

        close(done)
    }(done)

  //...
}

func insert(db *sqlx.DB) {
    fmt.Print(">")
    db.MustExec(`INSERT INTO dummy (contents) VALUES (?)`, uuid.New().String())
}

func copyOldRows(db *sqlx.DB) error {
    const copyRowsN = 1000
    var max int

    err := db.QueryRow(`SELECT MAX(id) FROM dummy`).Scan(&max)
    if err != nil {
        return errors.Wrap(err, "failed to fetch count of dummy")
    }

    for i := 1; i <= max; {
        var next int
        var begin int
        err := db.QueryRow(`SELECT MIN(id), MAX(id) + 1 FROM (SELECT id FROM dummy WHERE id >= ? ORDER BY id LIMIT ?) AS ids`, i, copyRowsN).Scan(&begin, &next)
        if err != nil {
            panic(err)
        }
        db.MustExec(`INSERT IGNORE INTO dummy_new (id, contents) SELECT id, contents FROM dummy WHERE id BETWEEN ? AND ? ORDER BY id LIMIT ?`, begin, max, copyRowsN)
        i = next

        time.Sleep(100 * time.Millisecond)
    }
    return nil
}

100万行近いデータもローカルのDocker環境でさえ160秒で完了しているのがわかる。

⟩ make test_online_ddl
go build main.go
: ====================================================
Start DDL with INSERT ( '>' is INSERTED COUNTER )
start create table. sql = `CREATE TABLE dummy_new LIKE dummy;ALTER TABLE dummy_new ADD COLUMN added_col INT`

complete create table.
start create insert trigger. sql = `
CREATE TRIGGER insert_new AFTER INSERT ON dummy FOR EACH ROW
BEGIN
  REPLACE INTO dummy_new (id, contents) VALUES (NEW.id, NEW.contents);
END`

complete create insert trigger.
start create update trigger. sql = `
CREATE TRIGGER update_new AFTER UPDATE ON dummy FOR EACH ROW
BEGIN
  DELETE FROM dummy_new WHERE id = OLD.id;
  REPLACE INTO dummy_new (id, contents) VALUES (NEW.id, NEW.contents);
END`

complete create update trigger.
start create delete trigger. sql = `
CREATE TRIGGER delete_new AFTER DELETE ON dummy FOR EACH ROW
BEGIN
  DELETE FROM dummy_new WHERE id = OLD.id;
END`

complete create delete trigger.
start copy old rows to new.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
complete copy old rows.
>>>>>>>>>start rename table. sql = `RENAME TABLE dummy TO dummy_old, dummy_new TO dummy`
>
complete rename table.
spend time 162.20 second
: ====================================================

オンラインDDLの注意点

  • テーブルに対するロックは取らないが、メタデータのロックを取得する必要がある（取得自体は一瞬）
  • メタデータのロックはトランザクションが張られている場合には取得できないため、DDL以降に発行された対象テーブルへのすべてのSQLはWaiting for table metadata lockでロック待ちの状態になる
  • つまり長時間のトランザクションを保持するようなテーブルに対してDDLをかけると、トランザクションが閉じるまでの間にSQLが大量に溜まって死ぬ可能性がある
  • 開発環境で再現させるのは難しいと思うので、本番で試すときには必ず SHOW PROCESSLIST を使ってメタデータロック待ちが起きてないかをチェックしたほうが無難

最後に

今回は思考の整理のためにアウトプットしてみたが、使っていないようなパターンについても紹介をしているので何かしら間違っていたら指摘をしてもらえると助かる。

参考サイト

  1. ちょっとここの理解が曖昧で実はINSTANTでもロックが掛かるパターンがあるかもしれない ↩

86
82
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
86
82