ISUCON初動のINDEX張りを支える技術

この記事は CARTA TECH BLOGアドベントカレンダーの12/6の記事になります。

こんにちは、ISUCON 13に参加しました、サポーターズのちゅうこ (@y_chu5) です。

先日行われたそーだい × CARTAエンジニアと振り返る「ISUCON13 反省会」 - connpass はお楽しみいただけましたでしょうか。

反省会では

初動に向けての準備は出来た
INDEXを張るいつもの作業が出来なかった

その他もろもろの反省をお話しました。

本記事では、その反省を活かしてINDEXを張る作業を半自動化することについて紹介します。

成果物は以下のリポジトリです。

github.com

初動に関しては、先日CARTA TECH BLOGに投稿した2件の記事の内容を元に準備を行いました。

初動の管理は今回完璧に行えましたが、その後の動きがいつもの練習の動きが出来ませんでした。なぜ出来なかったのかを自分なりに再度考えてみましたが、自動化だったり、頭を使わずに手癖でみたいなレベルに出来なかったからでは…と。

しかし実際にそのレベルに達したり、自動化することは出来るのでしょうか？

テーブルにINDEXを張るために必要なこと

テーブルにINDEXを張るために必要なことは、大まかに2点あると考えています。

テーブルの理解
どんなSQL文が実行されているかの理解

テーブルの理解は、例えばMySQLのクライアントを実行し、show create table <テーブル名>; で見ることは出来ますし、isuconの回によってはschemaファイルが提供されいることがあります。それらを確認することでテーブルを理解することは出来そうです。

それでは、どんなSQL文が実行されているかはどの様に確認することが出来るでしょうか？

実行されているSQL文を確認する

ISUCONでは多くの場合、DBにMySQLが使われています。 MySQLでは、general_log オプションを使うことで実行されたSQL文の確認が出来ます。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.4.3 一般クエリーログ

このオプションを有効にして、ベンチマークを実行すれば、実行されたSQL文を確認することが出来ますね。

実行されるSQL文をソースコードから抽出する

しかし、もう少し別のアプローチは無いだろうかといろいろ考えた結果、ソースコード自体を解析すればいいのでは？と思いついたので、これを試してみます。

なぜソースコードの解析に至ったかと言うと、最近サポーターズでライブラリの移行作業で jscodeshift を使っていたことがあり、言ってしまえば「ハンマーを持つ人には、全てが釘に見える」みたいな状態だからです。

~~いいじゃない、手段が目的になったって！！！！！~~

jscodeshiftで文字列抽出

jscodeshiftはTypeScriptやJavaScriptのソースコードをASTに変換出来ます。そのASTから文字列の型を指定し、とりあえずSQL文っぽくなくてもまずは一括で集めてみることをしてみます。

実際のコードは以下のURLから確認することが出来ます。

sql-index-helper/src/transforms/find-string-literals.ts at main · yamachu/sql-index-helper · GitHub

文字列を抽出した後、その文字列がSQL文っぽいかどうかを確認してみましょう。

文字列がSQL文かどうか判定する

文字列がSQL文かどうか判定するために、今回は node-sql-parser を使用しました。上記のフローで手に入れた文字列をこのライブラリのParserにどんどん投げ込んで、正常にparse出来たものを集めることで、実際に実行されるSQL文を集めることが出来ます。

select文の分析

node-sql-parserで正常にparseすると、結果として以下の項目を取得することが出来ます。

operation(selectやinsertなど)
参照されるテーブル
参照されるカラム
whereやgroup byの条件

この中からoperationがselectであるものを抽出し、そこで参照されるカラムをテーブル毎にまとめあげることで、それっぽいINDEXを生成することが出来ます。

sql-index-helperを使いINDEXを張ってみる

それでは、上記の手法を取り入れたアプリケーションである sql-index-helper を使ってみましょう。

INDEXの生成は以下の流れて行います。

sql-index-helperのインストール
ソースコードからSQL文を抽出
SQL文からINDEXを張るためのALTER TABLE文を生成

一つずつ流れを見ていきます。

sql-index-helperのインストール

今回は Node.js ライブラリとして作成したため、npmでインストールします。例えばISUCONのnode実装のディレクトリで以下のコマンドを叩きます。

$ npm i @yamachu/sql-index-helper

ソースコードからSQL文を抽出

npm iを実行したディレクトリで、どのファイルを解析対象とするかを指定します。以下では解析対象を ./src/index.ts とし、結果を collected_sql.log に出力するようにしています。

$ npx --yes jscodeshift --dry -s -t node_modules/@yamachu/sql-index-helper/dist/transforms/index.js --extensions ts ./src/index.ts > collected_sql.log

SQL文からINDEXを張るためのALTER TABLE文を生成

上記のフローで取得したlogファイルを分析し、ALTER TABLE文を生成します。こちらも先程と同じディレクトリで実行します。

-f オプションで、先程のログファイルを指定し実行すると、どの様なSQL文が実行され、それから推定したALTER TABLE文が出力されます。

$ npx sql-index-helper -f collected_sql.log

例えば以下のような出力を得ます。

-- start: auto generated add index --

-- my_player_score
--       SELECT player_id FROM my_player_score WHERE tenant_id = ? AND competition_id = ?
ALTER TABLE my_player_score ADD INDEX idx_player_id_tenant_id_competition_id (player_id, tenant_id, competition_id);
--       SELECT * FROM my_player_score WHERE tenant_id = ? AND competition_id = ? AND player_id = ?
ALTER TABLE my_player_score ADD INDEX idx_tenant_id_competition_id_player_id (tenant_id, competition_id, player_id);
-- tenant
--       SELECT * FROM tenant ORDER BY id DESC
--       SELECT * FROM tenant WHERE id = ?
-- ALTER TABLE tenant ADD INDEX idx_id (id);
--       SELECT * FROM tenant WHERE name = ?
ALTER TABLE tenant ADD INDEX idx_name (name);
-- end: auto generated add index --