OSS-DB Gold受験のための勉強メモ

情報処理試験

OSS-DB Gold受験のための勉強メモ

元ネタ(試験範囲)はこちら

OSS-DB Gold|試験概要|DBスペシャリストを認定する資格 OSS-DB技術者認定試験
「OSS-DB技術者認定資格」は、Postgresqlをはじめとするオープンソースデータベースのスペシャリストを認定する資格です。DBスペシャリストの認定を受けたい方や、SQLやデータベースのしくみを学びたいにもおすすめです。

試験範囲をベースに、勉強したことを追記していきます。
本人が調べて解釈を書いているものもありますが、たぶんあってるとは思うという気持ちで書いていますが、何分勉強中なので誤り等あった場合にはご容赦ください。
😂←このマークは自信なさ過ぎて再調査
空白は書けるほど理解できてないところ(適宜追加中です)
最初にも最後にも、アテにすべきは公式ドキュメント。↓

合格に必要な点数と出題構成

カテゴリ 出題構成 想定される問題数(約30問)
運用管理 30% 9問
性能監視 30% 9問
パフォーマンスチューニング 20% 6問
障害対応 20% 6問

問題は全部で30問。70点(30問中21問正解程度)で合格となります。
100点÷30問=3.33…(割り切れない)となりますので、若干の点数の重みづけがありそうですが詳細は不明です。

大体9問しか間違えられません

試験範囲とそれぞれの出題率を確認

LPICと同様のつくりだとすると(※GOLDに関してそういう文献を確認したわけではなく、あくまでも私の仮定ですよ)、【重要度】に従って出題数が決まってくるのではないかと思われます。あまり時間がない人は重要度が高めのものを優先してマスターするとよいかと思います。
たまたまかもしれませんが、出題数を重要度として足していくと、ちょうど30問になりました。
もしかして本当にこんな感じ??🤔

大項目 中項目 %からの
想定出題数
重要度からの出題数想定
運用管理(30%) データベースサーバ構築 【重要度:2】 9問 2問
運用管理用コマンド全般 【重要度:4】 4問
データベースの構造 【重要度:2】 2問
ホット・スタンバイ運用 【重要度:1】 1問
性能監視(30%) アクセス統計情報 【重要度:3】 9問 3問
テーブル / カラム統計情報 【重要度:2】 2問
クエリ実行計画 【重要度:3】 3問
その他の性能監視 【重要度:1】 1問
パフォーマンス
チューニング(20%)
性能に関係するパラメータ 【重要度:4】 6問 4問
チューニングの実施 【重要度:2】 2問
障害対応(20%) 起こりうる障害のパターン 【重要度:3】 6問 3問
破損クラスタ復旧 【重要度:2】 2問
ホット・スタンバイ復旧 【重要度:1】 1問

さて、いよいよ勉強部分です。調べたことをつらつらと記載していきます。
長いよ~…

運用管理(30%)
データベースサーバ構築 【重要度:2】

    • 説明:
      サーバ構築における容量見積もり、およびデータベースセキュリティに関する知識を問う
    • 主要な知識範囲:
      テーブル・インデックス容量見積もり
      セキュリティ
      – 通信経路暗号化(SSL)
      – データ暗号化
      – クライアント認証
      – 監査ログ
      データ型のサイズ
      ユーザ・データベース単位のパラメータ設定
    • 重要な用語、コマンド、パラメータなど:
      チェックサム

      9.3から「データチェックサム」追加
      PostgreSQLのデータブロックの中身が万が一壊れた時に、早期に検出できるようにする仕組み。無効の場合、実際にそのテーブルなどにアクセスするまでわからない
      ※データベースクラスタ初期化時に、有効にするかどうかを指定(initdb -k)
      PostgreSQL/EPAS 11以降のpg_basebackup時に、有効ならチェックすることが可能(それ以前のバージョンや無効の場合はステータス0でバックアップが終了する)

      実際に破損したの見たことないんだけど、たまにはあるんですかね…


      pg_xact

      コミットログファイル用のディレクトリ(ver10よりpg_clogからpg_xactへ変更)。
      変更の理由としては、「log」とついていると消してしまうユーザがいるかららしい。
      実際にはシステムの仕様上必須のファイルで、消してはいけない!

      確かに紛らわしいかも。
      logの付くディレクトリやたらたくさんあるしな~


      pg_multixact

      マルチトランザクションの状態のデータを保有するサブディレクトリ(共有行ロックで使用される)。ロック管理と覚えておく

      pg_notify

      通知を送信するためのpg_notify(text,text)関数。テーブルの変化などの通知に使えるようだ。不定のチャネル名、ペイロードで作業しなければならない場合はNOTIFYコマンドよりおすすめとのこと…

      pg_serial

      コミットされたシリアライザブルトランザクション(直列化トランザクション)に関する情報を保有するサブディレクトリ。

      忘れた:シリアライザブルトランザクションとは?
      …複数の並行に動作するトランザクションそれぞれの結果が、いかなる場合でも、それらのトランザクションを時間的重なりなく逐次実行した場合と同じ結果となる場合のやつ。
      ほかに、「REPEATABLE READ」、「READ COMMITTED」、「READ UNCOMMITTED」…などがあったのをsilverでやったようなかすかな記憶。


      pg_snapshots😂

      エクスポートされたスナップショットを保有するサブディレクトリ
      いろいろ発行されるSQLのトランザクション時の状況を把握するためのものという感じか?
      (Committed)、アボート(Abort)、進行中(In Progress)

      pg_stat_tmp

      統計情報にかかわる用語。
      統計情報コレクタが集める情報が納められるディレクトリ。stats_temp_directoryにて指定可能。サーバシャットダウン時はpg_statサブディレクトリへきちんと保存(tmpでなくなる)。
      ★統計情報コレクタとは?…サーバの活動状況に関する情報を収集し、報告するサブシステム。「テーブルとインデックスへのアクセス、各テーブルでのVACUUMやANALYZEの実施情報、ユーザ定義関数の呼ばれた回数、それぞれの消費した総時間」などが収集できるようだ。

      pg_subtrans

      サブトランザクションの状態のデータを保有するサブディレクトリ。
      サブトランザクションとは?…トランザクションのBEGIN;~COMMIT;内でSAVEPOINTを使用すると割り当てられる。外部のトランザクションから見た場合、サブトランザクションとトランザクションの区別はない。トランザクションが終了すればサブも終了する。

      pg_tblspc

      テーブル空間へのシンボリックリンクを保有するサブディレクトリ。
      “\db” コマンドで参照できる。
      テーブルスペースのOIDがどのディレクトリを使用しているかなどが確認できる。

      pg_twophase

      プリペアドトランザクション用の状態ファイルを保有するサブディレクトリ。
      プリペアド(準備)トランザクションとは?…2相コミットにかかわる用語。
      トランザクション中、各データベースに対してコミットできる状態であるかどうかを確認するための準備(prepare)を送り、各データベースが返事を返す。すべてOKならcommitするが、一つでもNGならばrollbackする。

      ssl

      PostgreSQLは標準でSSL接続をサポート。事前に要OpenSSLインストール。SSLサポートを有効にしてコンパイルされた場合はpostgresql.confで制御可能。
      SSLモードにて起動するためには、サーバ証明書と秘密鍵を含むファイルが必要。

      pg_stat_ssl

      動的統計情報ビューのうちの一つ。接続(通常およびレプリケーション)あたり1行の形式で、接続に使われるSSLの情報を表示できる。
      内容はpid(プロセスid),ssl(sslを使用しているかどうか),version(sslのバージョン),cipher(暗号名),bits(暗号アルゴリズムのビット数),compression (SSL圧縮を使用しているか),clientdn(クライアント証明書の識別名)など。
      上の項目は、SSLを使用していない場合に意味のない項目はNullになる。

      pgcrypto

      モジュール。PostgreSQL用の暗号関数を提供。
      汎用ハッシュ関数(md5,sha1,sha224,sha256,sha384,sha512)、HMAC関数、パスワード関数をサポート。
      ・SSLと併用しよう
      ・encrypt/decrypt 暗号化/復号
      ・DB管理者は復号可能!
      ・インデックスは通常通りに利用できない

      ALTER ROLE

      データベースロールを変更するコマンド。
      CREATE ROLEで設定された属性を変更できる(だが、メンバ資格については、GRANT,REVOKEを使用する。それぞれ、アクセス権限の付与と削除。)
      例として、ロールの名前や、スーパーユーザーかどうかや、データベースやロールの作成権限があるかどうかなどを変更できる。

      ロールはたぶん「ユーザ」と置き換えて読んでいい…はず…。厳密にはだめよとかあるのかしら。


      ALTER DATABASE

      データベースを変更するコマンド。
      名前や、オーナ変更、テーブルスペース、いろいろな設定値…などを変更できる。リセットもできる。
      データベース所有者やスーパユーザが使用可能。

      initdb -data-checksums (-k)

      関連:「チェックサム」項目
      データの破損をいち早く察知できるようにする仕組み。そのためのクラスタ初期化時のコマンド(-k)。

      log_statement

      どのSQL文をログに記録するかを制御する。
      有効な値は、none(off)、ddl、mod、およびall(全てのメッセージ)
      スーパーユーザのみ変更可能。
      allとしても、Execute段階以前(つまり、解析や計画作成期間)に失敗した文は記録されない。

      track_functions

      関数の呼び出し数と費やされた時間の追跡を有効にする。手続き言語関数…pl、SQL関数やC言語関数も追跡する場合にはallとする。デフォルトはnone。
      スーパーユーザのみ変更可能。

      track_activities

      各セッションで実行中のコマンドに関する情報とそのコマンドの実行開始時刻の収集を有効にする。デフォルトで有効。セッションの所有者とスーパーユーザのみ可視。
      設定はスーパーユーザのみ変更可能。


