参考にした資料たち
https://postgresqlco.nf/ja/doc/param/ (パラメータはここで調べろ！)
OSS-DB GOLD 公式
OSS-DB GOLD セミナー資料
OSS-DB GOLD 例題
[改訂新版］内部構造から学ぶPostgreSQL 設計・運用計画の鉄則
やったこと
ご親切なテキストなんてものは存在しない(ことにする)ので、とにかくPostgreSQLについて学ぶ
- PostgreSQLの公式リファレンスを読む
2）[改訂新版］内部構造から学ぶPostgreSQL 設計・運用計画の鉄則を3週 - セミナー資料を読む
- 例題をとく
- 不明点を公式リファレンスとすり合わせる
以下メモ書き
（重複やtypo等ございましたらすみませぬ）
名称が変更されたもの（まだありそう）
pg_log => log
pg_xlog => pg_wal
pg_clog => pg_xcal
pg_current_xlog_location => pg_current_wal_lsn
pg_xlogdump => pg_waldump
pg_receivexlog => pg_receivewal
メモリ
kernel.shmmax
共有メモリセグメント最大値
kernel.shmall
使用可能な共有メモリサイズ（ページ単位）
共有バッファ
shared_buffres
ディスクから読み取ったデータをキャッシュ、その後高速に応答
物理メモリの20%-40%
キャッシュヒット率の確認
キャッシュヒット率の算出
blks_hit / (blks_hit + blks_read)
WALバッファ
wal_buffers
WALファイルへのI/Oをシーケンシャルにするため
自動調整の上限は16MB
WALセグメントサイズの倍(32MB)程度が好ましい
WALフラッシュのタイミング
トランザクションコミット
wal_writer_dilay時間が経過したとき 3s
WAL_bufferが一杯になったとき
work_mem
セッションごとに確保される
ソート・ハッシュの一時領域
maintenance_work_mem
メンテナンス操作
autovacuum_work_mem(9.4~)
maxprepared_transactions
プリペアドトランザクションの上限
default: 0
max_locks_per_transaction
平均取得ロック数
default: 64
サーバ設定
postgresql.conf
max_connections
default: 100
superuser_reserved_connections
スーパーユーザー用予約枠
default: 3
deadlock_timeout
default: 1000ms
プロセス構造
バックグラウンドプロセス
logger process: サーバーログ出力
ディフォルトは無効だが、有効化すべき
logging_collector = on;
log_destination = 'ディフォルトでOK'
ログ循環設計
ログ出力設定
writer process: データ書き出し
wal writer process: WAL書き出し
wal sender / wal reciver
max_wal_sernderでマスター側にて指定した数のスタンバイを持つ
recovery.conf
hot_standby = on;
recovery.conf
autovacuum_lancher proces: VACUUM実行
自動VACUUMを活用
autovacuum = on;
autovacuum_vacuum_threshold
autovacuum_vacuum_scalefactor
性能影響を抑えるために、遅延VACUUMを使う
achiver process: WALアーカイブ
archive_mode
archive_command: WALファイルをコピーするコマンド
wal_level=replica / logical;
status collector process: 統計情報収集
データ構造
テーブルファイル
1ファイル最大1G,増加に伴いページ単位(8192B)に増加
メンテナンス処理によりVisibilityMap FreeSpaceMapといったファイルも作成
VisibilituMap: VACCUM時に不要なページをスキャンしないため
FreeSpaceMap: 空き容量を監視し、挿入時に使用
インデックスファイル
1ファイル最大1G,増加に伴いページ単位(8192B)に増加
先頭の1ページはメタページ・固定
その他のページはルート・リフーページ・インターナルページ
テーブル空間
- CREATE TABLESPACE <空間名> LOCATION '<ディレクトリ>';
OIDが割り当てられ、${pgdata}/pg_tblspc配下にシンボリックリンクとして配置
別デバイスに配置し、I/Oを改善したい場合に有効
オンラインバックアップではテーブル空間のバックアップも忘れずに
テーブル構造
ファイル構造やページ構造は理解必須
1つのオブジェクト
1GBごとのセグメントファイル+ VM,FSM
セグメントファイルは8KB単位のページで構成される
[ページヘッダ, アイテムポインタ1, アイテムポインタ2, ... ,行データ2,行データ1]
テーブルサイズの見積もり
ページヘッダは固定で 24byte
1行追加されるごとにアイテムポインタ 28byte
行データが消費するサイズ
1) 一行のサイズ = [行ヘッダ28byte] + [行データ4+ 8+ (4+平均文字サイズ) byte]
2) 1ページ内のデータ領域 = 8192byte -24byte * FILLFACTOR(割合に変換)
3) ページに格納できる行数 = 1ページ内のデータ領域 / 一行のサイズ
4) テーブル行数 / ページに格納できる行数 = 必要なページ数
5) 必要なページ数 * 8192byte = テーブルサイズ
インデックスサイズの見積もり
インデックスページの固定領域は 40byte
1エントリ消費されるごとにノードヘッダ 12byte
1) 1ノードのサイズ = [ノードヘッダ 12byte] + [int型のエントリ 4byte]
2) 1ページ中のデータ領域 = (8192byte -40byte) * FILLFACTOR(割合に変換)
3) ページに格納できるノード数 = 1ページ中のデータ領域 / 1ノードのサイズ
4) インデックスエントリ数(テーブル行数) / ページに格納できるノード数 = 必要なページ数
5) 必要なページ数 * 8192 byte = インデックスサイズ
I/O分散、故障リスク分散
データディレクトリ
指定: initdb -D
ディフォルト位置: $PGDATA
WALファイル出力先
指定: initdb -X
ディフォルト位置: $PGDATA/pg_xlog
ユーザデータ格納先
TABLESPACE機能
ディフォルト位置: $PGDATA/base
ログファイル出力先
パラメータ
$PGDATA/pg_log
アーカイブ退避先
パラメータ
考慮するポイント
データとWALは別のディスクとする
WALファイルは出力先を指定する
WALファイルは二重化できないので、RAIDによる保護を検討する
テーブル・INDXはTABLESPACE機能を利用し、配置先を分散する
レプリケーションの構成
データ同期モード
synchronous_commit
remote_apply: 完全同期
on / remote_write: 準同期
指定なし: 非同期
synchronous_standby_names
remote_apply: 同期対象スタンバイリスト
on / remote_write: 同期対象スタンバイリスト
マルチスタンバイ
同期対象スタンバイリストは通常は先頭1台が同期
その他のスタンバイは非同期スタンバイ
synchronous_standby_names = N(slave1, slave2, slave3)
上記の書式でN台の同期設定が可能(9.6～)
監視
サーバー監視
sar, iostat, vmstat, top, netstat (上げれば切りがない)
データベース領域
pg_database_size('db');
pg_relation_size('table');
pg_total_relation_size('table') インデックスのサイズも含む
WAL領域
1セグメント 16MB 循環利用する
チェックポイントが住めば不要
max_wal_size: 超えるとチェックポイント処理
min_wal_size: 再利用可能な状態で維持
リカバリ時はベースバックアップ以降のWALが必要となる
WALファイルの領域が枯渇するとDB停止
アーカイブモード
WAL消失を防ぐため
アーカイブ先の空き容量がななら巡回されるWALファイルを残しておく
レプリケーション
スタンバイに転送予定だが、まだ転送が完了してないWAL
checkpoint_segments (before 9.4)
checkpoint_timeout
checkpoint_completion_target
次のチェックポイント発生までの目標時間の割合
必要領域 = 16MB * checkpoint_segments * ( 3 + 1 )
df / du
アーカイブWAL領域
PITRで利用
df / du
テーブル・インデックス容量見積もり
列の型 indexの列の型 その型がどの程度のデータサイズなのか
各データ型のデータサイズ
smallint: 2byte
integer: 4byte
bigint: 8byte
real: 4byte
Timestamp: 8byte
date: 4byte
interval; 16byte
varchar(n),char(n),text: N<126の場合(1+N)byte N>=126の場合 (4+N)byte
VACUUM
VACUUM
更新/削除された行の再利用
VACUUM FULL
表の再作成を行う
VACUUM FREEZE
トランザクションID周回問題への対処
更新/削除された行の切り詰め
遅延VACUUM
vacuum_cost_delay
vacuum_cost_page_hit
vacuum_cost_page_miss
vacuum_cost_page_dirty
vacuum_cost_limit
自動VACUUM
autovacuum
log_autovacuum_min_duration
autovacuum_max_worker
autovacuum_naptime
autovacuum_vacuum_threshold
autovacuum_vacuum_scale_factor
vacuumdb
vacuumdb [接続オプション][オプション][db名]
-f | --full
-z | --analyze
pg_stat_user_table
n_live_tup: 有効行数
n_dead_tup: 無効行数
n_tup_hot_upd: HOTに更新された行数
last_vacuum: 最後にVACUUM ANALYZEが実行された日時
last_autovacuum
vacuum_count: 最後にリセットしてからの実行回数
pg_stat_user_indexed
idx_scan: インデックスキャンが実行された回数
プロセス監視・クエリ実行
ps -ef など
SELECT 1; など
pg_isreadyコマンド
1: 接続拒否
2: 無応答
3: 実行に失敗
pg_stat_archiverビュー
pg_stat_acrtivityビュー
pg_stat_progress_vacuumビュー
psql
HOTによる更新とFILFACTOR
HOTは行データが更新されてもインデックスを更新しない仕組み
変更前データの領域はVACUUM不要で再利用可能となる
有効になる条件
同一ページ内に更新後のデータが格納できる場合
インデックスに格納されたキーの値自体が変更されない
ロングトランザクションが古いデータを見ていないこと
ログ
pstgresql.confパラメータ
client_min_message
default: notice
log_min_message
default: warning
log_min_error_statement
default: error
log_checkpoints
チェックポイントの出力有無
log_connections
接続切断の情報有無
log_lock_waits
一定以上待たされたDB、テーブル、connなど
log_autovacuum_min\duration
自動バキュームの秒数をこえた場合に出力
log_line_prefix
先頭に出力する情報（silverででた）
log_rotation_age
log_rotation_size
log_truncate_on_rotation
名前が同じログファイルに対し、上書き(on)か追記か(off)
ストリーミング・レプリケーション
マスター側
pstgresql.conf
liste_address
wal_level = archive / host_standby
archive_mode = on
archive_command
max_wal_senders = 1
pg_hba.conf
2列めにreplication
host replication postgres 192.168.1.2/32 trust
スレーブ側
revovery.conf
standby_mode = on
primary_conninfo
trigger_file
restore_command
primary_conninf = 'host=192.168.1.1 port=5432 user=postgres'
手順
マスタ側の設定
マスタのベースバックアップ取得
ベースバックアップをスレーブで展開
スレーブの設定
スレーブの起動
LOG: streaming replication successfully connected to primary
フェイルオーバー
postgreSQLには無い
recovery.conf
trigger_fileで設定したトリガファイルを作成することでフェイルオーバー
touch /tmp/trigger.file
状態管理関数
pg_current_xlog_location() WAL書き出し位置を返却
pg_last_xlog_receive_location() WAL受信位置を返却
上記2つを比較
バックアップ・リカバリ
コールドバックアップ
オフラインバックアップ
データベースを停止した状態で取得する
rsyncやtarを使用する
storageスナップショット機能も利用可能
バックアップした時点にのみリカバリ可能
設定ファイルも含む
リストア時間が相対的に短い
類似構成の別マシンにコピーも可能
pg_tblspc内のシンボリックリンクに注意
データ実体もバックアップすること
pg_walの位置に注意
メジャーバージョンが一致していること★
ホットバックアップ
オンラインバックアップ
データベースを稼働させた状態で取得する
倫理バックアップを取得
バックアップ開始時点のもので一貫性有り
dp_dump / dp_dumpallコマンドで実施
pg_dump: データベース単位 バイナリ
テーブル空間も対応
以下のことを行わないこと
DDL操作
ラージオブジェクトのデータ書き換え
pd_dumpall: ユーザ情報含むデータベースクラスタ全体
テーブル空間も対応
設定ファイルはベットバックアップする必要あり
メジャーバージョンが異なっていてもリストア可能★
pg_basebuckup
レプリケーションできる状態であること前提
pg_archivecleanup (9.5~)
リカバリ
PITR ( POINT IN TIME RECOVERY)
WALをアーカイブしておき、定期的にデータベースクラスタ全体をコピー
インスタンにバックアップ開始・終了を通知する必要あり
任意の時点の状態にリカバリ可能
複雑だが、最も自由度が高い
事前準備(postgresql.conf)
wal_level=archive | hot_standby
archive_mode=on
archive_command='cp -i %p /path/to/archie%f'
バックアップ方法
低レベルAPI
1) SELECT pg_start_backup('label');
2) クラスタ全体をコピー
3) SElECT pg_stop_backup();
ベースバックアップが取れたらそれ以前のWALは破棄可
pg_basebackup
開始・終了の通知関数不要
リモートでも取得可能
superuserまたはreplication権限のあるユーザーで実行
GUCパラメータ max_wal_senderを 1～2増やす
リストア方法
1) インスタンスが稼働している場合は停止
2) $PGDATA/pg_xlogを任意の場所に退避
3) クラスタを削除
4) バックアップを&PGDATA/に再配置
5) $PGDATA/pg_xlogの中を全削除し2をコピー
6) recovery.confを作成し、&PGDATA配下に配置
リカバリ方法
1) pg_hba.confを編集、一般の接続をrefuse
2) インスタンスを起動、自動リカバリを開始
recovery.conf => recovery.done
LOG: archive recovery complete
3) DB内容を確認
4) pg_hba.confをもとに戻してreload
ホットスタンバイ
マスタ側
postgresql.conf
wal_level=hot_standby
スレーブ側
postgresql.conf
hot_standby=on
アクセス統計情報
pg_locks
実行中トランザクションにより獲得されたロックに関する情報
locktype
relation
page
tuple
transactionid
virtualxid
mode: ロックモード
granted: ロックを保持しているか
pg_stat_database
キャッシュヒット率=blks_hit / (blks_hit + blks_read)
tup_returned: シーケンシャルスキャンにより取得された行数
tup_fetched: インデックスキャン・ビットマップスキャンで取得した行数
pg_stat_clear_snapshot
最新情報に更新可能
標準統計情報ビュー
pg_stat_bgwriter
buffers_backendがbuffers_allocより大きい場合、shared_bufferの値が不足
buffers_checkpoint: チェックポイントで書き出されたdrityバッファ数
buffers_clean: バックエンドライタで書き出されたdirtyバッファ数
buffers_backend: バックエンドプロセスより書き出されたdirtyバッファ数
pg_stat_all_tables
seq_tup_read / seq_scan
n_dead_tup: バキュームの対象
pg_relation_size: バキュームの対象量
N_tup_hot_upd / n_tup_upd
pg_statio_all_tables
テーブルあたりのI/Oに関する統計情報
pg_stat_all_indexed
インデックスごとのアクセスに関する統計情報を表示
使われていないインデックスの特定
idx_scan: 実行回数
idx_tup_read: ノード数
idx_tup_fetch: レコード数
pg_statio_all_indexed
インデックスごとのI/Oに関する統計情報を表示
idx_blks_read: 共有バッファ以外からブロックを読み込んだ回数
idx_blks_hit: 共有バッファからブロックを読み込んだ回数
pg_stat_activity
バックエンドプロセス単位で表示
長時間経過してる・ロック待ち状態のSQLを調査可能
pid: プロセスID
query: 実行中のクエリ
waiting: ロックマチの状態か
backend_start: プロセス開始日時
xact_start: トランザクション開始日時
query_start: SQL実行の開始日時
client_addr: クライアントのIPアドレス
pg_cancel_backend(pid): クエリを取り消す
pg_terminate_backend(pid): 強制切断
pg_stat_statement
実行されたSQLを記録・分析
所要時間が長い/実行回数が多いSQLはチューニング効果大
contribツール
rmpやソースコード参照
CREATE EXTENSION, share_preload_libraries
実行計画
EXPLAIN 実行計画の表示のみ
EXPLAIN ANALYZE 実際にSQLが走り、実行結果に基づく情報も合わせて表示される
プランノード
スキャン
Seq Scan: 先頭ブロックから順にヒープ全体をスキャン
Index Scan: インデックスに基づいてヒープをスキャン
Index Only Scan: インデックのみを用いてスキャン
Bitmap Index Scan:
複数のインデックスから作成したビットマップに基づいてスキャン
結合
Nest Loop: Outer一行につきInner全体をスキャンし結合
Merge Join: 結合キーでソートされた結果同士を結合
Hash Join: Innerの結合キーでハッシュテーブルを作成しそれに基づきスキャン
そのた
Sort: 結果の並び替え
Limit: 結果の行方向の部分集合を取得
Materialize: 結果セットを一時領域に保存
オプション
ANALYZE(bool)
言わずもがな
VERBOSE(bool)
trueであれば各種追加情報出力
BUFFERS(bool)
trueであれば共有バッファの使用状況を出力
FORMAT(text,xml, json, yaml)
出力フォーマット指定
INDEXチューニング
インデックスを追加する
SELECTで取得する列がすべてインデックスに含まれるならIndexOnlyScan
WHERE以外でもインデックスは使われる
結合条件 / ソート条件
ANALYZE
EXPLAIN ANALYZEを実行し、乖離がないか調べる
手動でのANALYZEは基本的に不要
一時テーブルに対しては手動でのANALYZEが必要
work_mem
ソート・ハッシュ表作成時に利用できるメモリ量を指定する
shared_buffersとは個別に確保
大きすぎる値を設定するのは危険
log_temp_filesパラメータで調査が可能
effective_cache_size
カーネルのバッファキャッシュを含めた、利用可能なディスクキャッシュの推定値を設定
プランコスト定数の変更
データベースの大半がディスクキャッシュに乗る場合
seq_page_cost / random_page_cost を減らす
インデックススキャンを選択させる
random_page_cost / effective_cache_size を増やす
プランなメソッド設定の変更
postgresql.confで設定するとすべてのSQLに影響が出る
SETでセッションまたはトランザクションごとに設定すべき
テーブル結合最適化処理
パラメータに小さな値を設定することで、最適化処理時間を短縮化できる
実行計画の制度が低下する可能性がある
from_collapse_limit
join_collapse_limit
セキュリティ関連設定
データ暗号化
個人情報を含む列のみを暗号化するなどAP側で実装
通信経路暗号化
実行されるSQL文の盗聴を防ぐ
監査情報の取得
バックアップの持ち出しなどから保護
PostgreSQL
データ格納時にpgcrypto関数を使用し、列単位で暗号化
Open SSLライブラリをロードし通信経路暗号化
データファイル暗号化はできない
SSL通信設定を理解する
PostgreSQLのインストール時
configure実行時に --with openssl
RPMでは有効化済み
データベースクラスタ
ssl=on
サーバー証明書の設定、秘密鍵を含む設定ファイルを用意し指定
認証設定
クライアントごとにSSL通信を矯正するかどうかを指定可
pg_hba.conf type=hotsslを指定
監査情報の取得
log_statementの指定によるSQL文を記録
log_statementの設定
none: 出力なし
ddl: 実行されたDDL文のみ
mod: 実行されたDDLとDML文を記録
all: 実行されたすべて
ユーザー単位で指定可能
テーブル単位やSELECTのみは不可
log_connection
ログイン・ログアウトの記録
パフォーマンス監視
システムカタログの監視
pg_stat_bgwriter: チェックポイント感覚の適正値把握
pg_stat_all_tables: キャッシュヒット率HOT更新IndexScan割合VACUUM設定の適正値
pg_statio_all_tables: I/O
pg_stat_activity: ユーザセッションごとに実行されてるSQL
pg_locks: pg_stat_activityとjoinし、他セッションをブロックしているSQLを特定
スロークエリの検出
ログ監視設定
log_min_duration_statement
エラーレベル
PANIC: サーバーが停止している
FATAL: セッションが切断されている
ERROR: 処理が失敗し、セッションは残っている
loc_checkpoints
適切な間隔で動いているか
所領時間は意図した通りか
pg_stat_bgwriterビューでチェック
log_min_error_statement
指定のエラーレベルに対してSQLを記録
log_min_duration_statement
実行に長時間要したSQLのトクエイ
log_loc_waits / deadlock_timeout
ロック取得に要した時間がtimeoutを超過
log_line_prefix
%t: timestump
%u: username
%d: database
%p: PID
%c: sessionID
%x: transactionID
%e: SQLSTATE Error code
%a: Application name
%%: %そのもの
log_destination
サーバー側のログ出力先を指定
stderr: plain textで標準エラー出力
csvlog: csvで標準エラー出力
syslog: plain textでsyslogに出力
eentlog: windowsのイベントログへ
logging_collecotr
stderr, csvlogの内容を保存するか
log_directory
log_filename
%使用可能
オブジェクトのメンテナンス
インデックスの劣化
インデックスは表の更新に合わせて自動で書き換えられる
ソートされたデータ構造を保つため、ブロック内に空きがないと劣化する
インデックスを指定したVACUUMはできない
メンテナンス手段
以下を比較しページ数が非常に大きい場合には再作成を検討
pg_class
reltuples: インデックスエントリ
relages: インデックスのページ数
pgstatindex()
idx_j: 昇順
idx_i: 降順
pg_stats
correlation
テーブルのクラスタ性
CLUSTER
クラスタ性の復活
再作成
ロック強度と所要時間とのトレードオフ
REINDEX
DROP INDEX / CREATE INDEX
CREATE INDEX CONCURRENTRY
チューニング
ベンチマークの取得
pgbench: contribツール・TPSを計測
観点
自動VACCUMの遅延設定・ダーティバッファ書き出し
チェックポイント
pg_stat_xxxビューのどの列を見ればいいか、ログ出力を見れば良いか
パラメータ(リスクと効果を期待できるか理解する)
fsync
full_page_writes
synchronous_commit
commit_delay
wal_sync_method
性能改善
インデックスが聞かない原因例
統計情報が正しく取得されていない
自動バキュームが有効であったとしてもテーブル内容に大きな変更がされた場合
手動でVACUUM ANALYZEを実行
データが最初にロードされた時
手動でVACUUM ANALYZEを実行
検索条件に式・関数が利用されている
式・関数インデックスの作成で解決することもある
CREATE INDEX ON test (lower(mail_address));
他の方法で解決可能なら積極的には利用するべきではない
インデックスを利用しても効果がない
カーディナリティ度が低い場合はインデックスは利用されない
検索条件等に誤りがないか確認する必要がある
統計情報の精度が足りない
精度が足りない場合はインデックスが利用されない非効率な実行計画になる
https://www.postgresql.jp/document/11/html/row-estimation-examples.html
実行時間とネットワーク転送コスト
https://fo-pro.s3.ap-northeast-1.amazonaws.com/files/5c24dddf5b3a437d0e0059f3/20160806-gold-01.pdf
実行時間
以下の実行時間はすべて算出方法が異なる
log_min_duration_statement
duration
EXPLAIN ANALYZE
Execution time
psql > \timing
Time
アプリケーションログに出力される実行時間
ネットワーク転送コストが実行時間に含まれているかを意識すべき
ネットワーク転送コスト
クラウドでは帯域幅だけではなく、レイテンシもボトルネックとなる
PostgreSQLでは通信プロトコルはTCP/IPを用いるので、影響をもろに受ける
log_min_duration_statement
durationにはネットワーク転送コストは含まれていない
削減方法
コネクションプーリングを利用する
クエリの発行回数を抑える
ストアドプロシージャ
通信の圧縮を有効にする
sslcompression
TCPのチューニングを実行
tcp_slow_start_after_idle=off など
ネットワーク転送コストが問題となるような構成にしない
障害の種類
電源障害・プロセス障害
メモリ城のデータは消失するが、起動時にリカバリされる
ファイル破損
正常なバックアップがあれば復旧可能
限定的な障害の場合、少ない影響で復旧させることを検討
WALの破損 pg_xlog
pg_resetxlogの検討
データの破損
baseやテーブルスペース
システムデータの破損 global
シングルユーザーモードで接続・REINDEX
インデックス base テーブルスペース
インデックス再作成
データ・WALの物理ディスク障害
インデックスデータの場合
インデックス再作成を検討
システムテーブルのインデックスの場合は注意が必要
システムに与える影響を考慮して検討
WALファイル
起動時に読み込まれるため、破損していると起動できない
pg_restxlogを使用してWALのリセット
正常なトランザクション位置を指定してPITRを実行
pg_resetxlog -f -x XID $PGDATA
容量不足時
容量不足により各ファイルへの書き込みが失敗するとどうなるか
データファイル
データ破壊を引き起こす可能性がある
WALファイル
容量が不足すると、データベースクラスタが停止する
対処
LVMなどで容量追加
テーブルスペース機能で別領域に移動 データファイル
シンボリックリンクで別領域に移動 WALファイル