Hyperthreading (was: Two identical systems, radically different performance) - Mailing list pgsql-performance

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