運用管理用コマンド全般 【重要度:4】

  • 説明:
    データベースの運用管理に関する高度な知識を問う
  • 主要な知識範囲:
    バックアップ、PITR

    PITRはポイント・イン・タイム・リカバリ (アーカイブ・リカバリ)の略。
    オンラインバックアップ+WALログで、WALログが存在する時点までリカバリ可能。
    データ配置の上「recovery.conf」を作成してpostgresを起動する。
    使用済みのrecovery.conf ファイルは recovery.doneに名前が変わるらしい。

    VACUUM、ANALYZE、REINDEX

    VACUUM…物理削除されない「削除されたタプルや更新によって不要となったタプル」を回収する。定期的にやろう。(やらない限り領域は食い続ける)パラメータがない場合は全テーブルに実施する。
    ANALYZE…データベース内のテーブルの内容に関する統計情報を収集し、その結果をpg_statisticシステムカタログに保存する。パラメータがない場合は全テーブルに実施する。
    REINDEX…インデックスのテーブルに保存されたデータを使用してインデックスを再構築する。上二つよりはあまり使われないか。

    自動バキューム

    これはVACUUMとANALYZEコマンドの実行を自動化したもの。
    実施に当たって統計情報収集機能を使用するため、track_countsが有効になっている必要がある。

    チェックポイント

    ログ内の情報を反映するために全てのデータファイルを更新する、トランザクションログのある一時点を指す。
    全てのダーティデータページをディスクへ書き出す。

    サーバログ管理

    stderr、csvlog、syslogなど数種類のログ出力が可能。
    postgresql.confに記載可能(コマンドラインも可能)。

    ディスク容量監視😂

    ・VACUUM情報を使用してpsqlから
    ・contrib/dbsizeを使用してpsqlから
    ・contrib/oid2nameを使用してコマンドラインから行う方法ディレクトリとしては「PGDATA」、「WAL」などがあるディスクの領域監視はしておくべきであろう。

    これは別の監視(Zabbixとか…)からやるってことなのかな、項目の意図がよくわからないな


    自動VACUUMと手動VACUUM/ANALYZEの違い😂

    ??とりあえずすぐわかったところでは「一時テーブルには自動バキュームでアクセスすることができない」らしい。ほか、項目になるくらいだから何か違うのだろうと思われる。要調査。

  • 重要な用語、コマンド、パラメータなど:
    ALTER SYSTEM

    サーバの設定パラメータを変更する。
    postgresql.confを編集してもよいが、このコマンドからも編集できる。スーパーユーザのみ。
    サーバ設定の再ロード、あるいはサーバ開始時にのみ変更可能なパラメータについては次回のサーバ再起動で有効になる。

    ファイル編集のほうが好みかも。


    ANALYZE

    データベース内のテーブルの内容に関する統計情報を収集し、その結果をpg_statisticシステムカタログに保存する。パラメータがない場合は全テーブルに実施する。

    CLUSTER

    インデックスに従ってテーブルをクラスタ化する。
    index_nameで指定されたインデックスに基づき、table_nameで指定されたテーブルをクラスタ化するように、PostgreSQLに指示する。

    REINDEX

    インデックスのテーブルに保存されたデータを使用してインデックスを再構築する。

    VACUUM

    物理削除されない「削除されたタプルや更新によって不要となったタプル」を回収する。定期的にやろう。(やらない限り領域は食い続ける)パラメータがない場合は全テーブルに実施する。

    CHECKPOINT

    ログ内の情報を反映するために全てのデータファイルを更新する、トランザクションログのある一時点を指す。
    全てのダーティデータページをディスクへ書き出す。

    PITR

    ポイント・イン・タイム・リカバリ (アーカイブ・リカバリ)の略。

    WAL

    ログ先行書き込み(Write Ahead Log)。
    オンラインバックアップ+WALでかなり最新までのリストアが可能。

    pg_dump

    postgresのバックアップ用コマンド。
    ・オンラインで使用可能
    ・ほかのユーザの読み書きをブロックしません
    スクリプト形式(sql平文)かアーカイブファイル形式で出力する。

    pg_dumpall

    クラスタの全てのデータベースを一つのスクリプトファイルに書き出すユーティリティ。全てのデータベースに共通するグローバルオブジェクトもダンプする (pg_dumpはこれらのオブジェクトを保存しない)
    pg_dumpとpg_dumpallはファイルシステムレベルのバックアップを生成しないため、WALを使用してのリカバリには使用できない。

    pg_basebackup

    稼動中のPostgreSQLのデータベースクラスタのベースバックアップを取得する(常にデータベースクラスタ全体)
    WALを使用してのリカバリに使用できる(9のどこかから?)…はず

    pg_start_backup()

    オンラインでのベースバックアップの前に発行する。

    pg_stop_backup()

    オンラインでのベースバックアップの最後に発行する。

    ★ベースバックアップの取り方。
    ①pg_start_backup() → rsyncやtarなどで領域をバックアップ → pg_stop_backup()
    ②あるいは、pg_basebackup。
    pg_basebackupの場合、「pg_start_backup、pg_stop_backup」にあたる動作が自動で含まれるらしい。のでpg_basebackupのみ打てばよい。


    postgresql.conf

    パラメータ名 = 設定値として記載する、設定ファイル。listenアドレスの定義など…

    recovery.conf

    リカバリ用の設定ファイル。リカバリ時のみ有効。
    使用するWALファイルのある場所を記載(restore_command = ‘cp /mnt/server/archivedir/%f “%p”‘)したりすることが可能。

    vacuumdb

    SQLコマンドVACUUMのラッパ。コマンドラインから発行する。
    関連:VACUUM

    pgstattuple

    タプルレベルの統計情報を入手するための各種関数を提供するモジュール。

    pg_cancel_backend()

    pg_cancel_backend(pid int)
    バックエンドの現在の問い合わせを取り消すコマンド。
    同ロールのユーザ同士、あるいはスーパユーザから他のユーザへ発行可能。
    通常ユーザAのselectをスーパユーザがキャンセルしたりできる。取り消された側のユーザ画面には、キャンセルの旨表示される。
    ERROR: canceling statement due to user request
    SQLは蹴られるが、再接続は不要。

    pg_terminate_backend()

    pg_terminate_backend(pid int)
    バックエンドを終結させるコマンド。…終結て?と思うが、要はとあるユーザの接続を切断できるようだ。
    対象の接続のpidは把握しておかなくてはいけない。
    select * from pg_stat_activity;

    pg_isready

    PostgreSQLサーバの接続状態を検査するコマンド。
    pg_isready -h xxx.xxx.xxx.xxx
    などで、接続を受け付けているとか拒絶しているとか応答がないとか返ってくるようだ。死活監視など用なのかな?

    log_connections

    postgresql.confにてonかoffで設定する。onにしておくと、クライアント認証の成功終了などのサーバへの接続試行がログに残る。デフォルトはoff。
    #log_connections = off

    log_disconnections

    postgresql.confにてonかoffで設定する。onにしておくと、コネクション断時のことがログに残る。デフォルトはoff。
    #log_disconnections = off

    log_duration

    設定するとすべてのクエリの実行時間を記録する。
    遅いクエリを探したいときなどに使うのかもしれない。
    #log_duration = off


 

