Increasing Shared_buffers = slow commits?

Hi everyone,

I am testing my shared_buffers pool and am running into a problem with slow
inserts and commits. I was reading in several places that in the
8.XPostgreSQL engines should set the shared_buffers closer to 25% of
the
systems memory. On me development system, I have done that. We have 9GB of
memory on the machine and I set my shared_buffers = 292188 (~25% of total
memory).

When my users logged in today, they are noticing the system is much slower.
Tracing my log files, I am seeing that most of the commits are taking over
1sec. I am seeing a range of 1-5 seconds per commit.

What is the correlation here between the shared_buffers and the disk
activity? This is not something I would have expected at all.

I was wanting to test for improved performance so I can have a good basis
for making changes in my production systems.

My postgresql.conf is pasted below.

Thanks for any comments/clarifications,

chris
PG 8.1.3
RH 4 AS

# -----------------------------
# PostgreSQL configuration file
# -----------------------------

listen_addresses = '*' # what IP address(es) to listen on;

port = 50001

max_connections = 1024

superuser_reserved_connections = 10

shared_buffers = 292188 # setting to 25% of memory

max_prepared_transactions = 256 # can be 0 or more

work_mem = 16384 # min 64, size in KB

maintenance_work_mem = 1048576 # min 1024, size in KB

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

max_fsm_relations = 20000 # min 100, ~70 bytes each

vacuum_cost_delay = 0 # 0-1000 milliseconds

vacuum_cost_page_hit = 0 # 0-10000 credits

vacuum_cost_page_miss = 0 # 0-10000 credits

vacuum_cost_page_dirty = 0 # 0-10000 credits

vacuum_cost_limit = 1 # 0-10000 credits

wal_buffers = 64 # min 4, 8KB each

checkpoint_segments = 256 # in logfile segments, min 1, 16MB each

checkpoint_timeout = 300 # range 30-3600, in seconds

archive_command = '/home/postgres/bin/archive_pg_xlog.sh %p %f 50001' #
command to use to archive a logfile

effective_cache_size = 383490 # typically 8KB each

random_page_cost = 2 # units are one sequential page fetch

default_statistics_target = 100 # range 1-1000

constraint_exclusion = on

redirect_stderr = on # Enable capturing of stderr into log

log_directory = 'pg_log' # Directory where log files are written

log_truncate_on_rotation = on # If on, any existing log file of
the same

log_rotation_age = 1440 # Automatic rotation of logfiles will

log_rotation_size = 1048576 # Automatic rotation of logfiles will

log_min_messages = debug2 # Values, in order of decreasing detail:

log_min_duration_statement = 0 # -1 is disabled, 0 logs all
statements

log_connections = on

log_disconnections = on

log_duration = on

log_line_prefix = '%d,%p,%u,%m,%c,%l,%s,%x,%i,' # Special values:

log_statement = 'all' # none, mod, ddl, all

stats_start_collector = on

stats_command_string = on

stats_block_level = on

stats_row_level = on

stats_reset_on_server_start = on

autovacuum = on # enable autovacuum subprocess?

autovacuum_naptime = 60 # time between autovacuum runs, in secs

autovacuum_vacuum_threshold = 1000 # min # of tuple updates before

autovacuum_analyze_threshold = 500 # min # of tuple updates before

autovacuum_vacuum_scale_factor = 0.001 # fraction of rel size before

autovacuum_analyze_scale_factor = 0.0005 # fraction of rel size before

autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for

autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for

statement_timeout = 0 # 0 is disabled, in milliseconds

lc_messages = 'C' # locale for system error message

lc_monetary = 'C' # locale for monetary formatting

lc_numeric = 'C' # locale for number formatting

lc_time = 'C' # locale for time formatting

add_missing_from = on