Selfkleptomaniac

ディスクの空き容量がヤバいならVACUUM FULLすればいいじゃない

という考え方はPostgreSQL 9からは通用しなくなるようですね。

VACUUM FULL の高速化

VACUUM FULL のアルゴリズムが改められ、ゴミが多い場合の処理が大幅に高速化されました。一方、ゴミが少ない場合にはむしろ遅くなっているので、「毎晩 vacuumdb –full」するような運用は改める必要があります。また、処理中に元のテーブルと同じサイズの一時ディスク領域が必要になったため、ディスクフル間近の場合は実行できなくなりました。

以前はそんな感じの運用をやっていたので、PostgreSQL 9のこの変更はじわじわきました。HDDの価格は確かにどんどん安くなってきてはいるのですが、高価なSANストレージに永続的に保存したいデータを集約しているような環境を構築してしまったところでは、一般的なHDDの低価格化の恩恵も受けられないので涙ぐましい努力を強いられることでしょう。

PostgreSQL8.3の自動キャスト

以前のエントリでPostgreSQL8.3への移行は型キャストが自動的に行われないため結構大変だと書いたが、PostgreSQL8.3にはCREATE CASTという新しいキャストの定義を記述できる機能がある。Let’s Postgreｓの記事で知った。風説の流布もいいところだった。以下の内容はLet’s Postgresの記事にtimestampを追加しただけ。まったくもって無知の涙である。

以前サンプルにしていたデータでやってみる。

db_test=# SELECT * FROM test_table;            
      datetime       
---------------------
 2008-06-30 01:23:45
(1 row)

timestamp型のデータが格納されたテーブルがある。PostgreSQL7.4なら、

db_test=# SELECT SUBSTR(datetime, 1, 4) FROM test_table;
 substr
--------
 2008
(1 row)

こんな風にtimestamp型のデータにSUBSTR関数を実行しても自動的にキャストされて処理されている。自動的にキャストしないPostgreSQL8.3でこのSQLを実行するとエラーになるのだが、以下の方法で回避できた。

まずtimestamp型の入出力関数を調べる。

db_test=# SELECT oid, typname, typinput, typoutput
     FROM pg_catalog.pg_type WHERE oid = 'timestamp'::regtype;
 oid  |  typname  |   typinput   |   typoutput   
------+-----------+--------------+---------------
 1114 | timestamp | timestamp_in | timestamp_out
(1 row)

textと違ってinやoutの前に「_」が入るようだ。

これを利用してtimestamp型のデータをtext型にキャストする関数を作成し、自動キャストのルールを作成してしまえばいい。

db_test=# CREATE FUNCTION timestamp2text(timestamp) RETURNS text AS 
'SELECT textin(timestamp_out($1))' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION
db_test=# CREATE CAST (timestamp AS text) 
WITH FUNCTION timestamp2text(timestamp) AS IMPLICIT;
CREATE CAST

これで準備完了。では試してみよう。

db_test=# SELECT SUBSTR(datetime, 1, 4) FROM test_table;
 substr 
--------
 2008
(1 row)

お見事。

ただし、インデックスの作成プランなど見直す箇所も多いだろうから、単純にこれでおしまいというわけではないけれども。

PostgreSQL8.1以降の透過的なパーティショニング

PostgreSQLのConstraint Exclusionの使い方がよくわからなかったのでまとめた。

基本的にはマスタテーブルを作成して、そこで型の指定をする。で、Constraint Exclusionを利用することで透過的に選択、更新処理のSQLを扱うことができる。

たとえば、購入履歴だとかダウンロード履歴、閲覧履歴など、何らかのログを残したい場合、データ量が膨大になることが予想されるので期間やユーザでデータを分割しておきたくなる。でも、アプリケーション側から分割されたテーブルにアクセスするのは何かと面倒だ。

そんな場合、継承元と継承先を透過的に扱うにはこんな風にする手がある。期間別テーブルの例は以下の通り：

まずはマスタとなるテーブルで方を指定して、そいつを継承した子テーブルを作成する。

--マスタテーブル（SERIALにはあまり意味はない）
CREATE TABLE master_table (
    id SERIAL, name VARCHAR(20),
    created_at TIMESTAMP WITHOUT TIME ZONE,
    updated_at TIMESTAMP WITHOUT TIME ZONE
);

--子テーブル（2009年1月のデータ用）
CREATE TABLE child_table_2009_01 
    (CHECK(created_at >= '2009-01-01 00:00:00' 
     AND created_at < '2009-02-01 00:00:00'))
 INHERITS (master_table);

--子テーブル（2009年2月のデータ用）
CREATE TABLE child_table_2009_02 
    (CHECK(created_at >= '2009-02-01 00:00:00' 
    AND created_at < '2009-03-01 00:00:00')) 
 INHERITS (master_table);