データベースの構造 【重要度:2】

  • 説明:
    データベースの物理的な構造に関する知識を問う
  • 主要な知識範囲:
    データベースクラスタの構造

    ディスク上のデータベース格納領域をデータベースクラスタという。
    データベースファイルのレイアウト

    プロセス構造

    postgresでは、サーバをバックエンド、クライアントをフロントエンドという。
    ほか、postgres常駐プロセス、postgres子プロセス、ライタープロセス、WALライタープロセス、アーカイバープロセス、統計情報収集プロセス、ロガープロセス、自動vacuumプロセス、WAL(SENDER/RECEIVER)プロセスなどがある。

    データの格納方法

    PGDATA内のディレクトリ。
    PG_VERSION
    base★この下に各データベースのサブディレクトリが存在する。名前は各OID
    global
    pg_clog
    pg_subtrans
    pg_tblspc
    pg_xlog
    postmaster.opts
    postmaster.pid

  • 重要な用語、コマンド、パラメータなど:
    autovacuum

    autovacuum (boolean)
    サーバがautovacuumランチャデーモンを実行すべきかどうかを管理する。

    TOAST😂

    過大属性格納技法:The Oversized-Attribute Storage Technique
    (自信なし)
    PostgreSQLは固定長のページサイズ(通常8キロバイト)を使用し、複数ページにまたがるタプルを許さない。ので、この限界を何とかするため、裏で分割したりしてなんとかしている。ユーザからはあまり意識されないっぽい。

    FILLFACTOR

    テーブル領域を何パーセントまで使用するかの割合。10~100で、デフォルトは100。
    100以下の、例えば90にした場合では、10%は使用されずに更新用として扱われる。その場合、この10%を利用してテーブル更新が効率的に行われる。
    ※頻繁な更新のないテーブルであれば、100でよいっぽい

    アーカイブログ

    PITRするためのWALファイルのアーカイブ。
    最古のベースバックアップに使用できるものより前は消してしまってよい。
    (もうその分はベースバックアップに含まれており、リカバリに使わないから)

    ページヘッダ

    テーブルやインデックスはページ(通常固定8キロバイト・コンパイル時にのみ可変)の集まり。そのそれぞれのページの最初の24バイトはページヘッダ(PageHeaderData)。

    タプルヘッダ

    タプルの先頭には27バイトのタプルヘッダがある。らしい。
    タプル27バイト、パディング1バイト、OID4バイト、実データ
    という構造。

    postmasterプロセス(常駐postgresプロセスとも?)

    マルチユーザデータベースサーバ。接続を介する。複数建てる場合には、別のデータ領域・接続ポートを設定して使用する必要がある。

    postgresプロセス(子postgresプロセスとも?)

    pg_hba.conf によって接続の可否をチェックするプロセス。

    バックグラウンドプロセス(バックグラウンド・ライター・プロセスとも?)

    共有メモリ上のバッファを最適なタイミングでハードディスクに書出すプロセス。それによって、チェックポイント時の大量書き込み等を抑制する。
    postgresql.conf の bgwriter_delay で規定される時間 (デフォルトでは 200 ミリ秒) 休止してまた動作する。

    SQL実行のキャンセル

    pg_cancel_backend(pid int) …実行中の SQL をキャンセルする(セッションは残)
    pg_terminate_backend(pid int)…セッションを終了させる(接続断)
    いずれも同ロールか、あるいはスーパーユーザから。
    ・キャンセルしたい SQL を実行中のプロセス ID (procpid) を特定することが必要。

    シグナル(TERM/INT/HUP)によるサーバプロセスへの影響

    左から、シグナル、postmasterの動作、サーバの動作。
    SIGTERM、kill(*,sigterm)kill(*,9) を実行し、その後終了、 die() を実行
    …スマートシャットダウンモード。新しい接続は打ち切られるが、接続中のセッションがすべて切られるまではシャットダウンしない。また、バックアップ中もシャットダウンを待つ。バックアップによる待機中はスーパーユーザのみ新しく接続可能。
    SIGINT 、終了、 問い合わせを取り止め
    …高速シャットダウンモード。新しい接続を禁止してサーバもすべて終了し、即座に終了します。バックアップも打ち切られます。
    SIGHUP、kill(*,sighup) を実行、 read_pg_options() を実行
    …いわゆるリロード。設定ファイルのオプション変更などを再起動なく反映されることができる(ま、リロードでは反映できない項目もあったような?)。


 

ホット・スタンバイ運用 【重要度:1】

  • 説明:
    レプリケーション構成を組むための設定や構築手順、およびレプリケーションの仕組み(プロセスやフロー)、状態の監視などに関する知識を問う
  • 主要な知識範囲:
    ストリーミングレプリケーション機能とロジカルレプリケーション機能の概要

    2種のレプリケーションが使用可能。
    ストリーミングレプリケーション

    9.0以降の本体組み込みレプリケーション機能。マスタから参照のみ可能なスタンバイDBへレプリケーションする。遅延も少なくなかなか良いようだ。特に参照sqlの分散などに役に立つ。また、フェイルオーバも可能。WAL送信による反映である。
    ロジカルレプリケーション
    10からの新機能。データベース単位や、テーブル単位など、柔軟なレプリケーションが可能。丸ごとでなく部分的なレプリケーションに向く。WALを元ネタとするが、ロジカルデコーディング機能によって論理的な変更情報を抽出し、独自のバイナリ形式に変換したうえで送信する。
    送信先のDBでも更新が可能(ゆえに衝突したりもする…)
    色々できそうな拡張性は感じるが、扱いづらそうでもある…

    同期レプリケーションと非同期レプリケーション

    同期…スタンバイ側でのWAL書き込みを待つ。マスタへのデータ変更→WALをスタンバイが書き込む→OKになってから二つコミット。非同期に比べると遅いであろうが、確実系。
    非同期…スタンバイ側でのWAL書き込みを待たずにMasterがcommitする(反映が早いが、マスタにはあるがスタンバイにはないデータがある時間帯があるかも)。

     

    スタンバイにさらにスタンバイを持たせる(カスケードスタンバイ)場合は、非同期のみが使用可能。


    postgresql.conf、recovery.confの設定

    postgresql.conf … たくさんあるが…起動時の動作について設定。
    recovery.conf … とあるリカバリ時のみの設定。

    パブリケーションとサブスクリプションの定義😂

    パブリケーション … 物理論理レプリケーションのマスター。パブリッシャー。
    サブスクリプション … 論理レプリケーションの下流側。サブスクライバー
    パブリケーションの方、下のURLのドキュメントが間違っているのか???多分論理レプリケーションの用語だと思うのだが…。一応論理の方で覚えておく。
    31.1. パブリケーション

  • 重要な用語、コマンド、パラメータなど:
    wal_level

    wal_levelはどれだけの情報がWALに書かれるかを決定する。
    サーバ起動時のみ設定可能で、デフォルトはminimal。
    minimalは高速だが、リカバリのための情報としては不足する。そのため、WALアーカイブやレプリケーションを使用する場合にはarchiveまたはhot standby水準を使用すること。

    max_wal_senders

    スタンバイサーバや予備クライアントから同時接続をいくつ受けるかの定義。(=同時に稼働するWAL送信プロセスの最大値)デフォルトは0で、レプリケーション不可。

    wal_sender_timeout

    指定されたミリ秒以上動かなかったレプリケーション接続を停止する。デフォルト60秒。つまりスタンバイ側が60秒反応しなければレプリケーション停止。

    wal_receiver_timeout

    こちらも指定されたミリ秒以上動かなかったレプリケーション接続を停止する。目線はスタンバイサーバ側。プライマリノードが停止したか?などの検出に役立つ。デフォルト値は60秒。

    synchronous_standby_names

    スタンバイサーバの名前を指定する設定。

    synchronous_commit

    アプリケーションの応答をWALレコードがディスク上に書き込まれるまで待つかどうか。offだと待機しない。

    max_logical_replication_workers

    サブスクライバーの挙動を制御する。
    論理レプリケーションワーカーの最大数を指定する。

    CREATE/ALTER/DROP PUBLICATION/SUBSCRIPTION


    pg_stat_replication

    ビュー
    WAL送信プロセス毎に1行の形式で送信サーバが接続したスタンバイサーバへのレプリケーションに関する統計情報を表示する

    pg_stat_wal_receiver

    ビュー
    1行の形式で受信サーバが接続したサーバからWALレシーバに関する統計情報を表示する

    recovery_min_apply_delay

    スタンバイサーバでの遅延commitにかかる設定。
    本来可能なかぎり早くcommitするが、遅延させることで、例えばミスのTRUNCATEなどが反映前に何とかすることができるかもしれない。

    スタンバイでの問い合わせのコンフリクト(衝突)

    いろいろコンフリクトする…
    ホットスタンバイ

    hot_standby_feedback

    ホットスタンバイが処理している内容をプライマリに送信するかどうか。
    それをうけてマスタ側では削除保留をしたりなどに使うらしい。デフォルトoff
    #hot_standby_feedback = off # send info from standby to prevent

    max_standby_streaming_delay

    コンフリクトが発生した場合に何秒待ってクエリをキャンセルするか。「-1」でずっと待つ。
    #max_standby_streaming_delay = 30s # max delay before canceling queries

    pg_wal_replay_pause()

    リカバリ制御関数。即座にリカバリを停止する。
    リカバリ停止中は、それ以降のデータベースへの変更は適用されない。

    pg_wal_replay_resume()

    リカバリ制御関数。リカバリ停止中であれば再開する。

    トランザクションログが格納されているpg_xlogディレクトリがpg_walに変更になった。pg_xact同様、ログとついていると消してしまうなど問題が起きやすいが、実際には消したりしてはいけないためわかりやすくする意図。

    walsenderプロセス

    [pri]backend -> wal sender ->[stand by]wal receiver -> startup
    スタンバイは延々リカバリをしているのと同じ状態。

    walreceiverプロセス

    [pri]backend -> wal sender ->[stand by]wal receiver -> startup

    pg_receivewal

    WALをストリームするために使用される。リアルタイム。

    トランザクションログ(WAL)

    トランザクションのログを残すための一般的な手法。
    出力のレベルにもよるがリカバリに使えたり。

    スタンバイへ伝搬される処理とされない処理

    WALで転送される。完全な複製。

    多分、「プライマリ-スタンバイ」と書かれていると、物理レプリケーションの方なのかな。と判断している。
    「パブリケーション-サブスクライバ」が論理レプリケーション。


    スタンバイで実行可能な問い合わせ😂

    ・読み取り
    SELECT,COPY TO
    ・カーソル操作
    DECLARE FETCH CLOSE
    ・パラメータの操作
    SHOW,SET,RESET
    ・DCL
    BEGIN,END,ABORT,START TRANSACTION
    SAVEPOINT,RELEASE,ROLLBACK TO SAVEPOINT,
    EXCEPTION
    ・LOCK TABLE
    ・PREPARE,EXECUTE,DEALLOCATE,DISCARD
    色々あるな…他にもありそう

    ロジカルレプリケーションのサブスクライバ―へ伝搬される処理とされない処理

    変更された行の情報が伝搬される。
    ・TRUNCATEコマンドやDDLはレプリケーションされない。
    ・シーケンス、ラージオブジェクトや通常のテーブル以外(インデックス、ビュー、マテリアライズド・ビューやパーティションテーブルの親テーブルなど)もレプリケーションされない。
    ・★レプリケーションは基底テーブルから基底テーブルのみ。
    第3回「ロジカルレプリケーション」 | NTTデータ先端技術株式会社

