postgreSQL performance 8.2.6 vs 8.3.3

From: Battle Mage <battlemage(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: postgreSQL performance 8.2.6 vs 8.3.3
Date: 2009-02-20 21:34:23
Message-ID: f0afbb9b0902201334r3e35da87j85617fbde07a74b2@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I have a server box that has 4GB of RAM, Quad core CPU AMD Opteron 200.152
Mhz (1024 KB cache size each) with plenty of hard drive space.

I installed both postgresql 8.2.6 and 8.3.3 on it. I've created a basic
test db and used
pgbench -i -s 1 -U test -h localhost test
to create a sample test db.

Then, to benchmark the postgreSQLs, I executed this separately on each of
them:
pg_bench -h localhost -d test -t 2000 -c 50 -s 50 -U test
(2000 transactions per client, 50 clients, scalability factor of 50)

Using the above,
I get on postgreSQL 8.2.6:
Load average: Between 3.4 and 4.3
tps = 589 (including connections establishing)
tps = 590 (excluding connections establishing)

I get on postgreSQL 8.3.3
Load: Between 4.5 and 5.6
tps = 949 (including connections establishing)
tps = 951 (excluding connections establishing)

The amount of tps almost doubled, which is good, but i'm worried about the
load. For my application, a load increase is bad and I'd like to keep it
just like in 8.2.6 (a load average between 3.4 and 4.3). What parameters
should I work with to decrease the resulting load average at the expense of
tps?

Down below is my 8.3.3 configuration file. I removed everything that is
commented since if it's commented, it's default value. I also removed from
the sample below parameters related to logging.

===== postgresql.conf begins =====

port = 5432 # (change requires restart)
max_connections = 180 # (change requires restart)
superuser_reserved_connections = 5 # (change requires restart)
unix_socket_directory = '/var/run/postgresql' # (change requires
restart)
ssl = off # (change requires restart)

shared_buffers = 512MB # min 128kB or max_connections*16kB

temp_buffers = 8MB # min 800kB
max_prepared_transactions = 5 # can be 0 or more

work_mem = 16MB # min 64kB
maintenance_work_mem = 512MB # min 1MB
max_stack_depth = 2MB # min 100kB

# - Free Space Map -

max_fsm_pages = 2400000 # min max_fsm_relations*16, 6 bytes each

vacuum_cost_delay = 0 # 0-1000 milliseconds
vacuum_cost_page_hit = 1 # 0-10000 credits
vacuum_cost_page_miss = 10 # 0-10000 credits
vacuum_cost_page_dirty = 20 # 0-10000 credits
vacuum_cost_limit = 200 # 1-10000 credits

fsync = off # turns forced synchronization on or off

#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------

seq_page_cost = 1.0 # measured on an arbitrary scale
random_page_cost = 3.0 # same scale as above
effective_cache_size = 1024MB
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

autovacuum = on # Enable autovacuum subprocess? 'on'
autovacuum_naptime = 1min # time between autovacuum runs
autovacuum_vacuum_threshold = 500 # min number of row updates before
autovacuum_analyze_threshold = 250 # min number of row updates before
autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before
vacuum
autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
analyze
autovacuum_vacuum_cost_delay = 0 # default vacuum cost delay for
autovacuum_vacuum_cost_limit = 200 # default vacuum cost limit for

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
datestyle = 'iso, mdy'
timezone = UTC # actually, defaults to TZ environment
lc_messages = 'en_US.UTF-8' # locale for system error message
# strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting

#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------

escape_string_warning = off

===== postgresql.conf ends =====

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jonah H. Harris 2009-02-20 21:34:56 Re: Benchmark comparing PostgreSQL, MySQL and Oracle
Previous Message Merlin Moncure 2009-02-20 20:40:16 Re: Benchmark comparing PostgreSQL, MySQL and Oracle