社内SQLチューニングコンテストの開催にあたって得られた知見

エンジニアの吉田です。

フォルシアにはdevゼミという文化があり、エンジニアが講師となって自身の詳しい分野に関する講義やハンズオンを行っています。

私もこれまでに何度かSQLチューニングを題材としたdevゼミを開講してきましたが、いずれもこちらが一方的に話すという形式に終始しており、実際に受講者が手を動かせる形式での講義も望まれていました。
色々とやり方を模索した結果、コンテスト形式で実際にPostgreSQLのチューニングを行ってもらう、という形の講義を行うことになりました。コンテスト形式での実施にあたりいろいろと工夫した点や学びがあったので、以下にそれらをまとめます。

SQLチューニングをハンズオンすることの難しさ

これまでインプット中心の講義を行っていたのは、ひとえに SQLチューニングをハンズオン形式で実施することのハードルが高かった からです。
過去に社内でも何度かチューニングコンテストを開こうという機運はあったようですが、少なくとも近年ではそういった試みはありませんでした。

フォルシアでは複雑な検索クエリを書く機会が多いため、参加するエンジニアはある程度SQLに習熟していることが想定されます。
そのため、課題の性質としてはクエリの書き換えやインデックスの付与といった基本的な要素だけでなく、データパターンやテーブル構造に着目した設計変更を伴うようなチューニングの余地も欲しいところです。
また、フォルシアではデータサイズを圧縮してディスクI/Oを減らす、という観点での改善を頻繁に行います。これはある程度データサイズが大きくないと発生しないボトルネックなので、必然的に課題のデータセットもそれなりの規模が求められます。

このような理由で提供する課題はすこし高い難度に設定したいのですが、これは参加者だけでなく作問者の難易度も大きく引き上げることになります。

膨大なデータは、遅い

「実務に近い膨大なデータを扱う本格的なチューニングコンテスト」と銘打つと聞こえはよいですが、データを大きくすることには色々な問題が伴います。

まず何をするにも時間がかかります。環境構築、ベンチマークの実行、クエリの試行錯誤、インデックスの付与やデータ構造の変更など、ありとあらゆる操作に対して「本格的」な時間が伴うことになります。
データを増やすことで参加者に対してもストレスを与えてしまいますが、作問する側としてもこれは困ったものです。チューニングコンテストである以上初期状態はある程度遅い状態に設定する必要があるため、調整にあたって 遅い状態で何回も試行錯誤を繰り返す必要がある からです。

また、I/Oをボトルネックに絡ませる場合スコアの再現性も低くなります。
特に共有バッファやOSのキャッシュに乗っているか否かで同じクエリでも大きくスコアが変わるため、参加者としてはスコア向上が改善によるものかの把握が難しくなりますし、作問者としても計測ルールの設計時にはスコアがなるべく冪等になるよう配慮する必要があります。

社会人、そんなに暇じゃない

課題難度を上げるにあたって、ある程度長めに考察時間を確保したいです。しかしdevゼミは業務時間内で行われる活動であり、忙しいエンジニアをかき集めて丸一日コンテストを開くようようなことは現実的ではないです。
通常devゼミは1時間枠で行われますが、今回は相談の結果、なるべく多くのエンジニアに参加してもらいつつ腰を据えて考察や改善に取り組んでもらえそうなラインとして2時間枠で設定しました。

逆に言えば課題は 2時間でイテレーションを回せる分量に収める必要がある ということです。なるべく難易度を落とさず2時間で成立する課題にするためには、課題以外の部分を可能な限り削ぎ落す必要があります。

ここで一番のネックになるのは環境構築です。短い説明で全員が簡単にベンチマーク環境を用意する、というのは非常に難しいです。
実際これまでのハンズオン系のdevゼミでも触るまでのステップがどうしても必要となり、その時点で耳だけ参加になってしまう場合も少なくありませんでした。
ましてや今回は巨大なデータセットを伴う課題なので、参加ハードルはより高くなってしまいます。

複雑なクエリは、複雑すぎる

フォルシアの検索アプリでは、しばしば非常に複雑な参照系クエリが求められます。
これは多くの場合、チューニングのエッセンスと複雑なビジネスロジックがSQLに同居していることが原因です。

