GA

2019/04/26

MySQL 8.0.16にCHECK制約が来て、NOT ENFORCEDなんてものまでついてきた

TL;DR

  • MySQL 8.0.16 についに CHECK制約 が実装された
  • NOT ENFORCED なんてオプションが “MySQLっぽい”
  • ちなみにCHECK制約の追加はオンラインDDL不可、引っぺがすのはできる

最初のFeature Requestから15年、ついにMySQL 8.0.16にCHECK制約が実装された。
アルファベットしか入ってほしくない val カラムに対して、ひらがなの “さん” とかが入力されるのを制限できる、みたいなヤーツ。
フツーの遊び方は @taka_yuki_04 さんの記事に書いてある(というかドキュメントにも書いてある)ので、俺は「既存のテーブルにCHECK制約」ででも遊んでみる。
まず、こんなテーブルがあるじゃろ?
mysql80 8> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.16    |
+-----------+
1 row in set (0.00 sec)

mysql80 8> CREATE TABLE t1 (num serial, val varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql80 8> INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'さん');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql80 8> SELECT * FROM t1;
+-----+--------+
| num | val    |
+-----+--------+
|   1 | one    |
|   2 | two    |
|   3 | さん   |
+-----+--------+
3 rows in set (0.00 sec)
val にアルファベット以外( val RLIKE '^[a-z]+$' を満たさない)を入れさせないようなCHECK制約をかけようとするじゃろ?
mysql80 8> ALTER TABLE t1 ADD CHECK(val RLIKE '^[a-z]+$');
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.
既に制約違反しているから ALTER TABLE がエラーになるんじゃ。
という訳で制約に違反している行を消すじゃろ?
mysql80 8> DELETE FROM t1 WHERE num = 3;
Query OK, 1 row affected (0.00 sec)

mysql80 8> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
|   2 | two  |
+-----+------+
2 rows in set (0.00 sec)
すると通るんじゃ。
mysql80 8> ALTER TABLE t1 ADD CHECK(val RLIKE '^[a-z]+$');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql80 8> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  CONSTRAINT `t1_chk_1` CHECK (regexp_like(`val`,_utf8mb4'^[a-z]+$'))
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
CHECK制約があれば、 “さん” はそもそも入らないんじゃ。
mysql80 8> INSERT INTO t1 VALUES (3, 'さん');
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.

mysql80 8> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
|   2 | two  |
+-----+------+
2 rows in set (0.00 sec)
Σ(゚д゚lll) フツーのDBMSっぽい!
しかしまあ、MySQLっぽいと言われる謎の NOT ENFORCED なんて句がついてきて
mysql80 8> ALTER TABLE t1 DROP CHECK t1_chk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 8> ALTER TABLE t1 ADD CHECK(val RLIKE '^[a-z]+$') NOT ENFORCED;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 8> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  CONSTRAINT `t1_chk_1` CHECK (regexp_like(`val`,_utf8mb4'^[a-z]+$')) /*!80016 NOT ENFORCED */
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql80 8> INSERT INTO t1 VALUES (3, 'さん');
Query OK, 1 row affected (0.01 sec)

mysql80 8> SELECT * FROM t1;
+-----+--------+
| num | val    |
+-----+--------+
|   1 | one    |
|   2 | two    |
|   3 | さん   |
+-----+--------+
3 rows in set (0.00 sec)
:(;゙゚’ω゚’): なんと入る
誰得なんだろう…せめてワーニングとか出てくれるといいんだけどな。。
ところで、 ALTER TABLE でCHECK制約を追加するということは、それってオンラインでできるの的な疑問が湧いてくるんですが、2019年4月25日 20時10分02秒 JST 現在、InnoDBのオンラインDDL関連のページにはCHECK制約については特に記載なし。
mysql80 20> ALTER TABLE t1 ADD CHECK ('ワレワレ' <> '宇宙人'), ALGORITHM= INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY.

mysql80 20> ALTER TABLE t1 ADD CHECK ('ワレワレ' <> '宇宙人'), ALGORITHM= INPLACE;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.

mysql80 20> ALTER TABLE t1 ADD CHECK ('ワレワレ' <> '宇宙人'), ALGORITHM= COPY, LOCK= NONE;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