で、子テーブルに入るデータの制約を決める。

--2009年1月のデータしか入らない
CREATE RULE insert_child_table_2009_01 
    AS ON INSERT TO master_table 
    WHERE created_at >= '2009-01-01 00:00:00' 
    AND created_at < '2009-02-01 00:00:00' 
DO INSTEAD INSERT INTO child_table_2009_01 
    (name, created_at, updated_at) 
    VALUES 
    (NEW.name, NEW.created_at, NEW.updated_at);

--2009年2月のデータしか入らない
CREATE RULE insert_child_table_2009_02 
    AS ON INSERT TO master_table 
    WHERE created_at >= '2009-02-01 00:00:00' 
    AND created_at < '2009-03-01 00:00:00' 
 DO INSTEAD INSERT INTO child_table_2009_02 
    (name, created_at, updated_at) 
    VALUES 
    (NEW.name, NEW.created_at, NEW.updated_at);

とりあえず1月と2月のデータがあればいいので、こんな感じのテストデータを用意。

$ cat insert.sh 
#!/bin/sh

COUNT=0
INTERVAL=1000
MAX=3000000
BASETIME=`date --date "2009-01-01 09:00:00" "+%s"`

while test $COUNT -lt $MAX
do
        TIME=`expr $BASETIME + $COUNT`
        DATE=`date --date "1970-01-01 $TIME sec" "+%Y-%m-%d %T"`
        SQL="INSERT INTO master_table (name, created_at, updated_at) 
　　　　　　　　　　VALUES ('"$COUNT"', '"$DATE"', '"$DATE"');";
        echo $SQL
        COUNT=`expr $COUNT + $INTERVAL`
done

で、テスト用データベース（p_testにした）に流し込む。

$ ./insert.sh > $HOME/pgsql/test/insert.sql

