差分

このページの2つのバージョン間の差分を表示します。

--- it技術:データベース:postgresql:運用 [2019/08/06 11:17] – [max_wal_size] yajuadmin
+++ it技術:データベース:postgresql:運用 [2024/04/24 11:07] (現在) – [shared_buffers] yajuadmin
@@ 行 14: / 行 14: @@
 代表的なチューニング項目を記す。\\
 pg_confファイルの設定内容を見直す。設定変更後はPostgreSQLを再起動する。\\
+チューニング設定生成ツール「[[https://pgtune.leopard.in.ua/#/|PGTune]]」を使用すると、postgresql.confの推奨値を出力してくれる。
   * [[https://dbstudychugoku.github.io/pdf/20140713_postgre_tuning.pdf|まずやっとく PostgreSQLのチューニング - pdf]]
   * [[https://www.pgconf.asia/JA/2018/wp-content/uploads/sites/3/2018/12/D0P5.pdf|WindowsでもPostgreSQLが使いたい - pdf]]
   * [[https://pgtune.leopard.in.ua/#/|PGTune チューニング設定生成ツール]]
+  * [[https://tech-note.gaz.co.jp/postgresql/2018083001/|postgresqlのチューニング]]
@@ 行 25: / 行 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
@@ 行 50: / 行 54: @@
 [[https://dbstudychugoku.github.io/pdf/20140713_postgre_tuning.pdf|ディスクソートの発生を避ける]]
-  * メモリ上でソートが行えるようwork\_memパラメータを調整
+  * メモリ上でソートが行えるようwork_memパラメータを調整
   * work_memはセッションごとに確保される領域であるため、SETコマンドで処理に応じて調整するのが望ましい
@@ 行 74: / 行 78: @@
 ^搭載メモリ^推奨値^
-|2GB|512MB|
+|2GB|512MB→1GB|
-|4GB|1GB|
+|4GB|1GB→2GB|
-|8GB|2GB|
+|8GB|2GB→4GB|
-|16GB|4GB|
+|16GB|4GB→8GB|
 ==== random_page_cost ====
@@ 行 112: / 行 116: @@
 ==== wal_keep_segments ====
-バックアップ後にpg_logに下記のエラーが発生
+バックアップ後のpg_logに下記のエラーが発生していました。
 <code>
 -08-06 02:18:35 JST ERROR:  requested WAL segment 000000010000005A000000D1 has already been removed
@@ 行 133: / 行 137: @@
 pg_basebackup -U postgres -D "%bkPath%" -F t -x -z --checkpoint=fast
 </code>
+===== パフォーマンスチューニング =====
+[[https://www.fujitsu.com/jp/products/software/resources/feature-stories/postgres/article-index/tuningrule9-base/|パフォーマンスチューニング9つの技 ～「基盤」について～]]
+  - VACUUMで不要領域を再利用可能な状態にする
+  - REINDEXでインデックスの不要領域を削除する
+  - ANALYZEで統計情報を最新化する
+  - VACUUM FREEZEでトランザクションIDを凍結状態にする
 ===== データベース初期化 =====
 ==== 全てのデータベース初期化 ====
@@ 行 141: / 行 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コマンドを使用する場合]]
@@ 行 147: / 行 159: @@
 initdb: ディレクトリ "D:\Program Files\PostgreSQL\9.6\data"の権限を変更できませんでした:Permission denied
-Userに変更にチェックを追加 することで回避した。\\
+Userに変更のチェックを追加 することで回避した。\\
 他にもrunasコマンドを使用する(データベースクラスタの作成過程でpostgresプロセスを起動しますが、このプロセスは管理者権限では実行できないため)\\
   * [[https://blog.tpc.jp/2018/05/postgresql-10-install-error.html|PostgreSQL 10 インストール時の不具合解消方法]]
@@ 行 243: / 行 255: @@
 <code bash コマンドプロンプト>
-> pg_basebackup -U postgres -D "E:\backup" -F t -x -z
+> 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>
 ^Option^説明^
 |-D|ベースバックアップ出力先パスを指定。出力先は空でなければならない。 \\ ディレクトリが存在しなければ作ってくれる。|
-|-F t|出力する形式。tはtarで出力する。|
+|-F t|出力する形式。tはtarで出力する。圧縮するためにはtarの指定が必要|
-|-x|ベースバックアップ処理中、データベースが更新された際もファイルシステム上のファイルは更新しないようにする。出力中はメモリ上で頑張る。 \\ （そうすることで、出力ファイルが変な状態になることを防ぐ）|
+|-x|ベースバックアップ処理中、データベースが更新された際もファイルシステム上のファイルは更新しないようにする。出力中はメモリ上で頑張る。 \\ （そうすることで、出力ファイルが変な状態になることを防ぐ） \\ -X f と同じ(PostgreSQL12では英小文字の-xは使用不可になった)|
 |-z|gzipで圧縮した状態にする|
+|--checkpoint=fast|fast を指定すると、バックアップ開始時のチェックポイント処理は高速になりますが、集中した I/O のために動作中のアプリケーションへの性能の影響が大きくなります。 spread ではチェックポイントはゆっくり実行されるためアプリケーションへの影響は小さいですが、バックアップに時間がかかります。|
 -Dオプションでバックアップ保存先を指定する。サイズが大きい場合はtar-gz圧縮する。だいたい5分以内で完了する。
@@ 行 260: / 行 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>
@@ 行 280: / 行 295: @@
   * [[https://qiita.com/bwtakacy/items/65260e29a25b5fbde835|PostgreSQLのWALファイル(トランザクションログ)について]]
   * [[https://ja.wikipedia.org/wiki/%E3%83%AD%E3%82%B0%E5%85%88%E8%A1%8C%E6%9B%B8%E3%81%8D%E8%BE%BC%E3%81%BF|ログ先行書き込み - wikipedia]]
+  * [[https://qiita.com/kimullaa/items/e7bed14aaa680126b81a|PostgreSQL WALログの仕組みとタイミングを理解したい]]
 ==== 設定 ====
 postgresql.conf で設定する。
@@ 行 302: / 行 318: @@
 <code sql>
 SELECT pg_switch_xlog();
+↓ PostgreSQL10以降ではコマンド名変更
+SELECT pg_switch_wal();
 </code>
@@ 行 360: / 行 378: @@
   - pg_controlファイルを読み込む
-  - recovery.confを読み込む
+  - recovery.confを読み込む → PostgreSQL12ではrecovery.conf廃止、postgresql.confに統一
   - backup_labelを読み込む
   - pg_controlファイルを更新し、backup_labelを削除する
@@ 行 443: / 行 461: @@
 === VACUUM FULL ===
 物理的にファイルを圧縮します。
+VACUUM FULLは次のような処理を行っています。
+  - テーブルのデータを1行ずつ取ってきて、別の新しいテーブルに詰め込む
+  - 新しいテーブルのインデックスを作成する
+  - テーブルを入れ替える
+つまり、有効な行しか取ってこないので不要領域は一切存在しなくなる。VACUUM FULLは一時的に対象テーブルと新しいテーブルが同時に作成されるため容量不足で完遂できないことがあります。
 排他ロックが必要なため、VACUUM FULL中はテーブルへアクセス不可となります。\\
@@ 行 460: / 行 484: @@
 |約2400万件|6分|
-**VACUUM FULLを使うのではなく、pg_repackを使う。**
+**VACUUM FULLを使うのではなく、pg_repack(Windows版は非対応)を使う。**
 ==== AUTOVACUUM(自動実行) ====
 autovacuumは定期的にテーブル状態を監視し、必要があれば自動でVACUUMやANALYZE を実施する機能です。※VACUUM FULLは行わない。\\
@@ 行 480: / 行 503: @@
   * Dead Tupleが各テーブルで定められた閾値を超えた場合(autovacuum_vacuum_threshold)
-AUTOVACUUMデーモンが一定間隔でこれらの状況を監視し、ロック競合が発生しない限り、一定数量のAUTOVACUUM処理が実行される。
+**統計情報の再計算**\\
+以下の計算式以上のレコードが更新（UPDATE/DELETE）されたテーブルは自動的に実行
+autovacuum_vacuum_thresholdがデフォルト 50で、autovacuum_vacuum_scale_factorがデフォルト 0.1となっている。
+<code>
+autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * レコード数
+</code>
+AUTOVACUUMデーモン(Windowsサービス)が一定間隔でこれらの状況を監視し、ロック競合が発生しない限り、一定数量のAUTOVACUUM処理が実行される。
+パラメータはテーブル単位でも決定可能\\
+大規模テーブルではテーブル単位に 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実行確認 ====
@@ 行 507: / 行 551: @@
 </code>
+=== 最後にVACUUM/ANALYZEした日時確認 ===
+<code sql>
+SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
+FROM pg_stat_all_tables
+WHERE schemaname = 'public' -- schemaname or relnameで絞りこむと見やすい
+ORDER BY relname
+</code>
 ==== VACUUM手動実行判断 ====
 デッドタプルが多く、有効なレコードに対するデッドタプルの割合の多いテーブルに VACUUM をするようにしてみてください。
@@ 行 537: / 行 588: @@
   * [[https://qiita.com/seikoudoku2000/items/e49a321182b5f91b86fe|Postgresql サーバを移行したらAUTO VACUUM が実行されず、データベースが肥大化した時の話]]
   * [[http://kashi.way-nifty.com/jalan/2014/02/postgresqlconf-.html|postgresql.confの罠]]
+===== 統計情報 ======
+[[https://www.ospn.jp/osc2014.enterprise/pdf/OSC2014_Enterprise_hp.pdf|PostgreSQL安定運用のための障害予防と検知 - pdf]]
+==== ANALYZE =====
+ANYLYZEは、統計情報を行う。単独で動かせるし「VACUUM ANYLYZE」としてVACUUMと組み合わせることもできる。AUTOVACUUM時にもANYLYZEが行われている。\\
+対象とするテーブルの読み取りロックのみ(読み込みはできるが書き込みができない状態)であり、そのテーブルに対する他の操作と並行して実行することができます。
+==== 統計情報の取得 =====
+  * 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で参照しているテーブル、カラムを個別に拡張することが効果的。
+<code>
+ALTER TABLE テーブル名 ALTER COLUMN カラム名 SET STATISTICS 200;
+</code>
+上記設定が適用されるのはVACUUM実行時で、速くなるのは統計精度を拡張したカラムを条件としたSELECTのみで統計精度が関係ないカラムのSELECTやINSERTには何の影響もない。
+===　大規模なテーブルではサンプリング・レコード数を拡大 ===
+STATISTICS 値の拡大を推奨
+デフォルトの統計情報計算式はテーブルのレコード数を 1,000,000 レコードと想定。\\
+,000,000 レコードのテーブルでは STATISTICS = 114 程度にすると必要なサンプリングが行われる
+===== OID（Object Identifier：オブジェクト識別子） ======
+==== 特徴 ====
+特徴をまとめると次の通り
+  * データベース、テーブル、ロール、関数、操作、データ型などに割り振られる
+  * 0は使わない（0は無効な値の扱い）。符号なし4バイト（最大値：4,294,967,295）の値をとる
+  * OIDは一周して同じ値をとる場合があるため、一意であると仮定してはならない
+  * 主キーを持たないテーブル、重複行があるテーブルなどに有効
+<wrap em>※テーブル作成時の「WITH OIDS」はPostgreSQL12で廃止</wrap>
+[[https://postgresweb.com/post-5484|【PostgreSQL】OIDとは？について調べたことのメモ]]
+==== レコードのデータ識別 ====
+内部的にはレコードのデータにデータを識別するOID（オブジェクトID）とトランザクションを制御するXID（トランザクションID）をくっ付けて管理しています。\\
+新しいトランザクションが発生すると元のデータはそのままにして、OIDはそのままでXIDを一個増やしたデータを追記しようとします。\\
+[[https://thinkit.co.jp/cert/compare/1/3/3.htm|第３回：アーキテクチャ比較 ファイル構造の違い - PostgreSQLの特徴　MVCCの仕組み]]
 ===== トランザクション =====
@@ 行 547: / 行 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関数]]
+===== インデックスの再構築 =====
+PostgreSQL のインデックスサイズは一度大きくなると、その後小さくなるタイミングが限られています。
+  * DROP INDEX でテーブル自体を削除した場合
+  * TRUNCATE TABLE でテーブル全体を空にした場合
+  * REINDEX でインデックスを再構成した場合
+インデックスが肥大化した状況では実行計画のコスト計算に影響することがあります。これは適切な実行計画を選択する妨げとなるかもしれません。
+[[https://taityo-diary.hatenablog.jp/entry/2021/06/05/143617|PostgreSQL のインデックス肥大化と実行計画のコストへの影響]]
+==== リインデックス ====
+reindexコマンドを使用して、インデックスの再構築する。\\
+<code bat>
+rem PK_REP_RES_QUEをリインデックスする
+SET PGPASSWORD=wh_kousei
+psql -U wh_kousei -c "reindex INDEX pk_rep_res_que;"
+</code>
+==== テーブルロックなしのリインデックス ====
+reindexコマンドはテーブルロックがかかってしまうので、運用中のDBに対して使うのは難しい。\\
+ただ、PostgreSQLでは別名で全く同じインデックスの作成を行うことができる。\\
+<wrap em>※PostgreSQL12からは、REINDEX CONCURRENTLY が導入され、REINDEX による重いロックなしでインデックスを再構築できます。</wrap>
+  * [[http://cynipe.hateblo.jp/entry/2012/08/19/173230|PostgreSQLでテーブルロックせずにインデックスを再構築する方法]]
+  * [[https://tak-w.hatenadiary.org/entry/20111125|index 再構築方法]]
+  * [[https://tak-w.hatenadiary.org/entry/20111207/1323270854|index 再構築 Primary key]]
+  * [[http://kashi.way-nifty.com/jalan/2014/04/postgresql-81a7.html|定期的なインデックス再作成を自動化]]
+  * [[https://qiita.com/mkyz08/items/eb66419d43c56cce566e|PostgreSQLのインデックス再編成]]
+PostgreSQL 9.6上で疑似的にロック無しのインデックスの再構築
+<code bat>
+rem PK_REP_RES_QUEをリインデックスする
+SET PGPASSWORD=wh_test
+psql -U wh_test -f reindex_rep_res_que.sql
+</code>
+<code sql reindex_rep_res_que.sql>
+create unique index concurrently pk_rep_res_que_new on rep_res_que(que_id);
+alter table rep_res_que drop constraint pk_rep_res_que;
+alter index pk_rep_res_que_new rename to pk_rep_res_que;
+alter table rep_res_que add primary key using index pk_rep_res_que;
+</code>
 ===== 確認用SQL =====
 === テーブルのディスク使用量を取得する ===
@@ 行 575: / 行 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で各テーブルの総サイズと平均サイズを知る]]
 === テーブル単位のダンプ ===
@@ 行 601: / 行 773: @@
 |3|Z|マイナーバージョン番号|セキュリティバグやデータ破損の可能性のあるバグ等が修正された場合。\\ その他の軽微な修正も同時に行われる。|
-※10系から、PostgreSQL X.Zと二つの数字で表記に変更。最初がメジャーバージョン、新機能追加  最後がマイナーバージョン、バグフィックスなど
+※10系から、PostgreSQL X.Zと二つの数字で表記に変更。最初がメジャーバージョン：新機能追加、最後がマイナーバージョン：バグフィックスなど
 ==== マイナーアップデート ====
@@ 行 609: / 行 781: @@
 ==== メジャーアップデート ====
 メジャーアップデートでは、データのバックアップとリストアの作業が必要である。\\
-メジャーアップデート用ツールとして、[[https://www.postgresql.jp/document/9.6/html/pgupgrade.html|pg_upgrade]]がある。\\
+メジャーアップデート用ツールとして、[[https://www.postgresql.jp/document/9.6/html/pgupgrade.html|pg_upgrade]]がある。
-[[https://qiita.com/leon-joel/items/42eb018598052f8792af|pg_upgrade を使ったDBクラスタ移行手順 on Windows]]
+=== pg_upgrade ===
+  * [[https://qiita.com/leon-joel/items/42eb018598052f8792af|pg_upgrade を使ったDBクラスタ移行手順 on Windows]]
+  * [[https://tech-blog.rakus.co.jp/entry/20190829/postgresql|【PostgreSQL 9.4 → 11】pg_upgradeでデータベースクラスタをバージョンアップ]]
+  * [[https://www.ashisuto.co.jp/db_blog/article/201712-postgresql10-upgrade.html|PostgreSQL10へのアップグレード前に押さえておきたい3つの注意点]]
+  * [[https://se.ykysd.com/2019/06/18/pg-upgrade-96-108/|PostgreSQL9.6からPostgreSQL10にバージョンアップ]]