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

はじめに
本書を活用するために

Part 1 基本編
第1章 PostgreSQL“超”入門

  • 1.1 呼び方
  • 1.2 データベースとしての分類
  • 1.3 歴史
  • 【Column】メジャーバージョンとマイナーバージョン
  • 1.4 ライセンス
  • 1.5 コミュニティ

第2章 アーキテクチャの基本

  • 2.1 プロセス構成
    • 2.1.1 マスタサーバプロセス
    • 2.1.2 ライタプロセス
    • 2.1.3 WALライタプロセス
    • 2.1.4 チェックポインタプロセス
    • 2.1.5 自動バキュームランチャと自動バキュームワーカプロセス
    • 2.1.6 統計情報コレクタプロセス
    • 2.1.7 バックエンドプロセス
  • 2.2 メモリ管理
    • 2.2.1 共有メモリ域
      • 共有バッファ(shared_buffers)/WALバッファ(wal_buffers)/空き領域マップ(Free Space Map)/可視性マップ(Visibility Map)
    • 2.2.2 プロセスメモリ
      • 作業メモリ(work_mem)/メンテナンス用作業メモリ(maintenance_work_mem)/一時バッファ(temp_buffers)
  • 2.3 ファイル
    • 2.3.1 主なディレクトリ
      • base/global/pg_xlog/pg_clog/pg_tblspcディレクトリ
    • 2.3.2 主なファイル
      • PG_VERSION/テーブル/インデックス/TOAST/Free Space Map/Visibility Map/WAL/postmaster.pidファイル

第3章 各種設定ファイルと基本設定

  • 3.1 設定ファイルの種類
  • 3.2 postgresql.confファイル
    • 3.2.1 設定項目の書式
    • 3.2.2 設定の参照と変更
    • 3.2.3 設定項目の反映タイミング
    • 3.2.4 設定ファイルの分割と統合
    • 【Column】コマンドラインパラメータによる設定
  • 3.3 pg_hba.confファイル
    • 3.3.1 記述形式
    • 3.3.2 接続方式
      • local/host/hostssl/hostnossl
    • 【Column】SSL接続
    • 3.3.3 接続データベース
      • all/sameuser/samerole/replication/@記号が先頭にある場合
    • 【Column】ログイン権限
    • 3.3.4 接続ユーザ
      • 先頭に”+"がない場合/先頭に”+”がある場合
    • 【Column】特殊な名前のデータベースとユーザ
    • 3.3.5 接続元のIPアドレス
    • 3.3.6 認証方式
  • 3.4 pg_ident.confファイル
    • map name/system user name/database user name

第4章 処理/制御の基本

  • 4.1 サーバプロセスの役割
    • 4.1.1 マスタサーバプロセス
    • 4.1.2 ライタプロセス
    • 4.1.3 WALライタプロセス
    • 4.1.4 チェックポインタ
    • 4.1.5 自動バキュームランチャと自動バキュームワーカ
    • 4.1.6 統計情報コレクタ
    • 4.1.7 バックエンドプロセス
    • 【Column】バックグラウンドワーカプロセス
  • 4.2 クライアントとサーバの接続/通信
  • 4.3 問い合わせの実行
    • 4.3.1 パーサ
      • 字句解析/構文解析/実在するのか確認
    • 4.3.2 リライタ
    • 4.3.3 プランナ/オプティマイザ
      • 個々のテーブルに対するアクセス方法の選択/結合方法の選択
    • 4.3.4 エグゼキュータ
    • 4.3.5 SQL文の種別による動作
  • 4.4 トランザクション
    • 4.4.1 トランザクションの特性
      • 原子性(atomicity)/一貫性(consistency)/独立性(isolation)/永続性(durability)
    • 4.4.2 トランザクション制御
    • 4.4.3 トランザクションの分離レベル
  • 4.5 ロック
  • 【Column】勧告的ロック
  • 4.6 同時実行制御

