Thread: 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
Attachment
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
> 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@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
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, Pennsylvania19026
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