LoginSignup
0
0
@sk8er_boi_shin(しのじ)

データベース統計情報の最適化戦略: 自動更新と手動更新の使い分け

Posted at

データベース: 統計情報の更新方法

目次

  1. 統計情報とは
  2. 更新のタイミングとトリガー
  3. 手動 vs 自動の決定
  4. 更新頻度の最適化
  5. 更新失敗時のトラブルシューティング
  6. 各製品のメンテナンスツール（コマンド）
  7. 私が実務で実施した自動更新の例
  8. まとめ

統計情報とは

統計情報は、データベースに格納されているデータの分布や特性を把握し、クエリの実行計画を最適化するために利用される重要なメタデータです。データベース管理システム（DBMS）は、この統計情報を基に、最適なクエリプランを選定し、パフォーマンスを向上させます。

統計情報に含まれる主な項目

  • ヒストグラム: 特定のカラム内の値の分布や頻度を示すデータ。クエリのフィルタ条件に基づくパフォーマンス最適化に役立ちます。
  • NULLの数: NULL値が含まれているデータの割合を記録し、クエリの最適化に使用されます。
  • 最大値と最小値: 特定カラムのデータの範囲を示し、範囲検索などのクエリパフォーマンスに影響します。
  • 重複データの頻度: 特定のカラムにおける重複値の割合を示し、インデックスの使用効率に影響を与える重要な要素です。

統計情報の重要性

統計情報が最新の状態で維持されていれば、DBMSは効率的なクエリ実行計画を選択することができます。しかし、統計情報が古くなると、非効率なプランが選択され、パフォーマンスが低下するリスクが高まります。

具体例

例えば、インデックスを使用した検索が必要な場合、統計情報に基づいてクエリプランが決定されます。カラムに多くの重複値がある場合、インデックススキャンではなくシーケンシャルスキャンが選択されることもあります。統計情報が適切に更新されていないと、不適切な実行計画が選ばれ、クエリの処理速度が遅くなります。

更新のタイミングとトリガー

更新のタイミング

  • 定期的な更新: 夜間バッチ処理後やメンテナンスウィンドウ中に実施するのが一般的。
  • 大量データの変更後: 大きなデータ変更（10%以上）が発生した場合は統計情報の更新が推奨される。
  • オンデマンド更新: パフォーマンス問題が発生した際には即座に手動で更新を行う。

トリガー

  • データ変更割合: Oracleの場合、テーブルの10%以上が変更されたとき、自動的に統計情報が古くなるとみなされ、更新がトリガーされる。
  • イベント発生後: バッチ処理やデータ移行後など、特定のイベントが発生した際に統計情報の更新が必要になる。
  • パフォーマンス低下時: クエリのパフォーマンスが低下した場合、手動で統計情報を更新する必要がある。

手動 vs 自動の決定

自動更新

  • メリット:

    • 定期的に自動で統計情報を更新できるため、管理の手間が少ない。
    • データベース全体のパフォーマンスを維持でき、定期的なメンテナンスが不要。

  • デメリット:

    • 必ずしも最適なタイミングで更新されるわけではなく、バッチ処理やリソースが逼迫している時間帯に実行されることで、パフォーマンスが悪化する可能性がある。
    • 特定のテーブルに対する細かい対応が難しい。

手動更新

  • メリット:

    • 柔軟なタイミングで更新でき、特定のテーブルやクエリに対して優先的に対応できる。
    • バッチ処理後やシステムリソースが余裕のある時間帯を選んで実行可能で、業務に与える影響を最小限にできる。

  • デメリット:

    • 手動で定期的に更新する必要があるため、管理コストが高くなる。
    • 手動更新を怠ると統計情報が古くなり、パフォーマンスの低下を招くリスクがある。

手動と自動の併用

  • 戦略: 一般的な運用では自動更新を実施し、重要なバッチ処理や業務に応じて手動更新を行うことで、柔軟かつ効率的な運用が可能となる。

スクリーンショット 2024-09-17 22.23.46.png

各RDSの自動更新の有無と発動条件

  • Amazon RDS for Oracle: 自動更新あり。デフォルトで10%以上のデータ変更があった場合、夜間に自動更新。
  • Amazon RDS for PostgreSQL: 自動更新あり。テーブルの20%以上が変更されたときに自動更新。
  • Amazon RDS for MySQL/MariaDB: 自動更新あり。テーブル操作時に自動で統計情報を更新。
  • Amazon RDS for SQL Server: 自動更新あり。20% + 500行以上のデータが変更された場合に自動更新。
  • Azure SQL Database: 自動更新あり。SQL Serverと同様に、20% + 500行以上のデータ変更で自動更新。
  • PostgreSQL: 自動更新あり。テーブルの20%以上の行が変更された場合に自動更新。

