Perlハッカーのための遊べるPostgreSQL
メモメモ
PostgreSQL 8.4
HOT について
詳しい内容については、ポータルサイトを参考に。
PostgreSQL では DB を1つのファイルで管理しており、ファイルの中では各テーブルごとに"ページ"という単位に分割し管理している。
PostgreSQL は追記型の DB であり、Update や Insert すると、ページの後ろの方へと新たに書き込み、必要に応じて Index を張り直す。
つまり、Update の際には、新しくレコードをページに追加し、古いレコードから新しいレコードを参照するように変更しているため、古いレコードを削除、すなわち VACUUM が必要になる。
そこで HOT。
HOTでは、ページが何%以上いっぱいになったら、次のページに移動するか (作成するか) を決めることができる。
作成方法はこんな感じ。
CREATE TABLE ( ... ) WITH (FILLFACTOR=90); --- OR ALTER TABLE ... SET (FILLFACTOR=90);
この場合は 90% まで書き込んだら次のページへと移動するよう命令している。
Index を更新しなくてもよいケースでは、未使用の部分 (残りの10%) を使って更新をすることができる。
未使用レコードに対してはガベージコレクションを随時実行することで VACCUM の必要性が減少している。
旧バージョンでは VACUUM していいかは全レコード検索して確認しないと分からなかったが、 HOT を使うことで VACUUM のコストも低下。
ただ、アクセスするページの数が増えてしまうので、やたらと増やせばいいってもんじゃない。
増やすとスピードが落ちる。一般的には90%くらい?
その辺はシステムと相談して決定する必要があるよ。
実行計画について
EXPLAIN / EXPLAIN ANALYSE を使って、Index が使われているか / 実際にはどのくらいの時間を要しているのかを確認しよう。
遅いクエリを知る
- 統計情報ビュー (8.4〜)
有効に使うには Prepared Statement を使うなどをしないと、別々の統計としてカウントされてしまうので注意が必要。
- サーバログ
- log_min_duration_statement
- 指定した時間を超えたクエリをはき出す
- 0 を指定すると全クエリがでてくる
- 8.4 以降だと contrib/auto_expllain を使えば実行計画(EXPLAIN した結果)もログに出せる
- log_min_duration_statement
はまりがちなところ
- locale
- OS由来の locale のほか、DB 個々のエンコーディングの指定が存在している
- 矛盾があるとソートなどが正しく行えない
- C Locale で初期化しないと複数の Locale を扱うことができなかった
- initdb --no-locale
- ここの DB に対しては設定できない
- OS由来の locale のほか、DB 個々のエンコーディングの指定が存在している
8.4 以降では DB 個々に locale が指定できるようになった
Perl から PostgreSQL を拡張する
PL/Perl という言語がサポートされている。
PL/Perl での関数内から SQL を実行できる。ただし不完全。
トリガをPerlで記述できる。
SQL 内に Perl を書いているかんじ。
つかいどころ
- カラムの内容に対して演算や変換をした結果でテーブルを検索する
- インデックスも大事だよ
- 大人の事情でアプリ側が改修出来ない場合、DB側で対応してしまう
- 他のDBで使っているような関数が欲しい場合
- そこに PL/Perl があるから
2つの PL/Perl
DB に組み込む場合は2通りのやりかたがある。
template1 に対して行う (全体に適用)
個々の DB に対して行う
CREATE LANGUAGE plperl(u)
そのた
11/20, 21 に PostgreSQL Conference 2009
pgpool など、クラスタリングについての話もあるよ