mysql80 20> ALTER TABLE t1 ADD CHECK ('ワレワレ' <> '宇宙人'), ALGORITHM= COPY, LOCK= SHARED;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql80 20> ALTER TABLE t1 ADD CHECK ('ワレワレ' <> '宇宙人'), ALGORITHM= COPY, LOCK= EXCLUSIVE;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0
試した感じ、 ALGORITHM= COPY (テーブルコピー)で LOCK= SHARED (共有ロック、読めるけど書けない)か LOCK = EXCLUSIVE (排他ロック、読めないし書けない)しか選べない模様。。
このへん、せめてINPLACEとかできるようになるといいな(いくら全行チェックが必要とはいえ、テーブルコピーする必要はないはず…)

2019/04/23

MySQL 8.0.15現在、 SET PERSIST_ONLY にはあんまり手を出さない方が良いと思う

TL;DR

  • 手を出さない方がいいのはSET PERSIST_ONLYの話で、SET PERSISTは手を出してもいいと思う
  • SET GLOBAL, SET PERSIST には値をセットするためのバリデーター(各サーバー変数ごとにある)が用意されているが、 SET PERSIST_ONLY はそのバリデーターを通らないため、不正な値を突っ込んでもエラーになってくれない
  • mysqld を再起動しようとした時に、 mysqld-auto.cnfSET PERSIST, SET PERSIST_ONLY の保管先)に不正な値が突っ込んであるとそれを適用できなくて mysqld が起動してくれない

最初に見つけた時は innodb_ft_aux_table で見つけたのでそんなにアレじゃないかなと思ってたんだけれども、 SET GLOBAL, SET PERSIST には値をセットするためのバリデーター(各サーバー変数ごとにある)が用意されているが、 SET PERSIST_ONLY はそのバリデーターを通らないため、不正な値を突っ込んでもエラーになってくれない、という不具合を3月にレポートしていた。
数値型のやつに文字列型、みたいなやつはちゃんと弾いてくれる。

mysql80 8> SET PERSIST_ONLY max_connections= 'abc';

ERROR 1232 (42000): Incorrect argument type to variable 'max_connections'
問題になるのは、「文字列型を受け取るけどそれにもちゃんとルールがある」やつだ。
ばぐれぽ で使っている innodb_ft_aux_table は「フルテキストインデックスが存在するテーブル」を指定しなければならない。それ以外のテーブルを指定した場合はエラーになる。
mysql80 8> SHOW CREATE TABLE d1.t1\G -- フルテキストインデックスがない
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql80 8> SET GLOBAL innodb_ft_aux_table = 'd1/t1';
ERROR 1231 (42000): Variable 'innodb_ft_aux_table' can't be set to the value of 'd1/t1'
SET PERSIST_ONLY はこれをすり抜けるので、次に mysqld を起動しようとした時にこの不正な値がキマって起動しなくなる。
mysql80 8> SET PERSIST_ONLY innodb_ft_aux_table = 'd1/t1';
Query OK, 0 rows affected (0.00 sec)

mysql80 8> SELECT * FROM performance_schema.persisted_variables;
+---------------------+----------------+
| VARIABLE_NAME       | VARIABLE_VALUE |
+---------------------+----------------+
| innodb_ft_aux_table | d1/t1          |
+---------------------+----------------+
1 row in set (0.01 sec)
ちなみに「間違った!」というのがわかるのであれば、 RESET PERSIST ステートメントでクリアできる。 mysqld を落とす前なら。
mysql80 8> RESET PERSIST innodb_ft_aux_table;
Query OK, 0 rows affected (0.00 sec)

mysql80 8> SELECT * FROM performance_schema.persisted_variables;
Empty set (0.00 sec)
話が厄介なのは、「ENUMなサーバー変数」で、コイツは「文字列でも数値でもセットできる」「本来正しい(?)値は文字列型」「数値を渡された時に文字列に変換しているのはどうやら変数セット時のバリデーター」というアレがあり、
mysql80 8> SELECT @@binlog_error_action;
+-----------------------+
| @@binlog_error_action |
+-----------------------+
| ABORT_SERVER          |
+-----------------------+
1 row in set (0.00 sec)

mysql80 8> SET GLOBAL binlog_error_action = 0; -- SET GLOBALで数値をセットするじゃろ?
Query OK, 0 rows affected (0.00 sec)