更新頻度の最適化

更新頻度の重要性

統計情報の更新は、データベースパフォーマンスを最適に保つための重要な要素です。更新頻度が適切でない場合、パフォーマンスが低下し、リソースを無駄に消費する可能性があります。

頻繁なデータ更新の場合

  • データ変更が頻繁に行われるシステムでは、統計情報を定期的に更新することが重要です。
  • 特に、リアルタイムでデータが増減するeコマースやSNSのようなシステムでは、統計情報の更新頻度を高く保つことで、クエリパフォーマンスを維持します。

データ変更が少ない場合

  • 更新頻度を下げ、リソースを効率的に利用できます。
  • バッチ処理後など、必要なタイミングでのみ統計情報を更新し、無駄なリソース消費を防ぎます。

リソース負荷を考慮した最適化

  • 統計情報の更新はCPU、メモリ、I/Oリソースを消費します。システムに大きな負荷をかけないように、リソースが余裕のある時間帯やバッチ処理後に更新を行うのが理想的です。

パフォーマンスモニタリングと調整

  • モニタリングツールを活用し、統計情報の更新が必要なタイミングを適切に把握し、リソースの消費やパフォーマンスに基づいて更新頻度を調整します。

更新失敗時のトラブルシューティング

エラーログの確認

  • 更新が失敗した場合、まずはエラーログを確認して問題の原因を特定します。DBMS_SCHEDULERなどを使用して自動化されたタスクのログも確認できます。
  • 手順: DBA_SCHEDULER_JOB_LOG や DBA_SCHEDULER_JOB_RUN_DETAILS ビューでエラーを調査し、特定のエラーメッセージや番号を確認。

リソース不足の確認

  • 統計情報の更新にはリソース（CPU、メモリ、I/O）が必要です。リソース不足の状態では更新が失敗する可能性があります。
  • 対策: CPUやメモリの使用率、I/Oの負荷状況をモニタリングし、リソースを追加したり、リソース負荷の少ない時間帯に再実行を行います。

テーブルのロック確認

  • 更新中に別のプロセスがテーブルをロックしていると、統計情報の更新が失敗することがあります。
  • 対策: v$lock ビューでテーブルのロック状況を確認し、必要に応じてロックを解除します。

ディスクスペースの確認

  • 統計情報の更新にはディスクスペースが必要です。スペース不足によって更新が失敗することがあります。
  • 対策: ディスクの空き容量を確認し、不足している場合は不要なファイルを削除するか、ディスク容量を拡張します。

パラメータ設定の見直し

  • autovacuum や ANALYZE のパラメータ設定が不適切だと、統計情報の更新が正しく行われないことがあります。
  • 対策: autovacuum 設定や work_mem の値を確認し、リソースに応じて適切に調整します。

手動での再実行

  • 自動更新が失敗した場合、手動で統計情報を更新することが必要です。DBMS_STATS.GATHER_TABLE_STATS などを使って手動で実行します。
  • コマンド例: 
    BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name');
END;

エラーログの通知設定

  • 更新失敗時にすぐ対応できるよう、エラーが発生した際にはメール通知などを設定しておくことが有効です。これにより、問題が発生しても迅速に対応できます。
  • 対策: Oracleの場合、DBMS_SCHEDULER で通知設定を行い、失敗時にアラートを受け取るようにします。

各製品のメンテナンスツール

Amazon RDS の各種データベースで使用できる、統計情報の更新に使用される主要なメンテナンスツールやコマンドを紹介します。

Amazon RDS for PostgreSQL

  • コマンド: ANALYZE
    • PostgreSQLで統計情報を更新するためのコマンドです。自動的に統計情報を収集しますが、手動での更新も可能です。
    • コマンド例: 
      ANALYZE; -- 全テーブルの統計情報を更新
ANALYZE my_table; -- 特定のテーブルの統計情報を更新

Amazon RDS for MySQL / MariaDB

  • コマンド: ANALYZE TABLE
    • MySQLおよびMariaDBでは、ANALYZE TABLE を使用して統計情報を更新します。インデックス統計情報も再計算され、クエリプランの精度が向上します。
    • コマンド例: 
      ANALYZE TABLE my_table; -- 特定テーブルの統計情報を更新

