原稿反映処理の性能劣化を食い止めるため、SQLチューニングに着手
短期・長期のアルバイト・パート情報を扱う「タウンワーク」は、全国の最新の求人情報をフリーペーパーとネットの両方で提供する総合情報サイトだ。最初のフリーペーパーは1998年発行と長い歴史があり、多くの広告主と求人者のマッチングを支えてきた。そんな同サイトにおいて、広告原稿を受注してから掲載するまでのプロセスはビジネスの根幹であり、正確な情報掲載は必須である。
しかし、その品質を重要視したあまり、新規案件を追加する際の影響範囲を極力閉じようという保守的なマインドに陥り、中間テーブルや類似処理の複製を「新規追加」する方向で対処。その結果、システムは複雑な迷路のまま膨れ上がってしまった。折しもオリンピック需要で掲載数は増加、入稿システム単体に手を入れて個別最適化を試みるも追いつかず、「性能は急激に劣化していった」とリクルートテクノロジーズの森廣隆行氏は明かす。
「なんとかしようと、リプレイスやリビルド、アーキテクチャ刷新も検討した」と森廣氏。しかし、開発期間が長期に及ぶこの手法では機会損失が大きく現実的ではない。悩んだ末、原稿入稿処理フロー全体にメスを入れ、企画やインフラ担当を巻き込みながら全体最適化を目指す決断をした。
原稿情報の入稿ジョブフローを見直した森廣氏は、3つの問題を発見した。
1つ目は、原稿掲載前の原稿データの処理だ。フリーペーパーは月曜に発行されるため、金曜の夕方に入稿を締め切り、そこから印刷、配送を行う。このフローにWebサイト側も引きずられ、毎週日曜夜に1週間分を一気に処理しなければならなかった。原稿反映処理は大きく分けて「新規掲載」「掲載停止」「掲載修正」の3つがあり、特に画像加工やPDF出力、コード変換、連携テーブルへのデータ反映処理を行う「新規掲載」で負担がかかっていた。そこで、こうした編集処理を広告主から入稿があった段階で実施することにした。
「工程は1つ増えるが、処理が分散したことで日曜夜の一括処理が軽くなった」
2つ目は、原稿入稿システムからタウンワークのシステムに取り込む際の処理だ。「両システムの開発チームがあまり仲良くなくて」と森廣氏は笑いを誘いながら、3つの原稿反映処理の連携がとれておらず、「タウンワーク側は原稿テーブルの既存データをDELETEしてから連携テーブルで受け渡されたデータをINSERTしていた」ことを明かす。この無駄をなくすため、新規掲載（INSERT）、掲載停止（UPDATE）、掲載修正（DELETE→INSERT）に処理を分割、高速化を実現した。また、両システムをつなぐ連携テーブルで両チームから削除処理が実行される無駄についても、処理をチームで役割分担して排除した。
3つ目は、オープンソースの全文検索システム「Solr」へのデータ反映処理だ。原稿テーブルや関連テーブルからデータを抽出、加工して中間テーブルに渡し、Solrのインプットファイルを作成してからCSVに吐き出すという一連の処理で遅延が発生。
調査の結果、SQLが重くなっていることが分かり、森廣氏はチューニングをすることにした。件数を減らしてからJOINする、INDEXを再確認してから張り直す、SUMやNOT EXISTSなどの使用関数や参照テーブルを見直すなどを実施した。
もっとも、これらは一筋縄ではいかなかった。「歴史を感じる500行超えのSQL」はネストを繰り返すJOINテーブルが大量に存在し、テーブル自体も大きすぎて読み込みやI/Oコストも急増。そこで森廣氏はネストを減らしながら、条件を絞り込むことでJOINする数も減らしてI/Oコストを削減した。
結果、66万近くあったI/Oコストを約42万にまで減らすことに成功し、さらにSolrのインプットファイル作成の部分を並列化した。
これでなんとかなるか。そう期待した森廣氏だったが、実行時間は2時間から1時間40分に変わっただけ。「なぜだ……」森廣氏は再度調査に乗り出した。