性能監視(30%)
アクセス統計情報 【重要度:3】

  • 説明:
    データベースの利用状況を示す稼働統計情報の内容や見方、収集方法に関する知識を問う
  • 主要な知識範囲:
    pg_locks

    ビュー。
    トランザクションにより獲得されたロックに関する情報がみられる。
    内容としてはロックタイプやら対象DBやらサーバプロセスのpidやらリレーションのoidやら。

    pg_stat_activity、pg_stat_database

    ビュー。
    pg_stat_activity … サーバあたり1行の形式で状態や現在の問い合わせ等のプロセスの活動状況を表示する。pidやらユーザ名やらIPやら状態やら。
    pg_stat_database … データベースあたり1行の形式で、データベース全体の情報を表示する。データベース名やら、トランザクション数やら、ロールバック数やら返された行数やら更新された行数やら削除された行数やら。

    pg_stat_all_tables 等、行レベル統計情報

    各テーブルごとのアクセス統計。

    pg_statio_all_tables 等、ブロックレベル統計情報

    特定のテーブルに対するI/Oに関する統計情報。読み取られたブロック数やバッファヒット数…など…

  • 重要な用語、コマンド、パラメータなど:
    pg_stat_archiver

    WALアーカイバプロセスの活動状況の統計情報。
    アーカイブに成功したWAL数やら最後のWALやら時刻やら失敗やらなんやら。

    pg_stat_bgwriter

    バックグラウンドライタプロセスの活動状況の統計情報。
    実行されたチェックポイントの個数やら要求されたチェックポイントの個数やらかかった時間やらバッファ数やらなんやら。

    待機イベント(pg_stat_activity.wait_event)

    pg_stat_activity(ビュー)に「wait_event」というものが含まれる。
    待機イベント名として、何待ちなのか(ロックの状態)が見れるようだ。
    例:OidGenLock OIDを割り当て、または付与するのを待っている状態
    みたいな。

    pg_stat_progress_vacuum

    ビュー。
    現在バキュームしているバックエンドごとに1行。
    pidやらデータベースのOIDやらデータベース名やら処理フェーズやらブロックサイズやら…


 

テーブル / カラム統計情報 【重要度:2】

  • 説明:
    プランナが利用するテーブル・カラムの統計情報についての理解を問う
  • 主要な知識範囲:
    pg_class

    テーブルや列を持つもの、テーブルに似たすべてのものの目録(カタログ)。
    relkindでrは通常のテーブル、iはインデックス、Sはシーケンス、vはビュー、mはマテリアライズドビュー、cは複合型、tはTOASTテーブル、fは外部テーブル。

    pg_stats

    pg_statisticカタログの情報にアクセスするためのビュー。
    スキーマ名、テーブル名、列名やnullとなっている項目の割合、バイト単位の平均幅…など。

    テーブル・インデックスの実ファイルとパス

    テーブルとインデックスには1つにつき1つの物理ファイルが割り当てられる。
    その物理ファイルとパスの追い方
    1.DBのIDの確認 → [データベースid]
    SELECT datid, datname FROM pg_stat_database;
    2.テーブル等のファイル名の確認 → [ファイル名]
    SELECT relid, relname FROM pg_stat_all_tables;
    ↓物理パスはこのように…
    PGDATA/base/[データベースid]/[ファイル名]

    実行計画時に利用される統計情報やパラメータ

    実行計画 … ユーザによって発行されたクエリを実行するための手順書。PostgreSQLプランナが生成する。EXPLAINで確認できる。
    ANALYZE(実際の実行時間やその他の統計情報を表示)をオプションで付ける場合は、rollbackできるようにすること。BEGIN;
    EXPLAIN ANALYZE …;
    ROLLBACK;基本、自動VACUUMによって統計情報は最新化されているため、ANALYZEは必要ないが、直近で大きな変更を加えられたテーブルに発行する場合は自分でANALYZEオプションをつける必要がある。プランナが使用する統計情報は、「各テーブルとインデックスの項目の総数と、各テーブルとインデックスが占めるディスクブロック数」。それぞれpg_class内のreltuples、relpagesに保持される。この値は、随時ではなくVACUUMやANALYZE時などに更新される。人間がみる場合はpg_classよりもpg_statsビューの方が見やすい。
    それぞれの列に対するmost_common_vals内とhistogram_bounds配列のエントリの最大数は、列ごとにdefault_statistics_target設定パラメータに設定することによってグローバルに設定することができる。
    プランナで使用される統計情報

    重要な用語、コマンド、パラメータなど:
    pg_statistic😂

    データベースの内容に関する統計データのカタログ。
    ANALYZE(分析)で作成され、テーブルの列に対し一つのエントリが存在する。
    内容はテーブルやインデックスのoid・記述された列数・他難解な統計情報など・・・。
    一般ユーザは読めない。

    pg_stats

    pg_statisticの中で一般ユーザが読める部分だけを表示できるビュー。

    null_frac

    pg_stats内のNULLとなっている列項目の割合。

    n_distinct

    列内の個別値の推定数(?)

    most_common_freqs

    最も一般的な値の出現頻度のリスト。よくある値の出現率。

    histogram_bounds

    列の値を満遍なく似たような数でグループに分配した値のリスト。分布。

    correlation

    ディスク上の並び順と論理的な列の値の並び順に関する統計的相関。
    -1~1となるが、どちらかに近ければアクセスがあまりランダムにならず安価であるらしい。

    default_statistics_target

    postgresql.confの設定。
    デフォルトの統計対象を設定する。大きくすると負荷が上がるがプランナの予測の品質は高くなる。
    #default_statistics_target = 100 # range 1-10000

    effective_cache_size

    単一の問い合わせで利用できるディスクキャッシュの実効容量に関するプランナの条件を設定する。色々考慮して決める必要がある。
    #effective_cache_size = 4GB


 

