it技術:データベース:postgresql:運用
差分
このページの2つのバージョン間の差分を表示します。
両方とも前のリビジョン前のリビジョン次のリビジョン | 前のリビジョン | ||
it技術:データベース:postgresql:運用 [2021/06/25 02:14] – [レコードのデータ識別] yajuadmin | it技術:データベース:postgresql:運用 [2024/04/24 11:07] (現在) – [shared_buffers] yajuadmin | ||
---|---|---|---|
行 27: | 行 27: | ||
※Windowsシステムでの有効なshared_buffersの範囲は一般的に64MBから512MBです。Windows版は64MBあれば十分のようなのでデフォルトのまま。\\ | ※Windowsシステムでの有効なshared_buffersの範囲は一般的に64MBから512MBです。Windows版は64MBあれば十分のようなのでデフォルトのまま。\\ | ||
https:// | https:// | ||
+ | |||
+ | <wrap em> | ||
既定値:128MB | 既定値:128MB | ||
行 151: | 行 153: | ||
</ | </ | ||
- | * [[https:// | + | * [[https:// |
* [[http:// | * [[http:// | ||
行 254: | 行 256: | ||
<code bash コマンドプロンプト> | <code bash コマンドプロンプト> | ||
> pg_basebackup -U postgres -D " | > pg_basebackup -U postgres -D " | ||
+ | ↓ PostgreSQL12では-xは使えない | ||
+ | > pg_basebackup -U postgres -D " | ||
</ | </ | ||
行 259: | 行 263: | ||
|-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 ではチェックポイントはゆっくり実行されるためアプリケーションへの影響は小さいですが、バックアップに時間がかかります。| | ||
行 271: | 行 275: | ||
</ | </ | ||
- | 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 |
</ | </ | ||
行 314: | 行 318: | ||
<code sql> | <code sql> | ||
SELECT pg_switch_xlog(); | SELECT pg_switch_xlog(); | ||
+ | ↓ PostgreSQL10以降ではコマンド名変更 | ||
+ | SELECT pg_switch_wal(); | ||
</ | </ | ||
行 372: | 行 378: | ||
- pg_controlファイルを読み込む | - pg_controlファイルを読み込む | ||
- | - recovery.confを読み込む | + | - recovery.confを読み込む |
- backup_labelを読み込む | - backup_labelを読み込む | ||
- pg_controlファイルを更新し、backup_labelを削除する | - pg_controlファイルを更新し、backup_labelを削除する | ||
行 497: | 行 503: | ||
* Dead Tupleが各テーブルで定められた閾値を超えた場合(autovacuum_vacuum_threshold) | * Dead Tupleが各テーブルで定められた閾値を超えた場合(autovacuum_vacuum_threshold) | ||
- | 統計情報の再計算\\ | + | **統計情報の再計算**\\ |
以下の計算式以上のレコードが更新(UPDATE/ | 以下の計算式以上のレコードが更新(UPDATE/ | ||
+ | autovacuum_vacuum_thresholdがデフォルト 50で、autovacuum_vacuum_scale_factorがデフォルト 0.1となっている。 | ||
+ | |||
< | < | ||
autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * レコード数 | autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * レコード数 | ||
行 505: | 行 513: | ||
AUTOVACUUMデーモン(Windowsサービス)が一定間隔でこれらの状況を監視し、ロック競合が発生しない限り、一定数量のAUTOVACUUM処理が実行される。 | AUTOVACUUMデーモン(Windowsサービス)が一定間隔でこれらの状況を監視し、ロック競合が発生しない限り、一定数量のAUTOVACUUM処理が実行される。 | ||
- | パラメータはテーブル単位でも決定可能 \\ 大規模テーブルではテーブル単位に autovacuum_vacuum_scale_factor を 0 に \\ autovacuum_vacuum_threshold を適切な値に変更し、頻繁に VACUUM を実行することを推奨\\ | + | パラメータはテーブル単位でも決定可能\\ |
- | [[https:// | + | 大規模テーブルではテーブル単位に 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実行確認 ==== | ||
行 572: | 行 591: | ||
===== 統計情報 ====== | ===== 統計情報 ====== | ||
[[https:// | [[https:// | ||
+ | |||
+ | ==== ANALYZE ===== | ||
+ | ANYLYZEは、統計情報を行う。単独で動かせるし「VACUUM ANYLYZE」としてVACUUMと組み合わせることもできる。AUTOVACUUM時にもANYLYZEが行われている。\\ | ||
+ | 対象とするテーブルの読み取りロックのみ(読み込みはできるが書き込みができない状態)であり、そのテーブルに対する他の操作と並行して実行することができます。 | ||
==== 統計情報の取得 ===== | ==== 統計情報の取得 ===== | ||
* SQL文の実行計画作成には統計情報を使用 | * SQL文の実行計画作成には統計情報を使用 | ||
行 634: | 行 657: | ||
SELECT datname, age(datfrozenxid) FROM pg_database | SELECT datname, age(datfrozenxid) FROM pg_database | ||
</ | </ | ||
- | ageが15億になる前にVACUUMをかける。かけるとまた10億になる。 | + | |
+ | ageが15億になる前にVACUUMをかける。かけるとまた10億になる。\\ | ||
[[http:// | [[http:// | ||
行 665: | 行 689: | ||
* [[http:// | * [[http:// | ||
* [[https:// | * [[https:// | ||
+ | |||
+ | PostgreSQL 9.6上で疑似的にロック無しのインデックスの再構築 | ||
<code bat> | <code bat> | ||
行 705: | 行 731: | ||
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:// | ||
=== テーブル単位のダンプ === | === テーブル単位のダンプ === |
it技術/データベース/postgresql/運用.txt · 最終更新: 2024/04/24 11:07 by yajuadmin