コンテストの主眼はあくまで手を動かしてチューニングをしてもらうことなので、クエリそのものが難しすぎると読解や改修のコストが上がってしまい、時間がもったいないです。
また計測前には改善の正当性（改善前後で結果が変わらないこと）を担保する必要がありますが、「この改善は劇的な効果があるが、このビジネスロジックが邪魔で採用できない」という場合も多く、こういった自由度を狭めるものはなるべく除外したいです^[1]。
このような理由から、業務で使っているデータとクエリをそのまま流用するというのは好ましくありません。

また、クエリの実行方式にも工夫が必要です。
webサービスではほとんどの場合サーバーアプリがパラメータから動的にクエリを発行します。しかし今回の主眼はSQLであり、アプリケーション側の理解に時間を割くのはもったいないです。
そのためアプリケーションを設けず直接SQLを実行する形式としたいですが、固定のSQLだけを投げるような課題にしてしまうと、作問者が想定している変数を参加者が類推しづらくなったり、逆に過剰な最適化を起こしやすくなったりするため悩ましいところです。

実際にやったこと

上記のような問題を考慮し、課題の難易度を担保しつつなるべくストレスフリーな課題になるよう以下のようなことを考慮して設計しました。

実務のスケール感を意識しつつ、ストレスになりすぎないデータとクエリ

開始地点として、本番環境相当のデータを抽象化したデータセットを用意しました。これは幸いサマーインターン^[2]での高速化課題用に整備して毎年利用しているものがあり、容易に準備できました。
しかしインターン課題は一週間かけて取り組む想定の分量であり、2時間のコンテストにそのまま使うことはできません。したがってこれをベースに簡易化とデータ量の削減を繰り返して調整することにしました^[3]。

クエリに関しては、実務でのシナリオのエッセンスを残しつつ、性能劣化が起きがちなパターンで複数のクエリを用意しました。
一つのクエリに全てを詰め込むのではなく、まずボトルネックをいくつか決めてからそれぞれを踏むようなクエリとして組み立てることで、改善のヒントを残しつつ改善がスコアに反映されやすい設計にできました。

ベンチマーカーは同等のクエリを複数回実行し、その中央値をそのクエリのスコアとする、という形にしました。この方式ではほとんどの場合キャッシュが乗ったケースがスコアとなります。
多くの場合キャッシュがない初回のクエリと2回目以降のクエリには大きな差が出るため、再現性の観点ではキャッシュクリアしたいです。しかし、クリア処理とキャッシュのない状態でのクエリでベンチマークが伸びてしまうという欠点があります。
今回は素の状態だとキャッシュが乗った状態でもI/Oが発生するようなボトルネックを仕込めたので、I/Oボトルネックを意識する要素はそこで見るということにしてベンチマークを速く回すことを重視しました。

チューニングと関係ない領域に割く時間を最小化する

上述の通り環境構築コストはそのまま参入障壁に繋がります。また、各々が環境を用意する方式とコンテスト形式の相性がそもそも悪いので、いっそ環境構築をさせない方針のほうが良いのでは、ということに思い至りました。
結果として、今回はこちらで参加者一人一人の環境を用意して、開始時に配布するという形式をとりました^[4]。

また、前述の通りベンチマークには直接SQLを実行する形式をとりたいです。ここではpsqlのSQL差し替えという機能を活用しました。
SQL差し替えはSQLファイルに書かれているコロン始まりの変数を外から与えることができる機能です。SQLファイルにはあらかじめ想定された変数をコロン始まりで記述し、ベンチマーカーからは複数パターンの変数をセットしてpsqlコマンドを実行する形式にしました。

これによって投げ分けを実現しつつ参加者はSQLファイルとDB本体だけを意識すればよくなり、課題の構造をかなりシンプルにできました。

制作コストを上げすぎない形でインセンティブを設計する

課題の意図としては、各自で単純にどれくらい速度が改善したかを測るだけでも十分ではあります。
しかしせっかく参加者の環境を均一化したので、他の参加者のスコアも簡単に参照できると改善の目標にもなるだろうということで、簡易的なスコアボードを用意することにしました。

コンテスト中のスコアボード

一般的に競技会を開催する場合、不正防止の観点からスコアリングは参加側ではなく主催者側が担う必要があります。
競技プログラミングのようなコードを提出する形式であればコードを主催者に送るだけなのでいくらか単純に考えられますが、今回はISUCONのように実行環境が参加者側にあるため、主催者側でポータルを用意してリクエストに応じてベンチマークを実行する、という仕組みが必要になります。
しかし今回はあくまで内輪向けのコンテストであり、順位に何の権威もなく競技性はあくまでフレーバーです。そのためもっと単純に、各々のインスタンスからスコアを送信できれば十分です。
今回はベンチマークの実行とスコア送信を行うCLIツールを参加者側に用意し、最低限のカンニング防止としてバイナリ化して配置する形としました^[5]。

