原稿反映処理の性能劣化を食い止めるため、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分に変わっただけ。「なぜだ……」森廣氏は再度調査に乗り出した。
技術負債の巣窟に到達……最終的に実行時間を15分に短縮！
オラクル製品の管理ツール「Oracle Enterprise Manager」でSQLパフォーマンスを詳細調査したところ、データ読み込み待機イベント「DB File Sequential Read」が処理の大半を占めていることが分かった。Oracle Real Application Clusters（Oracle RAC）構成では、サーバーに対してSQLを発行する際、他サーバーに同様の問い合わせデータのキャッシュがないか先に確認する機能がある。ディスクのI/O負荷を軽減するためのものだが、このときキャッシュの一貫性を保つのが「キャッシュフュージョン技術」だ。
実は今回、Solrインプットファイル作成のバッチを並列化したところ、SQLの問い合わせ先が複数台になってしまった。例えば、サーバー1に対してバッチ処理が最初に走った場合、データはサーバー1に展開される。このときサーバー2・3に対してバッチ処理が行われると、データ転送したくてもサーバー1ではまだ処理が継続しているため、サーバー2と3は待機状態になる。このロック状態が長引くと、サーバー1はデータの変更の有無を確認、変更がないときは直接ディスクからデータを読み込むようサーバー2と3に命令を送る。その結果、ディスクへのDB File Sequential Readが発生し、性能が低下したということだ。
「対策は、無理やり擬似的なマスタースレーブ構成をとって、キャッシュフュージョンを削減。サーバー間のデータブロック転送回数を減らしつつ速くする試みだ。変更したところ、特にUPDATEとDELETEで効果が出た」
それでもなお、DB File Sequential Readは減らない。データフローをすべて洗い出して理由を探った森廣氏は、とうとう「技術負債の巣窟」に行き当たった。
「情報一覧を表示させたいなどの新規案件に対応するとき、前段部分のバッチを複数改修すると工数がかかるため、とりあえず中間テーブルを作成して対応。後で改修すればいいやと思っていたと推測する」と森廣氏。気付けば中間テーブルは大量に増殖していた。
そんな中間テーブルのバッチが原稿テーブルに対して大量データを取得するべく、長時間ロックをかけるようなSQLを発行。原稿テーブルは、タウンワークの詳細画面の表示でオンラインから逐次アクセスを受けるテーブルでもあり、つまりはオンラインとオフラインの双方からアクセスが集中していたことになる。DB File Sequential Readの原因がここにあるのは、明白だ。
「とにかく至るところを削除しまくって整理した」と、大胆にメスを振るった森廣氏。最終的には、原稿テーブルへのアクセスを最小限に抑え、オンラインとオフラインとでアクセス先のテーブルを分離したり、バッチ処理を機能ごとに分けたりと整理。こうして実行時間は2時間から15分に短縮された。「タウンワークの詳細画面のレスポンスも向上したのは、棚ぼただった」と森廣氏は笑う。
品質担保については、本番環境の処理前と処理後のデータをテスト環境にコピーし、処理後のJOBネットを流した結果を比較。また、オフショアで1週間分のデータ比較を実施した。システムが複雑すぎて商品も多く、処理後のデータも膨大、知識がある人も限られていることなどから、テスト環境を準備するやり方はとらなかった。
現在、掲載数は依然として増加しているが、処理時間は平均4時間ほどまでに短縮。障害も未だ発生していないという。
また、もう1つうれしい「棚ぼた」もあった。深夜のバッチ処理の遅延監視結果は毎朝5時に運用チームへ通知される設定になっているのだが、「6週連続『モーニングコール』がかかっていたのが、改善後の2018年6月以降は一度もコールがない」という。
「レガシーシステムの改善はリビルドやリプレイスに頼りがちだが、泥臭い作業の積み重ねでも十二分に成果を引き出せる」と森廣氏。「システム単体で改善するのもいいが、（今回のケースのように）違う角度から見つめ直せば技術負債が見つかることもある。業務やインフラ面も踏まえて検討することで、より高い改善効果も期待できるので、ぜひ試してもらいたい」
お問い合わせ
株式会社リクルート
- career_pr@r.recruit.co.jp（担当：岡田）
