postgreSQL performance 8.2.6 vs 8.3.3 - Mailing list pgsql-performance

From Battle Mage
Subject postgreSQL performance 8.2.6 vs 8.3.3
Date
Msg-id f0afbb9b0902201334r3e35da87j85617fbde07a74b2@mail.gmail.com
Whole thread Raw
Responses Re: postgreSQL performance 8.2.6 vs 8.3.3  (Kenneth Marshall <ktm@rice.edu>)
Re: postgreSQL performance 8.2.6 vs 8.3.3  (David Rees <drees76@gmail.com>)
Re: postgreSQL performance 8.2.6 vs 8.3.3  (Scott Marlowe <scott.marlowe@gmail.com>)
List 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 =====


pgsql-performance by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: Benchmark comparing PostgreSQL, MySQL and Oracle
Next
From: "Jonah H. Harris"
Date:
Subject: Re: Benchmark comparing PostgreSQL, MySQL and Oracle