namedatalen part 2 (cont'd)

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

On 23 Apr 2002 23:36:14 -0300
"Rod Taylor" <rbt(at)zort(dot)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(at)rogers(dot)com>
PGP Key ID: DB3C29FC

> 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.

Neil Conway <nconway(at)klamath(dot)dyndns(dot)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

Tom Lane wrote:
> Neil Conway <nconway(at)klamath(dot)dyndns(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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