Part 2 設計/計画編
第5章 テーブル設計

  • 5.1 データ型
    • 5.1.1 文字型
    • 【Column】char型に対する文字列操作の注意点
    • 【Column】内部的に使用される文字型
    • 5.1.2 数値データ型
    • 【Column】numeric型の性能
    • 5.1.3 日付/時刻データ型
    • 【Column】アンチパターン 文字型で日時を管理する
    • 5.1.4 バイナリ列データ型
    • 【Column】型名のエイリアス
  • 5.2 制約
    • 5.2.1 主キー
    • 5.2.2 一意性制約とNOT NULL制約
    • 5.2.3 外部キー制約
  • 5.3 PostgreSQL固有のテーブル設計
    • 5.3.1 TOASTを意識したテーブル設計
    • 5.3.2 結合を意識したテーブル設計
    • 【Column】遺伝的問い合わせ最適化

第6章 物理設計

  • 6.1 各種ファイルのレイアウトとアクセス
    • 6.1.1 PostgreSQLのテーブルファイルの実態
    • 6.1.2 テーブルファイル
      • ページヘッダ/アイテムIDデータ/空き領域/アイテム/特別な領域
    • 6.1.3 インデックスファイル
    • 6.1.4 テーブルファイルに対するアクセス
  • 6.2 WALセグメントファイルとアーカイブファイル
    • 6.2.1 WALセグメントファイル
    • 【Column】pg_xlogdumpでWALファイルを可視化する
    • 6.2.2 アーカイブファイル
  • 6.3 HOTとFILLFACTOR
    • 6.3.1 HOT
    • 6.3.2 FILLFACTOR
    • 【Column】FILLFACOTRの確認方法
  • 6.4 データ配置のポイント
    • 6.4.1 base領域
    • 6.4.2 WAL領域
    • 6.4.3 アーカイブ領域
  • 6.5 テーブル空間とテーブルパーティショニング
    • 6.5.1 テーブルパーティショニングとの組み合わせ
  • 6.6 性能を踏まえたインデックス定義
    • 6.6.1 インデックスの概念
    • 6.6.2 更新に対するインデックスの影響
    • 6.6.3 複数列インデックス使用時の注意
    • 6.6.4 関数インデックスの利用
    • 6.6.5 部分インデックスの利用
    • 【Column】インデックスの種類
  • 6.7 文字エンコーディングとロケール
    • 6.7.1 文字エンコーディング
    • 6.7.2 ロケール

第7章 バックアップ計画

  • 7.1 最初に行うこと
  • 7.2 PostgreSQLのバックアップ方式
    • 7.2.1 オフラインバックアップ
      • 物理バックアップ
    • 7.2.2 オンラインバックアップ
      • 論理バックアップ/物理バックアップ
  • 7.3 主なリカバリ要件/バックアップ要件
    • 7.3.1 要件と方式の整理方法
  • 7.4 各バックアップ方式の注意点
    • 7.4.1 コールドバックアップの注意点
    • 7.4.2 オンライン論理バックアップの注意点
    • 7.4.3 オンライン物理バックアップの注意点

第8章 監視計画

  • 8.1 監視とは
  • 8.2 監視項目の選定
    • 8.2.1 サーバに問題が起きていないか? を監視する
    • 8.2.2 PostgreSQLに問題が起きていないか? を監視する
      • 必要なプロセスは正常に動作しているか?/ディスク容量に問題はないか?/想定通りの性能が出せているのか?/PostgresSQLのログに異常を知らせる通知はないか?
  • 8.3 PostgreSQLログの設定
    • 8.3.1 PostgreSQLログをどこに出力するか
    • 8.3.2 PostgreSQLログをいつ出力するか
    • 8.3.3 PostgreSQLログに何を出力するか
      • log_line_prefixのエスケープシーケンス例
    • 8.3.4 PostgreSQLログをどのように保持するか
  • 8.4 異常時の判断基準

第9章 サーバ設定

  • 9.1 CPUの設定
    • 9.1.1 クライアント接続設定
      • クライアント接続設定の注意点
    • 9.1.2 ロックの設定
  • 9.2 メモリの設定
    • 9.2.1 OSのメモリ設定
    • 9.2.2 PostgreSQLのメモリ設定
      • 共有メモリ領域の設定/プロセスメモリ領域とその設定について
  • 9.3 ディスクの設定
    • 9.3.1 OSのディスク設定
    • 9.3.2 PostgreSQLのディスク設定
      • システム上の制限を設けるためのパラメータ/性能に影響を与えるパラメータ