$ head -1 $HOME/pgsql/test/insert.sql 
INSERT INTO master_table (name, created_at, updated_at) 
VALUES ('0', '2009-01-01 00:00:00', '2009-01-01 00:00:00');
$ tail -1 $HOME/pgsql/test/insert.sql 
INSERT INTO master_table (name, created_at, updated_at) 
VALUES ('2990000', '2009-02-04 14:33:20', '2009-02-04 14:33:
##テストだからまあいい。
$ psql -Upostgres -d p_test -f $HOME/pgsql/test/insert.sql

インデックスはそれぞれの子テーブルに作成しておく。

p_test=# CREATE INDEX idx_2009_01 ON child_table_2009_01(created_at);
p_test=# CREATE INDEX idx_2009_02 ON child_table_2009_02(created_at);
p_test=# CREATE INDEX idx_master_table ON master_table(created_at);

で、準備は完了。しかし、これだけだと透過的に扱えるといってもあまり効率はよくない。

p_test=# EXPLAIN ANALYZE 
SELECT * FROM master_table 
WHERE created_at BETWEEN '2009-01-01 00:00:00' 
AND '2009-01-15 00:00:00';

こんなクエリを実行すると、なぜかchild_table_2009_02まで検索されてしまう。

そこで

p_test=# SET constraint_exclusion TO on;
SET
p_test=# EXPLAIN ANALYZE 
SELECT * FROM master_table 
WHERE created_at BETWEEN '2009-01-01 00:00:00' 
AND '2009-01-15 00:00:00';

constraint_exclusionをセットすることでWHERE句から子テーブルの制約にひっかかるテーブルの検索がなくなる。

SELECTを発行するたびにconstraint_exclusionを設定するのはバカらしいので、postgresql.confを開き、

#constraint_exclusion = off

デフォルトでoffになっているconstraint_exclusionをonに変更する。

constraint_exclusion = on

これで本当にマスタテーブルと子テーブルを透過的に扱うことが出来るようになる。どれくらい透過的かというと、上のINSERTやSELECT文に加えて

p_test=# UPDATE master_table SET name = 'hoge' 
WHERE created_at >= '2009-01-01 00:00:00' 
AND created_at < '2009-02-01 00:00:00';

みたいなUPDATE文もみんなmaster_tableに向けて発行できる。

でも、まだまだ問題はある。たとえば、透過的に扱えるようになることでオーバーヘッドは増す。マニュアルによれば、

A rule has significantly more overhead than a trigger, but the overhead is paid once per query rather than once per row, so this method might be advantageous for bulk-insert situations. In most cases, however, the trigger method will offer better performance.

RULEによる処理ではクエリ毎に適用されるので一気に更新する場合はいいが、行毎の実行であれば都度トリガーを実行する方がオーバーヘッドは少ない。また、COPYによるデータ挿入はこのRULEを無視してしまう。また、RULEの記述ミスがあった場合、エラーにはならず、そのままマスターとなるテーブルにデータが追加されてしまう。

それから、CURRENT_DATEなどをWHERE句に使用するとクエリプランナはどのパーティションを使えばいいのか事前にわからないので避ける必要がある。またパーティションが多すぎる場合もクエリプランナによる最適化に時間がかかるためオーバーヘッドが大きくなるので、せいぜい100個程度にしておくべき、とのこと。

あと、ちょっと不思議なことに、relpagesからサイズを求めようとしてもmaster_tableのサイズが出ない。

$ vacuumdb -f -z -d p_test -U postgres
$ psql -p 5433 -Upostgres -d p_test
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

p_test=# select sum(relpages) * 8 * 1024 from pg_class 
where relname = 'master_table';
 ?column? 
----------
        0
(1 row)

でも子テーブルにはちゃんとデータがある。

p_test=# select sum(relpages) * 8 * 1024 from pg_class 
where relname = 'child_table_2009_01';
 ?column? 
----------
   155648
(1 row)

p_test=# select sum(relpages) * 8 * 1024 from pg_class 
where relname = 'child_table_2009_02';
 ?column? 
----------
    40960
(1 row)

このあたりはまだよくわかっていない…

PostgreSQLメモ

PostgreSQLのデータベース毎の容量調査方法を忘れていたのでメモ。統計情報が更新されていないと意味ないが。

psql -Upostgres -l -t -q | while read line
do
    DBNAME=`awk <<< $line '{print $1}'`
    if test -n "$DBNAME"&& test "$DBNAME" != "template0"
    then
        echo -n $DBNAME ":"
        psql -Upostgres -d $DBNAME -t -q -c "SELECT sum(relpages) * 8 * 1024 from pg_class"
    fi
done

PostgreSQLの長期間運用

PostgreSQLの運用では、システム停止をともなう定期的なメンテナンスが必要とされている。

が、じゃあいったいどれくらい、何のためにシステム停止とVACUUM処理をやらなければいけないのかは、いまいちはっきりしたところはわからなかった。たとえば、データ領域が大きくなりすぎてディスク容量を圧迫しているとか、なんとなくパフォーマンスが低下しているとか、そんなタイミングで実施するようなアバウトな運用をしている人も結構いるのではないか。

マニュアルにも明記されているが、上に挙げた理由の他に、PostgreSQLのVACUUMにはもうひとつ重要な機能がある。といっても、通常のVACUUMやVACUUM FULLでは実行されず、psqlから「VACUUM F」まで入力してtabを押しても出てこないのでやっかいなのだが、長期間運用されるPostgreSQLではVACUUM FREEZEを定期的に実行してやる必要がある。

VACUUMというのは、PostgreSQLが削除フラグを立てて未使用となったデータ領域を回収してあげるもので、ANALYZEはプランナが利用する統計情報を更新することで利用効率を向上させるものという理解で基本的には間違いないようなのだが、このFREEZEというのは正直なところこれまでその存在も全く知らなかった。今日、たまたま隣席で7.3系のPostgreSQLでVACUUM作業中の人がログに変なメッセージを見つけたのがきっかけで発見した。

同じような警告メッセージがPostgreSQL7.2のChangelogにある。

NOTICE:  Some databases have not been vacuumed in 1613770184 transactions.
        Better vacuum them within 533713463 transactions,
        or you may have a wraparound failure.

PostgreSQLのトランザクションには一意のID（XID）が振られていて、そのIDは32ビットのシステムでは40億くらい（2の32乗）が上限になっている。この上限値を超えると、トランザクションのIDはリセットされて0に戻ってしまう。数字が循環して0に戻ると、突然自分より前のトランザクションが存在しない状態になってしまい、逆に過去のトランザクションが未来のものに見えるようになるため、データ破壊を引き起こす。上のメッセージは、メンテナンス忘れなどでトランザクション数の累計が15億を超えるとVACUUM時に出てくるものだ。

VACUUM FREEZEはこの循環を回避するための機能で、現在のトランザクション以外のトランザクションにFrozenXIDというのを追加して、周回違いのIDを見間違わずに過去のものと判別できるようにする。何がなんだかわからないので、イメージしかわかないのだが、きっとPostgreSQLのトランザクションデータは構造体みたいなもので、そこにXIDというメンバ変数があって、そいつを更新してくれる、みたいなものなのだろう。

ちなみにVACUUM FREEZEはPostgreSQL 7.2から導入された機能で、上の説明もだいたいそれに合わせたものになっている。というのも、8.3のマニュアルではVACUUM FREEZEは廃止予定となっており、VACUUMの実行はpostgres.confのvacuum_freeze_min_ageの値を0に設定したものと同じことになる。つまり、VACUUMで代用できるわけだ。どうりでpsqlからもちゃんと出てこないわけだ。

PostgreSQLの7系で無停止運転が出来ないのは、こういう理由もあったからだ。