はじめに
こんにちは、転職会議の開発をしているエンジニアの yamitani です。
転職会議のサイトの歴史はもう少しで 10 年を迎えます。
サイトの運用期間が長いため、いくつかの負債を抱えています。
コードベースの負債は解消されやすいのですが、DB 周りの負債は解消されにくいです。
今回は 2 年間に渡りエンジニア工数の 10％を利用して、DB を少しずつ改善してきたことについて記載します。
改善にはそれぞれメリット・デメリットがあります。サイト規模や運用状況によってはコストが見合わない場合もありますので参考にする際はご注意ください。
※ この記事の DB とは RDB MySQL を指しています。
エンジニア工数10％制度の技術投資枠とは
まずはじめに、弊社の制度を少しだけ紹介させてください。
リブセンスではエンジニア工数の 10％以上を技術投資枠にできる制度があります。
工数のすべてを目の前の、事業要請にしたがって開発や運用に工数を割いてしまうと、長期的にメンテナンス性や生産性を損なうため、最低限 10％の工数はエンジニア観点での改善に投資するルールを設けています。
以下は一部ルールの抜粋です。
- どのような効果を目指しているか/生み出しているかは各事業内で報告する
- 原則として何に 10％工数を活用するかはエンジニア発案とする
- 10％はあくまで下限。10％で十分かどうかはプロダクトや事業の状況によるので、必要に応じて対話する
- 技術的負債の返却もこの投資枠に含める
今回はこの 10％の一部を使って改善業務をしています。
なぜDB改善をやろうと思ったか
DB のテーブルの利用状況や用途に詳しいため、お問合せや相談・質問がよくされます。質問される中で、未使用なテーブルのことについての問合せや質問などがあります。そもそも「問合せ」という行為をさせてしまうこと自体が無駄です。また、歴史を知っている人が少ないので、歴史を知らなくてもよい状態にします。
コードは断捨離されて改善されるのですが、DB 周りの問題はなかなか解消されません。自分としても DB の改善作業はやったことがなかったので改善してみたかったというのもあります。
プロジェクトを立ち上げるほどの緊急度は高くありませんが、効率が悪くなってきているので改善します。プロジェクトが立ち上がるレベルの緊急度だと遅いです。そうならないうちに少しずつ改善していきます。
振り返ってみて
振り返ってみると、当初思っていたものとは違う効果も出てきているようです。
- テーブルに関するお問合せが大きく減りました。 使用頻度の高いテーブルの比率が多くなったので、他のメンバーも答えることがでます。
- 未使用テーブルが参照されなくなる
- コードからの参照がなくなる
- データを検証する作業のときに、うっかり利用されるというが減りました
- 古いコードや機能の断捨離による開発作業の効率化
- DB のバージョンアップ作業の負荷軽減
- 移行時に検証するテーブルが減り、作業の短縮ができました
- リブセンスでは営業さん・CS さんも普通に SQL を書いて実行するので、取り扱うテーブルが整備され、欲しい情報がみつけやすくなりました
やったこと概要
この 2 年間で対応した主なことは以下です。
- DB の資料整備
- DB 肥大化の調査
- テーブルの DROP 作業
DBの資料整備
問題点
- 全体像がわからない
- ER 図などそんなものはない
- 情報の検索性
- 個々人で必要に応じて調査しているので、調査の無駄が多い
- メンテナンス性
- 設計資料が継続的にメンテナンスされない
改善の目標
- 全体像がわかる
- 自動生成される
- そこそこの資料ができる（完璧を求めない）
やったこと
schemaspy を導入して、継続的に資料がメンテナンスされる仕組みを構築しました。
schemaspy は DB の Schema 情報を読み取り、自動でテーブル構造やカラム、PK/FK などの定義を HTML ドキュメントとして吐き出してくれるツールです。
ER 図を生成するなど、大変便利でした。
転職会議では、migration ファイルを管理しており、CI で migraion ファイルからテストDB用の Docker イメージの生成をします。
Docker の イメージを生成処理の延長で、資料が自動生成されるように対応しました。
migration ファイルの更新 ＝ 資料が自動的にメンテナンスされるという形になりました。
テーブルのコメントを適当に書くことがありましたが、今回の対応によりコメントを書くことの重要性が増し、資料が生成される以外のメリットも生まれました。
以下処理の流れです。
- CI 起動
- migration ファイルでテストDB用 Docker イメージを構築
- schemaspy でテスト用イメージから HTML を生成
- S3 にアップロード
※ S3 にはバケットポリシーを指定して、社内からのみアクセス可能にしている。
DB肥大化の調査
サイト規模の割にテーブルが多かったので、なぜ肥大化しているのか把握することにしました。
DB の状態を知るにはサイトの歴史から学ぶのが最も効率的です。
肥大化原因
大きく分類すると、以下の問題がありました。
- 検索系のテーブルの存在
- ログ系のテーブルの存在
- 計測系のテーブルの存在
- 複数のマイクロサービスでの共有 DB
肥大化の主な背景
サイト規模が小さい間はアプリケーションが１つと DB が 1 つで対応していた時代がありました。
サイト規模が小さいうちは DB 一本の方が開発、管理、運用がしやすかった。
当時はオンプレでの運用でしたので、サーバの確保（リソース）の計画も大変でした。
各問題について掘り下げ
検索系テーブル
存在理由
求人や企業の情報を取得する際に、検索パフォーマンス改善のためにテーブル検索を効率的に行う検索に特化した専用のテーブルが生成された。
Solr や Elasticsearch の全文検索エンジンのシステム構築や運用コストを考えても DB 一本での運用の方がコストが低い。
また、当時は検索クエリ、パフォーマンスも DB のみで十分対応できていた。
対応
検索に特化したシステムの全文検索エンジン Elasticsearch に乗り換えました。
乗換えといっても、DB の肥大化とは別の文脈で移行しました。 検索精度・検索性能の問題がでてきており、Elasticsearch に移行する計画が上がっていたのです。
ですので、特別な対応は必要がなく、時間が解決してくる問題でした。
Elasticsearch に移行が完了した後は、該当テーブルを削除しましたので、対応としては完了です。
ログ系テーブル
存在理由
ユーザーのアクセス情報を記録しており、閲覧履歴など時系列の情報を取り扱っていました。
すべての情報を保存できるしているわけではなく、最終のアクセス履歴、閲覧履歴の情報をそれぞれ保存している感じでした。
当時は分析基盤が存在せず、各メディアが独自にデータを保持している形でした。
対応
時系列情報の取扱いの向いている DB に移行する。
リブセンスには Livesense Analytics という分析基盤が存在しています。
ユーザーのアクセス履歴や、イベント実行を記録するための基盤が用意されています。
分析基盤が便利で、ここ数年はそちらに時系列データやイベントデータをためています。
もはや、テーブルだけが存在している状態でしたので、該当テーブルを削除して対応完了でした。
計測系テーブル
存在理由
バッチで集計したデータを DB に保存して管理画面で集計結果をチェックしていました。
管理画面ではユーザーの退会数、月次の MAU、企業ごとのアクセス数など分析・監視の用途で利用されていました。
データに関しては日次・週次・月次のバッチで生成されています。
当時は分析基盤がそもそも存在しませんでした。
解消方法
分析用 DB を用意して、BI ツールを利用する。
Livesense Analytics や分析専用の DB を用意して、BI ツールの Redash を利用しています。
その方が分析の柔軟性が上がり、わざわざエンジニアに頼んで管理画面のシステムを改修する必要がないです。
そもそも、リブセンスでは営業さん・CS さんも SQL を普通にたたくので、分析に特化した管理画面の必要性がなくなりました。
既に、Redash は導入済みでしたので、該当テーブルを削除して対応完了でした。
DBの共有問題
先の 3 点は対応を検討する前に問題は解決済みで、テーブルを削除するだけでしたが、DB の共有問題は現在でも解決されていません。
発生理由
モノリシックなサービスからマイクロサービス化を推進するにあたり、共用 DB の構成にする対応を取りました。
当時はデメリットよりもメリットの方が大きかったのでそこまで問題を感じませんでした。
移行コストの問題もあり、DB の分割は行わずマイクロサービス化しました。
対応
理想は 1 アプリケーション 1DB ですが、移行コスト・運用コスト・開発効率の問題もあります。
DB を分割するとしてどの粒度で分割するのがよいのか、解決策は見つかっていませんし、このあたりは模索しながらやっていくことになるでしょう。
ですので、共有 DB でも利用されていないテーブルの削除作業からはじめ、分割するときに少しずつ楽になるように改善することにしました。
テーブルDROP
調査結果を元にどのような用途で利用されていたのかを調べれば、削除可能かどうかの判断が楽になります。
後はどれだけ、削除可能かの判断材料を増やしていくかだけになります。
利用状況の確認
これは安全に削除できるかどうかの判断材料の追加です。
いくつか確認方法を検討したうえで、私は 2 つを削除可能かどうかの判断材料に加えました。
CHECKSUMの比較
サイト運用していると、大体はデータの追加や更新があります。
そこで、テーブルの CHECKSUM を取得しておき、1ヵ月後に再度 CHECKSUM を実行します。
マスタ系のテーブル以外で全く値が変わらないテーブルはデータ更新されていないと判断して、 断捨離できる対象確率が高くなります。
テーブルのレコード数
こちらは判断材料としては弱いですが、機能の棚卸しで活躍しました。
CHECKSUM と同じような形でテーブルのレコード数を取得しておき、1ヵ月後に再度レコード数を取得します。
レコードの追加差分が少ない場合、機能ベースで断捨離を検討すると確率がよくなります。
ボツ案（query logの解析）
今回は利用しませんでした。 片手間で対応するのには準備や本番作業が大変ですし、パフォーマンスが劣化して本番影響がでないかなど検討事項が多くてやめました。
もしやるとしたら、本番の DB にログを出力する設定を仕込んでクエリログを取得。
取得したクエリログを用いて、テーブルの利用状況を詳細に分析する予定でした。
テーブル削除作業
基本的な流れとして、フロント、サーバサイド、DB の順番で削除対応していきます。
私の場合、ステージング環境の DB で毎朝テーブルの DROP 作業をします。1 週間から 2 週間ほど問題が発生しないか様子を見ます。
この期間で特に問題が発生しなければ、クリティカルな問題は発生する確率が低いと判断します。
実際にテーブルを削除するときは、削除するテーブルの特定を考えなら、どこまで安全側に倒して作業をするか判断しています。
※ テーブルを削除する際は、すぐ復旧できるようにテーブルの DUMP は必ず取得しておきましょう。
テーブル削除結果
全部で約 90 のテーブルを削除しました。
他にもいくつか消せそうなテーブルがありましたが、効率が悪くなってきたため、テーブル削除をやめて次のステップを目指すことにしました。
次なる施策・対応
次なる施策・対応として以下を検討しています。
- 継続的にDBをメンテナンスしてくための仕組みの検討
- DB 共通参照問題の解決
終わりに
日々、少しずつ改善していくことでおおがかりに対応する必要がなく、快適かつ効率よく開発を進めていけます。
短期で効果がでることを求める人がいますが、ちょっとしたことを少しずつでも続けることが大切です。
プロダクト開発は長く続きます。短期・中期・長期それぞれの視点を持って、バランスよく開発していきましょう。
