 |
技術評論社
発売日 : 2014-09-04
|
PostgreSQLを利用するにあたって、
MWとして見たときに必要なことが記さされていると感じました。
私自身はSQLServerをずっと利用してきて、
PostgreSQLを触り始めたところなので、
知識を移行するには非常に有用な本でした。
アプリケーションエンジニアがDBについて考え出したときに、
読むとちょうどいいぐらいの本な気がしました。
（以下抜粋。○：完全抜粋、●：簡略抜粋）
●text型は最大で1GBまで格納可能です。
char型は末尾を空白で埋めるなら意味がありますが、それ以外の意味はありません。
varcharはtext型とほぼ同じですが、格納時のサイズ上限チェックが行われるため、
ごくわずにtext型よりも遅くなります。（通常、無視できる程度の差です）。（P.80）
○暗黙のB-treeインデックスは主キーだけでなく、
一意性制約が定義された列にも作成されます（P.90）
●TOAST（The Oversized-Attribute Storage Techinique）（P.94-95）
行のサイズが2KBを超えると、業のサイズが2KBより小さくなるまで、
列の値を圧縮したり、行内とは別の領域に列の値を移動しようとします。
●ページ数の計算方法（P.102-103）
RN：想定レコード数
FF：FILLFACTOR
TS：行の平均サイズ
PS：ページサイズ（8192kb固定）
PH：ページヘッダー（24kb固定）
ページ数＝RN / ((PS * FF - PH) / TS)
●インデックスアクセスは、シーケンシャルアクセスと比較すると、
ファイル先頭から各ページを読む必要はないため、
テーブルサイズによる性能への影響は低くなりますが、
代わりにランダムアクセスが頻発することになります。（P.105）
●HOT（Heap On Tupple）（P.108）
・UPDATE時のインデックスエントリの追加処理をスキップする
・VACUUM処理を待つことなく不要領域の再利用を可能にする
・ただしインデックスを持たない列への更新時など、条件あり
●FILLFACTOR（P.110-111）
・更新、削除がない場合（挿入、検索のみ）であれば100%の設定でOK
・更新に対しては、空き領域が二行分のサイズになるようにする。
そうすると一度に二つの更新がこない限り、空き領域を交互に使ってくれる。
（新規ページを作成しない）
・下限は一般的に70%とされる
○部分インデックスは、特定の条件を満たす行の値のみをインデックスの対象とし、列値の分布に偏りがある場合に有効です。（P.118）
●稼動統計情報ビュー（P.133、P.201-203）
・pg_stat_database：コミット・ロールバック数、キャッシュヒット率、デッドロック発生有無
・pg_stat_user_tables、pg_statio_user_indexes：キャッシュヒット率
・pg_stat_activity：トランザクションの実行時間、実行中のクエリ
・pg_locks：ロック待ちプロセス
○クライアントからの要求を1つのプロセスが処理する
プロセスモデルのアーキテクチャを採用しています。（P.138）
○PostgreSQL9.2以降では、
少なくとも64コアのサーバまでCPUスケールすることが確認されています。（P.138）
●I/Oスケジューラ（P.146）
実際のI/O要求は、bgwriter（データ書き込みプロセス）、
wal writer（WAL書き込みプロセス）が占めるため
deadlineに設定することが推奨されてきました。
RAIDドライバに任せたほうが効率的な場合、
またSSDのようなランダムI/Oに強いデバイスの場合、
noopを設定することも選択肢となります。
○チェックポイントが発生する前にバックグラウンドライタが
ダーティバッファ（更新のあったデータ）の書き込みを行うことで、
I/O負荷を標準化できるため、データベース全体の性能を維持できます。
ただし、設定値を大きくするとリカバリに掛かる時間が延びるため、
リカバリ時間の見積もりを勘案したした設定値に調整することが必要になります。（P.149）
●正常動作の監視として、
OSのvmstat、netstat、iostat、sarなどのコマンドを利用します。（P.196-199）
●断片化（P.216）
・インデックスファイルの大部分で断片化が起こると、
キャッシュヒット効率が悪化し、性能に悪影響をおよぼします。
・断片化を調べる方法
contribモジュールのpgstattupleに含まれるpgstatindex関数が使えます。
●INDEXの作成方法（P.220）
・REINDEXを実行すると対象テーブルはロック状態になります
・CREATE INDEX CONCURRENTLY は一時的に別ノンデックスを作成するので、
ロックを取得しません。
●推定の基準となるコストは、random_page_costとseq_page_costで設定されています。
デフォルトの設定ではHDDを前提としてrandom_page_costが4.0に対して、
seq_page_costが1.0で計算されています。
（中略）
SSDを用いる場合はディスク特性がHDDとは異なることも考慮します。（P.227）
○SSDは、シーケンシャルアクセスとランダムアクセスの性能差がほとんどないため
コスト比も1:1に近づけることが必要になります。（P.265）
●収集した統計情報はシステムカタログ（pg_satistic）に格納されます。
（中略）
テーブルの内の最頻値リスト（most_common_vals）やその出現頻度（most_common_freqs）、
値の物理的な並び順と論理的な並び順の一致度合い（correlation）などを
参照することができます。（P.235）
●結合（P.245）
・入れ子ループ結合 ＜ ハッシュ結合 ＜ マージ結合
・ハッシュ結合は小さいテーブルと大きいテーブルの結合が得意
・マージ結合はソートしておいて結合する
○メモリ上で実行できる場合にはクックソートを用いますが、
それ以上のサイズになるとソート結果をファイルに書き出す外部ソートになります。（P.247）
MWとして見たときに必要なことが記さされていると感じました。
私自身はSQLServerをずっと利用してきて、
PostgreSQLを触り始めたところなので、
知識を移行するには非常に有用な本でした。
アプリケーションエンジニアがDBについて考え出したときに、
読むとちょうどいいぐらいの本な気がしました。
（以下抜粋。○：完全抜粋、●：簡略抜粋）
●text型は最大で1GBまで格納可能です。
char型は末尾を空白で埋めるなら意味がありますが、それ以外の意味はありません。
varcharはtext型とほぼ同じですが、格納時のサイズ上限チェックが行われるため、
ごくわずにtext型よりも遅くなります。（通常、無視できる程度の差です）。（P.80）
○暗黙のB-treeインデックスは主キーだけでなく、
一意性制約が定義された列にも作成されます（P.90）
●TOAST（The Oversized-Attribute Storage Techinique）（P.94-95）
行のサイズが2KBを超えると、業のサイズが2KBより小さくなるまで、
列の値を圧縮したり、行内とは別の領域に列の値を移動しようとします。
●ページ数の計算方法（P.102-103）
RN：想定レコード数
FF：FILLFACTOR
TS：行の平均サイズ
PS：ページサイズ（8192kb固定）
PH：ページヘッダー（24kb固定）
ページ数＝RN / ((PS * FF - PH) / TS)
●インデックスアクセスは、シーケンシャルアクセスと比較すると、
ファイル先頭から各ページを読む必要はないため、
テーブルサイズによる性能への影響は低くなりますが、
代わりにランダムアクセスが頻発することになります。（P.105）
●HOT（Heap On Tupple）（P.108）
・UPDATE時のインデックスエントリの追加処理をスキップする
・VACUUM処理を待つことなく不要領域の再利用を可能にする
・ただしインデックスを持たない列への更新時など、条件あり
●FILLFACTOR（P.110-111）
・更新、削除がない場合（挿入、検索のみ）であれば100%の設定でOK
・更新に対しては、空き領域が二行分のサイズになるようにする。
そうすると一度に二つの更新がこない限り、空き領域を交互に使ってくれる。
（新規ページを作成しない）
・下限は一般的に70%とされる
○部分インデックスは、特定の条件を満たす行の値のみをインデックスの対象とし、列値の分布に偏りがある場合に有効です。（P.118）
●稼動統計情報ビュー（P.133、P.201-203）
・pg_stat_database：コミット・ロールバック数、キャッシュヒット率、デッドロック発生有無
・pg_stat_user_tables、pg_statio_user_indexes：キャッシュヒット率
・pg_stat_activity：トランザクションの実行時間、実行中のクエリ
・pg_locks：ロック待ちプロセス
○クライアントからの要求を1つのプロセスが処理する
プロセスモデルのアーキテクチャを採用しています。（P.138）
○PostgreSQL9.2以降では、
少なくとも64コアのサーバまでCPUスケールすることが確認されています。（P.138）
●I/Oスケジューラ（P.146）
実際のI/O要求は、bgwriter（データ書き込みプロセス）、
wal writer（WAL書き込みプロセス）が占めるため
deadlineに設定することが推奨されてきました。
RAIDドライバに任せたほうが効率的な場合、
またSSDのようなランダムI/Oに強いデバイスの場合、
noopを設定することも選択肢となります。
○チェックポイントが発生する前にバックグラウンドライタが
ダーティバッファ（更新のあったデータ）の書き込みを行うことで、
I/O負荷を標準化できるため、データベース全体の性能を維持できます。
ただし、設定値を大きくするとリカバリに掛かる時間が延びるため、
リカバリ時間の見積もりを勘案したした設定値に調整することが必要になります。（P.149）
●正常動作の監視として、
OSのvmstat、netstat、iostat、sarなどのコマンドを利用します。（P.196-199）
●断片化（P.216）
・インデックスファイルの大部分で断片化が起こると、
キャッシュヒット効率が悪化し、性能に悪影響をおよぼします。
・断片化を調べる方法
contribモジュールのpgstattupleに含まれるpgstatindex関数が使えます。
●INDEXの作成方法（P.220）
・REINDEXを実行すると対象テーブルはロック状態になります
・CREATE INDEX CONCURRENTLY は一時的に別ノンデックスを作成するので、
ロックを取得しません。
●推定の基準となるコストは、random_page_costとseq_page_costで設定されています。
デフォルトの設定ではHDDを前提としてrandom_page_costが4.0に対して、
seq_page_costが1.0で計算されています。
（中略）
SSDを用いる場合はディスク特性がHDDとは異なることも考慮します。（P.227）
○SSDは、シーケンシャルアクセスとランダムアクセスの性能差がほとんどないため
コスト比も1:1に近づけることが必要になります。（P.265）
●収集した統計情報はシステムカタログ（pg_satistic）に格納されます。
（中略）
テーブルの内の最頻値リスト（most_common_vals）やその出現頻度（most_common_freqs）、
値の物理的な並び順と論理的な並び順の一致度合い（correlation）などを
参照することができます。（P.235）
●結合（P.245）
・入れ子ループ結合 ＜ ハッシュ結合 ＜ マージ結合
・ハッシュ結合は小さいテーブルと大きいテーブルの結合が得意
・マージ結合はソートしておいて結合する
○メモリ上で実行できる場合にはクックソートを用いますが、
それ以上のサイズになるとソート結果をファイルに書き出す外部ソートになります。（P.247）
コメント
コメントを投稿