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

Tom Lane wrote:
> Heikki Linnakangas <heikki.linnakangas@enterprisedb.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