ディスクの空き容量がヤバいならVACUUM FULLすればいいじゃない
という考え方はPostgreSQL 9からは通用しなくなるようですね。
VACUUM FULL のアルゴリズムが改められ、ゴミが多い場合の処理が大幅に高速化されました。一方、ゴミが少ない場合にはむしろ遅くなっているので、「毎晩 vacuumdb –full」するような運用は改める必要があります。また、処理中に元のテーブルと同じサイズの一時ディスク領域が必要になったため、ディスクフル間近の場合は実行できなくなりました。
以前はそんな感じの運用をやっていたので、PostgreSQL 9のこの変更はじわじわきました。HDDの価格は確かにどんどん安くなってきてはいるのですが、高価なSANストレージに永続的に保存したいデータを集約しているような環境を構築してしまったところでは、一般的なHDDの低価格化の恩恵も受けられないので涙ぐましい努力を強いられることでしょう。
pgpool-IIのオンラインリカバリではrsyncに要注意
pgpool-IIでレプリケーションをしていて縮退運転に入ったとき、オンラインリカバリを実行してrsyncでデータディレクトリを転送するときにちょっとしたコツがいる。
最近、どうもリカバリーに失敗したりすぐに縮退運転に入ってしまうので困っていたのだが、原因はどうも継承を利用しているテーブルにあることがわかった。PostgreSQLの継承はとても便利な機能で、例えばデータがたまる一方のログ用テーブルなどを、日付などで分けて親テーブルを継承した別テーブルに入れてしまうことができて、しかもデータの検索は親テーブルにクエリを投げるだけで実現できるのだが、バッチ処理で作成していた継承テーブルの親子関係がリカバリを実行すると壊れてしまうのだ。
原因は、rsyncされるPostgreSQLのデータ領域の継承関係のデータが格納されているファイルはレプリケーションされた二つのサーバの両方で最終更新日時とファイルサイズが同じで中身だけ違うので、転送を「rsync -avz」などで実行してもリモートサーバ側では更新の対象にならず、スルーされていたからだ。rsyncにはchecksumを利用する「c」オプションがあるので、そちらを利用しないと継承関係のデータが転送されず、結果として上のようなことになる。
PostgreSQL8.3の自動キャスト
以前のエントリでPostgreSQL8.3への移行は型キャストが自動的に行われないため結構大変だと書いたが、PostgreSQL8.3にはCREATE CASTという新しいキャストの定義を記述できる機能がある。Let’s Postgresの記事で知った。風説の流布もいいところだった。以下の内容は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
memcached(repcached)のデータをバックアップ
memcachedの評価中に、対障害性を高める方法についてあれこれ考えていたのだが、あまりいい案が浮かばなった。
とりあえずストレージの冗長化であればrepcachedでなんとかなる。でも、memcachedのストレージがいっぱいになったらアクセスの古いデータから順に捨てられるので、予想を超える事態になったときにそれを検知してなんとかDBにでも保存しておく方法はないものかと考えたのだが、なかなかスマートな解決方法がない。
たとえば、セッション情報をmemcachedに保存しているとする。期限が設定されていたら、少なくともその間はデータを保持したい。でも、データ量が予想を上回ると上記のロジックで古いセッションが破棄されてしまう。よほどケチった予想をしていない限りは実害はほとんどないのかもしれないが、保障されているセッションの期限が守れない可能性があるのは変わりない。
そこで、現在memcachedのストレージ上に保存されているデータを定期的にDBにバックアップできないかと考えた。でも、よく考えたら、セッションのユニークIDをキーにしてデータを保存している場合、現在どんなIDが保存されているのかを知る方法がどこにもない。RDBMSだと取得先テーブル名はたいていわかるので、条件を書けばいくらでもデータを取得できるが、そもそも前提となる条件がユニークIDであるセッションのような情報だと難しい。
RDBMS風にいえばこんな感じ。 table | data | ----------+----------------------------------+ yagi | I still consider myself rather likable | ----------+----------------------------------+ soey | Size don't matter | ----------+----------------------------------+ chobee | Baseball games are like snowflakes | ----------+----------------------------------+
そこで、memcached上にシーケンステーブルのようなものを用意してみた。セッションの期限は24時間だから
$myname = 'yagi';
$mydata = 'I still consider myself rather likable.';
$today = date("Y-m-d", time());
$new_id = $today . '_' . $memcached->increment($today);
$memcache->set($new_id, $myname);
$memcache->set($myname, $mydata);
これで、RDBMS風にいえば
sequence_name | last_value | increment_by | ----------------+-----------+--------------+ 2009-02-06 | 1 | 1 | id | myname | --------------+---------+ 2009-02-06_1 | yagi | myname | mydata | ---------+----------------------------------+ yagi | I still consider myself rather likable |
みたいなテーブルが出来たことになる。そこで、
$yesterday = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 1, date("Y")));
$max = $memcache->get($yesterday);
$i = 0;
$data = array();
while($max >= $i){
$save = $memcache->get($yesterday . '_' . $i);
if($save){//件数が多すぎたら配列に入れられないけどまあいい
$data[$save] = $memcache->get($save);
}
$i++;
}
という感じでバックアップは取れる。特にセッション情報があまり変わらないアフィリエイトのID保存のような目的に使われる場合は、成果発生時のチェックにmemcachedから読み込んだデータとDBのデータを使えばまあ確実になる。
でも、動くことは動くが1件につき2つのデータが必要になり、効率は悪い。
もっとうまい方法があれば教えてほしいと思うのであった。
PostgreSQL小ネタ
PostgreSQLのラージオブジェクトについて、妙なことを聞かされて以下のようなことを無条件に信じていたのだが。
- PHPでリモートサーバ上のPostgreSQLに格納されたラージオブジェクトを取得することはできない
- なぜなら、pg_lo_exportはローカルのファイルパスを指定できるが、それだとリモートサーバ上にファイルが書きだされるだけだから
どう考えても納得できないので、試してみたらやっぱり間違っていた。もっとも、pg_lo_exportは使わなかったが。以下がサンプル。
<?php require_once 'DB.php'; $dsn = array( 'hostspec' => '192.168.0.0',//どっかのリモートサーバ 'phptype' => 'pgsql', 'database' => 'test', 'username' => 'postgres', 'port' => '5432', ); $db = DB::connect($dsn); $file = './orig.png'; $to_file = './test.png'; $content = ''; //まずインポートしてPostgreSQL上にラージオブジェクトを作成 $db->query("BEGIN"); $pid = pg_lo_import($db->connection, $file); $db->query("COMMIT"); //取り出し $db->query("BEGIN"); if($lo = pg_lo_open($db->connection, $pid, 'r') !== false){ do{ $data = pg_lo_read($lo); if(strlen($data) == 0){ break; } $content .= $data; }while(true); } $db->query("COMMIT"); $db->disconnect(); //書き出し $fp = fopen($to_file, 'w'); fputs($fp, $content); fclose($fp); ?>
どうということもない内容だが、どうしてこれが出来ないと思いこんでいたのだろうか。
pgpool-IIがSERIALに対応
PostgreSQLがボトルネックになるサービスで負荷分散を組み込んだり、冗長性を要求される場合にレプリケーション用にpgpoolを利用したいケースはたくさんあるだろうが、これまで特にレプリケーションではみんな結構苦労していたと思う。
pgpoolのバックエンドサーバのミスマッチはSELECT時にどこかのテーブルの行数の違いを検出するもので、内容については関与していない。SERIALを指定したカラム(MySQLでいうとauto_incrementが指定されたカラム)の不整合が発生しても検知されないので、ここをキーとして構築されたサービスは大打撃となってしまう。
これまでざっとテストした限りではINSERT LOCKを有効にすればSERIALの不整合は防げるようなのだが、INSERT処理が複数のサーバで完了するまで待ちになってしまう(という理解でいいのかな?)ため負荷がシビアな環境ではそのオーバーヘッドを考えると躊躇せざるを得なかった。これでようやくこの問題ともおさらばできるかもしれない。
MySQLのカラム複製脆弱性
昨日書いたMySQLの脆弱性ですが、手元の環境で見事に再現しました。MySQLのバージョンはCentOSでyumからインストールしたmysql-4.1.20-3.RHEL4.1.el4_6.1です。
手順は以下の通り。まずデータベース、テーブルを作成します。
mysql> create database sec_test; Query OK, 1 row affected (0.02 sec) mysql> use sec_test Database changed mysql> create table test (username char(16)); Query OK, 0 rows affected (0.01 sec)
ご覧の通り、char(16)でusernameというカラムを持つテーブルを作成します。
まず「admin」というusernameを持つ行を作ります。
mysql> insert into test (username) values ('admin'); Query OK, 1 row affected (0.00 sec) mysql> select count(*) from test where username = 'admin'; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec)
出来ています。
次に、問い合わせの文字列部分の後ろに空白を追加してみます。全部で16文字になるようにしました。
mysql> select count(*) from test where username = 'admin '; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec)
後ろの空白が無視されているのがわかります。update:正確にいえば、char型なので指定された長さ未満の文字列は空白文字で埋められるみたいです。null文字とかじゃないんですね。
次に、char(16)の範囲を超えたところで「x」を追加して検索してみます。
mysql> select count(*) from test where username = 'admin x'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)
さすがにヒットしませんでしたが、そもそもカラムの制限長を超えているのにエラーにはなりません。
では、この値をINSERTしてみます。
mysql> insert into test (username) values ('admin x'); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select count(*) from test where username = 'admin'; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.01 sec)
「admin」としてINSERT出来てしまいました。
これで、(1)既存のデータに重複行があるかチェック(2)なければINSERT、という処理で重複登録を防ごうとしてもダメなことがわかります。
ちなみにPostgreSQL 7.4.19でテストすると
db_test=# create table security_test (username char(16)); CREATE TABLE db_test=# INSERT INTO security_test (username) VALUES ('admin'); INSERT 0 1 db_test=# SELECT COUNT(*) FROM security_test WHERE username = 'admin'; count ------- 1 (1 row) db_test=# SELECT COUNT(*) FROM security_test WHERE username = 'admin '; count ------- 1 (1 row) db_test=# SELECT COUNT(*) FROM security_test WHERE username = 'admin x'; count ------- 0 (1 row) db_test=# SELECT COUNT(*) FROM security_test WHERE username = 'admin '; count ------- 1 (1 row) db_test=# INSERT INTO security_test (username) VALUES ('admin x'); ERROR: value too long for type character(16) db_test=# SELECT COUNT(*) FROM security_test WHERE username = 'admin'; count ------- 1 (1 row) db_test=#
INSERTはできません。8.3.3でも。
db_test=# create table test (username char(16)); CREATE TABLE db_test=# INSERT INTO test (username) VALUES ('admin'); INSERT 0 1 db_test=# SELECT count(*) FROM test WHERE username = 'admin'; count ------- 1 (1 row) db_test=# SELECT count(*) FROM test WHERE username = 'admin '; count ------- 1 (1 row) db_test=# SELECT count(*) FROM test WHERE username = 'admin x'; count ------- 0 (1 row) db_test=# SELECT count(*) FROM test WHERE username = 'admin '; count ------- 1 (1 row) db_test=# INSERT INTO test (username) VALUES ('admin x'); ERROR: value too long for type character(16)
INSERT時にあふれた分が勝手にカットされることはありませんでした。
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系で無停止運転が出来ないのは、こういう理由もあったからだ。