昨年10月に Morgan Tocker氏よりcashapp/spirit についてのブログ記事が公開されました。
Spirit はALTER TABLEのようなSQLによる定義変更中もDMLの実行を妨げずにそれらを実施する方法を提供するツールで、そのような事を Online Schema Change の頭文字から OSC と呼ぶこともあります。
これまでもMySQLでOSCを行う方法としては、有名なものに以下がありました。
- InnoDBストレージエンジンの オンラインDDL
- pt-online-schema-change
- gh-ost
Hack MySQL (Efficient MySQL Performanceの著者のブログ)で紹介されていたりと、MySQL界隈でも注目度が高いと思われるSpiritについて検証していきたいと思います。
目次
Spiritの実行
すでに開発者のブログでも詳しい挙動についての解説がされておりますので、これまでのツールとの違いや機能についてご興味があればご一読いただければ幸いです。
※ ちなみに すでにとても詳しく内部的な挙動まで解説されているエンジニアの方もおり期待値の高さが伺えます…非常に参考にさせていただきました。
開発者の Morgan Tocker さんの言うように
Spirit is a reimplementation of the schema change tool gh-ost.
ということなので、基本的な動作は gh-ost と同じとなります。
- ALTERを実行するソースDBと(ラグを監視するなら)レプリカに接続
- ALTERを実行するテーブル(元テーブル)のコピー(コピーテーブル)を作成し、定義を変更
- spiritがレプリカのように振る舞いソースDBに接続、バイナリログを取得し自身のキューに入れる
- 3のキューから元テーブルに関するバイナリログイベントを定期的にコピーテーブルへ適用
- 4と並列に元テーブルからコピーテーブルへ
INSERT .. SELECTでデータをコピーしていく - コピーが完了したら 元テーブルとコピーテーブルを入れ替え、入れ替え後の元テーブルを削除
ではさっそく USAGE.md を見てインストールしていきたいと思います。
go.modより、Golangのバージョンは 1.21 を使用しているようでしたので、合わせて行きたいと思います。
検証用にOracle Cloud Infrastructureに以下の環境を構築しました。
| HOSTNAME | SHAPE | OCPU | MEMORY | OS | MYSQL VERSION | INFO |
|---|---|---|---|---|---|---|
| blog-mysql-primary | VM.Standard3.Flex | 2(=4core) | 32 GiB | Oracle Linux 9 | 8.0.36 | レプリケーションソースDB |
| blog-mysql-replica | VM.Standard3.Flex | 2(=4core) | 32 GiB | Oracle Linux 9 | 8.0.36 | レプリケーションレプリカDB |
まずは適当な環境でSpiritをbuildします
|
1 2 3 |
$ git clone https://github.com/cashapp/spirit.git $ podman pull docker.io/golang:1.21 $ podman run --rm -v ${PWD}/spirit:/go/src/spirit -w /go/src/spirit/cmd/spirit golang:1.21 go build |
バイナリをアップロードします
|
1 2 3 4 5 |
$ for h in blog-mysql-{primary,replica} do ssh $h -- mkdir -p bin scp spirit/cmd/spirit/spirit $h:~/bin done |
検証に使用するダミーデータはTPC Benchmark™H（TPC-H） ユーティリティを使い dbgen -s 10 で生成しました。
|
1 2 3 4 5 6 7 8 9 10 |
[opc@blog-mysql-primary dbgen]$ ls -lh data total 11G -rw-r--r-- 1 opc opc 234M Apr 9 08:23 customer.tbl -rw-r--r-- 1 opc opc 7.3G Apr 9 08:23 lineitem.tbl -rw-r--r-- 1 opc opc 2.2K Apr 9 08:23 nation.tbl -rw-r--r-- 1 opc opc 1.7G Apr 9 08:23 orders.tbl -rw-r--r-- 1 opc opc 1.2G Apr 9 08:23 partsupp.tbl -rw-r--r-- 1 opc opc 233M Apr 9 08:23 part.tbl -rw-r--r-- 1 opc opc 389 Apr 9 08:23 region.tbl -rw-r--r-- 1 opc opc 14M Apr 9 08:23 supplier.tbl |
今回検証で利用するLINEITEMのDDLは以下になります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
mysql> show create table tpch.lineitem\G *************************** 1. row *************************** Table: lineitem Create Table: CREATE TABLE lineitem ( l_orderkey int NOT NULL, l_partkey int NOT NULL, l_suppkey int NOT NULL, l_linenumber int NOT NULL, l_quantity decimal(15,2) NOT NULL, l_extendedprice decimal(15,2) NOT NULL, l_discount decimal(15,2) NOT NULL, l_tax decimal(15,2) NOT NULL, l_returnflag char(1) NOT NULL, l_linestatus char(1) NOT NULL, l_shipdate date NOT NULL, l_commitdate date NOT NULL, l_receiptdate date NOT NULL, l_shipinstruct char(25) NOT NULL, l_shipmode char(10) NOT NULL, l_comment varchar(44) NOT NULL, PRIMARY KEY (l_orderkey,l_linenumber) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
オプションは現時点では非常にシンプルです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[opc@blog-mysql-primary ~]$ spirit --help Usage: spirit Flags: -h, --help Show context-sensitive help. --host="127.0.0.1:3306" Hostname --username="msandbox" User --password="msandbox" Password --database="test" Database --table="stock" Table --alter="engine=innodb" The alter statement to run on the table --threads=4 Number of concurrent threads for copy and checksum tasks --target-chunk-time=500ms The target copy time for each chunk --force-inplace Force attempt to use inplace (only safe without replicas or with Aurora Global) --checksum Checksum new table before final cut-over --replica-dsn=STRING A DSN for a replica which (if specified) will be used for lag checking. --replica-max-lag=120s The maximum lag allowed on the replica before the migration throttles. --lock-wait-timeout=30s The DDL lock_wait_timeout required for checksum and cutover --skip-drop-after-cutover Keep old table after completing cutover --defer-cutover Defer cutover (and checksum) until sentinel table is dropped |
README に従いspiritを実行するユーザを作成します。
以下ではCREATE USER時にパスワードを省略していますが、環境に合わせてパスワードを指定してください。
|
1 2 3 4 5 6 7 8 |
mysql> CREATE USER spirit; mysql> GRANT ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE on tpch.* TO spirit; mysql> GRANT REPLICATION CLIENT, REPLICATION SLAVE on *.* TO spirit; mysql> GRANT SELECT on performance_schema.replication_applier_status_by_worker TO spirit; mysql> CREATE USER spirit@localhost; mysql> GRANT ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE on tpch.* TO spirit@localhost; mysql> GRANT REPLICATION CLIENT, REPLICATION SLAVE on *.* TO spirit@localhost; mysql> GRANT SELECT on performance_schema.replication_applier_status_by_worker TO spirit@localhost; |
では試しに実行してみます。
デフォルトでは、 --alter=engine=innodb となっているので InnoDB以外のテーブルに実行した場合は事故りますが InnoDBテーブルであれば特に定義は変更されずにテーブル全体を再構築するという動作になるはずです。
|
1 |
[opc@blog-mysql-primary ~]$ spirit --username=spirit --database=tpch --host=127.0.0.1:3306 --table=lineitem |
実行中に確認すると、INSERT IGNORE によるデータコピースレッドが複数と、ユーザが spirit になっている Binlog Dump スレッドが存在する事がわかります。
|
1 2 3 4 5 6 7 8 9 |
5 event_scheduler localhost NULL Daemon 86243 Waiting on empty queue NULL PRIMARY 13433 spirit localhost:56094 NULL Sleep 159 NULL PRIMARY 13434 spirit localhost:56102 NULL Binlog Dump 159 Source has sent all binlog to replica; waiting for more updates NULL PRIMARY 13438 spirit localhost:56126 tpch Query 0 executing INSERT IGNORE INTO <code>tpch</code>.<code>_lineitem_new</code> (<code>l_orderkey</code>, <code>l_partkey</code>, <code>l_suppkey</code>, <code>l_linenumber</code>, <code>l_quantity</code>, <code>l_extendedprice</code>, <code>l_discount</code>, <code>l_tax</code>, <code>l_returnflag</code>, <code>l_linestatus</code>, <code>l_shipdate</code>, <code>l_commitdate</code>, <code>l_rec eiptdate</code>, <code>l_shipinstruct</code>, <code>l_shipmode</code>, <code>l_comment</code>) SELECT <code>l_orderkey</code>, <code>l_partkey</code>, <code>l_suppkey</code>, <code>l_linenumber</code>, <code>l_quantity</code>, <code>l_extendedprice</code>, <code>l_discount</code>, <code>l_tax</code>, <code>l_returnflag</code>, <code>l_linestatus</code>, <code>l_shipdate</code>, <code>l_commitdate</code>, <code>l_receiptdate</code>, <code>l_shipinstruct</code>, <code>l_shipmode</code>, <code>l_commen t</code> FROM <code>tpch</code>.<code>lineitem</code> FORCE INDEX (PRIMARY) WHERE ((<code>l_orderkey</code> > 23278788)\n OR (<code>l_orderkey</code> = 23278788 AND <code>l_linenumber</code> >= 2)) AND ((<code>l_orderkey</code> < 23292448)\n OR (<code>l_orderkey</code> = 23292448 AND <code>l_linenumber</code> < 3)) PRIMARY 13476 spirit localhost:42526 tpch Query 0 executing INSERT IGNORE INTO <code>tpch</code>.<code>_lineitem_new</code> (<code>l_orderkey</code>, <code>l_partkey</code>, <code>l_suppkey</code>, <code>l_linenumber</code>, <code>l_quantity</code>, <code>l_extendedprice</code>, <code>l_discount</code>, <code>l_tax</code>, <code>l_returnflag</code>, <code>l_linestatus</code>, <code>l_shipdate</code>, <code>l_commitdate</code>, <code>l_rec eiptdate</code>, <code>l_shipinstruct</code>, <code>l_shipmode</code>, <code>l_comment</code>) SELECT <code>l_orderkey</code>, <code>l_partkey</code>, <code>l_suppkey</code>, <code>l_linenumber</code>, <code>l_quantity</code>, <code>l_extendedprice</code>, <code>l_discount</code>, <code>l_tax</code>, <code>l_returnflag</code>, <code>l_linestatus</code>, <code>l_shipdate</code>, <code>l_commitdate</code>, <code>l_receiptdate</code>, <code>l_shipinstruct</code>, <code>l_shipmode</code>, <code>l_commen t</code> FROM <code>tpch</code>.<code>lineitem</code> FORCE INDEX (PRIMARY) WHERE ((<code>l_orderkey</code> > 23265125)\n OR (<code>l_orderkey</code> = 23265125 AND <code>l_linenumber</code> >= 2)) AND ((<code>l_orderkey</code> < 23278788)\n OR (<code>l_orderkey</code> = 23278788 AND <code>l_linenumber</code> < 2)) PRIMARY 2253 repl blog-mysql-replica:51462 NULL Binlog Dump GTID 22542 Source has sent all binlog to replica; waiting for more updates NULL PRIMARY 13497 spirit localhost:56870 tpch Query 0 executing INSERT IGNORE INTO <code>tpch</code>.<code>_lineitem_new</code> (<code>l_orderkey</code>, <code>l_partkey</code>, <code>l_suppkey</code>, <code>l_linenumber</code>, <code>l_quantity</code>, <code>l_extendedprice</code>, <code>l_discount</code>, <code>l_tax</code>, <code>l_returnflag</code>, <code>l_linestatus</code>, <code>l_shipdate</code>, <code>l_commitdate</code>, <code>l_rec eiptdate</code>, <code>l_shipinstruct</code>, <code>l_shipmode</code>, <code>l_comment</code>) SELECT <code>l_orderkey</code>, <code>l_partkey</code>, <code>l_suppkey</code>, <code>l_linenumber</code>, <code>l_quantity</code>, <code>l_extendedprice</code>, <code>l_discount</code>, <code>l_tax</code>, <code>l_returnflag</code>, <code>l_linestatus</code>, <code>l_shipdate</code>, <code>l_commitdate</code>, <code>l_receiptdate</code>, <code>l_shipinstruct</code>, <code>l_shipmode</code>, <code>l_commen t</code> FROM <code>tpch</code>.<code>lineitem</code> FORCE INDEX (PRIMARY) WHERE ((<code>l_orderkey</code> > 23251426)\n OR (<code>l_orderkey</code> = 23251426 AND <code>l_linenumber</code> >= 2)) AND ((<code>l_orderkey</code> < 23265125)\n OR (<code>l_orderkey</code> = 23265125 AND <code>l_linenumber</code> < 2)) PRIMARY 13524 spirit localhost:45304 tpch Query 0 waiting for handler commit COMMIT PRIMARY 13529 root localhost NULL Query 0 executing select * from performance_schema.processlist PRIMARY |
実行ログも割と親切で migration status: という列には、現在の処理行数/％、実行時間が表示されているのでいつ終わるのか見積もりが簡単にできそうです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
INFO[0000] Starting spirit migration: concurrency=4 target-chunk-size=500ms table=tpch.lineitem alter="engine=innodb" INFO[0000] unable to use INPLACE: ALTER either does not support INPLACE or when performed as INPLACE could take considerable time. Use --force-inplace to override this safety check INFO[0000] could not resume from checkpoint: reason=could not read from table '_lineitem_new' INFO[0000] create BinlogSyncer with config {ServerID:1104 Flavor:mysql Host:127.0.0.1 Port:3306 User:spirit Password: Localhost: Charset:utf8 SemiSyncEnabled:false RawModeEnabled:false TLSConfig:<nil> ParseTime:false TimestampStringLocation:UTC UseDecimal:false RecvBufferSize:0 HeartbeatPeriod:0s ReadTimeout:0s MaxReconnectAttempts:0 DisableRetrySync:false VerifyChecksum:false DumpCommandFlag:0 Option:<nil> Logger:0xc000048d50 Dialer:0x76d120 RowsEventDecodeFunc:0x9a2980 TableMapOptionalMetaDecodeFunc:<nil> DiscardGTIDSet:false EventCacheCount:10240} INFO[0000] skip dump, use last binlog replication pos (binlog.000059, 3303) or GTID set <nil> INFO[0000] begin to sync binlog from position (binlog.000059, 3303) INFO[0000] start sync binlog at binlog file (binlog.000059, 3303) INFO[0030] migration status: state=copyRows copy-progress=4529335/59461561 7.62% binlog-deltas=0 total-time=30s copier-time=30s copier-remaining-time=TBD copier-is-throttled=false conns-in-use=5 INFO[0030] finished periodic flush of binary log: total-duration=908.761µs batch-size=1000 INFO[0050] checkpoint: low-watermark={"Key":["l_orderkey","l_linenumber"],"ChunkSize":10727,"LowerBound":{"Value": ["7442374","3"],"Inclusive":true},"UpperBound":{"Value": ["7452930","2"],"Inclusive":false}} log-file=binlog.000059 log-pos=3303 rows-copied=7451955 rows-copied-logical=7451282 INFO[0060] migration status: state=copyRows copy-progress=8878945/59461561 14.93% binlog-deltas=0 total-time=1m0s copier-time=1m0s copier-remaining-time=5m54s copier-is-throttled=false conns-in-use=5 INFO[0060] finished periodic flush of binary log: total-duration=917.721µs batch-size=1000 : INFO[0412] copy rows complete INFO[0412] starting to flush changeset INFO[0412] blocking until we have read all binary logs: current-pos=(binlog.000064, 938254073) target-pos=(binlog.000064, 938253854) INFO[0412] Running ANALYZE TABLE INFO[0412] starting to flush changeset INFO[0412] blocking until we have read all binary logs: current-pos=(binlog.000064, 938254475) target-pos=(binlog.000064, 938254256) INFO[0412] starting checksum operation, this will require a table lock WARN[0412] trying to acquire table lock, timeout: 30 WARN[0412] table lock acquired WARN[0412] table lock released INFO[0412] table unlocked, starting checksum INFO[0420] migration status: state=checksum checksum-progress=3001573/60000000 binlog-deltas=0 total-time=7m0s checksum-time=8s conns-in-use=5 INFO[0420] finished periodic flush of binary log: total-duration=940.708µs batch-size=1000 : INFO[0550] checkpoint: low-watermark={"Key":["l_orderkey","l_linenumber"],"ChunkSize":9849,"LowerBound":{"Value": ["59984900","1"],"Inclusive":true},"UpperBound":{"Value": ["59994596","3"],"Inclusive":false}} log-file=binlog.000059 log-pos=3303 rows-copied=59986052 rows-copied-logical=59986395 INFO[0560] checksum passed INFO[0560] starting to flush changeset INFO[0560] blocking until we have read all binary logs: current-pos=(binlog.000064, 938256269) target-pos=(binlog.000064, 938256050) INFO[0560] starting to flush changeset INFO[0560] blocking until we have read all binary logs: current-pos=(binlog.000064, 938256704) target-pos=(binlog.000064, 938256485) WARN[0560] Attempting final cut over operation (attempt 1/5) WARN[0560] trying to acquire table lock, timeout: 30 WARN[0560] table lock acquired WARN[0560] table lock released WARN[0560] final cut over operation complete INFO[0566] successfully dropped old table INFO[0566] apply complete: instant-ddl=false inplace-ddl=false total-chunks=4099 copy-rows-time=6m52s checksum-time=2m28s total-time=9m26s conns-in-use=0 INFO[0566] closing canal INFO[0566] syncer is closing... INFO[0566] kill last connection id 13434 INFO[0566] syncer is closed |
データコピーフェーズが完了すると、元テーブルから取得した際のデータのチェックサムと、コピーテーブル上のデータのチェックサムを突合するというのがデフォルトの動作のようですので、データの信頼性もバッチリです。
今回は spirit のインストール～使用までということで行ってみました。
次回はいくつかの実行パターンやベンチマークを試して行きたいと思います。
Let’s Enjoy New Toys!!
