gihyo.jpの連載「MySQL道普請便り」の最新回(第123回)は、ロッキングリード(SELECT ~ FOR UPDATE)のつかいどころのお話が興味深かったです。
gihyo.jp
乱暴に要約すると、例えば 5000枚のチケットを捌くUPDATEは、いきなり UPDATE するのではなくて、SELECT ~ FOR UPDATE でロックを取ってからUPADTEすると、同時実行数が大きくなっても遅くならないぞ、という内容です。人気のあるチケットの販売って、瞬時に大量の更新が走りますからね。 まさにチケットを捌くと、あっという間に砂漠みたいに何もなくなります。ひと仕事終えた人たちはデザートでもどうぞ。砂漠だけに。
想像ですが、これは、いきなりUPDATEをするとロック競合が発生してしまって、待ちがどんどん大きくなっていくところを、敢えて、より「軽い」SELECTでロックだけ取って(取れない場合は待たずにすぐにやめる)、その後にロックが取れた人だけ悠然とUPDATEをすることで、ロック待ちが発生しなくなる、とか、そんな感じなのですかねぇ。
試してみた
ということで、自分のところでも試してみました。8.0.20 on linux。
テーブル作成。
DROP TABLE IF EXISTS reserve_ticket;
CREATE TABLE `reserve_ticket` (
`id` bigint NOT NULL AUTO_INCREMENT,
`ticket_type` int NOT NULL,
`user_id` bigint DEFAULT NULL,
`update_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `tickettype_userid` (`ticket_type`,`user_id`)
) ENGINE=InnoDB;
データ作成の前に、cte_max_recursion_depth の最大値が1000では足りないので、上げておきます。
mysql> SELECT @@cte_max_recursion_depth;
+---------------------------+
| @@cte_max_recursion_depth |
+---------------------------+
| 1000 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SET @@cte_max_recursion_depth = 10000;
データ挿入(まだ売れていないチケット5000枚)と確認。
INSERT INTO reserve_ticket (ticket_type, update_at)
WITH RECURSIVE t AS (SELECT 1 as i UNION ALL SELECT i+1 FROM t WHERE i<5000 )
SELECT 1200, null FROM t;
COMMIT;
SELECT * FROM reserve_ticket;
bash のほうで、以下の2つのSQLを、mysqlslap を使って実行。
SQL1が単純にUPDATEをするもの、SQL2がロッキングリードをしてからUPDATEするもの。
SQL1=`cat << EOS
BEGIN;
UPDATE reserve_ticket SET user_id=1 WHERE ticket_type=1200 AND user_id IS NULL;
COMMIT;
EOS`;
mysqlslap --host=localhost -p --port=3306 --user=root --query="$(echo $SQL1)" --concurrency=50 --number-of-queries=5000 --create-schema=test
SQL2=`cat << EOS
BEGIN;
SELECT @id := id FROM reserve_ticket WHERE ticket_type=1200 AND user_id IS NULL LIMIT 1 FOR UPDATE;
UPDATE reserve_ticket SET user_id=1 WHERE id = @id;
COMMIT;
EOS`;
mysqlslap --host=localhost -p --port=3306 --user=root --query="$(echo $SQL2)" --concurrency=50 --number-of-queries=5000 --create-schema=test
実行は、毎回、テーブルを DROP/CREATE して発売前チケット5000枚をINSERTしてから行いました。
結果は、、、、、、
SQL1(単純UPDATE) (2回やりました):
Benchmark
Average number of seconds to run all queries: 5.894 seconds
Minimum number of seconds to run all queries: 5.894 seconds
Maximum number of seconds to run all queries: 5.894 seconds
Number of clients running queries: 50
Average number of queries per client: 100
Benchmark
Average number of seconds to run all queries: 7.749 seconds
Minimum number of seconds to run all queries: 7.749 seconds
Maximum number of seconds to run all queries: 7.749 seconds
Number of clients running queries: 50
Average number of queries per client: 100
SQL2(SELECTでロック取ってからUPDATE) (2回やりました):
Benchmark
Average number of seconds to run all queries: 23.950 seconds
Minimum number of seconds to run all queries: 23.950 seconds
Maximum number of seconds to run all queries: 23.950 seconds
Number of clients running queries: 50
Average number of queries per client: 100
Benchmark
Average number of seconds to run all queries: 24.234 seconds
Minimum number of seconds to run all queries: 24.234 seconds
Maximum number of seconds to run all queries: 24.234 seconds
Number of clients running queries: 50
Average number of queries per client: 100
あれっ!? あれれっ????
ということで、今日は「ちょっとそのまま動かしてみるくらいなら時間取れるかな」という感じで始めたものなので、これを追求する時間は取れず。 必要なコマンド類は上に全部揃っていますので、どなたか続きをお願いします(笑)。
少し考えてみた程度では、何が道普請の実験結果との相違に寄与しているのか、まったく見当もつかず。
バージョン? サーバのパラメタ? 他に動いているプロセス? メモリに入っちゃうとかそういうやつ? 操作した人の思いやりの心? ストレージ(HDD/SSD)? うーむ。
ということで、煮え切らないまま、今日の日記はおしまいです。
追記
という日記を公開したら、道普請著者の北川さんから速攻でコメントいただきました。
ぱっと見て「あ、ここおかしいわ」って分かるの、(自分の記事とはいえ)すごいです。ちょっと感動しました。
そんなわけで、「遅くなってほしかったクエリ」は、最初の一発で5000件すべてupdateして、残りは(nullの行がひとつもないので)何もしていないという実験を行っていたのでした。そりゃ速い(笑)。
LIMIT 1 を追加しての再実験です。
SQL1b=`cat << EOS
BEGIN;
UPDATE reserve_ticket SET user_id=1 WHERE ticket_type=1200 AND user_id IS NULL LIMIT 1;
COMMIT;
EOS`;
mysqlslap --host=localhost -p --port=3306 --user=root --query="$(echo $SQL1b)" --concurrency=50 --number-of-queries=5000 --create-schema=test
結果は(これも2回実行):
Benchmark
Average number of seconds to run all queries: 23.837 seconds
Minimum number of seconds to run all queries: 23.837 seconds
Maximum number of seconds to run all queries: 23.837 seconds
Number of clients running queries: 50
Average number of queries per client: 100
Benchmark
Average number of seconds to run all queries: 24.036 seconds
Minimum number of seconds to run all queries: 24.036 seconds
Maximum number of seconds to run all queries: 24.036 seconds
Number of clients running queries: 50
Average number of queries per client: 100
おお!!! 「SELECT~FOR UPDATE」と同じくらいの時間になりました!!!
・・・って、これじゃぁまだ足りないんですよね。もっと競合起こして遅くなりまくる状況を再現したいので、たぶん私の実験では、遅くなれてない(変な言い方ですが)ような気がします。この状況での律速って何なんだろう。 メモリでもストレージでもないような気がしていますが。。
追追記
という追記を終えて公開した途端、twitterで更なる情報をいただきました。
おいこら!!! いや、、、もう、北川さんに言っているというよりも、記事の内容を理解して、自分で気づけよ!!というツッコミです。 ここがキモじゃないですか!読んで、内容理解してないってことじゃないですか(自分に対して)。
ということで、気を取り直して、SELECTでロック取得できなかった時にはスキップしちゃう(その結果、UPDATEのWHEREにマッチするものがないのでUPDATEはすぐ終わる)という、本来やりたかった実験です。
SQL2b=`cat << EOS
BEGIN;
SELECT @id := id FROM reserve_ticket WHERE ticket_type=1200 AND user_id IS NULL LIMIT 1 FOR UPDATE SKIP LOCKED;
UPDATE reserve_ticket SET user_id=1 WHERE id = @id;
COMMIT;
EOS`;
mysqlslap --host=localhost -p --port=3306 --user=root --query="$(echo $SQL2b)" --concurrency=50 --number-of-queries=5000 --create-schema=test
結果(これもテーブル作り直して2回実行):
Benchmark
Average number of seconds to run all queries: 3.544 seconds
Minimum number of seconds to run all queries: 3.544 seconds
Maximum number of seconds to run all queries: 3.544 seconds
Number of clients running queries: 50
Average number of queries per client: 100
Benchmark
Average number of seconds to run all queries: 3.714 seconds
Minimum number of seconds to run all queries: 3.714 seconds
Maximum number of seconds to run all queries: 3.714 seconds
Number of clients running queries: 50
Average number of queries per client: 100
やったね!たったの4秒弱で完了するようになりました。
著者じきじきのtwitterサポートをいただき、ありがとうございます!