it技術:データベース:postgresql:運用
差分
このページの2つのバージョン間の差分を表示します。
両方とも前のリビジョン前のリビジョン次のリビジョン | 前のリビジョン次のリビジョン両方とも次のリビジョン | ||
it技術:データベース:postgresql:運用 [2020/02/28 18:16] – [トランザクション寿命の確認] yajuadmin | it技術:データベース:postgresql:運用 [2022/02/22 09:13] – [AUTOVACUUM(自動実行)] yajuadmin | ||
---|---|---|---|
行 14: | 行 14: | ||
代表的なチューニング項目を記す。\\ | 代表的なチューニング項目を記す。\\ | ||
pg_confファイルの設定内容を見直す。設定変更後はPostgreSQLを再起動する。\\ | pg_confファイルの設定内容を見直す。設定変更後はPostgreSQLを再起動する。\\ | ||
+ | チューニング設定生成ツール「[[https:// | ||
* [[https:// | * [[https:// | ||
* [[https:// | * [[https:// | ||
* [[https:// | * [[https:// | ||
+ | * [[https:// | ||
行 50: | 行 52: | ||
[[https:// | [[https:// | ||
- | * メモリ上でソートが行えるようwork\_memパラメータを調整 | + | * メモリ上でソートが行えるようwork_memパラメータを調整 |
* work_memはセッションごとに確保される領域であるため、SETコマンドで処理に応じて調整するのが望ましい | * work_memはセッションごとに確保される領域であるため、SETコマンドで処理に応じて調整するのが望ましい | ||
行 74: | 行 76: | ||
^搭載メモリ^推奨値^ | ^搭載メモリ^推奨値^ | ||
- | |2GB|512MB| | + | |2GB|512MB→1GB| |
- | |4GB|1GB| | + | |4GB|1GB→2GB| |
- | |8GB|2GB| | + | |8GB|2GB→4GB| |
- | |16GB|4GB| | + | |16GB|4GB→8GB| |
==== random_page_cost ==== | ==== random_page_cost ==== | ||
行 133: | 行 135: | ||
pg_basebackup -U postgres -D " | pg_basebackup -U postgres -D " | ||
</ | </ | ||
+ | |||
+ | ===== パフォーマンスチューニング ===== | ||
+ | [[https:// | ||
+ | |||
+ | - VACUUMで不要領域を再利用可能な状態にする | ||
+ | - REINDEXでインデックスの不要領域を削除する | ||
+ | - ANALYZEで統計情報を最新化する | ||
+ | - VACUUM FREEZEでトランザクションIDを凍結状態にする | ||
===== データベース初期化 ===== | ===== データベース初期化 ===== | ||
==== 全てのデータベース初期化 ==== | ==== 全てのデータベース初期化 ==== | ||
行 147: | 行 157: | ||
initdb: ディレクトリ " | initdb: ディレクトリ " | ||
- | Userに変更にチェックを追加 することで回避した。\\ | + | Userに変更のチェックを追加 することで回避した。\\ |
他にもrunasコマンドを使用する(データベースクラスタの作成過程でpostgresプロセスを起動しますが、このプロセスは管理者権限では実行できないため)\\ | 他にもrunasコマンドを使用する(データベースクラスタの作成過程でpostgresプロセスを起動しますが、このプロセスは管理者権限では実行できないため)\\ | ||
* [[https:// | * [[https:// | ||
行 244: | 行 254: | ||
<code bash コマンドプロンプト> | <code bash コマンドプロンプト> | ||
> pg_basebackup -U postgres -D " | > pg_basebackup -U postgres -D " | ||
+ | ↓ PostgreSQL12では-xは使えない | ||
+ | > pg_basebackup -U postgres -D " | ||
</ | </ | ||
行 249: | 行 261: | ||
|-D|ベースバックアップ出力先パスを指定。出力先は空でなければならない。 \\ ディレクトリが存在しなければ作ってくれる。| | |-D|ベースバックアップ出力先パスを指定。出力先は空でなければならない。 \\ ディレクトリが存在しなければ作ってくれる。| | ||
|-F t|出力する形式。tはtarで出力する。圧縮するためにはtarの指定が必要| | |-F t|出力する形式。tはtarで出力する。圧縮するためにはtarの指定が必要| | ||
- | |-x|ベースバックアップ処理中、データベースが更新された際もファイルシステム上のファイルは更新しないようにする。出力中はメモリ上で頑張る。 \\ (そうすることで、出力ファイルが変な状態になることを防ぐ)| | + | |-x|ベースバックアップ処理中、データベースが更新された際もファイルシステム上のファイルは更新しないようにする。出力中はメモリ上で頑張る。 \\ (そうすることで、出力ファイルが変な状態になることを防ぐ) |
|-z|gzipで圧縮した状態にする| | |-z|gzipで圧縮した状態にする| | ||
|--checkpoint=fast|fast を指定すると、バックアップ開始時のチェックポイント処理は高速になりますが、集中した I/O のために動作中のアプリケーションへの性能の影響が大きくなります。 spread ではチェックポイントはゆっくり実行されるためアプリケーションへの影響は小さいですが、バックアップに時間がかかります。| | |--checkpoint=fast|fast を指定すると、バックアップ開始時のチェックポイント処理は高速になりますが、集中した I/O のために動作中のアプリケーションへの性能の影響が大きくなります。 spread ではチェックポイントはゆっくり実行されるためアプリケーションへの影響は小さいですが、バックアップに時間がかかります。| | ||
行 261: | 行 273: | ||
</ | </ | ||
- | postgresql.conf の max_wal_sender 設定のデフォルトは 0 です。最低でも 1 以上でないと pg_basebackup コマンドは実行できないので修正します。 | + | postgresql.conf の max_wal_sender 設定のデフォルトは 0 です。最低でも 1 以上でないと pg_basebackup コマンドは実行できないので修正します。※PostgreSQL12ではデフォルトは 10 となった。 |
<code bat postgresql.conf> | <code bat postgresql.conf> | ||
- | max_wal_sender = 1 | + | max_wal_sender = 10 |
</ | </ | ||
行 304: | 行 316: | ||
<code sql> | <code sql> | ||
SELECT pg_switch_xlog(); | SELECT pg_switch_xlog(); | ||
+ | ↓ PostgreSQL10以降ではコマンド名変更 | ||
+ | SELECT pg_switch_wal(); | ||
</ | </ | ||
行 362: | 行 376: | ||
- pg_controlファイルを読み込む | - pg_controlファイルを読み込む | ||
- | - recovery.confを読み込む | + | - recovery.confを読み込む |
- backup_labelを読み込む | - backup_labelを読み込む | ||
- pg_controlファイルを更新し、backup_labelを削除する | - pg_controlファイルを更新し、backup_labelを削除する | ||
行 445: | 行 459: | ||
=== VACUUM FULL === | === VACUUM FULL === | ||
物理的にファイルを圧縮します。 | 物理的にファイルを圧縮します。 | ||
+ | |||
+ | VACUUM FULLは次のような処理を行っています。 | ||
+ | - テーブルのデータを1行ずつ取ってきて、別の新しいテーブルに詰め込む | ||
+ | - 新しいテーブルのインデックスを作成する | ||
+ | - テーブルを入れ替える | ||
+ | つまり、有効な行しか取ってこないので不要領域は一切存在しなくなる。VACUUM FULLは一時的に対象テーブルと新しいテーブルが同時に作成されるため容量不足で完遂できないことがあります。 | ||
排他ロックが必要なため、VACUUM FULL中はテーブルへアクセス不可となります。\\ | 排他ロックが必要なため、VACUUM FULL中はテーブルへアクセス不可となります。\\ | ||
行 462: | 行 482: | ||
|約2400万件|6分| | |約2400万件|6分| | ||
- | **VACUUM FULLを使うのではなく、pg_repackを使う。** | + | **VACUUM FULLを使うのではなく、pg_repack(Windows版は非対応)を使う。** |
==== AUTOVACUUM(自動実行) ==== | ==== AUTOVACUUM(自動実行) ==== | ||
autovacuumは定期的にテーブル状態を監視し、必要があれば自動でVACUUMやANALYZE を実施する機能です。※VACUUM FULLは行わない。\\ | autovacuumは定期的にテーブル状態を監視し、必要があれば自動でVACUUMやANALYZE を実施する機能です。※VACUUM FULLは行わない。\\ | ||
行 482: | 行 501: | ||
* Dead Tupleが各テーブルで定められた閾値を超えた場合(autovacuum_vacuum_threshold) | * Dead Tupleが各テーブルで定められた閾値を超えた場合(autovacuum_vacuum_threshold) | ||
- | AUTOVACUUMデーモンが一定間隔でこれらの状況を監視し、ロック競合が発生しない限り、一定数量のAUTOVACUUM処理が実行される。 | + | **統計情報の再計算**\\ |
+ | 以下の計算式以上のレコードが更新(UPDATE/ | ||
+ | autovacuum_vacuum_thresholdがデフォルト 50で、autovacuum_vacuum_scale_factorがデフォルト 0.1となっている。 | ||
+ | |||
+ | < | ||
+ | autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * レコード数 | ||
+ | </ | ||
+ | |||
+ | AUTOVACUUMデーモン(Windowsサービス)が一定間隔でこれらの状況を監視し、ロック競合が発生しない限り、一定数量のAUTOVACUUM処理が実行される。 | ||
+ | |||
+ | パラメータはテーブル単位でも決定可能\\ | ||
+ | 大規模テーブルではテーブル単位に autovacuum_vacuum_scale_factor を 0 に\\ | ||
+ | autovacuum_vacuum_threshold を適切な値に変更し、頻繁に VACUUM を実行することを推奨\\ | ||
+ | [[https:// | ||
+ | |||
+ | 例えば、10000件廃止行数が超えたら、自動的にVACUUMさせたいって場合 | ||
+ | |||
+ | < | ||
+ | ALTER TABLE {table_name} SET (autovacuum_vacuum_threshold = 10000); | ||
+ | ALTER TABLE {table_name} SET (autovacuum_vacuum_scale_factor = 0); | ||
+ | </ | ||
+ | <wrap em> | ||
==== VACUUM実行確認 ==== | ==== VACUUM実行確認 ==== | ||
行 546: | 行 586: | ||
* [[https:// | * [[https:// | ||
* [[http:// | * [[http:// | ||
+ | |||
+ | ===== 統計情報 ====== | ||
+ | [[https:// | ||
+ | ==== 統計情報の取得 ===== | ||
+ | * SQL文の実行計画作成には統計情報を使用 | ||
+ | * 統計情報とはテーブルやインデックスのデータ情報 | ||
+ | * レコード数、ブロック数 | ||
+ | * ヒストグラム(データの最頻値、ばらつき) | ||
+ | * 取得契機 | ||
+ | * 自動 VACUUM | ||
+ | * VACUUM ANALYZE文の実行 | ||
+ | * ANALYZE 文の実行 | ||
+ | * 統計情報の確認 | ||
+ | * pg_statistic カタログ | ||
+ | * pg_stats カタログ(pg_statistic カタログを見やすく整形している) | ||
+ | |||
+ | ==== サンプリング ===== | ||
+ | === 統計情報はサンプリングによって行われる === | ||
+ | * サンプリング量はデフォルト 30,000 レコード( MAX(列の STATISTICS 値)× 300 レコード) | ||
+ | * 列の STATISTICS 値とは? | ||
+ | * ヒストグラムを収集するバケット数(データの範囲を入れる箱の数) | ||
+ | * デフォルト値はパラメータ default_statistics_target で決まる(デフォルト 100) | ||
+ | * 「ALTER TABLE table_name ALTER column_name SET STATISTICS value」文で変更 | ||
+ | |||
+ | default_statistics_targetを単純に増やしてしまうとVACUUMの処理時間が長くなる。\\ | ||
+ | よって、性能が低下しているSQLで参照しているテーブル、カラムを個別に拡張することが効果的。 | ||
+ | < | ||
+ | ALTER TABLE テーブル名 ALTER COLUMN カラム名 SET STATISTICS 200; | ||
+ | </ | ||
+ | |||
+ | 上記設定が適用されるのはVACUUM実行時で、速くなるのは統計精度を拡張したカラムを条件としたSELECTのみで統計精度が関係ないカラムのSELECTやINSERTには何の影響もない。 | ||
+ | |||
+ | === 大規模なテーブルではサンプリング・レコード数を拡大 === | ||
+ | STATISTICS 値の拡大を推奨 | ||
+ | |||
+ | デフォルトの統計情報計算式はテーブルのレコード数を 1,000,000 レコードと想定。\\ | ||
+ | 10,000,000 レコードのテーブルでは STATISTICS = 114 程度にすると必要なサンプリングが行われる | ||
+ | |||
+ | ===== OID(Object Identifier:オブジェクト識別子) ====== | ||
+ | ==== 特徴 ==== | ||
+ | 特徴をまとめると次の通り | ||
+ | |||
+ | * データベース、テーブル、ロール、関数、操作、データ型などに割り振られる | ||
+ | * 0は使わない(0は無効な値の扱い)。符号なし4バイト(最大値:4, | ||
+ | * OIDは一周して同じ値をとる場合があるため、一意であると仮定してはならない | ||
+ | * 主キーを持たないテーブル、重複行があるテーブルなどに有効 | ||
+ | |||
+ | <wrap em> | ||
+ | |||
+ | [[https:// | ||
+ | |||
+ | ==== レコードのデータ識別 ==== | ||
+ | 内部的にはレコードのデータにデータを識別するOID(オブジェクトID)とトランザクションを制御するXID(トランザクションID)をくっ付けて管理しています。\\ | ||
+ | 新しいトランザクションが発生すると元のデータはそのままにして、OIDはそのままでXIDを一個増やしたデータを追記しようとします。\\ | ||
+ | [[https:// | ||
===== トランザクション ===== | ===== トランザクション ===== | ||
行 556: | 行 651: | ||
SELECT datname, age(datfrozenxid) FROM pg_database | SELECT datname, age(datfrozenxid) FROM pg_database | ||
</ | </ | ||
- | ageが15億になる前にVACUUMをかける。かけるとまた10億になる。 | + | |
+ | ageが15億になる前にVACUUMをかける。かけるとまた10億になる。\\ | ||
[[http:// | [[http:// | ||
===== インデックスの再構築 ===== | ===== インデックスの再構築 ===== | ||
- | PostgreSQLにはreindexコマンドがあるけれど、これはテーブルロックがかかってしまうので運用中のDBに対して使うのは難しい。\\ | + | PostgreSQL |
- | ただ、PostgreSQLでは別名で全く同じインデックスの作成を行うことができる | + | |
+ | * DROP INDEX でテーブル自体を削除した場合 | ||
+ | * TRUNCATE TABLE でテーブル全体を空にした場合 | ||
+ | * REINDEX でインデックスを再構成した場合 | ||
+ | |||
+ | インデックスが肥大化した状況では実行計画のコスト計算に影響することがあります。これは適切な実行計画を選択する妨げとなるかもしれません。 | ||
+ | |||
+ | [[https:// | ||
+ | ==== リインデックス ==== | ||
+ | reindexコマンドを使用して、インデックスの再構築する。\\ | ||
+ | <code bat> | ||
+ | rem PK_REP_RES_QUEをリインデックスする | ||
+ | SET PGPASSWORD=wh_kousei | ||
+ | psql -U wh_kousei -c " | ||
+ | </ | ||
+ | |||
+ | ==== テーブルロックなしのリインデックス ==== | ||
+ | reindexコマンドはテーブルロックがかかってしまうので、運用中のDBに対して使うのは難しい。\\ | ||
+ | ただ、PostgreSQLでは別名で全く同じインデックスの作成を行うことができる。\\ | ||
+ | <wrap em> | ||
* [[http:// | * [[http:// | ||
行 567: | 行 682: | ||
* [[https:// | * [[https:// | ||
* [[http:// | * [[http:// | ||
+ | * [[https:// | ||
+ | |||
+ | PostgreSQL 9.6上で疑似的にロック無しのインデックスの再構築 | ||
<code bat> | <code bat> | ||
行 607: | 行 725: | ||
SELECT pg_database_size(' | SELECT pg_database_size(' | ||
</ | </ | ||
+ | |||
+ | === テーブル単位のサイズを取得するSQL === | ||
+ | <code sql> | ||
+ | SELECT pgn.nspname, | ||
+ | ' | ||
+ | ' | ||
+ | FROM 10) = REPLACE(SUBSTRING(pg.relname FROM 10), ' | ||
+ | pg_class pgc WHERE pg.reltoastrelid = pgc.oid) END:: | ||
+ | ' | ||
+ | (SELECT pgt.oid FROM pg_class pgt WHERE SUBSTRING(pgt.relname FROM 10) = REPLACE(SUBSTRING(pg.relname | ||
+ | FROM 10), ' | ||
+ | FROM pg_class pg, pg_namespace pgn WHERE pg.relnamespace = pgn.oid AND pgn.nspname NOT IN | ||
+ | (' | ||
+ | </ | ||
+ | |||
+ | [[https:// | ||
=== テーブル単位のダンプ === | === テーブル単位のダンプ === | ||
行 633: | 行 767: | ||
|3|Z|マイナーバージョン番号|セキュリティバグやデータ破損の可能性のあるバグ等が修正された場合。\\ その他の軽微な修正も同時に行われる。| | |3|Z|マイナーバージョン番号|セキュリティバグやデータ破損の可能性のあるバグ等が修正された場合。\\ その他の軽微な修正も同時に行われる。| | ||
- | ※10系から、PostgreSQL X.Zと二つの数字で表記に変更。最初がメジャーバージョン、新機能追加 | + | ※10系から、PostgreSQL X.Zと二つの数字で表記に変更。最初がメジャーバージョン:新機能追加、最後がマイナーバージョン:バグフィックスなど |
==== マイナーアップデート ==== | ==== マイナーアップデート ==== | ||
行 641: | 行 775: | ||
==== メジャーアップデート ==== | ==== メジャーアップデート ==== | ||
メジャーアップデートでは、データのバックアップとリストアの作業が必要である。\\ | メジャーアップデートでは、データのバックアップとリストアの作業が必要である。\\ | ||
- | メジャーアップデート用ツールとして、[[https:// | + | メジャーアップデート用ツールとして、[[https:// |
- | [[https:// | + | |
+ | === pg_upgrade === | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ |
it技術/データベース/postgresql/運用.txt · 最終更新: 2024/04/24 11:07 by yajuadmin