it技術:データベース:postgresql:運用
差分
このページの2つのバージョン間の差分を表示します。
両方とも前のリビジョン前のリビジョン次のリビジョン | 前のリビジョン次のリビジョン両方とも次のリビジョン | ||
it技術:データベース:postgresql:運用 [2019/07/29 00:37] – [WALアーカイブの削除] yajuadmin | it技術:データベース:postgresql:運用 [2021/06/24 18:08] – [wal_keep_segments] yajuadmin | ||
---|---|---|---|
行 50: | 行 50: | ||
[[https:// | [[https:// | ||
- | * メモリ上でソートが行えるようwork\_memパラメータを調整 | + | * メモリ上でソートが行えるようwork_memパラメータを調整 |
* work_memはセッションごとに確保される領域であるため、SETコマンドで処理に応じて調整するのが望ましい | * work_memはセッションごとに確保される領域であるため、SETコマンドで処理に応じて調整するのが望ましい | ||
行 74: | 行 74: | ||
^搭載メモリ^推奨値^ | ^搭載メモリ^推奨値^ | ||
- | |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 ==== | ||
行 111: | 行 111: | ||
* [[https:// | * [[https:// | ||
+ | ==== wal_keep_segments ==== | ||
+ | バックアップ後のpg_logに下記のエラーが発生していました。 | ||
+ | < | ||
+ | 2019-08-06 02:18:35 JST ERROR: | ||
+ | 要求された wal セグメント * はすでに削除されています。 | ||
+ | </ | ||
+ | |||
+ | 対処として、wal_keep_segmentsをゼロ以外の値に設定する。\\ | ||
+ | これは、ストリームの危険性が追いつかなくなるのを防ぐためです。\\ | ||
+ | ただしpg_basebackupを使用する場合は、 – checkpoint = fastも忘れないでください。\\ | ||
+ | https:// | ||
+ | |||
+ | < | ||
+ | # | ||
+ | ↓ | ||
+ | wal_keep_segments = 128 | ||
+ | |||
+ | backup.bat の見直し | ||
+ | pg_basebackup -U postgres -D " | ||
+ | ↓ | ||
+ | pg_basebackup -U postgres -D " | ||
+ | </ | ||
+ | |||
+ | ===== パフォーマンスチューニング ===== | ||
+ | [[https:// | ||
+ | |||
+ | - VACUUMで不要領域を再利用可能な状態にする | ||
+ | - REINDEXでインデックスの不要領域を削除する | ||
+ | - ANALYZEで統計情報を最新化する | ||
+ | - VACUUM FREEZEでトランザクションIDを凍結状態にする | ||
===== データベース初期化 ===== | ===== データベース初期化 ===== | ||
==== 全てのデータベース初期化 ==== | ==== 全てのデータベース初期化 ==== | ||
行 125: | 行 155: | ||
initdb: ディレクトリ " | initdb: ディレクトリ " | ||
- | Userに変更にチェックを追加 することで回避した。\\ | + | Userに変更のチェックを追加 することで回避した。\\ |
他にもrunasコマンドを使用する(データベースクラスタの作成過程でpostgresプロセスを起動しますが、このプロセスは管理者権限では実行できないため)\\ | 他にもrunasコマンドを使用する(データベースクラスタの作成過程でpostgresプロセスを起動しますが、このプロセスは管理者権限では実行できないため)\\ | ||
* [[https:// | * [[https:// | ||
行 221: | 行 251: | ||
<code bash コマンドプロンプト> | <code bash コマンドプロンプト> | ||
- | > pg_basebackup -U postgres -D " | + | > pg_basebackup -U postgres -D " |
</ | </ | ||
^Option^説明^ | ^Option^説明^ | ||
|-D|ベースバックアップ出力先パスを指定。出力先は空でなければならない。 \\ ディレクトリが存在しなければ作ってくれる。| | |-D|ベースバックアップ出力先パスを指定。出力先は空でなければならない。 \\ ディレクトリが存在しなければ作ってくれる。| | ||
- | |-F t|出力する形式。tはtarで出力する。| | + | |-F t|出力する形式。tはtarで出力する。圧縮するためにはtarの指定が必要| |
|-x|ベースバックアップ処理中、データベースが更新された際もファイルシステム上のファイルは更新しないようにする。出力中はメモリ上で頑張る。 \\ (そうすることで、出力ファイルが変な状態になることを防ぐ)| | |-x|ベースバックアップ処理中、データベースが更新された際もファイルシステム上のファイルは更新しないようにする。出力中はメモリ上で頑張る。 \\ (そうすることで、出力ファイルが変な状態になることを防ぐ)| | ||
|-z|gzipで圧縮した状態にする| | |-z|gzipで圧縮した状態にする| | ||
+ | |--checkpoint=fast|fast を指定すると、バックアップ開始時のチェックポイント処理は高速になりますが、集中した I/O のために動作中のアプリケーションへの性能の影響が大きくなります。 spread ではチェックポイントはゆっくり実行されるためアプリケーションへの影響は小さいですが、バックアップに時間がかかります。| | ||
-Dオプションでバックアップ保存先を指定する。サイズが大きい場合はtar-gz圧縮する。だいたい5分以内で完了する。 | -Dオプションでバックアップ保存先を指定する。サイズが大きい場合はtar-gz圧縮する。だいたい5分以内で完了する。 | ||
行 258: | 行 289: | ||
* [[https:// | * [[https:// | ||
* [[https:// | * [[https:// | ||
+ | * [[https:// | ||
==== 設定 ==== | ==== 設定 ==== | ||
postgresql.conf で設定する。 | postgresql.conf で設定する。 | ||
行 323: | 行 355: | ||
よって、不要なWALアーカイブの削除は手動で行うこととする。\\ | よって、不要なWALアーカイブの削除は手動で行うこととする。\\ | ||
※postgreSQLサービスを再起動すればリカバリが実行され、recovery.confはrecovery.doneになります。 | ※postgreSQLサービスを再起動すればリカバリが実行され、recovery.confはrecovery.doneになります。 | ||
- | ==== WALアーカイブからのリストア | + | ==== WALアーカイブからのリカバリ |
- | [[http:// | + | リストアとリカバリとは区別しないで使うときもあるが、今回は分けました。 |
+ | * リストアはバックアップデータを、バックアップを取ったときと同じ状態に物理的に復元すること | ||
+ | * リカバリはリストアしたデータに何かの処理をして最新の状態や正常な状態に復旧させること | ||
+ | |||
+ | === PITRの仕組み === | ||
+ | PITR(Point In Time Recovery)は、WALレコード適用によるリカバリが前提となっている。 | ||
+ | * [[http:// | ||
+ | * [[http:// | ||
+ | |||
+ | **WALレコードの適用までの流れ**\\ | ||
+ | リカバリを開始してWALレコードを適用するまでの流れは次のようになっている。 | ||
+ | |||
+ | - pg_controlファイルを読み込む | ||
+ | - recovery.confを読み込む | ||
+ | - backup_labelを読み込む | ||
+ | - pg_controlファイルを更新し、backup_labelを削除する | ||
+ | - 必要なWALを繰り返し適用する | ||
+ | |||
+ | ※backup_labelファイルから適用を開始すべきWALの位置を取得できなかった場合は、pg_controlファイルの情報を元にリカバリを開始します。 | ||
+ | |||
+ | pg_controlファイルは、バイナリファイルなので通常のエディタでは内容を確認できません。そのために pg_controldata コマンドが用意されています。\\ | ||
pg_controldata コマンドで最終チェックポイントのREDO WALファイルが分かる。 | pg_controldata コマンドで最終チェックポイントのREDO WALファイルが分かる。 | ||
行 465: | 行 517: | ||
</ | </ | ||
+ | === 最後にVACUUM/ | ||
+ | <code sql> | ||
+ | SELECT relname, n_live_tup, n_dead_tup, last_vacuum, | ||
+ | FROM pg_stat_all_tables | ||
+ | WHERE schemaname = ' | ||
+ | ORDER BY relname | ||
+ | </ | ||
==== VACUUM手動実行判断 ==== | ==== VACUUM手動実行判断 ==== | ||
デッドタプルが多く、有効なレコードに対するデッドタプルの割合の多いテーブルに VACUUM をするようにしてみてください。 | デッドタプルが多く、有効なレコードに対するデッドタプルの割合の多いテーブルに VACUUM をするようにしてみてください。 | ||
行 507: | 行 566: | ||
ageが15億になる前にVACUUMをかける。かけるとまた10億になる。 | ageが15億になる前にVACUUMをかける。かけるとまた10億になる。 | ||
[[http:// | [[http:// | ||
+ | |||
+ | ===== インデックスの再構築 ===== | ||
+ | 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:// | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | * [[http:// | ||
+ | * [[https:// | ||
+ | |||
+ | <code bat> | ||
+ | rem PK_REP_RES_QUEをリインデックスする | ||
+ | SET PGPASSWORD=wh_test | ||
+ | psql -U wh_test -f reindex_rep_res_que.sql | ||
+ | </ | ||
+ | |||
+ | <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; | ||
+ | </ | ||
===== 確認用SQL ===== | ===== 確認用SQL ===== | ||
=== テーブルのディスク使用量を取得する === | === テーブルのディスク使用量を取得する === | ||
行 559: | 行 661: | ||
|3|Z|マイナーバージョン番号|セキュリティバグやデータ破損の可能性のあるバグ等が修正された場合。\\ その他の軽微な修正も同時に行われる。| | |3|Z|マイナーバージョン番号|セキュリティバグやデータ破損の可能性のあるバグ等が修正された場合。\\ その他の軽微な修正も同時に行われる。| | ||
- | ※10系から、PostgreSQL X.Zと二つの数字で表記に変更。最初がメジャーバージョン、新機能追加 | + | ※10系から、PostgreSQL X.Zと二つの数字で表記に変更。最初がメジャーバージョン:新機能追加、最後がマイナーバージョン:バグフィックスなど |
==== マイナーアップデート ==== | ==== マイナーアップデート ==== | ||
行 567: | 行 669: | ||
==== メジャーアップデート ==== | ==== メジャーアップデート ==== | ||
メジャーアップデートでは、データのバックアップとリストアの作業が必要である。\\ | メジャーアップデートでは、データのバックアップとリストアの作業が必要である。\\ | ||
- | メジャーアップデート用ツールとして、[[https:// | + | メジャーアップデート用ツールとして、[[https:// |
- | [[https:// | + | |
+ | === pg_upgrade === | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ |
it技術/データベース/postgresql/運用.txt · 最終更新: 2024/04/24 11:07 by yajuadmin