差分

このページの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あれば十分のようなのでデフォルトのまま。\\
 https://www.postgresql.jp/document/9.6/html/runtime-config-resource.html
+<wrap em>PostgreSQL 10以降のマニュアル(shared_buffers欄)には、「Windowsシステムでの有効なshared_buffersの範囲は一般的に64MBから512MBです。」の記載が消えている。https://github.com/le0pard/pgtune/discussions/50</wrap>
 既定値：128MB
@@ 行 151: / 行 153: @@
 </code>
-  * [[https://blogs.yahoo.co.jp/yukigaku0313/21115869.html|PostgreSQL インストールのトラブルシューティング]]
+  * [[https://web.archive.org/web/20180912063813/https://blogs.yahoo.co.jp/yukigaku0313/21115869.html|PostgreSQL インストールのトラブルシューティング]]
   * [[http://software.fujitsu.com/jp/manual/manualfiles/m130025/b1ws1136/01z200/b1136-00-04-03-02.html|4.3 インスタンスの作成 > 4.3.2 initdbコマンドを使用する場合]]
@@ 行 254: / 行 256: @@
 <code bash コマンドプロンプト>
 > pg_basebackup -U postgres -D "E:\backup" -F t -x -z --checkpoint=fast
+↓ PostgreSQL12では-xは使えない
+> pg_basebackup -U postgres -D "E:\backup" -F t -X f -z --checkpoint=fast
 </code>
@@ 行 259: / 行 263: @@
 |-D|ベースバックアップ出力先パスを指定。出力先は空でなければならない。 \\ ディレクトリが存在しなければ作ってくれる。|
 |-F t|出力する形式。tはtarで出力する。圧縮するためにはtarの指定が必要|
-|-x|ベースバックアップ処理中、データベースが更新された際もファイルシステム上のファイルは更新しないようにする。出力中はメモリ上で頑張る。 \\ （そうすることで、出力ファイルが変な状態になることを防ぐ）|
+|-x|ベースバックアップ処理中、データベースが更新された際もファイルシステム上のファイルは更新しないようにする。出力中はメモリ上で頑張る。 \\ （そうすることで、出力ファイルが変な状態になることを防ぐ） \\ -X f と同じ(PostgreSQL12では英小文字の-xは使用不可になった)|
 |-z|gzipで圧縮した状態にする|
 |--checkpoint=fast|fast を指定すると、バックアップ開始時のチェックポイント処理は高速になりますが、集中した I/O のために動作中のアプリケーションへの性能の影響が大きくなります。 spread ではチェックポイントはゆっくり実行されるためアプリケーションへの影響は小さいですが、バックアップに時間がかかります。|
@@ 行 271: / 行 275: @@
 </code>
-postgresql.conf の max_wal_sender 設定のデフォルトは 0 です。最低でも 1 以上でないと pg_basebackup コマンドは実行できないので修正します。
+postgresql.conf の max_wal_sender 設定のデフォルトは 0 です。最低でも 1 以上でないと pg_basebackup コマンドは実行できないので修正します。※PostgreSQL12ではデフォルトは 10 となった。
 <code bat postgresql.conf>
-max_wal_sender = 1
+max_wal_sender = 10
 </code>
@@ 行 314: / 行 318: @@
 <code sql>
 SELECT pg_switch_xlog();
+↓ PostgreSQL10以降ではコマンド名変更
+SELECT pg_switch_wal();
 </code>
@@ 行 372: / 行 378: @@
   - pg_controlファイルを読み込む
-  - recovery.confを読み込む
+  - recovery.confを読み込む → PostgreSQL12ではrecovery.conf廃止、postgresql.confに統一
   - backup_labelを読み込む
   - pg_controlファイルを更新し、backup_labelを削除する
@@ 行 497: / 行 503: @@
   * Dead Tupleが各テーブルで定められた閾値を超えた場合(autovacuum_vacuum_threshold)
-統計情報の再計算\\
+**統計情報の再計算**\\
 以下の計算式以上のレコードが更新（UPDATE/DELETE）されたテーブルは自動的に実行
+autovacuum_vacuum_thresholdがデフォルト 50で、autovacuum_vacuum_scale_factorがデフォルト 0.1となっている。
 <code>
 autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * レコード数
@@ 行 505: / 行 513: @@
 AUTOVACUUMデーモン(Windowsサービス)が一定間隔でこれらの状況を監視し、ロック競合が発生しない限り、一定数量のAUTOVACUUM処理が実行される。
-パラメータはテーブル単位でも決定可能 \\ 大規模テーブルではテーブル単位に autovacuum_vacuum_scale_factor を 0 に \\ autovacuum_vacuum_threshold を適切な値に変更し、頻繁に VACUUM を実行することを推奨\\
+パラメータはテーブル単位でも決定可能\\
-[[https://tihiro.hatenablog.com/entry/2018/07/17/223913|テーブルごとにAUTO_VACUUMおよびAUTO_ANALYZEをする閾値を設定する]]
+大規模テーブルではテーブル単位に autovacuum_vacuum_scale_factor を 0 に\\
+autovacuum_vacuum_threshold を適切な値に変更し、頻繁に VACUUM を実行することを推奨\\
+[[https://web.archive.org/web/20190531052308/https://tihiro.hatenablog.com/entry/2018/07/17/223913|テーブルごとにAUTO_VACUUMおよびAUTO_ANALYZEをする閾値を設定する]]
+例えば、10000件廃止行数が超えたら、自動的にVACUUMさせたいって場合
+<code>
+ALTER TABLE {table_name} SET (autovacuum_vacuum_threshold = 10000);
+ALTER TABLE {table_name} SET (autovacuum_vacuum_scale_factor = 0);
+</code>
+<wrap em>巨大なテーブルに対して実行すると、vacuum処理に時間がかかりすぎることによって、表面のアプリケーション側に影響を及ぼすことがあるので、パラメータ変更はせずに非稼働時間に定期的にVACUME ANALIZEを各テーブルに行うようにしたい。</wrap>
 ==== VACUUM実行確認 ====
@@ 行 572: / 行 591: @@
 ===== 統計情報 ======
 [[https://www.ospn.jp/osc2014.enterprise/pdf/OSC2014_Enterprise_hp.pdf|PostgreSQL安定運用のための障害予防と検知 - pdf]]
+==== ANALYZE =====
+ANYLYZEは、統計情報を行う。単独で動かせるし「VACUUM ANYLYZE」としてVACUUMと組み合わせることもできる。AUTOVACUUM時にもANYLYZEが行われている。\\
+対象とするテーブルの読み取りロックのみ(読み込みはできるが書き込みができない状態)であり、そのテーブルに対する他の操作と並行して実行することができます。
 ==== 統計情報の取得 =====
   * SQL文の実行計画作成には統計情報を使用
@@ 行 634: / 行 657: @@
 SELECT datname, age(datfrozenxid) FROM pg_database
 </code>
-ageが15億になる前にVACUUMをかける。かけるとまた10億になる。
+ageが15億になる前にVACUUMをかける。かけるとまた10億になる。\\
 [[http://blog.livedoor.jp/moonfishnet/archives/26220925.html|VACUUMとage関数]]
@@ 行 665: / 行 689: @@
   * [[http://kashi.way-nifty.com/jalan/2014/04/postgresql-81a7.html|定期的なインデックス再作成を自動化]]
   * [[https://qiita.com/mkyz08/items/eb66419d43c56cce566e|PostgreSQLのインデックス再編成]]
+PostgreSQL 9.6上で疑似的にロック無しのインデックスの再構築
 <code bat>
@@ 行 705: / 行 731: @@
 SELECT pg_database_size('db_name');
 </code>
+=== テーブル単位のサイズを取得するSQL ===
+<code sql>
+SELECT pgn.nspname, relname, pg_size_pretty(relpages::bigint * 8 * 1024) AS size, CASE WHEN relkind =
+'t' THEN (SELECT pgd.relname FROM pg_class pgd WHERE pgd.reltoastrelid = pg.oid) WHEN nspname =
+'pg_toast' AND relkind = 'i' THEN (SELECT pgt.relname FROM pg_class pgt WHERE SUBSTRING(pgt.relname
+FROM 10) = REPLACE(SUBSTRING(pg.relname FROM 10), '_index', '')) ELSE (SELECT pgc.relname FROM
+pg_class pgc WHERE pg.reltoastrelid = pgc.oid) END::varchar AS refrelname, CASE WHEN nspname =
+'pg_toast' AND relkind = 'i' THEN (SELECT pgts.relname FROM pg_class pgts WHERE pgts.reltoastrelid =
+(SELECT pgt.oid FROM pg_class pgt WHERE SUBSTRING(pgt.relname FROM 10) = REPLACE(SUBSTRING(pg.relname
+FROM 10), '_index', ''))) END AS relidxrefrelname, relfilenode, relkind, reltuples::bigint, relpages
+FROM pg_class pg, pg_namespace pgn WHERE pg.relnamespace = pgn.oid AND pgn.nspname NOT IN
+('information_schema', 'pg_catalog') ORDER BY relpages DESC
+</code>
+[[https://qiita.com/awakia/items/99c3d114aa16099e825d|PostgreSQLで各テーブルの総サイズと平均サイズを知る]]
 === テーブル単位のダンプ ===