mysql80 8> SELECT @@binlog_error_action; -- ちゃんと文字列で表示されるじゃろ?
+-----------------------+
| @@binlog_error_action |
+-----------------------+
| IGNORE_ERROR          |
+-----------------------+
1 row in set (0.00 sec)
mysql80 8> SET PERSIST_ONLY binlog_error_action = 0; -- PERSIST_ONLYで数値をセットするじゃろ?
Query OK, 0 rows affected (0.00 sec)

mysql80 8> SELECT * FROM performance_schema.persisted_variables; -- 0のままじゃろ?
+---------------------+----------------+
| VARIABLE_NAME       | VARIABLE_VALUE |
+---------------------+----------------+
| binlog_error_action | 0              |
+---------------------+----------------+
1 row in set (0.00 sec)

mysql80 8> RESTART; -- キマるんじゃ
Query OK, 0 rows affected (0.01 sec)
エラーログはこんなんなりました。
無限にプロセスの起動失敗と再起動を繰り返しております(たぶん RESTART ステートメントじゃなくて systemctl restart mysqld とかだと綺麗に死んでくれるかな…わからん)
2019-04-23T08:29:56.321633Z 8 [System] [MY-011086] [Server] Received RESTART from user root.  Restarting mysqld (Version: 8.0.15).
..
2019-04-23T08:30:00.242090Z 0 [System] [MY-010910] [Server] /usr/mysql/8.0.15/bin/mysqld: Shutdown complete (mysqld 8.0.15)  Source di
stribution.
2019-04-23T08:30:00.388768Z mysqld_safe Number of processes running now: 0
2019-04-23T08:30:00.393888Z mysqld_safe mysqld restarted

..
2019-04-23T08:30:12.326063Z 5 [ERROR] [MY-011268] [Server] Configuring persisted options failed: "Variable 'binlog_error_action' can't
 be set to the value of '0'".
2019-04-23T08:30:12.326121Z 0 [ERROR] [MY-010175] [Server] Setting persistent options failed.
2019-04-23T08:30:12.440194Z mysqld_safe Number of processes running now: 0
2019-04-23T08:30:12.444490Z mysqld_safe mysqld restarted

..
2019-04-23T08:30:13.507286Z 5 [ERROR] [MY-011268] [Server] Configuring persisted options failed: "Variable 'binlog_error_action' can't
 be set to the value of '0'".
2019-04-23T08:30:13.507327Z 0 [ERROR] [MY-010175] [Server] Setting persistent options failed.
2019-04-23T08:30:13.633597Z mysqld_safe Number of processes running now: 0
2019-04-23T08:30:13.638389Z mysqld_safe mysqld restarted

..
2019-04-23T08:30:15.253603Z 5 [ERROR] [MY-011268] [Server] Configuring persisted options failed: "Variable 'binlog_error_action' can't
 be set to the value of '0'".
2019-04-23T08:30:15.253652Z 0 [ERROR] [MY-010175] [Server] Setting persistent options failed.
2019-04-23T08:30:15.364141Z mysqld_safe Number of processes running now: 0
2019-04-23T08:30:15.368649Z mysqld_safe mysqld restarted

..





2019/04/22

gh-ostの最後のステップの `RENAME TABLE ..` を任意のタイミングまで遅延させる

TL;DR

  • --postpone-cut-over-flag-file でテキトーなファイルを指定する
  • RENAME TABLE .. (gh-ostの cut-over フェーズ)をしても良いタイミングになったら、「指定したファイルを消す」または 「 Interactive commandsunpostpone コマンドを放り込む」

