====== 運用 ======
※Windows版PostgreSQL9.6を使用している。
^Windows版の未対応ツール^^
^ツール名^内容^
|pg_rman|バックアップ/リストアを体系的に管理する|
|pg_statsinfo|PostgreSQLのサーバ運用状況を定期的に取得する|
|pg_mons|zabbixを使ったサーバ運用状況の監視|
* [[https://pgecons-sec-tech.github.io/tech-report/pdf/wg3_windows.pdf|2017年度WG3活動報告書Windows環境調査編]]
* [[https://pgecons-sec-tech.github.io/tech-report/html_wg3_performance/wg3_performance.html|2018年度WG3活動報告書 性能トラブル調査編]]
===== チューニング =====
代表的なチューニング項目を記す。\\
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のチューニング]]
==== shared_buffers ====
PostgreSQL全体で試用する共有メモリキャッシュとなり、データの蓄積が進んだ際の応答性能に影響します。\\
サーバの搭載メモリの25％程度が目安となり、増やしすぎても効果はありません。\\
※Windowsシステムでの有効なshared_buffersの範囲は一般的に64MBから512MBです。Windows版は64MBあれば十分のようなのでデフォルトのまま。\\
https://www.postgresql.jp/document/9.6/html/runtime-config-resource.html
PostgreSQL 10以降のマニュアル(shared_buffers欄)には、「Windowsシステムでの有効なshared_buffersの範囲は一般的に64MBから512MBです。」の記載が消えている。https://github.com/le0pard/pgtune/discussions/50
既定値：128MB
^搭載メモリ^推奨値^
|2GB|512MB|
|4GB|1GB|
|8GB|2GB|
|16GB|4GB|
==== work_mem ====
PostgreSQLの各プロセスが使用するソート用のメモリバッファです。\\
瞬間最大でメモリを「work_mem」×「max_connection」＋α を消費するため、ピークを想定した設定を行う。\\
**搭載メモリに合わせる。**
既定値：1MB
^搭載メモリ^推奨値^
|2GB|4MB|
|4GB|8MB|
|8GB|8MB|
|16GB|16MB|
=== バッチ処理等で大量のソートが発生する場合 ===
[[https://dbstudychugoku.github.io/pdf/20140713_postgre_tuning.pdf|ディスクソートの発生を避ける]]
* メモリ上でソートが行えるようwork_memパラメータを調整
* work_memはセッションごとに確保される領域であるため、SETコマンドで処理に応じて調整するのが望ましい
postgres=# SET work_mem TO '256MB';
postgres=# SELECT ・・・・・・・ ORDER BY ・・・・・・・;
==== max_connections ====
同時接続受付数となります。\\
CGI実行数＞max_connectionsになるとタイムアウトが発生し、応答性が大きく低下します。\\
参考事例：5,000ユーザー規模のピーク時瞬間最大アクセスが50～75CGI/秒程度
既定値：100
基本、変更は不要となります。
==== effective_cache_size ====
OSがディスクキャッシュにどれくらいメモリを使用できるかを推定するための参考値。\\
**インデックスの格納に使えるメモリ量です。インデックスがメモリ上にのりきれば、ディスクアクセスが減る／効率化されます。**\\
サーバの搭載メモリの25～50％程度が目安となり、実メモリの消費はしません。
既定値：128MB
^搭載メモリ^推奨値^
|2GB|512MB→1GB|
|4GB|1GB→2GB|
|8GB|2GB→4GB|
|16GB|4GB→8GB|
==== random_page_cost ====
HDDからランダムにブロックを読み取るコスト\\
この値を小さくすると、問い合わせオプティマイザが seq scan ではなく積極的に index scan を選択する効果があります。
既定値：4
下記サイトはHDDではなくSSDの場合、random_page_costを1にしてハッシュ結合ではなくネステッドループ結合されるようなり速くなった。\\
[[http://pgsqldeepdive.blogspot.com/2017/11/50-how-single-postgresql-config-change.html|[翻訳] たった一つの設定変更が如何にしてクエリのパフォーマンスを50倍も改善したか]]
==== checkpoint_segments ====
checkpoint_segmentsのデフォルト値は「3」ですので、デフォルトの設定だと48MB（16MB×3）のトランザクションログを書くとチェックポイントが発生することになります。\\
しかし、この設定のままだとチェックポイントが頻発してしまうことがあります。
checkpoint_segmentsに64～128程度を初期値として設定する。\\
[[https://blog.server-tech.xyz/2015/07/21/checkpoint_segments/|checkpoint_segmentsのチューニングでPostgresのパフォーマンスが10倍以上に]]
PostgreSQL 9.5でcheckpoint_segmentsが2つのパラメータ(max_wal_size,min_wal_size)に置き替えられた。\\
[[https://www.ashisuto.co.jp/db_blog/article/20160603_max_wal_size.html|【PostgreSQL 9.5】max_wal_sizeとmin_wal_sizeの概要]]
==== checkpoint_timeout ====
チェックポイント発生する間隔を「時間」で指定するものです。\\
一般的には30min～1hがいいと言われている。
checkpoint_timeoutのデフォルト値は「5分」になっていますが、少し短すぎるので「30分」を初期値として設定する。
* [[https://qiita.com/U_ikki/items/89b1eea657e47120e3ee|PostgreSQLのチェックポイント処理のチューニング]]
==== max_wal_size ====
WALファイル数の上限\\
5分で400MBとするとcheckpoint_timeoutが30分なら6倍して、400MBx6=2400MB。\\
これを3倍したら7200MBなので10GBあればいい。
* [[https://qiita.com/U_ikki/items/89b1eea657e47120e3ee|PostgreSQLのチェックポイント処理のチューニング]]
==== wal_keep_segments ====
バックアップ後のpg_logに下記のエラーが発生していました。
2019-08-06 02:18:35 JST ERROR: requested WAL segment 000000010000005A000000D1 has already been removed
要求された wal セグメント * はすでに削除されています。
対処として、wal_keep_segmentsをゼロ以外の値に設定する。\\
これは、ストリームの危険性が追いつかなくなるのを防ぐためです。\\
ただしpg_basebackupを使用する場合は、 – checkpoint = fastも忘れないでください。\\
https://codeday.me/jp/qa/20190609/972945.html
#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables
↓
wal_keep_segments = 128
backup.bat の見直し
pg_basebackup -U postgres -D "%bkPath%" -F t -x -z
↓
pg_basebackup -U postgres -D "%bkPath%" -F t -x -z --checkpoint=fast
===== パフォーマンスチューニング =====
[[https://www.fujitsu.com/jp/products/software/resources/feature-stories/postgres/article-index/tuningrule9-base/|パフォーマンスチューニング9つの技 ～「基盤」について～]]
- VACUUMで不要領域を再利用可能な状態にする
- REINDEXでインデックスの不要領域を削除する
- ANALYZEで統計情報を最新化する
- VACUUM FREEZEでトランザクションIDを凍結状態にする
===== データベース初期化 =====
==== 全てのデータベース初期化 ====
コマンドプロンプトから実行する。initdb.exeはPostgreSQLのBINフォルダにある。
>initdb --locale=C --encoding=UTF-8 -D "D:\Program Files\PostgreSQL\9.6\data" -U postgres -A md
* [[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コマンドを使用する場合]]
=== Permission denied ===
initdb: ディレクトリ "D:\Program Files\PostgreSQL\9.6\data"の権限を変更できませんでした:Permission denied
Userに変更のチェックを追加 することで回避した。\\
他にもrunasコマンドを使用する(データベースクラスタの作成過程でpostgresプロセスを起動しますが、このプロセスは管理者権限では実行できないため)\\
* [[https://blog.tpc.jp/2018/05/postgresql-10-install-error.html|PostgreSQL 10 インストール時の不具合解消方法]]
* [[https://postgresql.g.hatena.ne.jp/umitanuki/20090815/1250347309|WindowsXPでinitdb runasコマンドを使用]]
==== 特定のデータベース初期化 ====
スーパーユーザー(例 postgres)にてデータベースを削除する。\\
[[https://www.dbonline.jp/postgresql/database/index3.html|データベースの削除]]
drop database test;
===== バックアップとリストア =====
* [[https://www.postgresql.jp/document/9.4/html/backup.html|第 24章バックアップとリストア]]
* [[https://qiita.com/rice_american/items/ceae28dad13c3977e3a8|PostgresSQLのbackup, restore方法まとめ]]
* [[https://qiita.com/bwtakacy/items/65260e29a25b5fbde835|PostgreSQLのバックアップ手法のまとめ]]
==== フォルダコピーによるバックアップとリストア ====
ActiveImage Protector ではFドライブ丸ごとバックアップされるので、リストアする際にはFドライブを丸ごとコピーすればいい。\\
[[http://www.cyber-funnel.com/postgres/index1625.html|PostgreSQL 9.2のバックアップリストア Windowsのフォルダコピーでバックアップとリストア]]
=== フォルダコピーによるバックアップ ===
バックアップは以下の手順で行います。
- PostgreSQLサービスの停止
- バックアップ(Dataフォルダのコピー)
- PostgreSQLサービスの開始
=== フォルダコピーによるリストア ===
リストアは以下の手順で行います。
- PostgreSQLサービスの停止
- リストア(Dataフォルダの書き戻し)
- PostgreSQLサービスの開始
==== バックアップ ====
PostgreSQLのバックアップはpg_dumpコマンドで取得するが、オプションによって大きく二つに分かれる。\\
* スクリプト形式(デフォルト)
* アーカイブ形式
=== スクリプト形式 ===
スクリプト形式の出力は、リストアに必要な平文のSQL文で取得してpsqlコマンドでリストアする。\\
スクリプト形式の場合はプレーンテキストなので、リストアの際にエラーが出たら、中を見れるという利点がある。
pg_dumpはローカルだけでなくリモートからも実行できる
$ pg_dump -h hostname -p port database名 backup_file名
=== アーカイブ形式 ===
バイナリの形で出力される。リストアはpsqlコマンドでなくpg_restoreコマンドで行う。\\
アーカイブ形式の利点は、 ｢指定したtableのみを選択してリストアできる｣。
また、アーカイブ形式には、custom形式とtar形式の2種類がある。custom形式はデフォで圧縮されるのでファイルサイズが小さい。
バックアップをとる
$ pg_dump -Fc database名 backup_file名
-Fオプションを付ければ良い。\\
-cはcustom形式を表す。-tとするとtar形式になる
==== リストア ====
=== スクリプト形式 ===
リストアは次のように行う
$ psql database名 < backup_file名
=== アーカイブ形式 ===
リストアは次のように行う
$ pg_restore -C -d postgres バックアップファイル名
-C オプションを付けるとリストア前にデータベースを作成し、-d オプションは、その際に接続するデータベース名を指定
==== pg_basebackup ====
PostgreSQL 9.1からの標準コマンド。PostgreSQLを停止しない状態のオンラインバックアップができる。\\
* [[https://qiita.com/bwtakacy/items/65260e29a25b5fbde835|PostgreSQLのバックアップ手法のまとめ]]
* [[http://sios-oss.blogspot.com/2016/08/postgresql_31.html|PostgreSQLのバックアップとリストア]]
* [[https://www.lancard.com/blog/2018/03/22/pg_basebackup%E3%82%92%E8%A9%A6%E3%81%99/|pg_basebackupを試す]]
* [[https://postgresql.g.hatena.ne.jp/pgsql/20110216|pg_basebackup in PG9.1]]
pg_basebackupでオンライン・バックアップを行う際の手順
- pg_start_backup()を実行
- データベースのファイルをコピー（オフライン・バックアップと同様）
- pg_stop_backup()を実行
pg_start_backup()関数とpg_stop_backup()関数を実行することで、バックアップが始まった時点と終わった時点のWALファイルがどれなのかがPostgreSQLによって記録されます。この情報はWALファイルと同じ場所に保存されるので、WALアーカイブを行っていれば同じくバックアップすることができます。
上記の3ステップをすべてひっくるめて行ってくれるのが、「pg_basebackup」コマンドとなります。
pg_dump と同じ手軽さで、より処理の軽い物理バックアップを取得できます。自前で用意するには若干面倒な、テーブルスペース (表領域) に対応した物理バックアップとしても嬉しいところです。-P, --progress オプションでバックアップの進捗も確認できる。\\
> 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
^Option^説明^
|-D|ベースバックアップ出力先パスを指定。出力先は空でなければならない。 \\ ディレクトリが存在しなければ作ってくれる。|
|-F t|出力する形式。tはtarで出力する。圧縮するためにはtarの指定が必要|
|-x|ベースバックアップ処理中、データベースが更新された際もファイルシステム上のファイルは更新しないようにする。出力中はメモリ上で頑張る。 \\ （そうすることで、出力ファイルが変な状態になることを防ぐ） \\ -X f と同じ(PostgreSQL12では英小文字の-xは使用不可になった)|
|-z|gzipで圧縮した状態にする|
|--checkpoint=fast|fast を指定すると、バックアップ開始時のチェックポイント処理は高速になりますが、集中した I/O のために動作中のアプリケーションへの性能の影響が大きくなります。 spread ではチェックポイントはゆっくり実行されるためアプリケーションへの影響は小さいですが、バックアップに時間がかかります。|
-Dオプションでバックアップ保存先を指定する。サイズが大きい場合はtar-gz圧縮する。だいたい5分以内で完了する。
「FATAL: pg_hba.conf にホスト"127.0.0.1"、ユーザ"postgres", SSL無効用のエントリがありません.」のエラーが出た場合、以下のコメントアウトを外す。
host replication postgres 127.0.0.1/32 md5
postgresql.conf の max_wal_sender 設定のデフォルトは 0 です。最低でも 1 以上でないと pg_basebackup コマンドは実行できないので修正します。※PostgreSQL12ではデフォルトは 10 となった。
max_wal_sender = 10
=== pg_rman ===
pg_rmanという世代管理対応のツールがあるが、Windowsは動作保証されていない。
* [[https://qiita.com/bwtakacy/items/84a446c642ffae76859b|pg_rmanによるPostgreSQLの簡単バックアップ＆リカバリ]]
* [[https://www.sraoss.co.jp/technology/postgresql/3rdparty/pg_rman.php|pg_rman (PostgreSQL のバックアップ/リストア管理ツール)]]
===== WALアーカイブログ =====
WALアーカイブログは、PostgreSQLが出力したトランザクションログ(WAL)ファイルを別の場所にコピーすることです。\\
WALファイルはデフォルト設定では直近の一定数しか保存されず、古くなったものは削除もしくは再利用されていきます。そのため、リカバリに使えるようにWALファイルを別の場所に保存しておく必要があります。
==== WALファイルとは ====
WALはログ先行書き込みのことで、トランザクションのログを残すための一般的な手法です。\\
データベースソフトウェアの本質として、ディスクに書き込まれていない状態で障害が発生するとリカバリにWALファイルが必要になる。
* [[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 で設定する。
wal_level = replica # リカバリに使える情報を保存するようにminimal以外の値にしておく
archive_mode = on # PostgreSQLによるWALアーカイブ処理を有効にする
archive_command = 'copy "%p" "D:\\PostgreSQL\\ARCHIVELOG\\%f"' # WALアーカイブ処理時に使われるコマンドを指定する %pはwalファイル名、%fはwalアーカイブファイル名が自動で入力される
※PostgreSQL9.6を使用する場合、archiveとhot_standbyが新しい設定値「replica」に統合\\
※archive_commandのフォルダは先に作成しておく。\\
※archive_commandで「"%p"」などのようにパスの部分をダブルクォテーションでくくらないと正しく実行されません。
* [[http://www.fujitsu.com/jp/products/software/resources/feature-stories/201509wal/|富士通の技術者に聞く!PostgreSQL最新技術 WALが損失するという課題の解決]]
* [[http://blog.poppypop.mydns.jp/2016/03/03/postgresql_onlinebackup_recovery/|PostgreSQLのオンラインバックアップとリカバリ]]
* [[https://qiita.com/U_ikki/items/11e859f15901fe3e9bfd|溜まったアーカイブログを削除するタイミング]]
=== 確認方法 ===
簡易的な確認は、WALのスイッチの実行を行う。\\
PSQLで下記を実行すると、archive_commandの内容を実行します。
SELECT pg_switch_xlog();
↓ PostgreSQL10以降ではコマンド名変更
SELECT pg_switch_wal();
[[https://oshiete.goo.ne.jp/qa/6869390.html|PostgresのPITR]]
==== WALのアーカイブの流れ ====
WALレコードが更新処理によって挿入され、16MBいっぱいになる、もしくはWALのスイッチが実行されると、pg_xlog/archive_statusディレクトリ配下に対象のWALファイルをアーカイブしてもよいことを示す「.ready」というファイルが作成されます。
アーカイブプロセスは、次のタイミングでarchive_statusディレクトリをチェックし、「.ready」があれば、archive_commandの内容を実行します。
* 60秒間隔(archive_timeoutが指定されている場合)
* PostgreSQLの停止時 \\ 無事にWALファイルを処理できたら、archive_statusディレクトリ「.ready」ファイルは「.done」 にリネームされます。
[[https://books.google.co.jp/books?id=D3JqBAAAQBAJ&printsec=frontcover&hl=ja&source=gbs_ge_summary_r&cad=0#v=onepage&q&f=false|内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 - 本]]
=== archive_timeout ===
archive_timeoutはトランザクションがほとんど発生しない「なぎ」のとき、WALにたまった内容がいつまで経ってもアーカイブされないことを防ぐ。デフォルトでは0（機能無効）
archive_timeout = 60 とした場合、16MB*60回/時*24時間=23040MB/日≒23GB/日 が発生しうる。
[[http://dbnote.web.fc2.com/note_param_pitr_walarchive.html|archive_timeoutの意味]]
==== WALアーカイブの削除 ====
pg_archivecleanupコマンドで削除する。
pg_basebackup を実行すると、WALアーカイブのフォルダに拡張子backupが作成される。\\
これにより指定した拡張子backupより前の不要なWALアーカイブが削除される。
pg_archivecleanup "D:\\PostgreSQL\\ARCHIVELOG" "000000020000000600000036.00000028.backup"
[[https://qiita.com/U_ikki/items/11e859f15901fe3e9bfd|溜まったアーカイブログを削除するタイミング]]
=== archive_cleanup_command ===
recovery.conf設定ファイルにarchive_cleanup_commandを指定するとpg_archivecleanupコマンドの実行後に不要なWALアーカイブを自動で削除してくれる。\\
[[https://monota.tokyo/?p=61|PostgreSQL の archive_cleanup_command はいつ実行されるのか？]]
restore_command = 'copy "D:\\PostgreSQL\\ARCHIVELOG\\%f" "%p"'
archive_cleanup_command = 'pg_archivecleanup "D:\\PostgreSQL\\ARCHIVELOG" "%r"'
しかし、recovery.confはリカバリをする時に必要なので不要なWALアーカイブを削除するためだけに使うわけにはいかない。\\
よって、不要なWALアーカイブの削除は手動で行うこととする。\\
※postgreSQLサービスを再起動すればリカバリが実行され、recovery.confはrecovery.doneになります。
==== WALアーカイブからのリカバリ ====
リストアとリカバリとは区別しないで使うときもあるが、今回は分けました。
* リストアはバックアップデータを、バックアップを取ったときと同じ状態に物理的に復元すること
* リカバリはリストアしたデータに何かの処理をして最新の状態や正常な状態に復旧させること
=== PITRの仕組み ===
PITR(Point In Time Recovery)は、WALレコード適用によるリカバリが前提となっている。
* [[http://ossfan.net/setup/postgresql-37.html|PostgreSQL 9.6.3で時刻指定のPITR(Point In Time Recovery)を実行する]]
* [[http://blog.poppypop.mydns.jp/2016/03/03/postgresql_onlinebackup_recovery/|PostgreSQLのオンラインバックアップとリカバリ]]
**WALレコードの適用までの流れ**\\
リカバリを開始してWALレコードを適用するまでの流れは次のようになっている。
- pg_controlファイルを読み込む
- recovery.confを読み込む → PostgreSQL12ではrecovery.conf廃止、postgresql.confに統一
- backup_labelを読み込む
- pg_controlファイルを更新し、backup_labelを削除する
- 必要なWALを繰り返し適用する
※backup_labelファイルから適用を開始すべきWALの位置を取得できなかった場合は、pg_controlファイルの情報を元にリカバリを開始します。
pg_controlファイルは、バイナリファイルなので通常のエディタでは内容を確認できません。そのために pg_controldata コマンドが用意されています。\\
pg_controldata コマンドで最終チェックポイントのREDO WALファイルが分かる。
pg_controldata -D C:\PostgreSQL\9.6\data
pg_controlバージョン番号: 960
カタログバージョン番号: 201608131
データベースシステム識別子: 6599999130305146812
データベースクラスタの状態: 運用中
pg_control最終更新: 2019/07/28 20:58:08
最終チェックポイント位置: 0/B01B140
前回のチェックポイント位置: 0/B01B060
最終チェックポイントのREDO位置: 0/B01B108
最終チェックポイントのREDO WALファイル: 00000004000000000000000B
最終チェックポイントの時系列ID: 4
最終チェックポイントのPrevTimeLineID: 4
最終チェックポイントのfull_page_writes オン
最終チェックポイントのNextXID: 0:610
最終チェックポイントのNextOID: 24655
最終チェックポイントのNextMultiXactId: 1
最終チェックポイントのNextMultiOffset: 0
最終チェックポイントのoldestXID: 536
最終チェックポイントのoldestXIDのDB: 1
最終チェックポイントのoldestActiveXID: 610
最終チェックポイントのoldestMultiXid: 1
最終チェックポイントのoldestMulti'sのDB: 1
最終チェックポイントのoldestCommitTsXid:0
最終チェックポイントのnewestCommitTsXid:0
最終チェックポイント時刻: 2019/07/28 20:58:08
ログを取らないリレーション向けの偽のLSNカウンタ: 0/1
最小リカバリ終了位置: 0/0
最小リカバリ終了位置のタイムライン: 0
バックアップ開始位置: 0/0
バックアップ終了位置: 0/0
必要なバックアップ最終レコード: no
wal_level の設定： replica
wal_log_hints の設定： オフ
max_connections の設定： 100
max_worker_processes の設定： 8
max_prepared_xacts の設定： 0
max_locks_per_xact の設定： 64
track_commit_timestamp の設定： オフ
最大データアラインメント 8
データベースのブロックサイズ: 8192
ラージリレーションのセグメント当たりのブロック数: 131072
WALブロックのサイズ: 8192
WALセグメント当たりのバイト数: 16777216
識別子の最大長: 64
インデックス内の最大列数: 32
TOASTチャンクの最大サイズ: 1996
ラージオブジェクトチャンクのサイズ: 2048
日付/時刻型の格納方式: 64ビット整数
Float4 引数の渡し方： 値渡し
Float8 引数の渡し方： 値渡し
データベージチェックサムのバージョン: 0
===== VACUUM処理 =====
PostgreSQLは、追記型アーキテクチャ(MVCC:MultiVersion Concurrency Control)を採用している。データの変更があっても元のレコードを物理的に消さずに、新しい行を追加して、元のレコードを無効マークとします。\\
VACUUMは、不要領域を再利用可能にし、その箇所をFSM(空き領域マップ)に登録するコマンドです。
==== VACUUMとVACUUM FULL ====
=== VACUUM ===
不要領域を再利用可能な状態にします。
VACUUMは回収したレコードが占めていた領域を再利用可能にするだけであって、肥大化の進行を食い止めることはできますが、肥大化を解消することはできません。
PostgreSQLが管理するトランザクションIDのメンテナンスのために、定期的(デフォルトでは2億トランザクション毎)にデータベース全体のVACUUMが実行されます。
^件数^実行時間^
|約9万件|4秒|
|約2400万件|5分10秒|
=== VACUUM FULL ===
物理的にファイルを圧縮します。
VACUUM FULLは次のような処理を行っています。
- テーブルのデータを1行ずつ取ってきて、別の新しいテーブルに詰め込む
- 新しいテーブルのインデックスを作成する
- テーブルを入れ替える
つまり、有効な行しか取ってこないので不要領域は一切存在しなくなる。VACUUM FULLは一時的に対象テーブルと新しいテーブルが同時に作成されるため容量不足で完遂できないことがあります。
排他ロックが必要なため、VACUUM FULL中はテーブルへアクセス不可となります。\\
より多くのディスク容量を回収することができますが、実行にとても時間がかかります。より高速な代替手段としては、テーブルの再構成を行うかバックアップ・リストアの実施があげられる。
重要な点は、VACUUM FULLはテーブルを小さくしますが、インデックスは小さくしないことです。\\
実際にはインデックスのサイズは大きくなる可能性があり、低速化、インデックスの使用時のより多くのディスクI/Oの発生、必要とするメモリ量の増加が起こります。VACUUM FULLの後にREINDEXが必要になるかもしれません。
^VACUUM FULL^^
^件数^実行時間^
|約9万件|6秒|
|約2400万件|7分|
^REINDEX^^
^件数^実行時間^
|約9万件|6秒|
|約2400万件|6分|
**VACUUM FULLを使うのではなく、pg_repack(Windows版は非対応)を使う。**
==== AUTOVACUUM(自動実行) ====
autovacuumは定期的にテーブル状態を監視し、必要があれば自動でVACUUMやANALYZE を実施する機能です。※VACUUM FULLは行わない。\\
PostgreSQLのオフィシャル布告としてはAUTO推奨で、定期実行などはあまりすべきではない、という推奨状態になっている。
=== 設定確認 ===
有効になっているかは、PostgreSQLの設定ファイル postgresql.conf を確認する。
autovacuum = on
※PostgreSQLの8.3からデフォルトは autovacuum = on であり、コメントアウトされていても明示的にしていることになる。
=== 実行条件 ===
AUTOVACUUMが稼動するのは次の条件に当てはまった場合。
* Dead Tupleがデータ領域の一定割合を超えた場合(autovacuum_vacuum_scale_factor)
* Dead Tupleが各テーブルで定められた閾値を超えた場合(autovacuum_vacuum_threshold)
**統計情報の再計算**\\
以下の計算式以上のレコードが更新（UPDATE/DELETE）されたテーブルは自動的に実行
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://web.archive.org/web/20190531052308/https://tihiro.hatenablog.com/entry/2018/07/17/223913|テーブルごとにAUTO_VACUUMおよびAUTO_ANALYZEをする閾値を設定する]]
例えば、10000件廃止行数が超えたら、自動的にVACUUMさせたいって場合
ALTER TABLE {table_name} SET (autovacuum_vacuum_threshold = 10000);
ALTER TABLE {table_name} SET (autovacuum_vacuum_scale_factor = 0);
巨大なテーブルに対して実行すると、vacuum処理に時間がかかりすぎることによって、表面のアプリケーション側に影響を及ぼすことがあるので、パラメータ変更はせずに非稼働時間に定期的にVACUME ANALIZEを各テーブルに行うようにしたい。
==== VACUUM実行確認 ====
AUTO VACUUMが実行されていない場合、last_autovacuum がnullとなる。
select n_live_tup,n_dead_tup,schemaname, relname,last_vacuum,last_autovacuum
from pg_stat_user_tables where n_dead_tup != 0
=== VACUUM進捗状況のレポート ===
PostgreSQLの9.6からVACUUM実行中の進捗状況のレポート「pg_stat_progress_vacuum」ビューに書き込まれる。\\
[[https://www.postgresql.jp/document/9.6/html/progress-reporting.html|PostgreSQL 9.6.5文書 28.4. 進捗状況のレポート]]
SELECT
pid
, datname
, relname
, ((heap_blks_scanned / heap_blks_total::numeric(10,2)) * 100)::numeric(10,2) as percentage
, p.phase
, index_vacuum_count
FROM pg_stat_progress_vacuum as p, pg_class as c
WHERE p.relid = c.oid
=== 最後にVACUUM/ANALYZEした日時確認 ===
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
==== VACUUM手動実行判断 ====
デッドタプルが多く、有効なレコードに対するデッドタプルの割合の多いテーブルに VACUUM をするようにしてみてください。
SELECT
relname, n_live_tup, n_dead_tup, round(n_dead_tup*100/n_live_tup,2) AS ratio
FROM
pg_stat_user_tables WHERE n_live_tup <> 0
* [[http://blog.fusic.co.jp/archives/747/|PostgreSQL の VACUUM をなんとなくでするのはやめよう]]
* [[http://kashi.way-nifty.com/jalan/2014/02/autovacuum-64d5.html|autovacuum関連のパラメータ設定]]
==== pg_repack =====
pg_repackは、排他ロックを瞬間的にだけ掛けて、あとは必要最小限のロックだけかけるため、処理中もテーブルへの参照・更新処理が実施できます。
* [[https://github.com/reorg/pg_repack/blob/master/doc/pg_repack_jp.rst|pg_repack -- PostgreSQLデータベースのテーブルを最小限のロックで再編成します]]
* [[https://qiita.com/bwtakacy/items/3be7a3e1540ccef7c9e3|運用で役立つツールpg_repackのご紹介]]
* [[http://techblog.lclco.com/entry/2016/01/27/032402|PostgreSQLでWebサービスを運用するためにやっていること]]
* [[http://a4dosanddos.hatenablog.com/entry/2016/05/06/121708|pg_repack のインストール]]
* [[http://everything-you-do-is-practice.blogspot.jp/2017/09/pgreorg-24365.html|PostgreSQL のテーブルを無停止で最適化する]]
* [[https://www.slideshare.net/hadoopxnttdata/postgresql-operations-monitoring-etc|PostgreSQLの運用・監視にまつわるエトセトラ - Slideshare]]
==== 参照 =====
* [[https://www.slideshare.net/masahikosawada98/inside-vacuum|Inside vacuum - 第一回PostgreSQLプレ勉強会]]
* [[https://wiki.postgresql.org/wiki/VACUUM_FULL/ja|VACUUM FULL/ja]]
* [[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で参照しているテーブル、カラムを個別に拡張することが効果的。
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,294,967,295）の値をとる
* OIDは一周して同じ値をとる場合があるため、一意であると仮定してはならない
* 主キーを持たないテーブル、重複行があるテーブルなどに有効
※テーブル作成時の「WITH OIDS」はPostgreSQL12で廃止
[[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の仕組み]]
===== トランザクション =====
==== トランザクション寿命の確認 ====
age が 20 億に近い場合、そのデータベースは、周回問題の危険性を回避するために、再度バキュームされなければなりません。\\
[[https://www.postgresql.jp/document/7.4/html/maintenance.html|第 21章定常的なデータベース保守作業]]
postgresqlはすべてのトランザクションにxidが割り当てられている。xidは2の32乗(42億）。 VACUUMせずにほっておくとxidが0に戻った時点で行が見えなくなる。（行のxidが大きいので） VACUUMをかける時期はage関数で把握。
SELECT datname, age(datfrozenxid) FROM pg_database
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コマンドを使用して、インデックスの再構築する。\\
rem PK_REP_RES_QUEをリインデックスする
SET PGPASSWORD=wh_kousei
psql -U wh_kousei -c "reindex INDEX pk_rep_res_que;"
==== テーブルロックなしのリインデックス ====
reindexコマンドはテーブルロックがかかってしまうので、運用中のDBに対して使うのは難しい。\\
ただ、PostgreSQLでは別名で全く同じインデックスの作成を行うことができる。\\
※PostgreSQL12からは、REINDEX CONCURRENTLY が導入され、REINDEX による重いロックなしでインデックスを再構築できます。
* [[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上で疑似的にロック無しのインデックスの再構築
rem PK_REP_RES_QUEをリインデックスする
SET PGPASSWORD=wh_test
psql -U wh_test -f 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;
===== 確認用SQL =====
=== テーブルのディスク使用量を取得する ===
SELECT relname, relfilenode, relpages FROM pg_class;
=== 実行中のSQL文を出力するSQL ===
SELECT procpid,start,now() - start AS lap,current_query
FROM (SELECT backendid,pg_stat_get_backend_pid(S.backendid) AS procpid,pg_stat_get_backend_activity_start(S.backendid) AS start,pg_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S ) AS S WHERE current_query <> '' ORDER BY lap DESC;
=== 実行中のselectをキャンセルするSQL ===
SELECT pg_cancel_backend(PID);
=== テーブル単位のバキューム ===
vacuumdb db_name -v -z -t 'tbl_name'
=== データベースのサイズを取得するSQL ===
SELECT pg_database_size('db_name');
=== テーブル単位のサイズを取得する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
[[https://qiita.com/awakia/items/99c3d114aa16099e825d|PostgreSQLで各テーブルの総サイズと平均サイズを知る]]
=== テーブル単位のダンプ ===
pg_dump db_name --table=tbl_name > tbl_name.sql
===== アップデート =====
マイナーバージョンアップは、バイナリ変更だけで対応できますが、メジャーバージョンアップでは、データファイルの移行の必要があるので、メジャーアップデート作業はアップデート計画に沿って、慎重に（シュミュレーション・予行演習など）行うことが大切となります。
PostgreSQLは、開発コミュニティによるサポート期間がメジャーバージョンアップから5年間となっている。
* [[https://www.postgresql.jp/news|日本PostgreSQLユーザ会 ニュース(アップデート情報)]]
* [[https://www.pgecons.org/wp-content/uploads/PGECons/2013/WG2/12_VersionUp.pdf|PostgreSQL エンタープライズ・コンソーシアム 技術部会 WG#2]]
==== バージョン番号の説明 ====
PostgreSQL X.Y.Z
例えば、9.6.3なら、メジャーバージョンが9.6、マイナーバージョンが3となります。\\
メジャーバージョンアップは１年毎、マイナーバージョンアップは、年に３回～５回程度実施されます。
X, Y, Z は以下の様な定義で管理されています。
^No.^バージョン記号^メジャーバージョン/マイナーバージョン番号^バージョンアップのタイミング^
|1|X|メジャーバージョン番号|システムテーブルやデータファイルの構造が変更された場合。|
|2|Y|メジャーバージョン番号|上記メジャーバージョンとの相違点は明確に定義されていません。|
|3|Z|マイナーバージョン番号|セキュリティバグやデータ破損の可能性のあるバグ等が修正された場合。\\ その他の軽微な修正も同時に行われる。|
※10系から、PostgreSQL X.Zと二つの数字で表記に変更。最初がメジャーバージョン：新機能追加、最後がマイナーバージョン：バグフィックスなど
==== マイナーアップデート ====
インストーラの動作は普段と同じ。前のバージョンがあるので、プログラムの上書き確認と、既にあるデータフォルダを使う確認を経て始まります。PostgreSQL本体だけのアップデートなので、データは何もしない。\\
[[http://kenpg2.seesaa.net/article/414464482.html|PostgreSQL 9.4.1 ほかアップデート版をインストール]]
==== メジャーアップデート ====
メジャーアップデートでは、データのバックアップとリストアの作業が必要である。\\
メジャーアップデート用ツールとして、[[https://www.postgresql.jp/document/9.6/html/pgupgrade.html|pg_upgrade]]がある。
=== 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にバージョンアップ]]