データベースのチューニング運用について考える

こんにちは！

ROBOT PAYMENT サブスクペイのシステム基盤チームの yoponpon です。
システム基盤チームではアプリケーションやシステムインフラの基礎部分の構築や管理、メンテナンス、性能改善などを行っています。

今回は、データベース（Microsoft SQL Server）のチューニング運用について考えていきたいと思います。

はじめに

データベースのチューニングというキーワードを考えた時にその手段や目的は様々かと思います。

例えば目的で考えた時

慢性的なデータベースのパフォーマンス問題に対策を打ちたい
突発的に発生したデータベースの高負荷に対策をしたい
定常的な運用でデータベースの安定性を保ちたい

など、（その他諸々）があり、それに対する手段も色々かと思います例えば以下です。

ハード面の強化（CPU、メモリ、ストレージ等）
内部のチューニング（データ構造、インデックス等）
用法の見直し（クエリ、データの読み出し方、書き方、頻度を見直す）
別のストレージの検討など

弊社では現在、データベースに対しての定常的な運用を検討しております。

これまでもデータ構造の見直しやインデックスの修正、断片化の解消など、何かしらの出来事の発生ベースでデータベースチューニングを行っておりました。ただ、これだけでは発生した問題には対処できるものの今後発生しうる問題への事前の対処などができていない状況となってしまい、計画性を持ったシステム運用を阻んでしまう可能性があります。

では「今後発生しうる問題に対処するためのアクションとして何をするべきなのか？」という点について考察した内容を今回は軽くまとめさせていただきました。（一旦DBに限定して考察しています）

目的・目標について考えた

先述の通り「データベースのチューニング」というキーワードだけでは目的も手段も明確ではありません。まずは、何が現在問題なのかを考えました。

最初に直近のデータベースで発生した問題の洗い出しや、現在のデータベースの状態を参照しました。

データベース周り、パフォーマンス周りでどのような問題が発生していたか調べた
データベースの状態を調べた
- CPU、メモリ、ディスク利用量などの直近1ヶ月くらいの推移を見た
- 動的管理ビューなどで重たいクエリや実行頻度等を調べた
- インデックスの断片化状況やディスク利用率を調べた
（など見るべき場所はまだあるかもしれないですが一旦これくらいにしました）

なお、これらの調査は以下のドキュメントを軸に調べました。

learn.microsoft.com

調査結果

調べた結果ですが

慢性的なパフォーマンスの問題が起きているというよりピンポイントや特定の機能などで発生している点が見受けられました。
CPU、メモリ、ディスク利用量などは安定しており慢性的なハード不足は見受けられませんでした。（慢性的に重たい場合はスペックアップ等を行っていたため）
クエリの調査でピンポイントで重たいクエリが検知されたり、インデックスの断片化が見受けられました。

今後のプランについて考察

まずは重たいクエリへの対処をと考えたのですが、重たいクエリがなぜ重たいのか？から考える必要がありました。
どういうことかというと、クエリが重たくなる理由としては以下が考えられるからです。（他にもあるかも）

①データの構造や呼び出し方の効率がよくない
②必要なインデックスや付加列が不足している
③クエリ実行プランが効率的でない

①、②が原因であればアプリケーションの修正やインデックスの見直しで解決できるかと思います。

③が原因であった場合は、インデックス断片化の改善、統計情報の更新が必要になるかと思います。
インデックスの断片化や統計情報はクエリ・オプティマイザーの挙動に影響を与えるため、それらが最新ではない場合に効率的ではないクエリ実行プランが生成される可能性があります。

なお、このあたりを参考にしました。 learn.microsoft.com

チーム内で相談

重たいクエリが複数検知された上でそれらの対処を考えた時に、インデックスの断片化や古い統計情報がある状態では修正自体の妥当性が検証できない可能性がありました。なぜならば実行プランが新しい統計情報をもとに生成されていない為、実際のデータに対して効率的な実行計画が生成されていない可能性があるからです。なので、まずはインデックスの断片化率を一定値まで下げる運用をして必要なタイミングで統計情報を更新していこうという方向の話になりました。

チーム内で考えた今後の計画は以下のような感じです。

①すべてのテーブルでインデックス断片化を特定比率まで下げる
②統計情報の更新頻度、インデックスの断片化修正を定期的に実行する運用を設ける
③重たいクエリを定期的に棚卸しして修正計画を検討する