Each test set has 3 time sets.

First is on pgbench -i (-s 5)
Second is on pgbench -t 3000 -s 5

Third is on postmaster during the run of the first 2.


The first test on a slow harddrive has a large effect for increasing the
namedatalen length.

Second through 4th sets don't really show any issues when the drives are
quite a bit quicker -- IBM Deskstars stripped).  Looks like something
threw the results off near the end of the second set / beginning of the
3rd set.  Probably nightly maintenance.  I'm afraid I don't have a good
box for benchmarking (potential for many variables).

Anyway, if people are using machines that are heavily disk bound,
they're probably not running a production Postgresql installation. 
Those who are will benefit from the longer length of namedatalen.

That said, the pg_depend patch going through fixes 50% of the serial
problem (auto-drop on table drop).  The other 50% (short non-conflicting
names) is very easy to complete.



------ SLOW SINGLE IDE DRIVE ---------
NAMEDATALEN: 32

       89.34 real         1.85 user         0.13 sys
      146.87 real         1.51 user         3.91 sys

      246.63 real        66.11 user        19.21 sys


NAMEDATALEN: 64

       93.10 real         1.82 user         0.16 sys
      147.30 real         1.45 user         3.90 sys

      249.28 real        66.01 user        18.82 sys


NAMEDATALEN: 128

       99.13 real         1.80 user         0.51 sys
      169.47 real         1.87 user         4.54 sys

      279.16 real        67.93 user        29.72 sys


NAMEDATALEN: 256

      106.60 real         1.81 user         0.43 sys
      166.61 real         1.69 user         4.25 sys

      283.76 real        66.88 user        26.59 sys


NAMEDATALEN: 512

       88.13 real         1.83 user         0.22 sys
      160.77 real         1.64 user         4.48 sys

      259.56 real        67.54 user        21.89 sys


