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
#!/bin/sh
PGSRC=/home/rbt/postgresql/pgsqlnamedatalen
PGBASEPORT=6400
PGBASEBIN=/data/buildtree/dbname
LOG=/home/rbt/bench_namedatalen.log
for newDATALEN in 32 64 128 256 512 ; do
PGBIN=${PGBASEBIN}_${newDATALEN}
PGPORT=`echo "${PGBASEPORT}+${newDATALEN}" | bc`
sed -E 's/NAMEDATALEN\s[0-9]+/NAMEDATALEN ${newDATALEN}/g' ${PGSRC}/src/include/postgres_ext.h > ${PGSRC}/src/include/postgres_ext.h.tmp
mv ${PGSRC}/src/include/postgres_ext.h.tmp ${PGSRC}/src/include/postgres_ext.h
cd ${PGSRC}
./configure --prefix=${PGBIN} --with-pgport=${PGPORT} || (echo "UNABLE TO CONFIGURE"; exit)
make clean
make clean install
cd ${PGSRC}/contrib/pgbench
gmake install
${PGBIN}/bin/initdb -D ${PGBIN}/data || (echo "UNABLE TO INITIALIZE"; exit 1)
time -a -o ${LOG} ${PGBIN}/bin/postmaster -D ${PGBIN}/data -F &
sleep 5
echo "NAMEDATALEN: ${newDATALEN}" >> ${LOG}
echo >> ${LOG}
time -a -o ${LOG} ${PGBIN}/bin/pgbench -i -s 5 -d template1 -p ${PGPORT}
time -a -o ${LOG} ${PGBIN}/bin/pgbench -t 3000 -s 5 -d template1 -p ${PGPORT}
echo >> ${LOG}
${PGBIN}/bin/pg_ctl -D ${PGBIN}/data stop
echo >> ${LOG}
echo >> ${LOG}
rm -rf ${PGBIN}
done
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