gh-ost は起動してからよしなにバイナリーログを吸い上げてゴーストテーブル(スキーマに対する変更を適用しつつ、オリジナルのテーブルへの更新とオリジナルのテーブルからデータを少しずつフェッチしてマージしたもの)を作り、最後にはオリジナルのテーブルとゴーストテーブルを RENAME TABLE .. で入れ替えることでオンラインスキーマ変更を実現している。デフォルトでは用済みになったオリジナルのテーブルもドロップしない。
pt-online-schema-change がトリガーを仕掛けてゴーストテーブル(Percona Toolkitではそうは呼ばないけれど)を組み立てて、 RENAME TABLE .. をかけてトリガーも片付けて更に入れ替えて用済みになったオリジナルのテーブルに対して DROP TABLE までかけてくれる(デフォルト。しないようにもできる)のに比べれば、メタデータロックを取らないといけない操作も少ないし突然のLazy Drop Tableで死ぬことも少ないのだけれども。
それでも「その瞬間だけSELECTすらブロックするような」でかいロックを取る操作の前には心の準備として一拍置きたい気持ちがあったりなかったりする。
オリジナルのテーブルへのアクセスが十分少ない(人によって変わると思う)場合、pt-oscくらい何回もメタデータロックを取っていても全然問題なかったりする。なので別にこれがデフォルトになってほしいとは思わない(大概の場合、迷っているより流れちゃった方がさっさと済むのだ)
前置きが長くなったけど、やり方は TL;DR の通り。
$ gh-ost --database=d1 --table=t1 --alter="Engine = InnoDB" --port=64057 --user=root --host=127.0.0.1 --allow-on-master --execute --ok-to-drop-table --postpone-cut-over-flag-file=/tmp/moge
..
Copy: 0/2 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: bin.000014:566423; State: migrating; ETA: N/A
Copy: 0/2 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: bin.000014:570719; State: migrating; ETA: N/A
Copy: 2/2 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: bin.000014:571447; State: migrating; ETA: due
Copy: 2/2 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: bin.000014:575747; State: postponing cut-over; ETA: due
Copy: 2/2 100.0%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 1s(copy); streamer: bin.000014:580057; State: postponing cut-over; ETA: due
..
cut-overの遅延が有効になっている場合、ゴーストテーブルを組み立て終わった時点で State: postponing cut-over になる。
この状態ではもうオリジナルテーブルからのマージは終わっているから、ひたすらバイナリーログを吸い上げてはその差分を適用し続けるだけの状態。
$ ll /tmp/moge
-rwxr-xr-x 1 yoku0825 yoku0825 0 Apr 22 15:51 /tmp/moge

$ file /tmp/moge
/tmp/moge: empty
--postpone-cut-over-flag-file に渡したパスには空っぽのファイルが出来上がっていて、これを消すかInteractive commandsから unpostpone を放り込むとcut-overフェーズに突入する。
$ rm /tmp/moge

..
Copy: 2/2 100.0%; Applied: 0; Backlog: 1/1000; Time: 10s(total), 1s(copy); streamer: bin.000014:611206; State: migrating; ETA: due
# Migrating `d1`.`t1`; Ghost table is `d1`.`_t1_gho`
# Migrating 163-44-175-117:64057; inspecting 163-44-175-117:64057; executing on 163-44-175-117
# Migration started at Mon Apr 22 16:49:10 +0900 2019
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# postpone-cut-over-flag-file: /tmp/moge
# Serving on unix socket: /tmp/gh-ost.d1.t1.sock
Copy: 2/2 100.0%; Applied: 0; Backlog: 0/1000; Time: 10s(total), 1s(copy); streamer: bin.000014:611647; State: migrating; ETA: due
[2019/04/22 16:49:20] [info] binlogsyncer.go:164 syncer is closing...
[2019/04/22 16:49:21] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2019/04/22 16:49:21] [info] binlogsyncer.go:179 syncer is closed
# Done
State: migrating になって、最後の処理である RENAME TABLE .. などが行われる。

ところで、この cut-over を遅延させるための(ファイルを指定する以外の)オプションだと思っていた --cut-over というのがあったんだけれど、こっちは何の関係もなかった。。
$ gh-ost .. --cut-over=two-step
..

2019-04-22T15:37:48.792707+09:00           40 Query     lock /* gh-ost */ tables `d1`.`t1` write
2019-04-22T15:37:49.793151+09:00           40 Query     alter /* gh-ost */ table `d1`.`t1` rename `_t1_del`
2019-04-22T15:37:49.798139+09:00           42 Query     alter /* gh-ost */ table `d1`.`_t1_gho` rename `t1`
2019-04-22T15:37:49.800834+09:00           40 Query     unlock /* gh-ost */ tables

$ gh-ost .. --cut-over=atomic
..

2019-04-22T16:49:19.941893+09:00           81 Query     select get_lock('gh-ost.81.lock', 0)
2019-04-22T16:49:19.949646+09:00           81 Query     lock /* gh-ost */ tables `d1`.`t1` write, `d1`.`_t1_del` write
2019-04-22T16:49:20.942595+09:00           87 Query     rename /* gh-ost */ table `d1`.`t1` to `d1`.`_t1_del`, `d1`.`_t1_gho` to `d1`.`t1`
2019-04-22T16:49:20.948139+09:00           81 Query     unlock tables
2019-04-22T16:49:21.039657+09:00           81 Quit
atomic (こっちがデフォルト)の方が良さそう。