------ 2 IDE Raid 0 (Set 1 ---------
NAMEDATALEN: 32

       61.00 real         1.85 user         0.12 sys
       87.07 real         1.66 user         3.80 sys

      156.17 real        65.65 user        20.41 sys


NAMEDATALEN: 64

       60.20 real         1.79 user         0.19 sys
       93.26 real         1.54 user         4.00 sys

      162.31 real        65.79 user        20.30 sys


NAMEDATALEN: 128

       60.27 real         1.86 user         0.12 sys
       86.23 real         1.59 user         3.83 sys

      154.65 real        65.76 user        20.45 sys


NAMEDATALEN: 256

       62.17 real         1.86 user         0.12 sys
       89.92 real         1.31 user         4.13 sys

      160.37 real        66.58 user        20.08 sys


NAMEDATALEN: 512

       62.12 real         1.82 user         0.16 sys
       87.08 real         1.50 user         4.00 sys

      157.37 real        66.54 user        19.57 sys

------ 2 IDE Raid 0 (Set 2) ---------
NAMEDATALEN: 32

       62.33 real         1.83 user         0.13 sys
       91.30 real         1.62 user         3.96 sys

      161.91 real        65.93 user        20.80 sys


NAMEDATALEN: 64

       62.39 real         1.83 user         0.13 sys
       93.78 real         1.68 user         3.85 sys

      164.34 real        65.72 user        20.70 sys


NAMEDATALEN: 128

       62.91 real         1.84 user         0.15 sys
       90.87 real         1.58 user         3.91 sys

      161.86 real        66.14 user        20.21 sys


NAMEDATALEN: 256

       72.59 real         1.79 user         0.52 sys
       97.40 real         1.62 user         4.71 sys

      178.38 real        68.78 user        31.35 sys


NAMEDATALEN: 512

       80.64 real         1.87 user         0.41 sys
       99.19 real         1.64 user         4.87 sys

      188.45 real        67.33 user        35.22 sys

------ 2 IDE Raid 0 (Set 3) ---------
NAMEDATALEN: 32

       79.63 real         1.80 user         0.41 sys
       89.69 real         1.54 user         4.69 sys

      177.55 real        68.65 user        28.34 sys


NAMEDATALEN: 64

       74.94 real         1.89 user         0.52 sys
       91.44 real         1.70 user         4.32 sys

      174.74 real        66.25 user        33.59 sys


NAMEDATALEN: 128

       64.07 real         1.86 user         0.16 sys
       79.06 real         1.53 user         4.35 sys

      151.27 real        67.92 user        22.96 sys


NAMEDATALEN: 256

       78.43 real         1.81 user         0.58 sys
       89.23 real         1.58 user         4.27 sys

      175.71 real        66.94 user        37.41 sys


NAMEDATALEN: 512

       60.67 real         1.87 user         0.10 sys
       84.61 real         1.47 user         3.88 sys

      153.49 real        66.04 user        19.84 sys
23 Apr 2002 23:36:14 -0300
Rod Taylor <rbt@zort.ca>
namedatalen part 2 (cont'd)
On 23 Apr 2002 23:36:14 -0300
"Rod Taylor" <rbt@zort.ca> wrote:
> First is on pgbench -i (-s 5)
> Second is on pgbench -t 3000 -s 5

Haven't several people observed that the results from pgbench are
very inconsistent? Perhaps some results from OSDB would be worthwhile...

> The first test on a slow harddrive has a large effect for increasing the
> namedatalen length.
> 
> Second through 4th sets don't really show any issues when the drives are
> quite a bit quicker -- IBM Deskstars stripped).

AFAICS, the only consistent results are the first set (on the slow
IDE drive) -- in which the performance degredation is quite high.
Based on that data, I'd vote against making any changes to NAMEDATALEN.

For the other data sets, performance is inconsistent. I'd be more inclined
to write that off as simply unreliable data and not necessarily an
indication that high NAMEDATALEN values don't have a performance impact
on that machine.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Tue, 23 Apr 2002 22:50:37 -0400
Neil Conway <nconway@klamath.dyndns.org>
Re: namedatalen part 2 (cont'd)
> Haven't several people observed that the results from pgbench are
> very inconsistent? Perhaps some results from OSDB would be
worthwhile...

I've not looked very hard at OSDB.  But it doesn't seem to run out of
the box.

> Based on that data, I'd vote against making any changes to
NAMEDATALEN.

I'm fine with that so long as that SERIAL thing fixed before 7.3 is
released but someone was asking about benches with recent changes to
name hashing.   Seems degradation is closer to 10% per double rather
than 15% as before.



Tue, 23 Apr 2002 23:12:15 -0400
"Rod Taylor" <rbt@zort.ca>
Re: namedatalen part 2 (cont'd)
Neil Conway <nconway@klamath.dyndns.org> writes:
> ...Based on that data, I'd vote against making any changes to NAMEDATALEN.

It looked to me like the cost for going to NAMEDATALEN = 64 would be
reasonable.  Based on these numbers I'd have a problem with 128 or more.

But as you observe, pgbench numbers are not very repeatable.  It'd be
nice to have some similar experiments with another benchmark before
making a decision.

			regards, tom lane

Wed, 24 Apr 2002 01:40:01 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: namedatalen part 2 (cont'd) 
Tom Lane wrote:
> Neil Conway <nconway@klamath.dyndns.org> writes:
> > ...Based on that data, I'd vote against making any changes to NAMEDATALEN.
> 
> It looked to me like the cost for going to NAMEDATALEN = 64 would be
> reasonable.  Based on these numbers I'd have a problem with 128 or more.
> 
> But as you observe, pgbench numbers are not very repeatable.  It'd be
> nice to have some similar experiments with another benchmark before
> making a decision.

Yes, 64 looked like the appropriate value too.  Actually, I was
surprised to see as much of a slowdown as we did.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 24 Apr 2002 09:57:29 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: namedatalen part 2 (cont'd)
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Yes, 64 looked like the appropriate value too.  Actually, I was
> surprised to see as much of a slowdown as we did.

I was too.  pgbench runs the same backend(s) throughout the test,
so it shouldn't be paying anything meaningful in disk I/O for the
larger catalog size.  After the first set of queries all the relevant
catalog rows will be cached in syscache.  So where's the performance
hit coming from?

It'd be interesting to redo these runs with profiling turned on
and compare the profiles at, say, 32 and 512 to see where the time
is going for larger NAMEDATALEN.  Might be something that's easy
to fix once we identify it.

			regards, tom lane

Wed, 24 Apr 2002 10:20:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: namedatalen part 2 (cont'd) 