クエリ実行計画 【重要度:3】

ここ、全体的によくわからない…文系だからかな?!

  • 説明:
    EXPLAINが出力する実行計画を読み取り、チューニングを行う。
  • 主要な知識範囲:
    EXPLAIN / EXPLAIN ANALYZE 出力

    実行計画(EXPLAIN)で確認できる。
    EXPLAIN SELECT * FROM tenk1; などとして実行する。
    ANALYZEをオプションにつける場合、CREATEやDELETEが実際に行われるのでrollbackするように記載する。
    EXPLAINのANALYZEオプションを使用して、プランナが推定するコストの精度を点検することができる。
    EXPLAIN時のコストと実際の実行時間が比較できる(乖離が大きいほど、プランナの精度が低かったことに)。
    Planning time: 0.181 ms
    Execution time: 0.501 ms
    EXPLAINの利用

    計画型😂

    プランナがどのようなふるまいをするか…?
    ビットマップスキャン計画型
    ハッシュ集約計画型
    ハッシュ結合計画型
    インデックス走査計画型
    インデックスオンリースキャン計画型
    マージ結合計画型
    TID走査計画型

    この資料はわかりやすい↓
    https://www.postgresql.jp/sites/default/files/2017-01/T6_ExplainingExplain_pgday2012_for_print.pdf


    EXPLAINからのチューニング

    ・時間がかかっているものを見つけ、
    →インデックスを適正に作成する
    →VACUUMをかけるなど…

    結合の種類(Nested Loop、Hash、Merge)と性能特性

    NestedLoop … 片方のテーブルから結果を取り出したあとでその結果をもう片方のテーブルへ問い合わせて結合する。基本的には索引アクセスとなる。
    Hash … 結合の片方から候補となるレコードをハッシュテーブルにロードし、それをもう片方の各行と突き合わせる。Nestedに比べ、データ件数が多くなるほど早い可能性が高い。
    Merge … ソートした二つのリストをマージする。ほとんどハッシュと同じ動作。
    完全な対称性があることが特徴。完全外部結合が可能。

    SQL構文(JOIN/GROUP BY/ORDER BY/LIMIT)に対応する実行計画😂

    上記のお題目からはさっぱりわからなかったのだが、「プラン演算子」なるものがあるらしい。きっとこれのことだろうか。。。?
    JOIN … (おそらく)Nested Loop,Merge Join,Hash Joinのこと?
    GROUP BY … Group演算子
    ORDER BY … Sort演算子
    LIMIT … Limit演算子

    こちらにあるスライドがとてもありがたい


    集約関数(sum/count)を伴うSQLに対応する実行計画😂

    基本的にはインデックスを使用しない
    Aggregate演算子

    パーティションに対するSQLの実行計画

    パラレルクエリが使用できるようになった。

    パラレルクエリに対応する実行計画

    パラレルクエリは9.6から導入
    パラレルではない場合の実行計画よりもコスト推定値が下回った場合に使用される

    ウィンドウ関数(row_number/rankなど)のSQLに対応する実行計画

    正体はSort演算子らしい。
    SQLにおける行間比較で、相関サブクエリを使わなくて済む「ウィンドウ関数」とは?
     SQLの達人と呼ばれるミックさんがモダンなSQLプログラミングの手法について解説した『達人に学ぶSQL徹底指南書 第2版』。今回、本書からミックさんが主役級と語る「ウィンドウ関数」について紹介します。ウィンドウ関数を利用することで、SQLでの行間比較において難しい相関サブクエリを使わなくてよくなるのです。

  • 重要な用語、コマンド、パラメータなど:
    EXPLAIN / EXPLAIN ANALYZE

    実行計画の最適化のため、ある条件を満たす行数と統計情報が必要となる。ANALYZEコマンドにて収集する。保存先はpg_stat、pg_statistic


 

その他の性能監視 【重要度:1】

  • 説明:
    性能監視に関するその他の手法
  • 主要な知識範囲:
    スロークエリの検出

    pg_stat_user_functions(関数のプロファイリングを行うビュー) や contrib/pg_stat_statements(SQLのプロファイリングを行うビュー)
    を使うか、
    log_min_duration_statementなどで一定以上かかったクエリをログに出すか

    付属ツールによる解析


    性能劣化要因(リソース枯渇、ロック競合)


  • 重要な用語、コマンド、パラメータなど:
    shared_preload_libraries

    サーバ起動時にプリロードされる一つ以上の共有ライブラリを指定する。起動時にのみ有効。
    例えばsql文実行時の統計情報を記録する手段を提供する「pg_stat_statements」など。
    postgresql.confに以下のように定義する。
    # postgresql.conf
    shared_preload_libraries = ‘pg_stat_statements’

    auto_explain

    自動的に遅い文の実行計画をログ記録する。
    最適化されていないSQLを探し出すのに有効。
    LOAD ‘auto_explain’;

    auto_explain.*

    いろいろオプションがある。
    auto_explain.log_min_duration (integer) → どのくらいの実行時間で記録するか。ミリ秒
    auto_explain.log_analyze (boolean) → ログが取得されたときに出力される際、EXPLAINではなくEXPLAIN ANALYZE出力を行う。
    auto_explain.log_buffers (boolean) → バッファ使用統計を出力するかどうか。EXPLAINのBUFFERSオプションと同様。
    auto_explain.log_timing (boolean)  → ノード毎の時間的調整情報を出力するかどうか。TIMINGオプションと同様。
    auto_explain.log_triggers (boolean)  → トリガ実行の統計を出力するかどうか。
    auto_explain.log_verbose (boolean)  → 詳細ログ。VERBOSEオプションと同様。
    auto_explain.log_format (enum)  → 出力形式を選ぶ。text,xml,json,yaml
    auto_explain.log_nested_statements (boolean)  →入れ子状の(関数内から実行されるような)ものを考慮する。オフの場合は最上位のみ記録される。

    log_min_duration_statement

    許容できないレスポンスタイムを書いておく。超えるとログ出力される。

    pg_stat_statements

    ビュー。
    サーバで実行されたすべてのSQL文の実行時の統計情報を記録する手段を提供する。

    log_autovacuum_min_duration

    log_autovacuum_min_duration (integer)
    autovacuumで実行される各活動がログに残るようになる。0にすると全部残る

    log_lock_waits

    log_lock_waits (boolean)
    セッションがロックを獲得するまでの間に、「deadlock_timeout」より長く待機する場合にログに出すかどうか。ロック待ちで性能が出せていない状況かを確認するのに有用。

    log_checkpoints

    log_checkpoints (boolean)
    チェックポイントをサーバログに記録するようにする。書き出されたバッファ数や書き出しに要した時間などの一部の統計情報が含まれる。コマンドラインからも設定可。

    log_temp_files

    log_temp_files (integer)
    一時ファイルが削除された時にログを出力するかどうか。-1で無効、0でall、数値で指定キロバイト単位の容量のファイルのみ出力。

