前回、【第2回】基本から始める Azure Database for PostgreSQL 【PostgreSQL アーキテクチャ 2】で、PostgreSQLの ファイルシステム、メモリアーキテクチャ、追記型アーキテクチャ などについてまとめてみました。
今回は、PostgreSQLの ユーザーアカウント、バックアップ、テーブル メンテナンスの方法などについて、まとめてみようと思います。
※ PostgreSQL (オンプレミス) は、Azure Database for PostgreSQL フレキシブル サーバー で選択可能なバージョン「14」を使用しています。
PostgreSQLのアーキテクチャ 3
PostgreSQLのユーザーアカウント管理
PostgreSQLはロールを使用してデータベースへの接続、オブジェクトの所有、操作権限などを管理することが可能であり、「CREATE ROLE 」コマンドにてロールを作成することが可能となっています。
スーパーユーザー (特権ユーザー) でのみ参照可能な情報、実行可能な関数へのアクセス権限を簡易的に一般ユーザーに付与できるよう、以下のようなデフォルト ロールが用意されています。
PostgreSQLのバックアップ
「pg_dump」コマンドにより、データベース単位でオンライン バックアップを実施することが可能になっています。
※「pg_dumpall」では、データベース クラスタ内の複数データベースのバックアップを採取することが可能となりますが、データベースを跨ぐ一貫性については保証されない点に注意が必要になります。
※「pg_dump」では、特定のデータベース、テーブル単位でのバックアップが可能となり、「pg_dumpall」では、データベース クラスタ内の複数データベースのバックアップを採取することが可能となりますが、データベース クラスタ全体のバックアップを採取する場合は、「pg_basebackup」および「pg_start_backup/pg_stop_backup」を使用することも検討する必要があります。
「pg_basebackup」, 「pg_start_backup/pg_stop_backup」の詳細については、以下のURLを参照。
[補足]
SQL Server のバックアップコマンド 「backup database」の場合、完全バックアップから整合性を保った状態でリストアするために必要となるトランザクションログ情報もバックアップに含まれる動作となり、完全バックアップ完了時間時点のデータの整合性が保証されることになります。
PostgreSQLのログ設定
ログ設定関連のパラメータについてまとめてみます。
- ログの出力先
- ログの出力内容
- その他
【メッセージ レベル】
深刻度 低 (DEBUG) -------------------------------------------------> 深刻度 高 (PANIC)
[補足]
- データベース システムの性能低下、キャパシティ不足を判別する基準として、SQLの実行時間を監視することが有効。
- 遅いクエリの監視のために「log_min_duration_statement」設定する。設定値については、性能要件に応じて決定する。
- 遅いクエリの分析を実施する場合、拡張モジュール「auto_explain」を利用することを検討する。(実行プランの解析を実施)
PostgreSQLのテーブル メンテナンス
主なテーブル メンテナンス
- VACUUM (不要領域の解放)
- VACUUM FULL (不要領域の解放およびテーブルの物理的な圧縮)
- ANALYZE (統計情報の更新)
- REINDEX (インデックスの再構築)
- CLUSTER (テーブルの再作成)
各々のメンテナンスについてまとめてみます。
1. VACUUM (不要領域の解放)
〇 VACUUMの目的
- 不要領域の解放と再利用
・ 更新、削除された行が占有している領域の解放／再利用
・ スキャン性能の維持 (読み取りページ数の削減)
- トランザクション ID の周回防止
・ VACUUMにより トランザクション ID を凍結
- 統計情報の更新
・ 自動VACUUMが有効の場合、ANALYZE を発行し、統計情報を更新
- 可視性マップ (Visibility Map) の更新
・ 不要領域の場所を保持している可視性マップを最新化する。
〇 VACUUMのメカニズム
- 不要領域の列挙
・ 全ヒープページを読み込み、不要領域の⼀覧を作成 (maintenance_work_mem にその一覧を保持)
- 対応するインデックス・エントリの削除
・ 全インデックスページを読み込み、不要領域へのエントリを除去
- 不要領域の除去
・ VACUUM 対象ページを読み込み、不要領域を除去
- テーブル末尾の切り詰め
・ テーブル末尾に有効な⾏が無い場合、その領域を切り詰め、ファイルを縮⼩
〇 VACUUMの実施方法
- AUTOVACUUMによる自動実施を推奨
・ 大規模データベースの場合、ピーク時間帯を避けて、手動でVACUUMを実行することも検討する。
１．AUTOVACUUM : VACUUMの実行を自動的に判断し実施。
２．手動実施
・ システムの負荷が低い時間帯にスケジュールで実施。
・ 大量データの挿入後に手動で実施。
〇 AUTOVACUUM関連のパラメータ
AUTOVACUUMが利用するプロセス、メモリ関連の設定
AUTOVACUUM実行関連の設定
※ 「トランザクションID周回」は、PostgreSQLではデータの状態を判断するため、更新の度に内部的に「トランザクションID」(32bit整数)を割り当てるが、このIDを使い切った場合、先に割り当てられたトランザクションIDを重複して別のデータに割り当ててしまうという現象であり、後からトランザクションIDに割り当てられたデータが参照できなくなってしまうため、トランザクションIDが枯渇しないよう、VACUUMを定期的に実行する必要があります。
〇 AUTOVACUUMの実行タイミング
「autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * タプル数」の不要タプルが発生する度にVACUUMが実行される。
※ autovacuum設定はテーブル毎に設定が可能
2. VACUUM FULL (不要領域の解放およびテーブルの物理的な圧縮)
排他ロックを取得し、テーブルのコピーを新しく書き出すことで、不要領域の解放および物理的にデータベースサイズを縮⼩する処理となります。
〇 VACUUM FULL の注意点
VACUUM FULL は以下の理由により通常時には実行しないことが推奨されています。
- 排他ロックを取得するため VACUUM FULL 実⾏中のテーブル操作が出来ない
- VACUUM FULL は通常の VACUUM に⽐べて処理時間やコストが⼤きい
- VACUUMを適切に使⽤することで不要領域の肥⼤化を防ぐことが可能
3. ANALYZE (統計情報の更新)
プランナが作成する実⾏計画は統計情報に依存するため、最適な実⾏計画を作成するために、定期的に統計情報を更新することが重要。
※ AUTOVACUUMを利用している場合、ANALYZE処理も AUTOVACUUMプロセスの中で実行することが可能。
〇 ANALYZE の運用指針
- AUTOVACUUMによる運用を推奨
- 以下のような状況時には、手動で ANALYZE 処理を実行することを検討
・大規模データの挿入後
・CLUSTER, VACUUM FULL, テーブルの再構築を伴うALTER TABLEコマンド実行後
・関数インデックスの作成後
4. REINDEX (インデックスの再構築)
- インデックスを再構築し、古いインデックスと置き換える処理
- REINDEX では対象のインデックスに対する排他ロックが必要
〇 インデックスの再構築をオンラインで実施
- CREATE INDEX CONCURRENTLY オプションの利⽤
・対象テーブルに対する同時挿⼊、更新、削除を防⽌するようなロックを獲得せずにインデックスを作成可能。インデックスを削除し、本オプションを利⽤してインデックスを作成することにより、ロックを獲得せずにインデックスを再作成可能となります。
- pg_repack の利⽤
・元のインデックス定義に従って、新しいインデックスをCONCURRENTLYオプションを利⽤して作成し、システムカタログを更新し、元のインデックスと新しいインデックスを⼊れ替えることで、排他ロックを保持し続けずにインデックスの再構築が可能となります。
5. CLUSTER (テーブルの再作成)
- インデックスに従ってテーブルを再編成する処理
・再編成と同時にテーブルの不要領域を除去、テーブルサイズの縮⼩
・インデックスの再構築も同時に実施
・対象テーブルの排他ロックが必要
※ テーブル断⽚化による性能低下に対処したい場合に利⽤。
〇 CLUSTER の注意点
- 対象テーブルに対して排他ロック(ACCESS EXCLUSIVE)を取得するため、CLUSTER が完了するまで、テーブル操作が出来ない。
※ オンラインでテーブル再構築を⾏いたい場合は pg_repack の利⽤を検討
- レコードの順序が変わるため、CLUSTER実⾏後に該当テーブルへのANALYZE が推奨
- CLUSTER 実⾏には、対象テーブルサイズ＋インデックスサイズと同じディスク領域が必要となる。
※ CLUSTER実⾏の際に、元テーブル、インデックスを保持しながら、新規のテーブル、インデックスを作成。
[まとめ]
今回は、PostgreSQLの ユーザーアカウント、バックアップ、テーブル メンテナンスの方法などについて、まとめてみました。
次回は、Azure Database for PostgreSQL をデプロイし、pgAdmin などのツールから実際に接続する方法についてまとめてみようと思います。
【第4回】基本から始める Azure Database for PostgreSQL 【基本１】 へ
※ 2022年7月現在
