TEMPORARY TABLE（一時テーブル）を探る
PostgreSQLのTEMPORARY TABLE(一時テーブル)について主に速度面について探っていきます。
ここで書かれていることはPostgreSQL 10を対象としています。
はじめに
PostgreSQLのTEMPORARY TABLE(一時テーブル)は接続したセッションの間にしか生存できないテーブルです。このテーブルは他のセッションからはアクセスすることができません。
作成は通常のCREATEの後にTEMPORARY又はTEMPを付けてテーブルを作成します。
CREATE TEMP TABLE temp_sample1 ( i int);
作成後セッションが継続している間は通常のテーブルと同様に使えますが、セッションが終わると自動で削除されます。セッション終了時だけでなく、トランザクション終了時に消したりも出来ます。（他にも ON COMMIT DELETE ROWS で全行消すことも可能です）。
CREATE TEMP TABLE temp_sample1 ( i int) ON COMMIT DROP;
他のセッションからはアクセス出来無いと書きましたが、システムカタログには登録されているので、違うセッションでログインした場合、権限さえあれば存在は確認することができます。
SELECT * FROM pg_tables;
....
pg_temp_3 | temp_sample1 | noborus | | f | f | f | f
ただし、違うセッションでは中身を見ることは出来ません。
そもそもスキーマが違うので、テーブルがサーチパスに入っていませんが、スキーマを指定しても以下のようにエラーになります。
SELECT * FROM pg_temp_3.temp_sample1;
ERROR: 0A000: cannot access temporary tables of other sessions
LOCATION: ReadBufferExtended, bufmgr.c:657
格納のされ方
PostgreSQLは共有バッファを使用してテーブルにアクセスしていることはよく知られています。通常のテーブルの場合は、実際に格納されているファイルから共有バッファに読み込んでアクセスされます。
共有バッファは複数の接続でサーバープロセスが複数になっても共有されるメモリ空間です。
一時テーブルはそれとは違い、そのサーバープロセスが持つローカルバッファというメモリ空間を使用します。
共有バッファとローカルバッファは同じバッファ・マネージャーを通して、一部だけローカルバッファのフラグを判別して動作を変えています。
そのため、実テーブルと一時テーブルはほとんど同じ動作をし、SQLレベルでは基本的に制限はありません（当然ながらログ(WAL)を取らないテーブルであり、レプリケーションその他からは、違って見えます）。
メモリ空間は別ですが、通常のテーブルはファイル（ディスク）にも書き込まれます。一時テーブルはどうでしょうか？というのが今回の主題です。
マニュアルには以下のように書かれています。
しかし一時的なリレーションでは、ファイル名はtBBB_FFFという形になります。
ここでBBBはファイルを生成したバックエンドのバックエンドID、FFFはファイルノード番号です。
つまり一時テーブルでも通常のテーブルと同様なファイルが用意されて書かれる前提になっています。
ファイルに書かれるのか？
一時テーブルはUnloggedテーブルと同様にログ（WAL)に書かれません。クラッシュしたら当然消えますが、Unloggedテーブルとは違いそれは問題にはなりません（クラッシュしなくてもセッションが終われば消えるので）。
問題は、INSERTされた内容がローカルバッファに書き込まれつつファイル（ディスク）にも書き込まれるのかです。共有バッファに書かれるのであれば、それを書き出す専用のプロセスが居るので、裏側で書いているときにも次の処理に移れますが、ローカルバッファは他のプロセスから見えないので、そうもいきません。ファイルに書くとすると通常のテーブルよりも（処理が戻ってくるのは）遅くなることになりかねません。
答えを書いてしまうと「ローカルバッファから溢れなければ書き込まれない」です。
PostgreSQLでは設定にも「temp_buffers」（デフォルト8MB)があり、このtemp_buffersの範囲内であれば、ファイルに書かれないまま動作します。ここから溢れるとファイルに書き込まれます。
溢れた途端に遅くなりそうですが、動作としては妥当な動作ですね。
一時テーブルを使用する時はtemp_buffersのサイズを意識しましょう。めでたしめでたし...
とは、なりません。もうちょっと続きます。
実際のデータファイルを覗いてみるとtemp_buffersが何であれ、一時テーブルを作ると上記のデータファイルが作成されます。
temp_buffersのサイズを大きくして、そこは超えないようなデータをINSERTしていくと...データファイルのサイズが8K毎に増加していきます。ああ、いきなり書いたことと挙動が違う...
この謎を探ってみます。
一時テーブルのファイルを追う
まず temp_buffersが溢れないように大きく(100MB)設定して、その範囲内の一時テーブルを作成します。
作成したら、一時テーブルのファイルを探します。t3_27064というファイルが一時テーブルのファイルでした。
lsで確認すると8.9MBのファイルですが、hexdumpで確認すると中身がスカスカです。
$ hexdump -b t3_27064
0000000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000
*
08da000
今度はtemp_buffersを小さく(800KB)して再起動し、再度同じことをしてみます。
$ hexdump -b t3_27070
0000000 000 000 000 000 000 000 000 000 000 000 000 000 040 001 070 001
0000010 000 040 004 040 000 000 000 000 230 237 302 000 060 237 302 000
0000020 320 236 272 000 110 236 020 001 320 235 352 000 150 235 302 000
0000030 000 235 302 000 230 234 302 000 060 234 302 000 320 233 272 000
....
今度は、中身が詰まってました。
つまりファイルの領域を確保するだけで、temp_buffersから溢れた場合に実際に書かれるということでした。
この挙動は、一時テーブルに限らず、通常のテーブルでもすぐには書き込まれずに領域だけ確保されて、後で書き込まれます。実際に書き込まれる速度よりも確保だけならずっと早く済みます。
ということで、めでたしめでたし。
...では、あるのですが、まだ終わりません。
大抵の場合は、これで十分で言えるでしょうが、temp_buffersに収まる場合でも一時テーブルがディスクにアクセスするのが気になります。
md.cを追って確認する
細かい説明は省きますが、PostgreSQLの物理的な格納はストレージマネージャーを通してmd.cが請け負っています。ということで、md.cを見張っていれば実際にディスクに書き込まれるところがわかります。
ここでは流れは気にせずファイルの領域を確保しているところとファイルに書き込んでいるところを調べていくとmd.cの中の「mdextend」で確保して、「mdwrite」で書き込んでいることがわかりました。
md.cの中のコメントにもそれぞれ以下のように書いてあります。
mdextend() -- Add a block to the specified relation.
mdwrite() -- Write the supplied block at the appropriate location.
この２つが、何回呼ばれて、どのくらい時間がかかっているか、がわかると安心出来ます。
SystemTapを使ってみる
PostgreSQLでは、Dtrace相当のツールで追跡できる機構があらかじめ入っています。動作しているOSはLinuxなのでSystemTapを使って追ってみます。
SystemTapは、systemtapを使った動的追跡を参考にmd.cの計測をするように書き換えて使うことにします。
ところが、mdwriteにはsmgr-md-write-startとsmgr-md-write-doneというプローブが入っていますが、mdextendにはプローブが入っていません。マニュアルを見るとプローブの追加が可能だと書いてあるので、既に入っているところを参考に追加してみたのが以下です。
diff --git a/src/backend/storage/smgr/md.c b/src/backend/storage/smgr/md.c
index 65e0abe..08ce5cf 100644
--- a/src/backend/storage/smgr/md.c
+++ b/src/backend/storage/smgr/md.c
@@ -499,6 +499,12 @@ mdextend(SMgrRelation reln, ForkNumber forknum, BlockNumber blocknum,
int nbytes;
MdfdVec *v;
+ TRACE_POSTGRESQL_SMGR_MD_EXTEND_START(forknum, blocknum,
+ reln->smgr_rnode.node.spcNode,
+ reln->smgr_rnode.node.dbNode,
+ reln->smgr_rnode.node.relNode,
+ reln->smgr_rnode.backend);
+
/* This assert is too expensive to have on normally ... */
#ifdef CHECK_WRITE_VS_EXTEND
Assert(blocknum >= mdnblocks(reln, forknum));
@@ -557,6 +563,14 @@ mdextend(SMgrRelation reln, ForkNumber forknum, BlockNumber blocknum,
if (!skipFsync && !SmgrIsTemp(reln))
register_dirty_segment(reln, forknum, v);
+ TRACE_POSTGRESQL_SMGR_MD_EXTEND_DONE(forknum, blocknum,
+ reln->smgr_rnode.node.spcNode,
+ reln->smgr_rnode.node.dbNode,
+ reln->smgr_rnode.node.relNode,
+ reln->smgr_rnode.backend,
+ nbytes,
+ BLCKSZ);
+
Assert(_mdnblocks(reln, forknum, v) <= ((BlockNumber) RELSEG_SIZE));
}
diff --git a/src/backend/utils/probes.d b/src/backend/utils/probes.d
--- a/src/backend/utils/probes.d
+++ b/src/backend/utils/probes.d
@@ -86,6 +86,8 @@ provider postgresql {
probe smgr__md__read__done(ForkNumber, BlockNumber, Oid, Oid, Oid, int, int, int);
probe smgr__md__write__start(ForkNumber, BlockNumber, Oid, Oid, Oid, int);
probe smgr__md__write__done(ForkNumber, BlockNumber, Oid, Oid, Oid, int, int, int);
+ probe smgr__md__extend__start(ForkNumber, BlockNumber, Oid, Oid, Oid, int);
+ probe smgr__md__extend__done(ForkNumber, BlockNumber, Oid, Oid, Oid, int, int, int);
probe wal__insert(unsigned char, unsigned char);
probe wal__switch();
TRACE_POSTGRESQL_SMGR_MD_WRITE_START と TRACE_POSTGRESQL_SMGR_MD_WRITE_DONE を同じ感じで入れればいけそうだったので、名前を変えて入れてみただけです。
これでコンパイルしたものをインストールして起動させます。
$ configure --prefix=/home/noborus/pgsql/10.0 -enable-dtrace --enable-debug
$ make & make install
$ cd /home/noborus/pgsql/10.0
$ ./bin/initdb -D data
$ ./bin/pg_ctrl -D data start
起動したらSystemTapを準備します。今回は以下のようにして回数と処理時間を出すようにして実行します。ちょっと長いですが、そのまま付けます。
#!/usr/bin/stap
# シェル内で使用する変数宣言
global extend_count, write_count
# プローブ開始時の処理
probe begin
{
printf("probe start!\n")
}
# process のところには、使用するpostgresのパスを記述します。
probe process("/home/noborus/pgsql/10.0/bin/postgres").mark("query__start")
{
printf("%d:%s:[%s]\n", pid(),"query__start", user_string($arg1))
}
probe process("/home/noborus/pgsql/10.0/bin/postgres").mark("smgr__md__write__start")
{
write_count ++
start_stopwatch("mdwrite")
}
probe process("/home/noborus/pgsql/10.0/bin/postgres").mark("smgr__md__write__done")
{
stop_stopwatch("mdwrite")
}
probe process("/home/noborus/pgsql/10.0/bin/postgres").mark("smgr__md__extend__start")
{
extend_count ++
start_stopwatch("mdextend")
}
probe process("/home/noborus/pgsql/10.0/bin/postgres").mark("smgr__md__extend__done")
{
stop_stopwatch("mdextend")
}
# プローブ終了時の処理
probe end
{
printf("\nprobe end! count...\n")
# 各処理の累積数の表示
printf("extend count :%8d :%12dus\n", extend_count, read_stopwatch_us("mdextend"))
printf("write count :%8d :%12dus\n", write_count, read_stopwatch_us("mdwrite"))
}
PostgreSQLは自分の権限で動作するようにインストールしましたが、SystemTapの実行自体は権限が必要なので、sudoで起動します。
sudo ./pgtab.stap
他の端末からpsqlでログインしてクエリーを打つとsystemtap実行側で表示されるはずです。
probe start!
15801:query__start:[SELECT * FROM sample1 ;]
続けて一時テーブルを作成して書き込んでみます。「CREATE TABLE AS」で試すのが簡単です。
CREATE TEMP TABLE tmp_sample1 AS SELECT * FROM sample1;
そうするとsystemstapを実行している端末で以下のように表示されます。
15801:query__start:[CREATE TEMP TABLE tmp_sample1 AS SELECT * FROM sample1;]
終了時に集計が出るようにしているので、CTRL+Cでsystemtapを終了します。
^C
probe end! count...
extend count : 1134 : 50925us
write count : 0 : 0us
sample1は9MB程度の内容だったので、8k × 1134回拡張されました。temp_buffersのサイズは大きくしていたので writeは発生しませんでした。temp_buffersのサイズを変更してwriteが発生するのか確認も出来ますが、ここでは省略します。
問題はextendの方で1134回呼ばれて、トータルで50925マイクロ秒(0.050925秒)掛かっていること（当然ながらこの時間は環境によります）です。通常のテーブルでは他で時間が掛かるため、気にならないかもしれませんが、一時テーブルに大量に書き込みたいときには気になるコストです。
## RAMDISK(tmpfs)を使ってみる
出来れば必要のないときは、mdextendが呼ばれないようにしたいところですが、単に止めれば良い訳はないので、その前にこのコストを減らせないか探ってみます。
PostgreSQLにはテーブルスペースがあり、さらに設定の中にtemp_tablespaceがあります。
このtemp_tablespaceを実際のディスクではなくRAMDISK(tmpfs)に変更した場合に時間が短くなるのか試してみます。
テーブルスペースとしてRAMDISKを登録します。
$ mkdir -p ramdisk
$ chmod 777 ramdisk
$ sudo mount -t tmpfs size=4G ramdisk
$ mkdir ramdisk/data
$ psql -c "CREATE TABLESPACE tmpspace LOCATION '/home/noborus/pgsql/10.0/ramdisk/data';"
temp_tablespacesを登録したtmpspaceにセットしてから、一時テーブルを作成します。
SET temp_tablespaces='tmpspace';
CREATE TEMP TABLE tmp_sample1 AS SELECT * FROM sample1;
SystemTapの結果は以下のようになりました。
extend count : 1134 : 6025us
write count : 0 : 0us
おおお、50925usが6025usになりました。ひと桁違います！
これで動かせばディスクにアクセスせずに一時テーブルを作成することが出来ます。一時テーブルならRAMDISKでも困りませんからね。
実際にもっと大きなデータでほんとに時間が短くなるのか確認してみます。2GBぐらいのデータを用意して、そこから一時テーブルを作るSQLを実行してみます。
SET文でtemp_tablespacesを切り替えて同じSQLを実行します。以下のファイルをそれぞれ用意して、
SET temp_tablespaces TO DEFAULT ;
CREATE TEMP TABLE tmp_pcount AS SELECT * FROM pcount ;
SET temp_tablespaces='tmpspace';
CREATE TEMP TABLE tmp_pcount AS SELECT * FROM pcount ;
timeを付けてpsqlで実行します。
$ time psql -f default.sql
SET
SELECT 1000000
psql -f default.sql 0.00s user 0.00s system 0% cpu 15.618 total
$ time psql -f ramdisk.sql
SET
SELECT 1000000
psql -f ramdisk.sql 0.00s user 0.00s system 0% cpu 2.442 total
何度か順番を入れ替えて試してもRAMDISKにすることで速度UPができているようです。
めでたしめでたし。
感想など
そもそもmdextendが呼ばれるのは、通常のテーブルと同様の機構を使っているためで、一時テーブル側から考えると、まだまだ改善の余地がありそうです。