ユーザ用ツール

サイト用ツール


it技術:データベース:postgresql:運用

文書の過去の版を表示しています。


運用

※Windows版PostgreSQL9.6を使用している。

Windows版の未対応ツール
ツール名内容
pg_rmanバックアップ/リストアを体系的に管理する
pg_statsinfoPostgreSQLのサーバ運用状況を定期的に取得する
pg_monszabbixを使ったサーバ運用状況の監視

チューニング

代表的なチューニング項目を記す。
pg_confファイルの設定内容を見直す。設定変更後はPostgreSQLを再起動する。

shared_buffers

PostgreSQL全体で試用する共有メモリキャッシュとなり、データの蓄積が進んだ際の応答性能に影響します。
サーバの搭載メモリの25%程度が目安となり、増やしすぎても効果はありません。
※Windowsシステムでの有効なshared_buffersの範囲は一般的に64MBから512MBです。Windows版は64MBあれば十分のようなのでデフォルトのまま。
https://www.postgresql.jp/document/9.6/html/runtime-config-resource.html

既定値:128MB

搭載メモリ推奨値
2GB512MB
4GB1GB
8GB2GB
16GB4GB

work_mem

PostgreSQLの各プロセスが使用するソート用のメモリバッファです。
瞬間最大でメモリを「work_mem」×「max_connection」+α を消費するため、ピークを想定した設定を行う。
搭載メモリに合わせる。

既定値:1MB

搭載メモリ推奨値
2GB4MB
4GB8MB
8GB8MB
16GB16MB

バッチ処理等で大量のソートが発生する場合

ディスクソートの発生を避ける

  • メモリ上でソートが行えるよう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

搭載メモリ推奨値
2GB512MB
4GB1GB
8GB2GB
16GB4GB

random_page_cost

HDDからランダムにブロックを読み取るコスト
この値を小さくすると、問い合わせオプティマイザが seq scan ではなく積極的に index scan を選択する効果があります。

既定値:4

下記サイトはHDDではなくSSDの場合、random_page_costを1にしてハッシュ結合ではなくネステッドループ結合されるようなり速くなった。
[翻訳] たった一つの設定変更が如何にしてクエリのパフォーマンスを50倍も改善したか

checkpoint_segments

checkpoint_segmentsのデフォルト値は「3」ですので、デフォルトの設定だと48MB(16MB×3)のトランザクションログを書くとチェックポイントが発生することになります。
しかし、この設定のままだとチェックポイントが頻発してしまうことがあります。 checkpoint_segmentsに64~128程度を初期値として設定する。
checkpoint_segmentsのチューニングでPostgresのパフォーマンスが10倍以上に

PostgreSQL 9.5でcheckpoint_segmentsが2つのパラメータ(max_wal_size,min_wal_size)に置き替えられた。
【PostgreSQL 9.5】max_wal_sizeとmin_wal_sizeの概要

checkpoint_timeout

チェックポイント発生する間隔を「時間」で指定するものです。
一般的には30min~1hがいいと言われている。

checkpoint_timeoutのデフォルト値は「5分」になっていますが、少し短すぎるので「30分」を初期値として設定する。

max_wal_size

WALファイル数の上限
5分で400MBとするとcheckpoint_timeoutが30分なら6倍して、400MBx6=2400MB。
これを3倍したら7200MBなので10GBあればいい。

データベース初期化

全てのデータベース初期化

コマンドプロンプトから実行する。initdb.exeはPostgreSQLのBINフォルダにある。

>initdb --locale=C --encoding=UTF-8 -D "D:\Program Files\PostgreSQL\9.6\data" -U postgres -A md

Permission denied

initdb: ディレクトリ “D:\Program Files\PostgreSQL\9.6\data”の権限を変更できませんでした:Permission denied

Userに変更にチェックを追加 することで回避した。
他にもrunasコマンドを使用する(データベースクラスタの作成過程でpostgresプロセスを起動しますが、このプロセスは管理者権限では実行できないため)

特定のデータベース初期化

スーパーユーザー(例 postgres)にてデータベースを削除する。
データベースの削除

drop database test;

ログ設定

ログ出力設定変更

logging_collectorを「off」から「on」にして、pg_logフォルダにログ出力させる。
障害発生に備えて設定すべき3つのログ関連パラメーター

postgresql.conf
logging_collector = on		# Enable capturing of stderr and csvlog
log_line_prefix='[%t] %u %d %p[%l] '	# special values:

※変更後は、PostgreSQLサービス再起動