実際やってみてどうだったか

導入は◎、課題設計は△、スコアリングは×

初手で躓かないように工夫したのもあり、ほとんどの人が動作確認まではスムーズに終えられました。一方で具体的に正の進捗を生めた人は半分以下だったため、導入的な改善ポイントも数点残しておくべきだったかなと感じています。
ただしトータルで見れば時間と難易度のバランスはそこまで悪くはなかったと思います。解きやすさを意識して素直な設計にしていた部分もあり、AIを使うと簡単に見抜かれてしまう、というのは要対策だったかもしれません。

データセットはかなり削りましたが、それでもやや大きかったという感想もありました。この辺りは課題の意図を適切に共有できていなかったというのもありますが、調整の余地はありそうに感じました。

想定外の失敗としては、スコアリングをあくまでフレーバーとして適当に設計してしまった結果、特定の問題に気付けるか否かでスコアに100倍くらいの差を生んでしまった点にあります^[6]。
スコアリングルールを用意した時点で、参加者にとっては「スコアが高い＝評価が高い」ということになります。
一方で課題の趣旨としては（もちろん効果の高い改善を優先すべきですが）色々なボトルネックに対してそれぞれ解決策を考えてほしい、という意図があります。
そのため特定のボトルネックが過剰に評価されてしまう（逆にその他のボトルネックが過小評価されてしまう）というのは少し意図しない方向への誘導になってしまっていたように思います。
これに関しては用意するならちゃんと責任をもって監修すべき、という他にありません。

AI対策に関して

今回はコンテスト中のAI使用に関して特に制限は設けませんでした。実際にチューニングを行う場面であればAIを活用して解決策を練るのは当たり前であり、むしろいかに活用するか、という観点も重要になってくると考えているからです。
はい、すみません。これは後付けで、作問時はこのことをあまり考えていませんでした。その結果前述の穴をさっさとAIに見抜かれてしまい、早々に過剰なスコアが出てしまったところに若干の悔しさはあります。

ただ、いかに活用するかが重要というのは本当に思っていて、作問にも雑なAI活用を咎めるような要素を入れられると面白そうだなと感じました。
今回は例えば制約に表れないデータパターン（例えばbigint型だが実際に入っている値はたかだか1桁）もいくつかあり、こういったものに暗黙の仮定を置いた高速化も（妥当性を説明できれば）許容していました。
このあたりの判断も含めて、工夫次第でAIへの問い方によっても差がつくような作問にすることはできそうだなと思います。

感想

色々と粗削りな部分もありましたが、実際に参加いただいた方の多くから楽しかったとフィードバックをいただけたのは良かった点です^[7]。
今回で持っていた構想を出し尽くしてしまったので次回の開催計画はありませんが、コンテスト形式の企画という観点で参考にできる部分はたくさんあったので、また異なる題材で開くとなった時はより良い形で取り組めるのではないかと思います。

この記事を書いた人

吉田 侑弥
2020年新卒入社
devゼミ自体は2時間で終了しましたが、コンテストは私が総合スコアで1位を取るまで延長しました。5時間くらい。

脚注

1. 実業務においても性能改善のために機能の妥協が必要なことは多くあるので、何を落とせば速くできるのか、といった議論をするような方向性のハンズオンも検討できるかもしれません。

2. 過去のサマーインターンに関する記事はこちらを参照ください https://www.forcia.com/blog/jobs/Internship/

3. このあたりは完全に勘で、最終的には雑にデータの2/3を消すことで対策としました。

4. 社内のIaCとしてはAWS CDKが主流ですが、これを機に初めてterraformを用いた環境構築を試してみました。なおコードはほぼAI生成のため何も習熟できていません。

5. 実はAMIからソースコードを消し忘れていたのは、墓場まで持っていく秘密です。

6. 初期値のk倍になったらk点という問題を3問作り、kの合計をトータルスコアとするというルールで、問題2がすぐ100倍速くらいになってしまい問題1と3の地道な改善があまりスコアに影響しない形になってしまいました

7. ところで学びにはなりましたか、、？