Amazon RDS for Oracle

  • コマンド: DBMS_STATS
    • Oracleでは、DBMS_STATS パッケージを使って統計情報を収集・更新します。自動的に統計情報が更新されますが、手動での操作も可能です。
    • コマンド例: 
      BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name');
END;

Amazon RDS for SQL Server

  • コマンド: UPDATE STATISTICS
    • SQL Serverでは、UPDATE STATISTICS コマンドを使って統計情報を更新します。これにより、テーブルやインデックスの統計情報が最新の状態に保たれます。
    • コマンド例: 
      UPDATE STATISTICS my_table; -- 特定のテーブルの統計情報を更新

Amazon RDS for Azure SQL Database

  • コマンド: UPDATE STATISTICS
    • Azure SQL DatabaseでもSQL Serverと同様に、UPDATE STATISTICS コマンドを使用して統計情報を更新します。
    • コマンド例: 
      UPDATE STATISTICS my_table; -- 特定テーブルの統計情報を更新

私が実務で実施した自動更新の例

背景と課題

  • Oracleのシステムで、テーブルの10%以上のデータが変更された際に夜間に自動で統計情報が更新される仕組みがありました。
  • しかし、リリース後にデータ量が急激に増加し、1億件のレコードがあるテーブルでは1千万件以上の更新がないと統計情報が更新されない問題が発生。
  • 結果、SQLクエリが正しくインデックスを使用できず、パフォーマンスが低下する事態が発生。手動で頻繁に統計情報の更新を行う必要がありました。
  • また、統計情報が古くなったことでお客さまの業務に支障が出た過去の問題があり、Oracle側のシステムに依存しすぎない対策が必要でした。

提案と対応策

  • 統計情報の更新を効率化するため、部長に提案し承認を得た上で、次の対応を行いました。

    • 自動化タスクの作成: 夜間に自動でANALYZEを実行するタスクを作成し、統計情報を定期的に更新するようにしました。
    • Oracleの標準機能ではなく、自力でタスクを作成した理由は、夜間バッチ処理と統計情報の更新が重なった際に、バッチ処理のパフォーマンスに影響が出るリスクを避けるためです。また、業務時間中に統計情報を更新する際には、スケジューリングではなく、リソースを制御するため自作タスクを使用しました。

  • テーブル管理の効率化:

    • 更新対象テーブルをマスタテーブルで管理し、柔軟にスケジュールを設定可能にしました。これにより、運用の効率を向上させ、状況に応じたタイミングで更新を行うことができました。

リリース後のモニタリングと改善

  • リリース後、統計情報の更新が正しく行われているかを日々モニタリングし、AWS RDSを通じてデータベースの負荷を監視しました。
  • 必要に応じて、手動でも統計情報を更新し、SQLクエリのパフォーマンス低下を防ぎました。これにより、以前は手動で更新が遅れた際に発生していた業務の遅延リスクが大幅に減少しました。

結果と今後の展開

  • 自動化タスクの導入によって、SQLクエリのパフォーマンスが大幅に改善され、手動更新の負担も軽減しました。
  • この取り組みが評価され、他のチームでも同様の処理が検討されています。
  • 今後は、データの急激な増加にも対応できるよう、データをお客様ごとにパーティション化する方法を検討していますが、Oracleのライセンスコストが課題となっています。

フローチャート.png

まとめ

  • 統計情報の更新は、データベースのパフォーマンス維持とシステムの安定性において重要な役割を果たします。特に、大量データが日々更新されるシステムでは、適切なタイミングで統計情報を更新することがクエリパフォーマンスを最大限に引き出す鍵となります。

  • 自動更新と手動更新を併用することで、業務のニーズやシステムの負荷に応じた柔軟な運用が可能です。自動更新はメンテナンスの手間を減らし、手動更新はバッチ処理や特定のテーブルに対して柔軟に対応できるメリットがあります。

  • 実務で導入した自動化タスクによって、SQLクエリの遅延を大幅に削減し、パフォーマンスの向上を実現しました。また、他のチームでも同様の取り組みが検討されるなど、効果が確認されています。

  • 統計情報の更新を効果的に管理することで、システム全体の効率を向上させることが可能です。今後はデータの増加にも柔軟に対応し、より効率的なメンテナンス方法を追求していくことが課題となります。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0