ユーザ用ツール

サイト用ツール


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

運用

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

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

チューニング

代表的なチューニング項目を記す。
pg_confファイルの設定内容を見直す。設定変更後はPostgreSQLを再起動する。
チューニング設定生成ツール「PGTune」を使用すると、postgresql.confの推奨値を出力してくれる。

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→1GB
4GB1GB→2GB
8GB2GB→4GB
16GB4GB→8GB

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あればいい。

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

パフォーマンスチューニング

パフォーマンスチューニング9つの技 ~「基盤」について~

  1. VACUUMで不要領域を再利用可能な状態にする
  2. REINDEXでインデックスの不要領域を削除する
  3. ANALYZEで統計情報を最新化する
  4. 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

Permission denied

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

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

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

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

drop database test;

バックアップとリストア

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

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_basebackupでオンライン・バックアップを行う際の手順

  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 --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は使用不可になった)
-zgzipで圧縮した状態にする
–checkpoint=fastfast を指定すると、バックアップ開始時のチェックポイント処理は高速になりますが、集中した I/O のために動作中のアプリケーションへの性能の影響が大きくなります。 spread ではチェックポイントはゆっくり実行されるためアプリケーションへの影響は小さいですが、バックアップに時間がかかります。

-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 コマンドは実行できないので修正します。※PostgreSQL12ではデフォルトは 10 となった。

postgresql.conf
max_wal_sender = 10

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();
↓ PostgreSQL10以降ではコマンド名変更
SELECT pg_switch_wal();

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 設計・運用計画の鉄則 - 本

archive_timeout

archive_timeoutはトランザクションがほとんど発生しない「なぎ」のとき、WALにたまった内容がいつまで経ってもアーカイブされないことを防ぐ。デフォルトでは0(機能無効)

archive_timeout = 60 とした場合、16MB*60回/時*24時間=23040MB/日≒23GB/日 が発生しうる。 archive_timeoutの意味

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アーカイブからのリカバリ

リストアとリカバリとは区別しないで使うときもあるが、今回は分けました。

  • リストアはバックアップデータを、バックアップを取ったときと同じ状態に物理的に復元すること
  • リカバリはリストアしたデータに何かの処理をして最新の状態や正常な状態に復旧させること

PITRの仕組み

PITR(Point In Time Recovery)は、WALレコード適用によるリカバリが前提となっている。

WALレコードの適用までの流れ
リカバリを開始してWALレコードを適用するまでの流れは次のようになっている。

  1. pg_controlファイルを読み込む
  2. recovery.confを読み込む → PostgreSQL12ではrecovery.conf廃止、postgresql.confに統一
  3. backup_labelを読み込む
  4. pg_controlファイルを更新し、backup_labelを削除する
  5. 必要な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. テーブルのデータを1行ずつ取ってきて、別の新しいテーブルに詰め込む
  2. 新しいテーブルのインデックスを作成する
  3. テーブルを入れ替える

つまり、有効な行しか取ってこないので不要領域は一切存在しなくなる。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 を確認する。

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 を実行することを推奨
テーブルごとに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」ビューに書き込まれる。
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

pg_repack

参照

統計情報

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で廃止

【PostgreSQL】OIDとは?について調べたことのメモ

レコードのデータ識別

内部的にはレコードのデータにデータを識別するOID(オブジェクトID)とトランザクションを制御するXID(トランザクションID)をくっ付けて管理しています。
新しいトランザクションが発生すると元のデータはそのままにして、OIDはそのままでXIDを一個増やしたデータを追記しようとします。
第3回:アーキテクチャ比較 ファイル構造の違い - PostgreSQLの特徴 MVCCの仕組み

トランザクション

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

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関数

インデックスの再構築

PostgreSQL のインデックスサイズは一度大きくなると、その後小さくなるタイミングが限られています。

  • DROP INDEX でテーブル自体を削除した場合
  • TRUNCATE TABLE でテーブル全体を空にした場合
  • REINDEX でインデックスを再構成した場合

インデックスが肥大化した状況では実行計画のコスト計算に影響することがあります。これは適切な実行計画を選択する妨げとなるかもしれません。

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 による重いロックなしでインデックスを再構築できます。

PostgreSQL 9.6上で疑似的にロック無しのインデックスの再構築

rem PK_REP_RES_QUEをリインデックスする
SET PGPASSWORD=wh_test
psql -U wh_test -f reindex_rep_res_que.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

テーブルのディスク使用量を取得する

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

PostgreSQLで各テーブルの総サイズと平均サイズを知る

テーブル単位のダンプ

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

it技術/データベース/postgresql/運用.txt · 最終更新: 2022/06/06 11:17 by yajuadmin