Re: RAM-only temporary tables

Tom Lane wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> Tom Lane wrote:
>>> Heikki, would it be reasonable to fix things so that a nonexistent FSM
>>> fork is semantically the same as an empty one, and not create FSM until
>>> there's actually something to put in it?
>
>> Possibly, but I'd like to understand what exactly the problem is. 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.
>
> Try several thousand temp tables within one transaction.

After increasing max_lock_per_transaction to avoid the issue I mentioned
elsewhere in this thread, using this test script:

#/bin/sh

echo "BEGIN;"
for ((i=0;i<=10000;i+=1)); do
echo "CREATE TEMPORARY TABLE footemp$i (id int4);"
done
echo "COMMIT;"

And repeating a few times with:

$ time sh temptest.sh | ~/installations/cvshead/bin/psql postgres -q

I'm again seeing no big difference between 8.3 and CVS HEAD. I'm getting
values around both sides of 6 seconds, with 5.8s being the minimum for
CVS HEAD, and 5.6s the minimum for PG 8.3.

oprofile for 8.3:

samples % image name app name
symbol name
23844 11.1625 postgres postgres
LockReassignCurrentOwner
22299 10.4392 no-vmlinux postgres (no
symbols)
15461 7.2380 postgres postgres
hash_seq_search
12309 5.7624 postgres postgres
CatalogCacheFlushRelation
10981 5.1407 postgres postgres
hash_search_with_hash_value
7139 3.3421 postgres postgres
get_tabstat_entry
7133 3.3393 postgres postgres
XLogInsert
7112 3.3294 no-vmlinux no-vmlinux (no
symbols)
6294 2.9465 postgres postgres
_bt_compare
6027 2.8215 postgres postgres
LWLockAcquire
5189 2.4292 postgres postgres hash_any
4744 2.2209 postgres postgres
LWLockRelease
3355 1.5706 bash bash (no
symbols)

oprofile for CVS HEAD:

samples % image name app name
symbol name
30167 11.0321 postgres postgres
LockReassignCurrentOwner
27345 10.0001 no-vmlinux postgres (no
symbols)
19537 7.1447 postgres postgres
hash_seq_search
17376 6.3544 postgres postgres
CatalogCacheFlushRelation
14910 5.4526 postgres postgres
findDependentObjects
14187 5.1882 postgres postgres
hash_search_with_hash_value
9268 3.3893 postgres postgres
get_tabstat_entry
7478 2.7347 postgres postgres
_bt_compare
6861 2.5091 no-vmlinux no-vmlinux (no
symbols)
6779 2.4791 postgres postgres
LWLockAcquire
6764 2.4736 postgres postgres
XLogInsert
5122 1.8731 postgres postgres
LWLockRelease
4838 1.7693 postgres postgres hash_any
3501 1.2803 bash bash (no
symbols)

I'm quite surprised to see LockReassignCurrentOwner being called at all.
gdb shows where the call is coming from:

#0 LockReassignCurrentOwner () at lock.c:1653
#1 0x0000000000662865 in ResourceOwnerReleaseInternal (owner=0xab21f8,
phase=RESOURCE_RELEASE_LOCKS, isCommit=1 '\001', isTopLevel=0 '\0')
at resowner.c:261
#2 0x00000000006628f0 in ResourceOwnerRelease (owner=0xab21f8,
phase=RESOURCE_RELEASE_LOCKS, isCommit=1 '\001', isTopLevel=0 '\0')
at resowner.c:171
#3 0x000000000066139b in PortalDrop (portal=0xacc060,
isTopCommit=<value optimized out>) at portalmem.c:418
#4 0x00000000005aa162 in exec_simple_query (
query_string=0xa91350 "CREATE TEMPORARY TABLE footemp7013 (id int4);")
at postgres.c:972

I'm also a bit surprised that the higher number of syscalls in CVS HEAD
isn't visible in this oprofile report. The top percentages seemed to
vary by a couple percentage points from run to run, though, so perhaps
it's just drowned by noise.

Kevin, what was your original scenario like that led you to investigate
this?

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