WEB+DB PRESS Vol.108 詳解PostgreSQL 読書メモ

自分が知らなかったことや興味があったことだけをメモしているので、全体が知りたい人は WEB+DB PRESS を買って読みましょう

WEB+DB PRESS Vol.108

バージョニングについて

9系までは、x.y.z の x.y がメジャーバージョン(9.6.5 ならば、9.6がメジャーバージョン)

• ちなみに9系の最後のメジャーバージョンは9.6

10系からは、x.y の x がメジャーバージョン(10.1 ならば、10がメジャーバージョン)

2017年からは、2Q末にメジャーバージョンが毎年リリースされている

最新のメジャーバージョンはPostgreSQL11で、12が開発中

なお、各メジャーバージョンのサポート期限は4世代(4年)

初期設定いろいろ

ロケールのデフォルトは環境の言語に依存するので、依存しないように C にするのが一般的

文字コードはデフォルトは sql_ascii なので、UTF-8にしておくのが無難

パスワードと、IPアドレスによるアクセス制限をしっかりつけておく

PostgreSQLの内部構造

PostgreSQLはマルチプロセス(MySQLはマルチスレッド)

いろいろなプロセスが、それぞれの役割をこなしている

• マスタプロセス : 最初に起動して、各プロセスを制御する
• バックエンドプロセス : 接続要求毎に生成される
• ライタ : 共有メモリの内容を定期的にストレージに書き込む
• WALライタ : WAL(後述)をストレージに書き込む
• チェックポインタ : ライタに書き込みのタイミングを通知する

など

PostgreSQLのデータ更新方法

基本的にはメモリ内(共有バッファ)上で、データを更新する

そして、ライタプロセスが、 チェックポイント と呼ばれる特定のタイミングで、メモリの内容をストレージに書き込む

• これは、ストレージへの書き込みには時間がかかるので、高速化のため
• なお、ストレージに書き込まれる前のデータを ダーティーページ という

それとは別に、WALライタが、共有バッファの更新毎に、差分をWALファイルに書き込んでいる

サーバがクラッシュした時には、 チェックポイントとメモリの差分はWALファイルに格納されているので、それを使ってリカバリを行う

メモリ

共有メモリ と プロセスメモリ の2種類がある

共有メモリ

PostgreSQL全体で使用するメモリ

• 共有バッファ : テーブルやインデックスのデータをキャッシュする領域。 総メモリの25%程度が適切な値
• 可視性マップ 、 空き領域マップ : VACUUM処理(後述)が使用
• WALバッファ : 上述のWALをキャッシュする領域

プロセスメモリ

バックエンドプロセス毎に持つメモリ

• 作業メモリ : クエリ実行時に使用するメモリ。
  • 実行されるクエリのデータサイズが大きければ必要量が増える
  • ここが減るとswapが発生するので、 swapを監視すると良い
  • (OSのメモリ - 共有バッファのサイズ)/max_connection を超えない範囲でチューニングしていく必要がある
  • 最初は 32MB 程度にして、様子を見ていくのが良い
• 一時バッファ : 一時テーブル作成時に使用
• メンテナンス用作業メモリ : VACUUM(後述)、インデックス作成、外部キー制約の追加などのメンテナンス用の作業メモリ
  • 小さいとメンテナンス作業時にswapが発生して処理が遅くなる

ファイル

• データファイル : テーブルのデータ。8192バイト毎の複数のファイルの格納されている
• インデックスファイル : インデックスのデータ。同じく、8192バイト毎の複数のファイル
• WALファイル : 16MB固定
• TOASTファイル : 8192バイトを超えるデータの格納先、ただし最大1GBまで
  • そのため、PostgreSQLのTEXT型が持てる最大サイズも1GBまで

追記型アーキテクチャとVACUUM

PostgreSQLのデータの更新は、参照を置き換えるという形

• 遅いが、トランザクションやロックの制御がシンプルになるというメリットがあるらしい

参照を置き換えた結果、使用されなくなったデータファイルは VACUUM という仕組みを使って再利用される

• 共有メモリの 可視性マップ にデータファイルの参照状態が格納されているので、VACUUMはそれを参照する

なお、PostgreSQLでは データを削除してもデータファイルの合計サイズは減らない

• VACUUMは、使用されなくなったデータファイルを再利用可能にするだけ
• データファイルの総量を減らしたい場合は CLUSTER コマンドが適切らしい
• また、 pg_repack というツールもある

他のVACUUMの機能

• 統計情報の更新
• トランザクションID(XID)の再利用
  • PostgreSQLは、テーブルやレコード全てにXIDを割り当てている
  • XIDを使い切る前に、未使用のXIDを再利用可能にしている

バックアップ

• pg_dump
  • スタンダードな論理バックアップ
  • カスタムアーカイブ形式がオススメ(テーブルやスキーマ定義だけ分離できる&いざとなったらSQLに戻せる)
• pg_basebackup
  • DBを停止せずにバックアップを取る方法
  • データファイルのコピーをしながら、コピーしている間に発生したWALもアーカイブする(WALアーカイブモードを有効にする必要あり
  • pg_rman は、上記のバックアップ&リストアを簡単に行えるツール

レプリケーション

ストリーミングレプリケーションと、ロジカルレプリケーションがある

ストリーミングレプリケーション

• 主流。スタンバイが、プライマリのWALの更新を受け取って自身のDBを更新する
• プライマリとスタンバイのマイナーバージョンが異なっても、レプリケーションができる

ロジカルレプリケーション

• PostgreSQL10から追加
• 自由度が高い
  • メジャーバージョンが異なってもレプリケーションができる
  • 複数のプライマリを集約したりとかもできる
• その分壊れやすい
• AWS DMSっぽい印象

バージョンアップについて

マイナーバージョンアップ

積極的に対応するべき

PostgreSQLの再起動が必要だが、ストリーミングレプリケーションを使って、スタンバイから更新すれば難しいことはないはず

メジャーバージョンアップ

ストリーミングレプリケーションは使えない

データファイルやWALに互換性がないこともあるため、物理バックアップからの復旧もできない

pg_dump の結果を新バージョンのDBに反映させるのが一番スタンダード

• デメリット: 停止時間が長い。サーバを新規に用意する必要がある
• メリット: 元のサーバが残っているため復旧が容易

現在のサーバで pg_upgrade するほうが、停止時間は短い

• PostgreSQL 8.4 移行はデータファイルのフォーマットが変わっていないので、テーブルやインデックスの再構築が行われない
• デメリットは、バージョンダウンはできない
• EC2を使っている場合、スナップショットから復旧すれば良いので、デメリットは無視できそう

参考書籍

これからはじめる PostgreSQL入門

[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

参考リンク

記事だけよんでわからなかった部分を調べた時に読んだページ

morizyun.github.io

qiita.com

PostgreSQLのリカバリ超入門(もしくはWAL、CHECKPOINT、オンラインバックアップの仕組み) from Hironobu Suzuki

www.slideshare.net