Re: RAM-only temporary tables

Heikki Linnakangas wrote:
> I tried running this:
>
> CREATE TEMPORARY TABLE footemp (id int4);
> DROP TABLE footemp;
>
> with pgbench -f, but can't see any meaningful difference between 8.3 and
> CVS HEAD. Both can do about 300 tpm, or 700-800 with fsync=off. There
> probably is a measurable difference there if you run longer tests, but
> doesn't seem like the extra file creation+unlink is worth worrying
> about. With the caveat that this is on reiserfs, on my laptop. Does
> someone see a difference on other filesystems?

I just tried that on a test server with data directory a small RAID
array with ext3. Results are similar, at ~120 tps with both CVS HEAD and
8.3, and with fsync=off, around ~1700 tps on both versions.

oprofile does suggest that more time is spent in the kernel in CVS HEAD.
CVS HEAD:

37387 13.9383 no-vmlinux postgres (no
symbols)
20604 7.6814 postgres postgres
CatalogCacheFlushRelation
16075 5.9929 postgres postgres
hash_search_with_hash_value
10109 3.7688 postgres postgres
LWLockAcquire
9225 3.4392 postgres postgres
_bt_compare
8973 3.3452 postgres postgres
XLogInsert
8368 3.1197 postgres postgres
LWLockRelease
8009 2.9858 postgres postgres hash_any
6944 2.5888 no-vmlinux no-vmlinux (no
symbols)
4432 1.6523 no-vmlinux pgbench (no
symbols)
4197 1.5647 postgres postgres
AllocSetAlloc
3773 1.4066 libc-2.7.so postgres memcmp
3359 1.2523 postgres postgres
_bt_checkkeys
3315 1.2359 postgres postgres
FunctionCall2
3135 1.1688 postgres postgres
PinBuffer

8.3:

27264 9.3254 no-vmlinux postgres (no
symbols)
20673 7.0710 postgres postgres
CatalogCacheFlushRelation
18576 6.3537 postgres postgres
hash_search_with_hash_value
12795 4.3764 postgres postgres
LWLockAcquire
11565 3.9557 postgres postgres
_bt_compare
11538 3.9465 postgres postgres hash_any
10414 3.5620 postgres postgres
XLogInsert
10100 3.4546 postgres postgres
LWLockRelease
6306 2.1569 postgres postgres
_bt_checkkeys
5096 1.7430 postgres postgres
AllocSetAlloc
4835 1.6538 no-vmlinux no-vmlinux (no
symbols)
4090 1.3989 postgres postgres
PrepareToInvalidateCacheTuple
4021 1.3753 postgres postgres
FunctionCall2
3965 1.3562 no-vmlinux pgbench (no
symbols)
3869 1.3234 libc-2.7.so postgres memcmp

but frankly I can't get too excited about that.

I also quickly tried a test case with ON COMMIT DELETE ROWS temp table.
I modified pgbench slightly, so that it creates a temp table with ON
COMMIT DELETE ROWS after connecting, and then run a script with a simple
one row INSERT to the temp table. The results look similar; I'm getting
~11000-12000 tps on both 8.3 and CVS HEAD. oprofile suggests that about
~50% of the time is spent in kernel, so I'm actually a bit surprised
that the new FSM file isn't hurting more there.

So, I'm not convinced we need to do anything, based on these test. Maybe
I missed the point of the OP; if so, a repeatable test case would be nice.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com