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 =====