パフォーマンスチューニング(20%)
性能に関係するパラメータ 【重要度:4】

  • 説明:
    データベースの設定パラメータで、特にパフォーマンスに影響を与えるもの、パフォーマンスチューニングの参考になるものに関する理解を問う
  • 主要な知識範囲:
    資源の消費 (RESOURCE USAGE)

    メモリに関する:
    shared_buffers (integer)  メモリ量
    huge_pages (enum)  hugememoryページの利用を有効にする
    temp_buffers (integer)  一時バッファの最大数
    max_prepared_transactions (integer)  同時にPREPAREDできるトランザクションの最大数(PREPARE TRANSACTION — 二相コミット用に現在のトランザクションを準備)。
    work_mem (integer)  内部並べ替えとハッシュテーブル操作が使用するメモリ使用量。
    maintenance_work_mem (integer)  VACUUM,CREATE INDEX,ALTER TABLE ADD FOREIGN KEYなどの保守操作で使用されるメモリの最大量。
    replacement_sort_tuples (integer)  指定値よりタプル数が小さい場合はソート処理が変わる()
    autovacuum_work_mem (integer)  個々の自動バキュームワーカが使用する最大のメモリ量
    max_stack_depth (integer)  実行スタックの最大安全深度
    dynamic_shared_memory_type (enum)  サーバの使用する動的共有メモリの実装
    ディスクに関する:
    temp_file_limit (integer) 一時ファイルとして使用できるディスクの最大容量。
    カーネルに関する:
    max_files_per_process (integer) サーバ子プロセスが同時にオープンできるファイル数の最大値
    コストに基づくVacuum遅延:
    VACUUM、ANALYZEコマンドの実施中、vacuum_cost_limitをこえるコストが発生するとvacuum_cost_delayで指定されたちょっとの間スリープする。I/O管理のために使う。
    バックグラウンドライタに関する:
    「ダーティ」な共有バッファの書き込みを行う(待ちの軽減に役立つが負荷になったりもするので調整)
    非同期動作に関する:
    effective_io_concurrency (integer) PostgreSQLが同時実行可能であると想定する同時ディスクI/O操作の数
    max_worker_processes (integer)
    max_parallel_workers_per_gather (integer)
    backend_flush_after (integer)
    old_snapshot_threshold (integer)
    19.4. 資源の消費

    ログ先行書き込み (WRITE AHEAD LOG)

    原子性と永続性を実現するためのテクニックのひとつ
    実際の動作の前にログに記録される

    問い合わせ計画 (QUERY TUNING)

    スロークエリを特定してEXPLAINで解析し、遅くなっている原因別に最適化する

    実行時統計情報 (RUNTIME STATISTICS)

    問い合わせおよびインデックスに関する統計情報コレクタ
    生成されるデータはpg_statとpg_statio系のシステムビュー経由でアクセス可能

    ロック管理 (LOCK MANAGEMENT)

    deadlock_timeout (integer) ロックを待つ時間 ミリ秒
    max_locks_per_transaction (integer)
    max_pred_locks_per_transaction (integer)
    ロック管理

    軽量ロックと重量ロック

    軽量ロック
    共有メモリ上のデータ構造へのアクセスに関する排他制御機能を提供することを意図したもの
    重量ロック
    sqlのロック文・行ロック、テーブルロックの管理、デッドロックの検出、トランザクション終了時に開放 主にテーブルのようなSQLで可視なオブジェクトを保護

  • 重要な用語、コマンド、パラメータなど:
    shared_buffers

    データベースサーバが使用する共有メモリバッファのために使用するメモリ量

    huge_pages

    hugememoryページの利用を有効にする(通常より大きなメモリー・ページをサポートできるOSの機能)

    effective_cache_size

    PostgreSQLが同時実行可能であると想定する同時ディスクI/O操作の数

    work_mem

    内部並べ替えとハッシュテーブル操作が使用するメモリ使用量。

    maintenance_work_mem

    VACUUM,CREATE INDEX,ALTER TABLE ADD FOREIGN KEYなどの保守操作で使用されるメモリの最大量。

    autovacuum_work_mem

    個々の自動バキュームワーカが使用する最大のメモリ量

    wal_level

    wal_level (enum)
    どれだけの情報をWALに出力するかを決定する
    minimal < archive < hot_standby

    fsync

    fsync (boolean)
    onにした場合、fsync()システム・コールを発行したりなどの方法で更新が物理的にディスクに書き込まれたかの確証を得ようと試みる。OSやHWのクラッシュ後にDBを一貫した状態に復旧させることを保証する。

    synchronous_commit

    トランザクションのコミットがクライアントに”success”の表示を返す前に、WALレコードがディスク上に書き込まれるまで待つかどうかの指定
    トランザクションの信頼性が確実であることよりも性能が重要である場合に有効

    checkpoint_timeout

    checkpoint_timeout (integer)
    WALチェックポイント間の最大間隔を秒単位で指定。デフォルトは5分

    checkpoint_completion_target

    checkpoint_completion_target (floating point)
    チェックポイントの完了目標をチェックポイント間の総時間の割合として指定 デフォルトは0.5
    #5分の場合、半分の2.5分で完了することを目標とする?

    deadlock_timeout

    deadlock_timeout (integer)
    ロック待ちの時間


チューニングの実施 【重要度:2】

  • 説明:
    データベース、およびSQLのチューニングに関する理解を問う
  • 主要な知識範囲:
    パラメータのチューニング


    実行計画のチューニング


    SQL のチューニング


    テーブル構成のチューニング


    ディスクI/Oの分散

    WALをデータと別のファイルシステム上に配置するなど?

    パラメータの反映方法(パラメータ有効化のために必要なアクション)

    リロードか再起動(項目により異なる)

    インデックスがSQLの性能に与える影響


    Index Only Scan とVisibility Map★ver2では[削除]のようです

  • 重要な用語、コマンド、パラメータなど:
    Index Only Scan
障害対応(20%)
起こりうる障害のパターン 【重要度:3】

  • 説明:
    データベースでのSQL実行タイムアウトやサーバダウン、動作不良、データ消失、OSリソース枯渇などの故障が発生した場合について、エラーメッセージの内容から原因を特定し、適切な対応ができるかを問う
  • 主要な知識範囲:
    サーバダウン、動作不良、データ消失への対処


    OS リソース枯渇

     


    OSのパラメータ


    サーバプロセスの状態(idle、idle in transaction、active)


    シグナル(TERM/INT/HUP)によるサーバプロセスへの影響

    左から、シグナル、postmasterの動作、サーバの動作。
    SIGTERM、kill(*,sigterm)kill(*,9) を実行し、その後終了、 die() を実行
    …スマートシャットダウンモード。新しい接続は打ち切られるが、接続中のセッションがすべて切られるまではシャットダウンしない。また、バックアップ中もシャットダウンを待つ。バックアップによる待機中はスーパーユーザのみ新しく接続可能。
    SIGINT 、終了、 問い合わせを取り止め
    …高速シャットダウンモード。新しい接続を禁止してサーバもすべて終了し、即座に終了します。バックアップも打ち切られます。
    SIGHUP、kill(*,sighup) を実行、 read_pg_options() を実行
    …いわゆるリロード。設定ファイルのオプション変更などを再起動なく反映されることができる(ま、リロードでは反映できない項目もあったような?)。

    サーバプロセスのクラッシュ(セグメンテーションフォルトなど)と影響範囲


  • 重要な用語、コマンド、パラメータなど:
    statement_timeout

    指定されたミリ秒数かかったすべての命令文をtimeoutにする
    5000(5秒)にした場合、5秒以上かかるクエリは無効

    lock_timeout

    ロック獲得の試行中に指定されたミリ秒を超えたらタイムアウト。★この設定はロック待機時のみに適用される

    idle_in_transaction_session_timeout

    トランザクションが指定されたミリ秒をこえてアイドルだった場合にセッションを終了する。ロックは解放され、コネクション数も再利用されるようになる。VACUUM可能

    スタンバイでの問い合わせのコンフリクト(衝突)

    ・プライマリでアクセス排他ロックされた際、スタンバイのテーブルアクセスとコンフリクト
    ・プライマリでのテーブル削除
    ・プライマリでのデータベースの削除
    等…

    hot_standby_feedback

    ホットスタンバイがスタンバイサーバ上で現在処理を行っている問い合わせについて、プライマリーにフィードバックを送るか否かを指定。プライマリの負荷になることもある

    vacuum_defer_cleanup_age

    VACUUMおよびHOT更新が不必要となった行バージョンの後片付けをどれくらい遅らせるかをトランザクション数で指定する。デフォルトの0だと可能な限り早く消える。

    max_standby_archive_delay★ver2では[削除]のようです


    max_standby_streaming_delay★ver2では障害対応に関する左記コマンドは[削除]のようです


    fsync

    fsync (boolean)
    onにした場合、fsync()システム・コールを発行したりなどの方法で更新が物理的にディスクに書き込まれたかの確証を得ようと試みる。OSやHWのクラッシュ後にDBを一貫した状態に復旧させることを保証する。

    synchronous_commit

    synchronous_commit (enum)
    アプリケーションの応答をWALレコードがディスク上に書き込まれるまで待つかどうか

    restart_after_crash

    restart_after_crash (boolean)
    真の場合、バックエンドのクラッシュの後、自動的に再初期化を行う

    pg_cancel_backend()

    pg_cancel_backend(pid int)
    バックエンドの現在の問い合わせを取り消すコマンド。
    同ロールのユーザ同士、あるいはスーパユーザから他のユーザへ発行可能。
    通常ユーザAのselectをスーパユーザがキャンセルしたりできる。取り消された側のユーザ画面には、キャンセルの旨表示される。
    ERROR: canceling statement due to user request
    SQLは蹴られるが、再接続は不要。

    pg_terminate_backend()

    pg_terminate_backend(pid int)
    バックエンドを終結させるコマンド。…終結て?と思うが、要はとあるユーザの接続を切断できるようだ。
    対象の接続のpidは把握しておかなくてはいけない。
    select * from pg_stat_activity;

    pg_ctl kill

    指定したプロセスにシグナルを送信する(組み込みのkillコマンドのないWindowsで有用)

    max_locks_per_transaction

    同時にロック可能なデータベース オブジェクトの数を指定

    max_files_per_process

    サーバ子プロセスが同時にオープンできるファイル数の最大値