ログ出力例
[2019-04-25 14:37:08 JST]  7496[1] LOG:  database system was shut down at 2019-04-25 14:37:06 JST
[2019-04-25 14:37:08 JST]  7496[2] LOG:  MultiXact member wraparound protections are now enabled
[2019-04-25 14:37:08 JST]  6592[3] LOG:  database system is ready to accept connections
[2019-04-25 14:37:08 JST]  3240[1] LOG:  autovacuum launcher started

バックアップとリストア

フォルダコピーによるバックアップとリストア

ActiveImage Protector ではFドライブ丸ごとバックアップされるので、リストアする際にはFドライブを丸ごとコピーすればいい。
PostgreSQL 9.2のバックアップリストア Windowsのフォルダコピーでバックアップとリストア

フォルダコピーによるバックアップ

バックアップは以下の手順で行います。

  1. PostgreSQLサービスの停止
  2. バックアップ(Dataフォルダのコピー)
  3. PostgreSQLサービスの開始

フォルダコピーによるリストア

リストアは以下の手順で行います。

  1. PostgreSQLサービスの停止
  2. リストア(Dataフォルダの書き戻し)
  3. 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を停止しない状態のオンラインバックアップができる。

pg_dumpでオンライン・バックアップを行う際の手順

  1. pg_start_backup()を実行
  2. データベースのファイルをコピー(オフライン・バックアップと同様)
  3. 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

-Dオプションでバックアップ保存先を指定する。サイズが大きい場合はtar-gz圧縮する。だいたい5分以内で完了する。

「FATAL: pg_hba.conf にホスト“127.0.0.1”、ユーザ“postgres”, SSL無効用のエントリがありません.」のエラーが出た場合、以下のコメントアウトを外す。

pg_hda.conf
host    replication     postgres        127.0.0.1/32            md5

postgresql.conf の max_wal_sender 設定のデフォルトは 0 です。最低でも 1 以上でないと pg_basebackup コマンドは実行できないので修正します。

postgresql.conf
max_wal_sender = 1

pg_rman

pg_rmanという世代管理対応のツールがあるが、Windowsは動作保証されていない。

WALアーカイブログ

WALアーカイブログは、PostgreSQLが出力したトランザクションログ(WAL)ファイルを別の場所にコピーすることです。
WALファイルはデフォルト設定では直近の一定数しか保存されず、古くなったものは削除もしくは再利用されていきます。そのため、リカバリに使えるようにWALファイルを別の場所に保存しておく必要があります。

WALファイルとは

WALはログ先行書き込みのことで、トランザクションのログを残すための一般的な手法です。
データベースソフトウェアの本質として、ディスクに書き込まれていない状態で障害が発生するとリカバリにWALファイルが必要になる。

設定

postgresql.conf で設定する。

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”」などのようにパスの部分をダブルクォテーションでくくらないと正しく実行されません。

確認方法

簡易的な確認は、WALのスイッチの実行を行う。
PSQLで下記を実行すると、archive_commandの内容を実行します。

SELECT pg_switch_xlog();

PostgresのPITR

WALのアーカイブの流れ

WALレコードが更新処理によって挿入され、16MBいっぱいになる、もしくはWALのスイッチが実行されると、pg_xlog/archive_statusディレクトリ配下に対象のWALファイルをアーカイブしてもよいことを示す「<WALファイル名>.ready」というファイルが作成されます。

アーカイブプロセスは、次のタイミングでarchive_statusディレクトリをチェックし、「<WALファイル名>.ready」があれば、archive_commandの内容を実行します。

  • 60秒間隔(archive_timeoutが指定されている場合)
  • PostgreSQLの停止時
    無事にWALファイルを処理できたら、archive_statusディレクトリ「<WALファイル名>.ready」ファイルは「<WALファイル名>.done」 にリネームされます。

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 - 本

WALアーカイブの削除

pg_archivecleanupコマンドで削除する。

pg_basebackup を実行すると、WALアーカイブのフォルダに拡張子backupが作成される。
これにより指定した拡張子backupより前の不要なWALアーカイブの削除される。

pg_archivecleanup "D:\\PostgreSQL\\ARCHIVELOG" "000000020000000600000036.00000028.backup"

溜まったアーカイブログを削除するタイミング

archive_cleanup_command

recovery.conf設定ファイルにarchive_cleanup_commandを指定するとpg_archivecleanupコマンドの実行後に不要なWALアーカイブを自動で削除してくれる。
PostgreSQL の archive_cleanup_command はいつ実行されるのか?

recovery.conf
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アーカイブからのリストア

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中はテーブルへアクセス不可となります。
より多くのディスク容量を回収することができますが、実行にとても時間がかかります。より高速な代替手段としては、テーブルの再構成を行うかバックアップ・リストアの実施があげられる。