Part 3 運用編
第10章 高可用化と負荷分散

  • 10.1 サーバの役割と呼び名
  • 10.2 ストリーミングレプリケーション
    • 10.2.1 ストリーミングレプリケーションの仕組み
      • WALの特性/walsender/walreceiverプロセスの設定方法/walsender/walreceiverプロセスの処理
    • 【Column】pg_resetxlogツール
    • 10.2.2 可能なレプリケーション構成
      • 同期/非同期の違い/複数のスタンバイがある場合/「同期」の呼び方に注意
    • 10.2.3 レプリケーションの状況確認
      • サーバログの確認/プロセスの確認/レプリケーション遅延の確認
    • 10.2.4 レプリケーションの管理
      • trigger_fileを用いる方法/pg_ctl promoteを用いる方法
    • 10.2.5 設定手順の整理
      • ①プライマリの設定/②スタンバイのデータベースクラスタを用意/③スタンバイの設定変更/④スタンバイの起動/⑤動作確認
    • 【Column】循環するレプリケーション
  • 10.3 PostgreSQLで構成できる3つのスタンバイ
    • 10.3.1 それぞれのメリットとデメリット
    • 10.3.2 コールドスタンバイ
    • 10.3.3 ウォームスタンバイ
    • 10.3.4 ホットスタンバイ
  • 10.4 ホットスタンバイ
    • 10.4.1 ホットスタンバイで実行可能なクエリ
      • ベースバックアップの取得
    • 10.4.2 ホットスタンバイの弱点はコンフリクト
  • 10.5 ストリーミングレプリケーションの運用
    • 10.5.1 フェイルオーバ時の処理
      • プライマリの故障時/スタンバイの故障時/walsender/walreceiverプロセスの動作確認/レプリケーションの状況確認
    • 10.5.2 プライマリ/スタンバイの再組み込み時の注意点
    • 10.5.3 コンフリクトの緩和策
      • vacum_defer_clenup_ageパラメータ/hot_standby_feedbackパラメータ

第11章 オンライン物理バックアップ

  • 11.1 オンライン物理バックアップの仕組み
    • 11.1.1 pg_start_backup関数の処理内容
      • チェックポイント処理の制御
    • 11.1.2 pg_stop_backup関数
      • WALスイッチ
    • 11.1.3 backup_labelとバックアップ履歴ファイルの内容
    • 11.1.4 WALのアーカイブの流れ
  • 11.2 PITRの仕組み
    • 11.2.1 WALレコード適用までの流れ
      • backup_labelファイルの情報からリカバリを開始した場合/pg_controlファイルの情報からリカバリを開始した場合
    • 11.2.2 pg_controlファイル
    • 11.2.3 recovery.confファイル
      • スタンバイサーバの設定/アーカイブリカバリの設定/リカバリ対象の設定
  • 11.3 バックアップ/リカバリの運用手順
    • 11.3.1 バックアップ手順
    • 11.3.2 リカバリ手順

第12章 死活監視と正常動作の監視

  • 12.1 死活監視
    • 12.1.1 サーバの死活監視
    • 12.1.2 PostgreSQLの死活監視(プロセスの確認)
      • psコマンドを用いる方法/pg_isreadyコマンドを用いる方法
    • 【Column】プロセス確認の落とし穴
    • 12.1.3 PostgreSQLの死活監視(SQL文の実行確認)
  • 12.2 正常動作の監視
    • 12.2.1 サーバの正常動作の監視
      • vmstatコマンド/netstatコマンド/iostatコマンド/sarコマンド
    • 12.2.2 PostgreSQLの正常動作の監視
      • pg_stat_databaseビュー/pg_stat_user_tablesビュー/pg_statio_user_table/pg_statio_user_indexesビュー/pg_stat_activityビュー/pg_locksビュー

第13章 テーブルメンテナンス

  • 13.1 テーブルメンテナンスが必要な状況
  • 13.2 VACUUMの内部処理
    • 13.2.1 不要領域の再利用
      • VMとFSM/空き容量を調べる方法
    • 13.2.2 トランザクションID(XID)周回問題の回避
      • XIDの差分確認方法
  • 13.3 自動バキュームによるメンテナンス
  • 13.4 VACUUM FULLによるメンテナンス
    • 13.4.1 VACUUMが機能しないケース(例)
      • LongTransactionの確認方法/終了方法
    • 13.4.2 VACUUM FULL実行時の注意点

