Re: massive speedup on temp table creation/destruction?

I was just trying (unsuccessfully so far) to replicate Csaba Nagy's
report of a strange failure with temp table creation. I made use
of pgbench's recent improvements to be able to push random scripts
at a collection of backends:

$ cat ttscript.sql
create temp table foo (f1 int);
drop table foo;
$ pgbench -n -c 98 -t 1000 -f ttscript.sql bench

This is just pushing a long string of create temp table/drop table
commands at a whole lot of backends concurrently.

What I found surprising is that I get numbers like 430 tps from HEAD
and 220 tps from REL8_1_STABLE. This test case is of course not about
performance, but I'm not quite sure what we changed that would produce
a 2X speedup from 8.1. Can anyone else replicate this?

regards, tom lane

On 8/31/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I was just trying (unsuccessfully so far) to replicate Csaba Nagy's
> report of a strange failure with temp table creation. I made use
> of pgbench's recent improvements to be able to push random scripts
> at a collection of backends:
>
> $ cat ttscript.sql
> create temp table foo (f1 int);
> drop table foo;
> $ pgbench -n -c 98 -t 1000 -f ttscript.sql bench
>
> This is just pushing a long string of create temp table/drop table
> commands at a whole lot of backends concurrently.
>
> What I found surprising is that I get numbers like 430 tps from HEAD
> and 220 tps from REL8_1_STABLE. This test case is of course not about
> performance, but I'm not quite sure what we changed that would produce
> a 2X speedup from 8.1. Can anyone else replicate this?

humm, you had me curious, so i ran the test.

postgresql. 8.2 ~ 2 weeks recent
number of clients: 98
number of transactions per client: 100
number of transactions actually processed: 9800/9800
tps = 216.424848 (including connections establishing)
tps = 217.558450 (excluding connections establishing)

8.1:
number of clients: 98
number of transactions per client: 100
number of transactions actually processed: 9800/9800
tps = 262.430496 (including connections establishing)
tps = 263.977714 (excluding connections establishing)

I did not confirm your results unfortunately. this is on quad opteron,
totally stock freshly minted database cluster :-)

I then did a cvs update on 8.2 just in case, and got:
number of clients: 98
number of transactions per client: 100
number of transactions actually processed: 9800/9800
tps = 223.026519 (including connections establishing)
tps = 224.233746 (excluding connections establishing)

note i only did a 100 transactions, so the results are expected to be
a bit more wild.

merlin

"Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> On 8/31/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> What I found surprising is that I get numbers like 430 tps from HEAD
>> and 220 tps from REL8_1_STABLE. This test case is of course not about
>> performance, but I'm not quite sure what we changed that would produce
>> a 2X speedup from 8.1. Can anyone else replicate this?

> I did not confirm your results unfortunately. this is on quad opteron,

I was trying it on a dual Xeon EM64T with hyperthreading on ... I wonder
if we've unintentionally done something that optimizes for that platform?

I don't make any claim that this test case is interesting in itself,
but it might be telling us something about more-mainstream cases.

regards, tom lane