はちゅにっき

こっちのブログはまったり更新

Perlハッカーのための遊べるPostgreSQL

メモメモ

PostgreSQL の変遷

  • PostgreSQL 8.3 で性能が大きくアップ
  • HOT という機能

などなど地道にVerUpしてきている

PostgreSQL 8.4

  • SQL
    • 再帰SQLのサポート
    • 8.2以前との互換性について改善
      • 暗黙のキャストに対する改善
    • 移植性の改善
  • 大規模対応
    • パーティショニング
    • ディスクの先読み etc...
  • 運用管理

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 した結果)もログに出せる

はまりがちなところ

  • locale
    • OS由来の locale のほか、DB 個々のエンコーディングの指定が存在している
      • 矛盾があるとソートなどが正しく行えない
    • C Locale で初期化しないと複数の Locale を扱うことができなかった
      • initdb --no-locale
      • ここの DB に対しては設定できない

8.4 以降では DB 個々に locale が指定できるようになった

Perl から PostgreSQL を拡張する

PL/Perl という言語がサポートされている。
PL/Perl での関数内から SQL を実行できる。ただし不完全。
トリガをPerlで記述できる。
SQL 内に Perl を書いているかんじ。

つかいどころ

  • カラムの内容に対して演算や変換をした結果でテーブルを検索する
    • インデックスも大事だよ
  • 大人の事情でアプリ側が改修出来ない場合、DB側で対応してしまう
  • 他のDBで使っているような関数が欲しい場合
  • そこに PL/Perl があるから

2つの PL/Perl

  • plperl = 信頼された PL/Perl
  • plperlu = 信頼されない PL/Perl
    • なんでもあり

DB に組み込む場合は2通りのやりかたがある。
template1 に対して行う (全体に適用)
個々の DB に対して行う

CREATE LANGUAGE plperl(u)

そのた

11/20, 21 に PostgreSQL Conference 2009
pgpool など、クラスタリングについての話もあるよ