破損クラスタ復旧 【重要度:2】

  • 説明:
    データファイルやトランザクションログファイルが破損した場合について、エラーメッセージの内容から原因を特定し、適切な対応ができるかを問う
  • 主要な知識範囲:
    トランザクションログ復旧


    システムテーブルのインデックス復旧


    開発者向けオプション


    テーブル・インデックスの実ファイルとパス

    テーブルとインデックスには1つにつき1つの物理ファイルが割り当てられる。
    その物理ファイルとパスの追い方
    1.DBのIDの確認 → [データベースid]
    SELECT datid, datname FROM pg_stat_database;
    2.テーブル等のファイル名の確認 → [ファイル名]
    SELECT relid, relname FROM pg_stat_all_tables;
    ↓物理パスはこのように…
    PGDATA/base/[データベースid]/[ファイル名]

    Relfilenode と OID


    インデックス破損とREINDEXによる復旧

    REINDEX…インデックスのテーブルに保存されたデータを使用してインデックスを再構築する。上二つよりはあまり使われないか。保守コマンド。

    チェックサムによる破損検知と復旧

    ・バックアップがあればそこから復旧
    ・なければ、zero_damaged_pagesパラメーターを有効にして破損データを含むブロックをゼロ埋めする(!?)。破損ブロックのデータは消失するが、テーブルの参照は可能になる。

    トランザクションIDの周回エラー

  • 重要な用語、コマンド、パラメータなど:
    PITR

    PITRはポイント・イン・タイム・リカバリ (アーカイブ・リカバリ)の略。

    pg_resetwal

    WALや制御情報の一部を初期化する。これらのデータが破損し、サーバを起動できないときの最後の手段として。

    ignore_system_indexes

    ignore_system_indexes (boolean)
    システムテーブル読み込み時にシステムインデックスを無視する。破損したインデックスをいったん無視した後復旧したい時などに有用。

    ignore_checksum_failure

    通常チェックサム障害が検出されると、エラーを報告してトランザクションを停止するが、onにしておくと警告のみで処理を継続する。障害を受けていないタプルの回収などは可能になるが、破損の隠蔽・また他に波及しかねないのでoffのほうが望ましいっぽい。

    コミットログ(pg_xact)

    すべてのトランザクションのコミット状態を保持するコミットログファイル。
    もとはディレクトリがpg_clogだったが変更された

    シングルユーザモード

    シングルユーザモードでは、セッションユーザはユーザID 1と設定され、暗黙的にスーパーユーザの能力がこのユーザに与えられる。デバッグには向かない。手動での復旧時などに使用することができる。

    VACUUM FREEZE

    積極的にタプルの”凍結”を選択します。 FREEZE指定は、vacuum_freeze_min_ageパラメータをゼロとしてVACUUMを実行することと同じ。FrozenXIDが与えられたトランザクションは古いものという認識となる。


ホット・スタンバイ復旧 【重要度:1】

  • 説明:
    レプリケーション構成でプライマリ側やスタンバイ側のPostgreSQLが停止・故障した場合について、適切な対応ができるかを問う
  • 主要な知識範囲:
    ストリーミングレプリケーションとロジカルレプリケーション

    物理レプリケーション(まるっとのみ、完全複製)と、論理レプリケーション(一部だけとか可能、ほかのDBからのものも可能、更新可能と柔軟性は高いがその分扱いづらそう)

    グファイル内のエラーメッセージ


    スタンバイへ伝搬される処理とされない処理

    現在ハッシュインデックスに対する操作はWALに記録されない。

    プライマリ側PostgreSQLの停止・故障と再開(再起動)の方法

    pg_ctl promoteにてスタンバイをプライマリに昇格させるべし(自動フェイルオーバーは別のツールに寄ることになる)。
    たいていの場合、旧プライマリはスタンバイとして戻すことにするのかな?
    pg_rewindなどを使って同期していく。

    スタンバイ側PostgreSQLの停止・故障と再開(再起動)の方法

    プライマリ側が元気なら、スタンバイ機器の故障等から復旧後、正しくDBを起動すればよいのかと思われる。もしディスクの破損などでWAL等が失われている状況なら、それはきちんと準備することとして。

    ロジカルレプリケーションのサブスクライバ―へ伝搬される処理とされない処理

    (同じ項目あったな?)変更された行の情報が伝搬される。
    ・TRUNCATEコマンドやDDLはレプリケーションされない。
    ・シーケンス、ラージオブジェクトや通常のテーブル以外(インデックス、ビュー、マテリアライズド・ビューやパーティションテーブルの親テーブルなど)もレプリケーションされない。
    ・★レプリケーションは基底テーブルから基底テーブルのみ。

    ロジカルレプリケーションのサブスクライバ―でのコンフリクト

    ロジカルレプリケーションの場合、サブスクライバー側で参照のみではなく更新にあたるcreateやinsertが実行できるため、もしかするとパブリケーションからのデータと一意制約違反などを起こす可能性があるかも。

  • 重要な用語、コマンド、パラメータなど:
    pg_ctl promote

    スタンバイをプライマリに昇格させるためのコマンド。即座に昇格

    pg_receivewal

    マスタに接続してWALの受信と書き込みができるクライアントツール
    DB機能はない、WALのreceiveのみリアルタイムなWALのアーカイブなどに利用できる

    pg_rewind

    クラスタのタイムラインが分岐した後、クラスタをその複製のクラスタに同期するためのツール。スタンバイへのフェイルオーバ後の旧マスタサーバのオンラインへの戻しなどに使われる。
    旧マスタの再組み込み時にDBデータを差分バックアップ転送できる

セミナー資料の確認

用語をざっくり確認しおえたら、無料セミナー資料をすべて確認します。
数は多いようですがまあそこまで大きいものじゃないのでざっと見ていきます。

親ページはこちら(中盤の「OSS-DB Exam Gold」から下の資料)

参考資料ダウンロード|DBスペシャリストを認定する資格 OSS-DB技術者認定試験
「OSS-DB技術者認定資格」は、Postgresqlをはじめとするオープンソースデータベースのスペシャリストを認定する資格です。DBスペシャリストの認定を受けたい方や、SQLやデータベースのしくみを学びたいにもおすすめです。

各リンクはこちら

https://oss-db.jp/__/download/5d64984f211994306a003138/20190907-gold-01.pdf

https://oss-db.jp/__/download/5c6ba0675b3a4308b90075bb/20181202-gold-01.pdf

https://subdomain18111320879.hmup.jp/__/download/5c24dde0f875dd167a00161b/20170930-gold-01.pdf

https://subdomain18111320879.hmup.jp/__/download/5c24dddf979afc5ec6002f5d/20170319-gold-01.pdf

https://subdomain18111320879.hmup.jp/__/download/5c24dddf5b3a437d0e0059f3/20160806-gold-01.pdf

https://subdomain18111320879.hmup.jp/__/download/5c24ddde7f0ebd4b64001223/20160327-01.pdf

https://subdomain18111320879.hmup.jp/__/download/5c24dde1f875dd167a00161d/20150905-01.pdf

https://subdomain18111320879.hmup.jp/__/download/5c24dde0979afc5ec6002f5f/20150222-01.pdf

https://subdomain18111320879.hmup.jp/__/download/5c24dde05b3a437d0e0059f5/20141111-01-report-01.pdf

https://subdomain18111320879.hmup.jp/__/download/5c24e39e979afc55020078e0/20140920-01.pdf

https://subdomain18111320879.hmup.jp/__/download/5c24e39e5b3a437d0e005d7d/20140524-01.pdf

https://subdomain18111320879.hmup.jp/__/download/5c24e39d7f0ebd2de6007cf7/20130615-01.pdf