重要な点は、VACUUM FULLはテーブルを小さくしますが、インデックスは小さくしないことです。
実際にはインデックスのサイズは大きくなる可能性があり、低速化、インデックスの使用時のより多くのディスクI/Oの発生、必要とするメモリ量の増加が起こります。VACUUM FULLの後にREINDEXが必要になるかもしれません。

VACUUM FULL
件数実行時間
約9万件6秒
約2400万件7分
REINDEX
件数実行時間
約9万件6秒
約2400万件6分

VACUUM FULLを使うのではなく、pg_repackを使う。

AUTOVACUUM(自動実行)

autovacuumは定期的にテーブル状態を監視し、必要があれば自動でVACUUMやANALYZE を実施する機能です。※VACUUM FULLは行わない。
PostgreSQLのオフィシャル布告としてはAUTO推奨で、定期実行などはあまりすべきではない、という推奨状態になっている。

設定確認

有効になっているかは、PostgreSQLの設定ファイル postgresql.conf を確認する。

postgresql.conf
autovacuum = on

※PostgreSQLの8.3からデフォルトは autovacuum = on であり、コメントアウトされていても明示的にしていることになる。

実行条件

AUTOVACUUMが稼動するのは次の条件に当てはまった場合。

  • Dead Tupleがデータ領域の一定割合を超えた場合(autovacuum_vacuum_scale_factor)
  • Dead Tupleが各テーブルで定められた閾値を超えた場合(autovacuum_vacuum_threshold)

AUTOVACUUMデーモンが一定間隔でこれらの状況を監視し、ロック競合が発生しない限り、一定数量のAUTOVACUUM処理が実行される。

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」ビューに書き込まれる。
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手動実行判断

デッドタプルが多く、有効なレコードに対するデッドタプルの割合の多いテーブルに 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

pg_repack

参照

トランザクション

トランザクション寿命の確認

age が 20 億に近い場合、そのデータベースは、周回問題の危険性を回避するために、再度バキュームされなければなりません。
第 21章定常的なデータベース保守作業

postgresqlはすべてのトランザクションにxidが割り当てられている。xidは2の32乗(42億)。 VACUUMせずにほっておくとxidが0に戻った時点で行が見えなくなる。(行のxidが大きいので) VACUUMをかける時期はage関数で把握。

SELECT datname, age(datfrozenxid) FROM pg_database

ageが15億になる前にVACUUMをかける。かけるとまた10億になる。 VACUUMとage関数

確認用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');

テーブル単位のダンプ

pg_dump db_name --table=tbl_name > tbl_name.sql

アップデート

マイナーバージョンアップは、バイナリ変更だけで対応できますが、メジャーバージョンアップでは、データファイルの移行の必要があるので、メジャーアップデート作業はアップデート計画に沿って、慎重に(シュミュレーション・予行演習など)行うことが大切となります。

PostgreSQLは、開発コミュニティによるサポート期間がメジャーバージョンアップから5年間となっている。

バージョン番号の説明

PostgreSQL X.Y.Z

例えば、9.6.3なら、メジャーバージョンが9.6、マイナーバージョンが3となります。
メジャーバージョンアップは1年毎、マイナーバージョンアップは、年に3回~5回程度実施されます。

X, Y, Z は以下の様な定義で管理されています。

No.バージョン記号メジャーバージョン/マイナーバージョン番号バージョンアップのタイミング
1Xメジャーバージョン番号システムテーブルやデータファイルの構造が変更された場合。
2Yメジャーバージョン番号上記メジャーバージョンとの相違点は明確に定義されていません。
3Zマイナーバージョン番号セキュリティバグやデータ破損の可能性のあるバグ等が修正された場合。
その他の軽微な修正も同時に行われる。

※10系から、PostgreSQL X.Zと二つの数字で表記に変更。最初がメジャーバージョン、新機能追加 – 最後がマイナーバージョン、バグフィックスなど

マイナーアップデート

インストーラの動作は普段と同じ。前のバージョンがあるので、プログラムの上書き確認と、既にあるデータフォルダを使う確認を経て始まります。PostgreSQL本体だけのアップデートなので、データは何もしない。
PostgreSQL 9.4.1 ほかアップデート版をインストール

メジャーアップデート

メジャーアップデートでは、データのバックアップとリストアの作業が必要である。
メジャーアップデート用ツールとして、pg_upgradeがある。
pg_upgrade を使ったDBクラスタ移行手順 on Windows

it技術/データベース/postgresql/運用.1559713921.txt.gz · 最終更新: 2019/06/05 14:52 by yajuadmin