2014年5月15日木曜日

PostgreSQL : マテリアライズドビューを利用した設計

マテリアライズドビューを利用した設計

こんにちは、稲垣です。
PostgreSQL9.3からビューを作成した時点からデータが変わらないビューである「マテリアライズドビュー」が追加されました。今回はPostgreSQL9.3の目玉新機能である、更新されないマテリアライズドビューの動作(ディスク使用量)を紹介します。

■ システムの要件

実用的な例として複数のデータソースからバッチジョブでデータを更新するシステムを想定します。

    システムの要件
  • システムは基本的に24稼働を前提とする
  • データが更新されている途中のデータは一般ユーザーが参照できない事
  • データ更新中も特定のユーザーは更新中のデータを参照できる事
  • データが更新された場合の更新時間を最小限にする事
  • データ量が多いのでディスク使用量は最小限にする事

このようなシステム要件でテーブル設計を考えてみます。

■ マテリアライズドビューが無い場合

更新途中のデータは参照できてはならないのでTRANSACTIONを利用します。TRANSACTIONの分離レベルは次のように定義されています。

Table 13-1. Standard SQL Transaction Isolation Levels

Isolation Level Dirty Read Nonrepeatable Read Phantom Read
Read uncommitted Possible Possible Possible
Read committed Not possible Possible Possible
Repeatable read Not possible Not possible Possible
Serializable Not possible Not possible Not possible

http://www.postgresql.org/docs/9.2/static/transaction-iso.html

PostgreSQLのTRANSACTIONのデフォルト分離レベルはRead Committedです。デフォルトのRead ComittedのTRANSACTIONを使えば良い事が分かります。
しかし、トランザクション内で更新されたデータは他のユーザーからは参照できません。データ更新中に特定のユーザーに更新中のデータを見えるようにするには別のテーブルを作成するしかありません。データ更新完了後に

BEGIN;
ALTER TABLE tbl RENAME TO tbl_old;
ALTER TABLE tbl_tmp RENAME TO tbl;
COMMIT;

として入れ替える仕組みにせざるを得ません。

■ マテリアライズドビューが在る場合

マテリアライズドビューがある場合は別テーブルを利用しなくても更新途中のデータは参照できません。テーブルの代わりにマテリアライズドビューを利用すれば良いだけです。

CREATE MATERIALIZED VIEW tbl_view AS SELECT * FROM tbl;

と定義し、一般ユーザーはtbl_viewを参照し、変更中のデータも参照する必要があるユーザーはtblを直接参照すれば要件を満たせます。

■ 別テーブルを使う場合とマテリアライズドビューを使う場合の比較

トランザクションとマテリアライズドビューでのディスク使用量の使い方違いを比較してみます。PostgreSQL9.3が/usr/local/pgsql9.3ディレクトリにインストールされているものとします。

データベースの作成と起動

[host@dev tmp]$ /usr/local/pgsql9.3/bin/initdb --no-locale testdb
 (省略)
[host@dev tmp]$ /usr/local/pgsql9.3/bin/pg_ctl -D testdb -l logfile start server starting

デフォルトままだと遅すぎるので、fsync=Off, shared_buffers=1280MBに設定してから起動します。

一時テーブルとMATERIALIZED VIEWを利用した場合とどの程度のディスク利用量の差がでるか、簡単なテストスクリプトで検証してみます。
このテストスクリプトは100万行のSHA512ハッシュを保存したテーブルを作成し、一時テーブルの場合とMATERIALIZED VIEWの場合でのディスク使用量の違いを出力します。

test.php (PHP5.5+Linux+PostgreSQL9.4)
-----
< ?php
// レコード数
$items = 1000000;
// 接続先DB
$dbconn = 'host=127.0.0.1 port=5432 user=yohgaki dbname=test';
// データベース操作コマンド
$dbstart = <<<EOD
/usr/local/pgsql9.3/bin/pg_ctl -D testdb -l logfile start;
sleep 2;
EOD;
$dbxlogreset = <<<EOD
/usr/local/pgsql9.3/bin/pg_resetxlog testdb;
EOD;
$dbstop = <<<EOD
/usr/local/pgsql9.3/bin/pg_ctl -D testdb -m fast stop;
sleep 2;
EOD;

$ddl = <<<EOD
CREATE TABLE test (
  id   INT8 PRIMARY KEY,
  txt  TEXT NOT NULL
);
EOD;

$ddl_tmp = <<<EOD
CREATE TABLE test_tmp (
  id   INT8 PRIMARY KEY,
  txt  TEXT NOT NULL
);
EOD;

!file_exists('./testdb') and die('testdb cluster must exist');

`$dbxlogreset`;
echo `$dbstart`;
$db = pg_connect($dbconn) or die('Failed to connect');
@pg_query('DROP TABLE test');
@pg_query('DROP TABLE test_tmp');
pg_query($ddl);
pg_query($ddl_tmp);
pg_query('CHECKPOINT;');

// クエリ準備
pg_prepare('insert_hash', 'INSERT INTO test (id, txt) VALUES ($1, $2);') or die('Failed to prepare');
pg_prepare('insert_tmp_hash', 'INSERT INTO test_tmp (id, txt) VALUES ($1, $2);') or die('Failed to prepare');

echo "BFORE INSERT\n";
echo `du -h testdb | tail -n 1`;
// ハッシュ値を保存する
pg_query('BEGIN;');
for ($i=0; $i<$items; $i++) {
    pg_execute('insert_hash', array($i, hash('sha512',uniqid(true)))) or die('Failed to execute');
}
pg_query('COMMIT;');
pg_query('CHECKPOINT;');