https://subdomain18111320879.hmup.jp/__/download/5c24e39df875dd167a001826/20130119-01.pdf

https://subdomain18111320879.hmup.jp/__/download/5c24e39d979afc55d8007702/20120818-01.pdf

https://subdomain18111320879.hmup.jp/__/download/5c24e39b5b3a437840007b27/20120121-01.pdf

サンプル問題の確認

公式サイトにサンプル問題があります。量はさほどありませんが、解説も付いているのですべて頭に入れておきましょう。

運用管理[17問]

Goldの例題解説「運用管理」
「OSS-DB技術者認定資格」は、Postgresqlをはじめとするオープンソースデータベースのスペシャリストを認定する資格です。DBスペシャリストの認定を受けたい方や、SQLやデータベースのしくみを学びたいにもおすすめです。
002_Goldの例題解説「運用管理」
「OSS-DB技術者認定資格」は、Postgresqlをはじめとするオープンソースデータベースのスペシャリストを認定する資格です。DBスペシャリストの認定を受けたい方や、SQLやデータベースのしくみを学びたいにもおすすめです。

性能監視[15問]

Goldの例題解説「性能監視」|サンプル問題/例題解説|受験対策|DBスペシャリストを認定する資格 OSS-DB技術者認定試験
OSS-DB技術者認定資格」は、Postgresqlをはじめとするオープンソースデータベースのスペシャリストを認定する資格です。DBスペシャリストの認定を受けたい方や、SQLやデータベースのしくみを学びたいにもおすすめです。

パフォーマンスチューニング[13問]

Goldの例題解説「パフォーマンスチューニング」|サンプル問題/例題解説|受験対策|DBスペシャリストを認定する資格 OSS-DB技術者認定試験
OSS-DB技術者認定資格」は、Postgresqlをはじめとするオープンソースデータベースのスペシャリストを認定する資格です。DBスペシャリストの認定を受けたい方や、SQLやデータベースのしくみを学びたいにもおすすめです。

障害対応[8問]

Goldの例題解説「障害対応」|サンプル問題/例題解説|受験対策|DBスペシャリストを認定する資格 OSS-DB技術者認定試験
OSS-DB技術者認定資格」は、Postgresqlをはじめとするオープンソースデータベースのスペシャリストを認定する資格です。DBスペシャリストの認定を受けたい方や、SQLやデータベースのしくみを学びたいにもおすすめです。

ver1とver2の差分の確認

OSSDBの試験には、ver1とver2があります(pdfによると、ver1は2019年9月30日以降は受験できなくなっているらしい?)。

既存の[公式教本やistudy]のみでは、ver2部分がカバーできなかったりするようなので、この差分については別途確認する必要アリ。これは…教本更新してほしいですね(特に高いistudy)。

https://lpi.or.jp/ossdb/docs/OSS-DB_V2.pdf

結構差分が多くて……。

上記pdfによると、代表的な追加事項は以下です。

・最新の PostgreSQL11 にまで対応
・データベースの大規模化、高信頼化を支える機能に対応
– ロジカルレプリケーション
– 宣言的パーティショニング
– パラレルクエリ改善
差分分メモ。若干ですが、[削除]になっているコマンドがあるのでそこは覚えなくてよさそう。
逆に、「追加・変更・修正」等は頭に入れておかないとです。
運用管理
データベースサーバ構築【重要度:2】
・重要な用語、コマンド、パラメータなど:
[追加] ssl
[追加] pg_stat_ssl
[追加] pgcrypto
[変更] 「pg_clog」⇒「pg_xact」
運用管理用コマンド全般【重要度:4】
・主要な知識範囲:
[追加] チェックポイント
・重要な用語、コマンド、パラメータなど:
[追加] CHECKPOINT
[追加] pg_start_backup()
[追加] pg_stop_backup()
データベースの構造【重要度:2】
[変更なし]
ホット・スタンバイ運用【重要度:1】
・主要な知識範囲:
[追加] パブリケーションとサブスクリプションの定義
[変更] 「レプリケーション機能の概要」
⇒「ストリーミングレプリケーション機能とロジカルレプリケーション機能の概要」
⇒「同期レプリケーションと非同期レプリケーション」
・重要な用語、コマンド、パラメータなど:
[追加] wal_level
[追加] max_wal_senders
[追加] wal_sender_timeout
[追加] wal_receiver_timeout
[追加] synchronous_standby_names
[追加] synchronous_commit
[追加] max_logical_replication_workers
[追加] CREATE/ALTER/DROP PUBLICATION/SUBSCRIPTION
[追加] pg_stat_replication
[追加] pg_stat_wal_receiver
[追加] スタンバイでの問い合わせのコンフリクト(衝突)
[追加] hot_standby_feedback
[追加] スタンバイで実行可能な問い合わせ
[追加] ロジカルレプリケーションのサブスクライバーへ伝搬される処理とされない
処理
[変更] 「pg_xlog_replay_pause()」⇒「pg_wal_replay_pause()」
[変更]「pg_xlog_replay_resume()」⇒「pg_wal_replay_resume()」
[変更]「wal_sender」⇒「walsender プロセス」
[変更]「wal_receiver」⇒「walreceiver プロセス」
[変更]「pg_receivexlog」⇒「pg_receivewal」
[削除] wal_writer_delay
[削除] commit_dalay
性能監視
アクセス統計情報【重要度:3】
・重要な用語、コマンド、パラメータなど:
[追加] 待機イベント(pg_stat_activity.wait_event)
[追加] pg_stat_progress_vacuum
テーブル / カラム統計情報【重要度:2】
・重要な用語、コマンド、パラメータなど:
[削除] pg_stat_replication
[削除] pg_stat_functions
クエリ実行計画【重要度:3】
・主要な知識範囲:
[追加] パーティションに対する SQL の実行計画
[追加] パラレルクエリに対応する実行計画
[追加] ウィンドウ関数(row_number/rank など)の SQL に対応する実行計画
[誤字修正] 「集約関数(sum/count)を伴う SQL に対応する実行契約」
⇒「集約関数(sum/count)を伴う SQL に対応する実行計画」
・重要な用語、コマンド、パラメータなど:
[追加] EXPLAIN / EXPLAIN ANALYZE
[削除] VACUUM FULL
その他の性能監視【重要度:1】
・重要な用語、コマンド、パラメータなど:
[追加] log_autovacuum_min_duration
[追加] log_lock_waits
[追加] log_checkpoints
[追加] log_temp_files
パフォーマンスチューニング
性能に関係するパラメータ【重要度:4】
・主要な知識範囲:
[削除] Index Only Scan と Visibility Map
・重要な用語、コマンド、パラメータなど:
[追加] huge_pages
[追加] maintenance_work_mem
[追加] autovacuum_work_mem
[追加] wal_level
[追加] checkpoint_timeout
[追加] checkpoint_completion_target
チューニングの実施【重要度:2】
・主要な知識範囲:
[追加] テーブル構成のチューニング
障害対応
起こりうる障害のパターン【重要度:3】
・重要な用語、コマンド、パラメータなど:
[追加] statement_timeout
[追加] lock_timeout
[追加] idle_in_transaction_session_timeout
[追加]スタンバイでの問い合わせのコンフリクト(衝突)
[追加] hot_standby_feedback
[追加] synchronous_commit
[追加] restart_after_crash
[削除] vacuum_cost_delay
[削除] autovacuum_vacuum_cost_delay
破損クラスタ復旧【重要度:2】
・主要な知識範囲:
[追加] トランザクション ID の周回エラー
・重要な用語、コマンド、パラメータなど:
[追加] PITR
[追加] VACUUM FREEZE
[変更] 「pg_resetxlog」⇒「pg_resetwal」
[変更] 「コミットログ(pg_clog)」⇒「コミットログ(pg_xact)」
ホット・スタンバイ復旧【重要度:1】
・主要な知識範囲:
[追加] ロジカルレプリケーションのサブスクライバーへ伝搬される処理とされない
処理
[追加] ロジカルレプリケーションのサブスクライバーでのコンフリクト
[変更] 「レプリケーション」
⇒「ストリーミングレプリケーションとロジカルレプリケーション」
重要な用語、コマンド、パラメータなどに以下を削除
[追加] pg_rewind
[変更] 「pg_receivexlog」⇒「pg_receivewal」
[削除] max_standby_archive_delay
[削除] max_standby_streaming_delay
[削除] vacuum_cost_delay
[削除] autovacuum_vacuum_cost_delay
[削除] vacuum_defer_cleanup_age

コメント

タイトルとURLをコピーしました