概要
ある小売向けテクノロジー企業では、特定の変更が原因とは言えないまま、18か月にわたって応答時間がじわじわと悪化していました。提案されていた対策はハードウェアの刷新でした。AceMQは、本当にハードウェアが制約になっているのかを見極めるために依頼を受けました。
課題
緩やかな劣化に、原因が1つだけということはまずありません。データ量の増加に伴ってクエリの実行計画が変わり、頻繁に更新されるいくつかのテーブルには大きな肥大化が生じ、アプリケーションはサーバーのメモリで効率よく処理できる数をはるかに超えるコネクションを開いていました。これらの影響を切り分けるには、目視の確認ではなく計測が必要でした。
環境
オンプレミスとクラウドのホストにまたがるPostgreSQL。受注管理と在庫のサービスを支えています。
アプローチ
AceMQは累積のステートメント統計を使い、個々の実行時間ではなく合計で消費した時間でクエリを順位付けしました。これにより、最も遅いクエリから、実際にクラスターのリソースを消費していたクエリへと注目が移りました。肥大化は直接計測し、コネクションの挙動はアプリケーションの設定から推測するのではなく、プーラーでプロファイリングしました。
ソリューション
- クエリのパターンを合計実行時間で順位付けし、負荷の大半を占めていた高頻度のクエリを明らかに
- テーブルとインデックスの実際の肥大化を計測し、その背景にある更新パターンとブロックされたVACUUMの実行を特定
- インデックスのカバー範囲、冗長なインデックス、プランナーに一度も選ばれないインデックスを確認
- コネクション数とプーラーの挙動を、work_memとshared_buffersのサイジングと照らし合わせてプロファイリング
- 実行計画が不安定なテーブルについて、プランナーの統計ターゲットと自動ANALYZEの適用範囲を評価
- 設定の修正、スキーマの改善、本当のキャパシティの限界を区別した、優先順位付きの調査結果レポートを提供
結果
アセスメントにより、制約はハードウェアではなくクエリと肥大化の挙動にあることが明らかになり、ハードウェアの刷新は見送られました。上位の調査結果に対処したことで、新たなキャパシティを追加せずに、応答時間は以前のベースラインに近い水準まで戻りました。