2019/04/18

gh-ostのinteractive-commandを使う

gh-ost には interactive-command なるものが用意されている。
これは gh-ost起動しちゃった後 、後からオプションを変更したりする機能を提供しているっぽい。

デフォルトではソケットファイルのみオープンする。デフォルトのパスは /tmp/gh-ost.<スキーマ名>.<テーブル名>.sock
パスを変えたかったりTCP経由も受け付けてほしい時は gh-ost --serve-socket-file=/tmp/hogegh-ost --serve-tcp-port=9999 とかで設定できる。
$ gh-ost --database=mysqlslap --table=t1 --alter="ADD KEY (intcol1)" --host=127.0.0.1 --port=21801 --user=msandbox --password=msandbox --execute --serve-socket-file=/tmp/hoge --serve-tcp-port=9999
..
ソケットでもTCPでも nc で1行押し込むのが良いっぽい。
パイプ通さなくてもいいけれど、その場合は1つコマンドを押し込むたびに切断されるので面倒っちゃ面倒。
$ echo "status" | nc -U /tmp/hoge
# Migrating `mysqlslap`.`t1`; Ghost table is `mysqlslap`.`_t1_gho`
# Migrating 163-44-175-117:21800; inspecting 163-44-175-117:21801; executing on 163-44-175-117
# Migration started at Thu Apr 18 13:54:50 +0900 2019
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/hoge
# Serving on TCP port: 9999
Copy: 0/11761 0.0%; Applied: 0; Backlog: 1000/1000; Time: 20s(total), 16s(copy); streamer: mysql-bin.000002:578358174; State: throttled, lag=2.786607s; ETA: N/A

$ echo "help" | nc localhost 9999
available commands:
status                               # Print a detailed status message
sup                                  # Print a short status message
coordinates                                                                                                      # Print the currently inspected coordinates
chunk-size=<newsize>                 # Set a new chunk-size
dml-batch-size=<newsize>             # Set a new dml-batch-size
nice-ratio=<ratio>                   # Set a new nice-ratio, immediate sleep after each row-copy operation, float (examples: 0 is aggressive, 0.7 adds 70% runtime, 1.0 doubles runtime, 2.0 triples runtime, ...)
critical-load=<load>                 # Set a new set of max-load thresholds
max-lag-millis=<max-lag>             # Set a new replication lag threshold
replication-lag-query=<query>        # Set a new query that determines replication lag (no quotes)
max-load=<load>                      # Set a new set of max-load thresholds
throttle-query=<query>               # Set a new throttle-query (no quotes)
throttle-http=<URL>                  # Set a new throttle URL
throttle-control-replicas=<replicas> # Set a new comma delimited list of throttle control replicas
throttle                             # Force throttling
no-throttle                          # End forced throttling (other throttling may still apply)
unpostpone                           # Bail out a cut-over postpone; proceed to cut-over
panic                                # panic and quit without cleanup
help                                 # This message
- use '?' (question mark) as argument to get info rather than set. e.g. "max-load=?" will just print out current max-load.

$ nc localhost 9999
status
# Migrating `mysqlslap`.`t1`; Ghost table is `mysqlslap`.`_t1_gho`
# Migrating 163-44-175-117:21800; inspecting 163-44-175-117:21801; executing on 163-44-175-117
# Migration started at Thu Apr 18 13:54:50 +0900 2019
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/hoge
# Serving on TCP port: 9999
Copy: 0/11761 0.0%; Applied: 0; Backlog: 1000/1000; Time: 3m6s(total), 3m3s(copy); streamer: mysql-bin.000002:578358174; State: throttled, lag=7.586699s; ETA: N/A
status

Ncat: Broken pipe.
今のところよく使いそうな気がしているのはこれ。
$ echo "max-lag-millis=500" | nc -U /tmp/hoge ### どのくらいレプリケーション遅延が出た時にSleepするかの閾値を変える。単位はミリ秒
$ echo "throttle" | nc -U /tmp/hoge ### gh-ostをサスペンドする
$ echo "no-throttle" | nc -U /tmp/hoge ### ↑でサスペンドしたのを再開する
--mal-lag-mills によるスロットルと throttle コマンドによるスロットルはどっちも State: throttled で表示されるけど、処理は独立しているっぽい。
max-lag-millis=10State: throttled -> throttle コマンド -> max-lag-millis=10000000State: throttled のまま -> no-throttle コマンドで State: migrating
さあ、次は 本番で試す 実践投入ですね!

