2021/04/14

JPUG & MyNA合同勉強会 -PLEASE 2021/4- を開催しました

エイプリルフール12日目にして JPUG & MyNA合同勉強会 -PLEASE 2021/4- を開催しました。

俺はこの手の「ジョークのために尽くされる ~方向を間違った~ 努力」をするのも見るのも大好きなので、とても俺得な楽しい会でした。
最初の流れを作ってくださった坂井さんと、大人げない大人たちのみなさんに超感謝しています :D


イノレカ㌠の坂井さん ( @sakaik )

🐘㌠の澤田さん ( @masahiko_sawada )

  • ターミナルのステータスラインを見て「メモリ32GBだ!」と思った。羨ましい。
  • 動画の冒頭、キーボードをガチャガチャガチャガチャやってるのは私です…。ごめんなさい。。
  • emacs 派だ!
  • yaccをいじって、キーワードとかをいじって、最後に実装を入れる。
  • mmake ( make install かも? ), ppsql になるようになっていたのが衣装的 ( restart masterpgctl あたりにつながっている?
  • 本当にその場その場で PLEASEが実装されていく様は圧巻
  • 「キーワードリストからドキュメントのキーワード一覧を作っている」とかいい仕組み。MySQL 8.0もDoxygenになってそういう風になったりしたかしら?
  • https://youtu.be/Ui2cBMh0AMU?t=949

🐬㌠よりわたし ( @yoku0825 )

  • 澤田さんの環境と打って変わって、 ConoHa VPSの1GBプラン なのでその場でビルドとかはできません :-P
  • やっていることは澤田さんとほぼ同じ流れで、 sql/sql_yacc.yy をいじって sql/lex.h をいじって sql/sql_parse.cc に実装を入れています
    • スレッド変数( SHOW SESSION VARIABLES で見られるアレ) に is_pleased を実装した関係で sql/class.hsql/sys_vars.cc に追加しています
  • 最後に sql_modeSTRICT_PLEASE_MODE を入れるために、再び sql/class.h, sql/sys_vars.ccsql_mode 周りの実装を入れて、 sql/sql_parse.cc の分岐を1段深くして完成
  • 途中 sql_mode の名前(= STRICT_PLEASE_MODE) とフラグ(= MODE_STRICT_PLEASE ) を紐づけるところがどこだか度忘れするという…
  • MySQL 5.6.51にPLEASE句を実装するパッチ
  • カメラの位置をこの時変えていたので、このあと無駄に俺のドアップがちょくちょく出てくるという致命的な失態…

redis㌠からmaruさん ( @maruloop )

  • 「23時に布団に入っていたんだけど今からやるか」
  • MySQLやPostgreSQLはその手の人間がいることは観測できていたけど、他のデータストアにもやっぱりいた!
    • 「5分でやるか」
    • 「PCをつけるまでにいくつかプランが」
  • 初手でパーザーに手を入れることだけを思い付いた俺とは対照的に、いくつかの実装方法を検討されているのがすごい
  • 地味に最後に「redisからも slavereplica に変わっている」と便利情報が

SparkSQL㌠から みやけるぴーさん ( @mikakelp_ )

  • みんな、夜から自分ちの子に実装を始めるのはなぜw (気持ちはわかります)
  • PLEASE句を突っ込んだところの近くのオブジェクトを検索していったらたまたまDISTINCTにたどり着いたので、 PLEASEが無ければ勝手にDISTINCTする という強烈な仕様が生まれた
  • タイムゾーンにモザイクをかけることで、15:02にビルドされたSparkSQLは無事にエイプリルフール当日の産物

Oracle㌠から 篠田さん ( @nori_shinoda )

  • タイトルの「Oracle DatabaseにPLEASEなどいらぬ!」から「負け惜しみです」までの速さw
  • https://www.youtube.com/watch?v=Ui2cBMh0AMU&t=3848s
  • パーサーに手を入れられないのでパースされる前にSQLをほげるSQL Translation Frameworkで先頭以外の部分にPLEASEを入れるのは成功
  • 同じくSQLをほげるQuery Rewriteでは先にパースが走っちゃうのでPLEASEは入れられなかった
  • 「できなかった話をします」と聞いていたんですが出来てる…

SQL Server㌠から小澤さん ( @Masayuki_Ozawa )

  • SE の雑記 の中の人
  • 「普段この手のジョークネタにSQL Serverが加わることは少ないのでちょっと寂しい…」
  • 「この日の仕事が定時に終わったのでこれから実装すれば仲間に入れるかも」
  • 「ソースコードに手を入れられないなら、SQL Serverに流れるT-SQLを直接ネットワークのパケットから書き換えてしまえばいいんじゃないかな」
  • MindFlavor/TDSBridge: TDS Proxy library
  • SQL Serverは知らないキーワードにぶち当たるとまずストアドと見做して検索するという豆知識
  • PLEASEがついていなければWAITFOR DELAYを挟んで、PLEASEが入っていたらストリップしてSQL Serverに投げることでPLEASE句を表現

みなさまありがとうございました!

2021/03/28

ぼくがかんがえたさいきょうのMySQL監視スクリプトのはなし

PHPerKaigi 2021 でトークしてきた話です。

これは yt-healthcheck の話で、ウチの環境ではこれを5分に1回、crondからキックしています。

喋っていた他にもいくつかひねりがあって、でも再集録の時間が取れなくて断念したネタもあります。


セッションのあと、Ask The Speakerで話しかけてくださったみなさまありがとうございました!
いつもこういう時ぼっちになるので助かりました!

Q. 「RDSではread_onlyの監視は要らない」って言ってたけどそれは何故?
A. RDSはRDSの基盤側で勝手にread_onlyを切り替えてユーザー側では指定できないので監視する必要はないのです

Q. SHOW ENGINE INNODB STATUS をもっと統一しようみたいな動きはないの?
A. アレは「InnoDBチームが自分たちのために出している出力」みたいなところがあるので、エンドユーザーが使っていることは知っていてもそういう流れにはならなさそう。一つの別解がMySQL 5.6から使える information_schema.innodb_metrics なんだと思う

Q. コネクション監視の閾値ってどれくらい?
A. デフォルトで70%がワーニングになってますけど、これ「そもそも平常状態のピーク * 2 = max_connection 」で普段設定していて、この前提の上で70%だと「想定していたピークの1.4倍くらいコネクションが来た」とか「オートスケールでコネクションプールが増えた」みたいなのが良い感じで引っ掛かります

【2021/03/29 11:31】

Q. AUTO_INCREMENTが40億行くってどういうケース?

A. 主にログ系。バッチで数か月分とか消し込みがかかっていてもAUTO_INCREMENTは巻き戻らないので、毎日100万とか1000万の単位で書き込まれると案外あふれる。あとはINSERT .. SELECT .. でバンバン連番を払い出すやつとか

2021/03/04

Perl MongersのためのMySQL InnoDB Cluster超入門のはなし

Japan.pm 2021 のトークセッションで喋らせてもらったネタ。
Perlの話は DBI->connect くらいしか出てこないのでPerl Mongersでなくてもお楽しみいただけるかと思います。


InnoDB Clusterのキモは何と言っても「MySQLとMySQL Routerはそれぞれ別の観点から別の仕事をしている」というところで(ついでに言うなら、オーケストレーター的に働くMySQL Shellは一度動き出してしまえば常駐しなくても良いところ)これを理解しておくと理解が色々と捗る。

このへんの機能も「MySQL Routerの」機能であって、グループレプリケーションはこの辺の機能には一切関係ない。

そのへんを認識すると、全てのmysqldがいなくなった時にmysqlrouterが返してくるこのへんのエラーも「ああ、如何にもそれっぽい」と思えるようになるんではなかろうかなと。

グループレプリケーションにブラックボックスを全てつぎ込んだが故に、全てを理解しようと思うと当然のようにMySQLのソースコードを当たる必要があるけれど、MySQL RouterやMySQL Shell部分はシンプルなので、「Paxosまではいいけど表面的な動作はコードレベルで押さえておきたい」みたいなケースには学習がしやすいんじゃないかなあと思ったりしました。

----

しかし Japan.pm 2021 は想像していたより色々チャレンジをされていたみたいで、 (discordには慣れなかったけど) トーク後のおしゃべり可能タイムが設けられていて「トークおつかれさまでした!」ってまいんだーさんの肉声で言われた時にはかなり嬉しかった。

トークのラインナップもさすがPerl Mongersって感じのアレでだいぶ好みでした。次回もあるといいなあ。

2020/12/26

pt-table-checksumでよく使うオプション

メリークリスマイエスキューエル! (と、1日)

この記事は GMOペパボエンジニア Advent Calendar 2020 の26日目の記事のつもりです。
ちなみに私の中の人は GMOペパボではない会社 に勤めています。


最近 pt-table-checksum にお世話になる機会が多くなって、使い方をまとめておこうと思ったメモです。

公式ドキュメントはこちら。

pt-table-checksum 自体は簡単に説明した昔の記事が出てきた。


シンプルな例えにすると、 pt-table-checksum はこんな感じに binlog_format=STATEMENT にしてクエリーを実行します。

mysql57 19> SET SESSION binlog_format = STATEMENT;
Query OK, 0 rows affected (0.00 sec)

mysql57 19> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql57 19> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql57 19> INSERT INTO checksum (table_name, master_cnt, this_cnd) SELECT 't1', 3, COUNT(*) FROM t1;

binlog_format=STATEMENT を押し込むことで INSERT INTO .. SELECT .. は「 master_cnt はリテラルでマスターの値がそのまま入り、 this_cnt はレプリカで SELECT が再実行されるためレプリカでの値が入る」ことになります。


master_crc, this_crc も同じように「マスターで計算済みのリテラルを入れるカラムとレプリカでリプレイされて計算された値を入れるカラム」に分けられて記録されます。
チェック対象のテーブルはチャンクに分けられて実行されます。1チャンクが1トランザクションで処理されるため、各チャンクをまたいだデータの一貫性はありません(2チャンク目を計算している間に1チャンク目の行の数が変わっているかもしれない、ということ)


バイナリログ直列化の恩恵を受けられるため、マスターの1チャンク目とレプリカの1チャンク目は「同じスナップショット」であることが期待できます。先の例だと、マスターで1チャンク目を更新したあとに1チャンク目に新しく行が追加されても、レプリカはその新しい行が追加される前のスナップショットでチャンクを再計算するため、よほどレアなケースでない限り(もともと binlog_format = STATEMENT && 非決定性関数が頻繁に使われている環境でない限り)計算済みのチャンクで新たな不整合が発生する可能性は低いはずです。
チャンク分けしているのはたぶん、 INSERT .. SELECT ..SELECT 対象のテーブルの行に共有ロックを置くためロックの範囲が大きくなりすぎないようにとか、そんなにでっかくするとチェックサムを計算するのにレプリケーションが遅れるからとかそういうのの配慮だと思います。

で、俺のよく使うオプションはこんな感じでした。

$ pt-table-checksum \
>   --host xxxx --port 3306 \
>   --user pt_tcs --password 'xxxx' \
>   --ignore-databases mysql,sys \
>   --replicate pt.pt_tcs \
>   --chunk-size=1000 \
>   --recursion-method=processlist \
>   --no-check-binlog-format \
>   --pause-file=/tmp/pt_tcs.pause \
>   --truncate-replicate-table

--host, --port はマスターのもの。
--user, --password もマスターのものですが、レプリカ監視用のアカウントを瞬間的に払い出すと楽です。

マスターとレプリカの間でバージョンが違う場合、 --ignore-databases=mysql,sys は必須です( information_schema, performance_schemapt-table-checksum が勝手に除けます )
pt-table-checksum のクエリーは個々のテーブルのカラムを全て参照するため、 マスターとレプリカでカラムの数が違ったりマスターにあってレプリカにないテーブルがあると転けます
転けるのはpt-table-checksumのみではなく、レプリケーションそのものが止まります

--replicate は「チェックサムの結果を記録するテーブル」を指定します。これを指定しないと、マスターで SELECT だけ投げて終わるのでレプリカとの比較ができません(更新止まってればできるだろうけど)

--chunk-size はチャンク分けする時の基本サイズです。特に何も出なければそのまま1000でいいんですが、「チャンク分けが上手くいかずにこのテーブルはスキップするよ!」みたいなワーニングが出ることがあるので、その時はこの --chunk-size を大きくしてから --tables でそのテーブルを指定して流しなおします。

--recursion-method は3306以外のポートを使っている時は hosts が便利ですが、3306だけなら processlist で問題ないです。

--no-check-binlog-format は「レプリカの binlog_format <> STATEMENT 」な時にスクリプトを実行する時に必要なオプションです。
↓を読むと「マスターとレプリカの binlog_format が違ったらスクリプトを実行させない」のように見えますが、マスターには既に SET SESSION binlog_format = STATEMENT が実行されているので、レプリカの binlog_format = STATEMENT でない限り必ず引っ掛かります。

--replicate で指定したテーブルにFKやTriggerを仕掛けるようなことをしていない限りは(そんなもの好きな人もいないと思う)特に問題ない気がしますが、まあエラーメッセージの通り If you understand the risks, specify --no-check-binlog-format to disable this check. という感じでお願いします。

--pause-file を指定しておくと、そのパスに「ファイルが存在している間はスクリプトがsleepする」ようになります。 pt-online-schema-change と違って実行中ずっと貼りついて見守っていないと不安になるようなスクリプトではないですが、明示的に止めたい時に便利です。 --continue でもいいんですけどね。

--truncate-replicate-table を指定すると、 --replicate で指定したテーブルの中身を1回 TRUNCATE します。これをやらないと過去の行が残るので、前に pt-table-checksum を同じコマンドで流した場合に前の結果の残骸が残ることがあります(書き込み自体は REPLACE INTO なので、過去にも今にも存在するチャンクの情報は上書きされるけれど、過去にあって今ないチャンクの情報が消えない)

--truncate-replicate-table を指定 しない 場合、前の情報が残っているので --continue が使えます。

これはCtrl+Cとかで pt-table-checksum を止めた場合、「その手前までは --replicate で指定したテーブルにチャンクのチェックサムが残っているはず」ということで、テーブルに載っていないチャンクから処理を再開させることができるオプションです。

なお俺は何度か --truncate-replicate-table--continue を同時に指定して「あれーおかしいなーまた先頭からチェックサム取ってるなー」とかやってたことがあります。我ながらアホだ。

それでは、良いお年を!

2020/12/16

Re: foreign_key_checksの挙動を知る(あるいは、OPTION_NO_FOREIGN_KEY_CHECKSについて)

この記事は MySQL Advent Calendar 2020 の16日目の記事です。

MySQL Advent Calendar 2020 8日目、 lhfukamachi さんの foreign_key_checks に関する記事を見て思い付いたものです。

システム変数のforeign_key_checks の話は上記の記事によくまとまっています。

この記事では、「じゃあ foreign_key_checks はどういう挙動で外部キー制約をチェックしないような実装になっているのか」を説明します。興味がない方はここでタブを閉じてもらって大丈夫なんですがおいちょっと待てふかまち、君は閉じるな。

さて foreign_key_checks がシステム変数(my.cnfや SET GLOBAL で変更できるやつ)である以上、 sql/sys_vars.cc のどこかにある可能性が高いでしょう。
Ctrl + Fならブラウザでも検索できる時代です。簡単に見つかりました。

5121 static Sys_var_bit Sys_foreign_key_checks(
5122     "foreign_key_checks", "foreign_key_checks",
5123     HINT_UPDATEABLE SESSION_VAR(option_bits), NO_CMD_LINE,
5124     REVERSE(OPTION_NO_FOREIGN_KEY_CHECKS), DEFAULT(true), NO_MUTEX_GUARD,
5125     IN_BINLOG);

Sys_var_* 系の構造体は引数が多いので最初のうちは何が何やらですが、慣れれば脳死で読めるようになるので大丈夫です。

大事なのは、「このシステム変数の本体が option_bits であり、OPTION_NO_FOREIGN_KEY_CHECKS の反転で表されるらしいこと」です。ついでですがこのオプションはバイナリログに記録されるので、外部キー制約があるテーブルに対して TRUNCATE とかする時でも、「マスターで foreign_key_checks=0 していればレプリカでリプレイされる時にも foreign_key_checks=0 として振る舞」います。安心ですね。

OPTION_NO_FOREIGN_KEY_CHECKS は2^26なので67108864らしいですがまあそれはどうでもいいですが、 sql/query_options.h にはその他にもいろいろなオプションが羅列されていて面白いです。 SELECT_HIGH_PRIORITY とか OPTION_FOUND_ROWS (!!) とか、 SELECT 文の修飾句は割とここらへんにいることが多いです。楽しいですね。

というわけで、 foreign_key_checks=0option_bitsに OPTION_NO_FOREIGN_KEY_CHECKS が立っている状態 、というのはわかりました。
となればあとは option_bits & OPTION_NO_FOREIGN_KEY_CHECKS とかやっていそうなところを探せば実装に行きつけそうですね。調べてみると実際結構出てきます。

待つんだそこで満足げにブラウザを閉じようとしているふかまち。まだ世の中には面白いことがある。
思い出していただきたい、「外部キー制約はストレージエンジンの機能であって、MySQLサーバーのコア機能ではない」ことを。 option_bits は基本的にサーバーコアが触るためのスレッド単位の変数なので、InnoDBはおそらくここをほとんど見ない(この記事書きながら調べたけど、NDBもそうで一度他のflagにここの値を写し取っている)
もう一度さっきの検索結果を見てみるのだ。驚くほど storage/innobase のコードはないだろう? ほとんどがサーバーコアのコードだ。

GitHubの検索結果に出てこない理由は俺は知らないけれど、InnoDBとしての「外部キー制約をチェックしない」の起点はここだ。 innobase_trx_init という如何にもトランザクションの開始時に呼ばれそうなところで、 trx->check_foreigns というプロパティに詰めている。

………あれ? ってことはトランザクションの途中で SET SESSION foreign_key_checks = 0 とかやるとどうなるの?

mysql80 23> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql80 23> DELETE FROM parent;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`d2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`id`) REFERENCES `parent` (`id`))

mysql80 23> SET SESSION foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)

mysql80 23> DELETE FROM parent;
Query OK, 2 rows affected (0.00 sec)

mysql80 23> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql80 23> SELECT * FROM parent;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

ちゃんと途中で変えても追随して動いた。これはどうも、Handlerの層からストレージエンジンの層に降りて来る時にInnoDB側でまだトランザクションを保持しているか確かめる(InnoDB側で保持していなかったら、サーバーコアの層に「ロールバックを知らせるエラー」を返すために) check_trx_existsをしょっちゅう呼んでいて、その中でも innobase_trx_init を呼んでいるからだ。

俺が最初に innobase_trx_init の響きから期待したようなものはinnobase_trx_allocate で、これも内部で innobase_trx_init を呼んでいる。こっちがトランザクションの起点かな。

gdb でアタッチしてとなりのコンソールから同じステートメントを流してみると、まあ大体合ってそう。

(gdb) b innobase_trx_allocate
+b innobase_trx_allocate
Breakpoint 1 at 0x21c8405: file /home/yoku0825/mysql-8.0.22/storage/innobase/handler/ha_innodb.cc, line 2509.
(gdb) b check_trx_exists
+b check_trx_exists
Breakpoint 2 at 0x21d2c2b: check_trx_exists. (2 locations)
(gdb) c
+c
Continuing.
Breakpoint 2, check_trx_exists (thd=0x68bbd40) at /home/yoku0825/mysql-8.0.22/storage/innobase/handler/ha_innodb.cc:2544
2544      if (trx == nullptr) {
(gdb) c
+c
Continuing.

Breakpoint 1, innobase_trx_allocate (thd=thd@entry=0x68bbd40)
    at /home/yoku0825/mysql-8.0.22/storage/innobase/handler/ha_innodb.cc:2509
2509      trx = trx_allocate_for_mysql();
(gdb) c
+c
Continuing.

Breakpoint 2, check_trx_exists (thd=0x68bbd40) at /home/yoku0825/mysql-8.0.22/storage/innobase/handler/ha_innodb.cc:2544
2544      if (trx == nullptr) {
(gdb) c
+c
Continuing.

Breakpoint 2, check_trx_exists (thd=0x68bbd40) at /home/yoku0825/mysql-8.0.22/storage/innobase/handler/ha_innodb.cc:2544
2544      if (trx == nullptr) {
(gdb) c
+c
Continuing.

Breakpoint 2, check_trx_exists (thd=0x68bbd40) at /home/yoku0825/mysql-8.0.22/storage/innobase/handler/ha_innodb.cc:2544
2544      if (trx == nullptr) {
(gdb) c
+c
Continuing.

Breakpoint 2, check_trx_exists (thd=0x68bbd40) at /home/yoku0825/mysql-8.0.22/storage/innobase/handler/ha_innodb.cc:2544
2544      if (trx == nullptr) {
..

あ、ちなみにここから先は row_ins_check_foreign_constraint, row_ins_check_foreign_constraints(複数形、ややこし…)あたりが trx->check_foreign == FALSE の時は即座にreturnするってだけなのでセルフでお願いします。
それでは良い年末を!