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