Re: RAM-only temporary tables - Mailing list pgsql-hackers

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