I've confirmed that hyperthreading causes a huge drop in performance on a
2x4-core Intel Xeon E5620 2.40GHz system. The bottom line is:
~3200 TPS max with hyperthreading
~9000 TPS max without hyprethreading
Here are my results.
"Hyprethreads" (Run1) is "out of the box" with hyperthreads enabled. Only
one column of hyperthread results are shown, but prior to today's work I
ran this a dozen or so times, and "Run1" is very representative of all
those runs. I also rebooted and confirmed that rebooting had no effect.
"+NoHyperthreads" (Run2-5) involved rebooting into the BIOS and disabling
hyperthreads.
"+NoAutoVacuum" (Run6-8) means I turned off autovacuum in the
postgresql.conf file and restarted Postgres (with hyperthreads still
disabled).
Hyperthreads +NoHyperthreads +NoAutoVacuum
------------ ---------------------- ----------------
-c -t Run1 Run2 Run3 Run4 Run5 Run6 Run7 Run8
5 20000 2733 2152 2352 2398 2769 2767 2777 4463
10 10000 2783 2404 3529 3365 4397 4457 4217 4172
20 5000 3241 3128 3728 5170 5253 5252 4832 8123
30 3333 2987 5699 6180 8173 8259 6435 8225 8123
40 2500 2739 7133 6507 9298 7845 9133 9298 9230
50 2000 2119 5420 5020 8411 5670 9344 7624 8304
I'll be upgrading to 8.4.14 and making more changes to postgres.conf based
on feedback. The server is available for a day or so for more tests if
anyone has suggestions.
Here's how I got these results:
su postgres
unset LANG
export LD_LIBRARY_PATH=/usr/local/pgsql/lib
/usr/local/pgsql/bin/initdb --pgdata=/data/postgres/main \
--xlogdir=/postgres_xlog/xlog --username=postgres
Edit config file:
max_connections = 500
shared_buffers = 1000MB
work_mem = 128MB
synchronous_commit = off
full_page_writes = off
wal_buffers = 256kB
checkpoint_segments = 30
effective_cache_size = 4GB
track_activities = on
track_counts = on
track_functions = none
autovacuum = on
autovacuum_naptime = 5min
escape_string_warning = off
createuser -U postgres test
createdb -U postgres -O test test
pgbench -i -s 100 -U test
for p in "5 20000" "10 10000" "20 5000" "30 3333" "40 2500" "50 2000" ; do
echo
c=`echo $p | cut -d' ' -f1`
t=`echo $p | cut -d' ' -f2`
cmd="pgbench -U test -c $c -t $t"
echo "--------- $cmd ---------"
$cmd
done
The hardware:
CPU: 2x4-core Intex Xeon E5620 2.40 GHz
Memory: 12 GB DDR EC
Disks: 12x500GB disks (Western Digital 7200RPM SATA)
XLOG: 2 disks, RAID1 ext2
PGDATA: 8 disks, RAID10
3WARE 9650SE-12ML with battery-backed cache. The admin tool (tw_cli)
indicates that the battery is charged and the cache is working on both
units.
Linux: 2.6.32-41-server #94-Ubuntu SMP (new server's disk was
actually cloned from old server).
Postgres: 8.4.4
Craig