echo "BFORE TEST\n";
echo `du -h testdb | tail -n 1`;

// 別のテーブルに追加
$start = microtime(true);
pg_query('BEGIN;');
for ($i=0; $i<$items; $i++) {
    if (!($i % 10000)) {
        // 1万行単位でコミット
        pg_query('COMMIT;');
        pg_query('BEGIN;');
    }
    pg_execute('insert_tmp_hash', array($i, hash('sha512',uniqid(true)))) or die('Failed to update');
}

echo "PEEK DISK USAGE\n";
echo `du -h testdb | tail -n 1`;

pg_query('COMMIT;');
pg_query('CHECKPOINT;');

echo "END TEST\n";
echo "TIME: ".(microtime(true)-$start)."\n";

// 一時テーブルを削除しテーブル名更新をシミュレート
pg_query('DROP TABLE test');
pg_query('VACUUM FULL;');
echo `du -h testdb | tail -n 1`;


// クリーンアップ
pg_query('DROP TABLE test_tmp');
pg_query('VACUUM FULL;');
pg_close($db);
// xlogをクリアするためにサーバーを停止し、pg_resetxlogを実行
echo `$dbstop`;
echo `$dbxlogreset`;
echo `$dbstart`;


// MATERIALIZED VIEWの場合
echo "==========================================\n";
$db = pg_connect($dbconn);
pg_query($ddl);
pg_query('CHECKPOINT;');

// クエリ準備
pg_prepare('insert_hash', 'INSERT INTO test (id, txt) VALUES ($1, $2);') or die('Failed to prepare');
pg_prepare('update_hash', 'UPDATE test SET txt = $1 WHERE id = $2;') or die('Failed to prepare');

echo "BFORE INSERT\n";
echo `du -h testdb | tail -n 1`;

// ハッシュ値を保存する
pg_query('BEGIN;');
for ($i=0; $i<$items; $i++) {
    pg_execute('insert_hash', array($i, hash('sha512',uniqid(true)))) or die('Failed to execute');
}
pg_query('COMMIT;');
pg_query('CHECKPOINT;');

echo "BFORE TEST\n";
echo `du -h testdb | tail -n 1`;

pg_query('CREATE MATERIALIZED VIEW test_view AS SELECT * FROM test;');
echo "AFTER MATELIALIZED VIEW CREATED\n";
echo `du -h testdb | tail -n 1`;

// ハッシュ値を更新
pg_query('BEGIN;');
$start = microtime(true);
for ($i=0; $i<$items; $i++) {
    if (!($i % 10000)) {
        // 1万行単位でコミット
        pg_query('COMMIT;');
        pg_query('BEGIN;');
    }
    pg_execute('update_hash', array(hash('sha512',uniqid(true)), $i)) or die('Failed to update');
}

echo "PEEK DISK USAGE\n";
echo `du -h testdb | tail -n 1`;

pg_query('COMMIT;');
pg_query('REFRESH MATERIALIZED VIEW test_view;');
pg_query('CHECKPOINT;');

echo "END TEST\n";
echo "TIME: ".(microtime(true)-$start)."\n";

pg_query('VACUUM FULL;');
echo `du -h testdb | tail -n 1`;

// クリーンアップ
pg_query('DROP TABLE test CASCADE;');
pg_query('VACUUM FULL;');
pg_close($db);
// データベースを停止
echo `$dbstop`;
echo `$dbxlogreset`;
?>

このテストスクリプトをtestdbクラスターのディレクトリがある場所から実行します。

[host@dev tmp]$ time php test.php
server starting
BFORE INSERT
41M testdb
BFORE TEST
341M testdb
PEEK DISK USAGE
529M testdb
END TEST
TIME: 128.83299303055
341M testdb
waiting for server to shut down.... done
server stopped
Transaction log reset
server starting
==========================================
BFORE INSERT
41M testdb
BFORE TEST
341M testdb
AFTER MATELIALIZED VIEW CREATED
508M testdb
PEEK DISK USAGE
717M testdb
END TEST
TIME: 147.21681809425
507M testdb
waiting for server to shut down.... done
server stopped
Transaction log reset

real 9m15.454s
user 0m48.019s
sys 0m50.059s

前半の結果が一時テーブルを利用した場合、後半の結果がMATERIALIZED VIEWを利用した場合の結果です。この結果からディスク利用量、速度の面では一時テーブルを利用する方法の方が優位であることが分かりました。マテリアライズドビューの場合、VIEWを作成した時点でのデータを保存しなければならないので、ほぼ同じ大きさの一時テーブルを作成した場合とあまり変わらないディスク容量が必要です。一時テーブルを利用する場合、不要になったテーブルは削除できるのでディスク容量は回復できます。マテリアライズドビューはVIEWを維持しなければならないのでディスク容量を利用したままになります。

速度面で有利であった理由は単純、更新より挿入の方が速いためでしょう。一時テーブルを利用した実装の場合、インデックスの構築をテーブル作成後にすれば更に速くなるはずです。

■ まとめ

マテリアライズドビューは今回例として挙げた仕様のシステムでは、構築面は容易になりますがディスク使用量、速度の面では不利になること分かります。特にデータ量の多いテーブルにマテリアライズドビューを作成するとかなりのディスク容量が必要であることは認識していなければなりません。

一時テーブル利用した実装の方がディスク使用量、性能面で優れているものの、マテリアライズドビューを利用するとより簡単に構築できると思います。簡単である=メンテナンス・運用も行い易い、のでバランスを考えてマテリアライズドビューを利用すると良いでしょう。

0 件のコメント:

コメントを投稿