2019/04/16

gh-ostでスレーブの遅延を見ながら処理速度を調整する

TL;DR

  • マスターに接続する( --allow-on-master )場合、 --throttle-control-replicas myhost1.com:3306,myhost2.com:3306 で遅延監視対象を指定する
    • スレーブからbinlogを吸い上げてマスターに当て込む場合は、接続先のスレーブで遅延監視をする
  • 閾値は gh-ost --max-lag-millis 1000 で指定する (デフォルト1500ミリ秒)
  • SHOW SLAVE STATUS は叩かず、 *_ghc テーブルに書き込んだハートビートの行と現在時刻の差分で計算する
    • --test-on-replica--migrate-on-replica の時だけ SHOW SLAVE STATUS するっぽい

2019-04-16T07:13:51.784309Z        22 Query     select value from `mysqlslap`.`_t1_ghc` where hint = 'heartbeat' and id <= 255
2019-04-16T07:13:52.784030Z        22 Query     select value from `mysqlslap`.`_t1_ghc` where hint = 'heartbeat' and id <= 255
2019-04-16T07:13:53.784096Z        22 Query     select value from `mysqlslap`.`_t1_ghc` where hint = 'heartbeat' and id <= 255
こんなレコードが入ってた。
slave1 [localhost] {msandbox} (mysqlslap) > SELECT * FROM _t1_ghc WHERE hint= 'heartbeat' AND id <= 255;
+----+---------------------+-----------+----------------------------------------+
| id | last_update         | hint      | value                                  |
+----+---------------------+-----------+----------------------------------------+
|  1 | 2019-04-16 16:13:31 | heartbeat | 2019-04-16T16:13:31.979520869+09:00    |
+----+---------------------+-----------+----------------------------------------+
1 rows in set (0.00 sec)
この value に入っている値を現在時刻と比較して遅延を計算する、と。
なるほど。 pt-heartbeat と同じような仕組みですね。
今日はこれまで。

2019/04/03

innotopにmy.cnfや.mylogin.cnfを食わせる方法

TL;DR

  • 何も設定しなくても食うけど、 [client] セクションしか読んでくれないので「読んでない」と思われることが多いっぽい
  • それ以外のセクションを食わせるためには ~/.innotop/innotop.conf に記述が必要だけど割と簡単だよ

innotop はPerl5製で DBD::mysql を使っているので、デフォルトファイルの読み込みは mysql_read_default_filemysql_read_default_group あたりに影響を受ける。
…と思って探してみれば割とさっくり見つかって、
あたりがそうなんだけど前後を読んでみるとどうも コマンドラインオプションからこれらを直接指定する方法はない
じゃあどうやって指定するかというと、「コンフィグファイルからコネクションのDSNを読みだそうとして、読み出せなければ渡されたオプションとかからDSNを作って mysql_read_default_group=client をくっつけて使う」ような作りになっているので、そもそもコンフィグファイルに好きなセクションを読みだすような(そして、それだけの) DSNを作ってやれば上手くいきそう。
(俺の手元では、何故か innotop.conf の先頭をからっぽの行にしてやらないとダメだった…)
というわけで、
$ cat ~/.innotop/innotop.conf

[connections]
test=dsn=dbi:mysql:;mysql_read_default_file=/home/yoku0825/hoge.cnf
こんな感じに書くと、 /home/yoku0825/hoge.cnf[client] セクションを読むし( mysql コマンドラインクライアントの --defaults-file と同じで、ファイルを指定するとそれ以外のデフォルトパスのコンフィグは読まない)
$ cat ~/.innotop/innotop.conf

[connections]
test=dsn=dbi:mysql:;mysql_read_default_group=innotop
こんな感じに書くと( mysql_read_default_file を指定していないので) /etc/my.cnf, /usr/local/mysql/my.cnf, .., ~/.my.cnf, ~/.mylogin.cnf の順で [innotop] セクションを読むようになる。
もちろん両方同時に指定しても動く。

しかしあの、この動作を調べてたらこんなの( DBD::Pg をサポートしようとする努力) を見つけたんだけど、 maakitの名残 みたいだった。歴史を感じる。