Increasing Shared_buffers = slow commits? - Mailing list pgsql-performance

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.X PostgreSQL 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