第14章 インデックスメンテナンス

  • 14.1 インデックスメンテナンスが必要な状況
    • 14.1.1 インデックスファイルの肥大化
    • 14.1.2 インデックスファイルの断片化
      • 断片化を調べる方法
    • 14.1.3 クラスタ性の欠落
      • クラスタ性を調べる方法
  • 14.2 【予防策】インデックスファイルの肥大化
  • 14.3 【改善策】インデックスファイルの断片化
  • 14.4 【改善策】クラスタ性の欠落
    • 14.4.1 CLUSTER実行時の適用されるインデックス
    • 14.4.2 CLUSTER実行時の注意点
  • 14.5 Index Only Scanの利用
    • 14.5.1 Index Only Scanの利用上の注意

Part 4 チューニング編
第15章 実行計画の取得/解析

  • 15.1 実行計画が最適化しない原因
    • 15.1.1 PostgreSQLが原因となる場合
      • コスト基準値の設定/統計情報の取得頻度/統計情報の取得粒度
    • 15.1.2 PostgreSQL以外が原因となる場合
      • ディスク性能によるもの/ネットワーク性能によるもの/アプリケーションによるもの/バッチ処理や瞬間的な大量アクセスによるもの
  • 15.2 実行計画の取得方法
    • 15.2.1 EXPLAIN文
      • ANALYZEオプション/VERBOSEオプション/COSTSオプション/BUFFERSオプション/TIMINGオプション/FORMATオプション
    • 15.2.2 ANALYZE文
      • VERBOSEオプション
    • 15.2.3 統計情報取得のためのパラメータ設定
      • ①autovacum(true)/②autovacum_analyze_threshold(50)/③autovacum_analyze_scale_factor(0.1)/④default_statistics_target(100)
    • 【Column】システムカタログpg_statistic
    • 15.2.4 実行計画を自動収集するauto_explain拡張モジュール
      • auto_explainのインストール方法/auto_explainの利用方法/auto_explainで利用可能なオプション/auto_explainのログ出力例
  • 15.3 実行計画の構造
    • 15.3.1 スキャン系ノード
    • 15.3.2 複数のデータを結合するノード
      • 入れ子ループ結合/ハッシュ結合/マージ結合
    • 15.3.3 データを加工するノード
    • 15.3.4 その他のノード
      • ソートノード
  • 15.4 実行計画(処理コスト)の見方
    • 15.4.1 処理コストの見積もり値
      • 行長と行数
    • 15.4.2 処理コスト見積もりのチューニング
  • 15.5 処理コスト(見積もり)のチューニング例
    • 15.5.1 シンプルなシーケンシャルスキャンの場合
    • 15.5.2 条件付きシーケンシャルスキャンの場合
    • 15.5.3 ソート処理の場合
    • 15.5.4 インデックススキャンの場合
      • インデックスのディスクアクセスコスト/インデックスのCPUコスト/テーブルデータのディスクアクセスのコスト/テーブルデータのCPUコスト
    • 15.5.5 見積もりと実行結果の差

第16章 パフォーマンスチューニング

  • 16.1 事象分析
    • 16.1.1 PostgreSQLログの取得
    • 16.1.2 テーブル統計情報の取得
    • 16.1.3 クエリ統計情報の取得
    • 16.1.4 システムリソース情報の取得
  • 16.2 事象分析の流れ
    • ①情報取得/②事象分析/③原因の絞り込み/④チューニング実装と効果測定/⑤影響評価
  • 16.3 スケールアップ
    • 16.3.1 【事例1】SSDに置き換えが有効なケース
    • 16.3.2 【事例2】メモリ容量の拡張が有効なケース
  • 16.4 パラメータチューニング
    • 16.4.1 【事例3】work_memのチューニング
    • 16.4.2 【事例4】チェックポイント間隔のチューニング
    • 16.4.3 【事例5】統計精度のチューニング
  • 16.5 クエリチューニング
    • 16.5.1 【事例6】ユーザ定義関数のチューニング
    • 16.5.2 【事例7】インデックスの追加
      • 関数インデックスの利用/式インデックスの利用
    • 16.5.3 【事例8】テーブルデータのクラスタ化

索引