【データベース】統計情報の更新【第3回】

Transcript

1. 【データベース】 統計情報の更新方法
2. 目次 1. クエリが実行される流れ 2. 更新のタイミングとトリガー 3. 手動 vs 自動の決定 4. 更新頻度の最適化 5. 更新失敗時のトラブルシューティング 6. 各製品のメンテナンスツール（コマンド） 7. 私が実務で実施した自動更新の例 8. まとめ
3. 1.クエリが実行される流れ SELECT * FROM 購入明細 WHERE CUSTOMER_ID = ‘C’ パーサ （構文解析） リライタ （書換） プランナ （オプティマイザ） エグゼ キュータ 実行結果 きゃ 実行 計画 統計 情報 適切な実行計画がない場合 統計情報を使用し 実行計画を作成 テーブル 実行計画に従い データアクセス 適切な 実行計画を取得 しに行く
4. 1.クエリが実行される流れ • 実行計画とは ◦ DBMSがクエリを最適に実行するための手順書 ▪ どのインデックスを使用するか ▪ どのテーブルをどの順番で結合するか ▪ テーブルスキャンorインデックススキャン ▪ 等
5. 1.クエリが実行される流れ • 統計情報とは ◦ 実行計画を作成するための材料 ◦ データの量や分布に関するメタ情報（ヒストグラム） ▪ 最大値 ▪ 最小値 ▪ NULLの数 ▪ 重複の値とその数 ▪ 等 ◦ 統計情報が最新なら正確な実行計画を作成できる ◦ 古くなるとパフォーマンスに悪影響を与える 　　　⇨以降のスライドでは統計情報が影響する範囲を解説
6. 2.更新のタイミングとトリガー • 更新のタイミング ◦ 定期的な更新 ▪ 夜間バッチ処理後やメンテナンスウィンドウ中に実施。 ◦ 大量データの変更後 ▪ データの大幅な変更があった場合に更新。 ◦ オンデマンド更新 ▪ パフォーマンス問題が発生した際に即時更新。 • トリガー（更新のきっかけ） ◦ データ変更割合 ▪ テーブルの10%以上が変更されたときに自動更新。 ◦ イベント発生後 ▪ バッチ処理やデータ移行後に更新。 ◦ パフォーマンス低下時 ▪ クエリパフォーマンスが低下した際に手動で更新。
7. 3.手動 vs 自動の決定 • 自動更新 ◦ メリット ▪ 定期的に自動更新、管理の手間が少ない ◦ 選択基準 ▪ データ変更が少なく、システム全体を自動管理したい場合 • 手動更新 ◦ メリット ▪ 柔軟なタイミングで更新、特定テーブルの優先対応が可能 ◦ 選択基準 ▪ バッチ処理後や業務中の即応対応が必要な場合 • 手動と自動の併用 ◦ 通常は自動更新、重要な場面では手動更新でパフォーマンス最適化
8. 3.手動 vs 自動の決定 • 各RDSの自動更新の有無と発動条件 ◦ Amazon RDS for Oracle ▪ 自動更新あり ▪ デフォルトで10%以上のデータ変更、夜間に自動更新 データの追加 ・更新 夜間にトリ ガー条件 確認 トリガー条件 10％ 10％以上変更が あるか？ リソース使 用状況確 認 負荷が高すぎない か？ 自動更新 開始 統計情報更新中 最新の統計情報 利用可能 更新完了 パフォーマン ス改善 SUCCESS
9. 3.手動 vs 自動の決定 • 各RDSの自動更新の有無と発動条件 ◦ 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%以上の行が変更されたときに自動更新
10. 4.更新頻度の最適化 統計情報の更新タイミングと頻度を最適化することでシステムパフォーマンスを維持。 リソース消費を抑えながらクエリパフォーマンスを向上させ、システムの安定性を確保。 • 頻繁なデータ更新が行われる場合 ◦ 統計情報の更新頻度を高め、クエリパフォーマンスの低下を防ぐ。 • 少ないデータ更新が行われる場合 ◦ 統計情報の更新頻度を下げてリソース消費を抑える。 • リソース負荷を考慮する ◦ バッチ処理後やシステムに余裕がある時間に統計情報を更新。 ◦ 他の業務に影響を与えないようにする。 • パフォーマンスモニタリングを監視し任意のタイミングで最適化 ◦ クエリ実行計画やモニタリングツールを使用し、統計情報の更新が適切に行われている か確認。必要に応じて更新頻度を調整する。
11. 5.更新失敗時のトラブルシューティング 統計情報の更新が失敗すると、 システムパフォーマンスの低下や非効率なクエリ実行が発生する可能性がある。 • エラーログの確認 ◦ エラー内容を特定し、問題を把握。 • リソース確認 ◦ CPU、メモリ、ディスク容量不足の確認。 • ロック確認 ◦ テーブルロックが原因であれば解除。 • ディスクスペース確認 ◦ 必要な容量が確保されているか確認。 • 手動で再実行 ◦ 自動更新が失敗した場合、手動で更新。 • 通知設定 ◦ 更新失敗時の通知を設定し、早期対応。
12. 6.各製品のメンテナンスツール（コマンド）AmazonRDS • PostgreSQL ◦ ANALYZE ▪ 例：ANALYZE; -- 全テーブルの統計情報を更新 ▪ 例：ANALYZE my_table; -- 特定のテーブルを更新 • MySQL / MariaDB ◦ ANALYZE TABLE ▪ 例：ANALYZE TABLE my_table; -- 特定のテーブルの統計情報を更新 • Oracle ◦ DBMS_STATS ▪ 例：BEGIN DBMS_STATS.GATHER_TABLE_STATS('my_schema', 'my_table'); END; ▪ ※OracleのANALYZEは9iから非推奨。 • SQL Server / Azure SQL Database ◦ UPDATE STATISTICS ▪ 例：UPDATE STATISTICS my_table; -- 特定のテーブルの統計情報を更新
13. 7.私が実務で実施した自動更新の例 • 背景と課題 ◦ データ増加により、Oracleの自動統計情報更新（10%以上の変更）が追いつかず、SQL クエリの遅延が発生。 ◦ 手動で頻繁に統計情報を更新する必要があり、過去に業務影響も出ていたため、 Oracleに依存しない対応が必要だった。 • 提案と対応策 ◦ 自動化タスクの作成 ▪ 夜間に統計情報を自動更新するタスクを作成。夜間バッチや業務時間に影響を与 えないために、独自タスクを採用。 ◦ テーブル管理の効率化 ▪ マスタテーブルで対象テーブルを管理し、お客さまの業務に合わせた柔軟なスケ ジュール設定を実現。
14. 7.私が実務で実施した自動更新の例 • リリース後のモニタリングと改善 ◦ 統計情報の更新状況を毎日確認し、 RDSの負荷状況を監視。 ◦ 自動化により、手動作業を減らし業務影響を最小限に。 • 結果と今後の展開 ◦ SQLの遅延が大幅に改善。他チームでも同様の処理が検討されている。 ◦ データ増加に対応するため、パーティション化やサンプリングも検討中。
15. 7.私が実務で実施した自動更新の例 設定した時間になっ たら処理開始 マスタテーブルに定義した テーブル名を指定の順番 通りに取得 テーブル名を一件ずつ取 り出して統計情報更新 更新成功 OR 同じテーブルが2回以上更 新失敗 終了 成功。 次のテーブル を取得し統計 情報更新。 1回目の失敗。 ログ出力。 再度同じテーブルに 対し統計情報更新 ざっくり フローチャート
16. 9.まとめ • 統計情報の更新は、クエリパフォーマンスとシステム安定性の 維持に重要。 • 自動更新と手動更新を使い分けることで、柔軟で効率的な運 用が可能。 • 実務での自動化タスクにより、SQL遅延が大幅に改善。 • 今後も業務に応じた最適な運用方法を検討していく。