Config review - Mailing list pgsql-performance

Postgresql is the backbone of our spam filtering system. Currently the
performance is OK. Wanted to know if someone could give this config a
quick run down and see if there is anything we can adjust here to smooth
out the performance. The IO Wait Times are outrageous, at times the load
will spike up to the 70 - 90 range.

Hardware:
Quad Opteron 2Ghz
Tyan Quad Opteron Board
16GB DDR Ram
Emulex LightPulse LP1050
EMC Clarion Fiber Array running Raid5
-----------------------------------------
Software:
RedHat Linux AS
Postgresql 7.4.6
-----------------------------------------
Detail:
pg_xlog is stored on a local 10k RPM SCSI drive.
The rest of the database is stored on the Fiber Array.

Currently the database is at a size of 87.6Gig. A Vacuum Analyze runs
every night and has been taking 4 or 5 hours to complete. Everything
seems to run fine for a while, then at some point the load goes through
the roof and the iowait % also goes way up. It will recover after a
little bit and then do the same thing all over again. When this happens
access to the web based user interface slows way down for our customers.
Any input for improvements to this config would be appreciated, Thanks.

------------------------------------------

------------------------------------------
Vacuum Output:

INFO:  analyzing "pg_catalog.pg_listener"
INFO:  "pg_listener": 0 pages, 0 rows sampled, 0 estimated total rows
INFO:  free space map: 79 relations, 1948399 pages stored; 5306160 total
pages needed
DETAIL:  Allocated FSM size: 500 relations + 2000000 pages = 11769 kB
shared memory.
VACUUM
--------------------------------------------

<--config-->

tcpip_socket = true
max_connections = 800
#superuser_reserved_connections = 2
port = 5432
#port = 9999
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#virtual_host = ''              # what interface to listen on; defaults
to any
#rendezvous_name = ''           # defaults to the computer name

# - Security & Authentication -

#authentication_timeout = 60    # 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false


#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------

# - Memory -

shared_buffers = 16000
sort_mem = 16384
vacuum_mem = 3200000

# - Free Space Map -

max_fsm_pages = 2000000
max_fsm_relations = 500

# - Kernel Resource Usage -

max_files_per_process = 100     # min 25
#preload_libraries = ''


#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------

# - Settings -

fsync = true                    # turns forced synchronization on or off
#wal_sync_method = fsync        # the default varies across platforms:
                                 # fsync, fdatasync, open_sync, or
open_datasync
wal_buffers = 64        # min 4, 8KB each

# - Checkpoints -

checkpoint_segments = 50        # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 60        # range 30-3600, in seconds
#checkpoint_warning = 30        # 0 is off, in seconds
#commit_delay = 0               # range 0-100000, in microseconds
#commit_siblings = 10           # range 1-1000


#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------

# - Planner Method Enabling -

#enable_hashagg = true
#enable_hashjoin = true
enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
#enable_seqscan = true
#enable_sort = true
#enable_tidscan = true

# - Planner Cost Constants -

effective_cache_size = 50000    # typically 8KB each
random_page_cost = 20           # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01          # (same)
#cpu_index_tuple_cost = 0.001   # (same)
#cpu_operator_cost = 0.0025     # (same)

# - Genetic Query Optimizer -

geqo = true
geqo_threshold = 11
geqo_effort = 1
geqo_generations = 0
geqo_pool_size = 0              # default based on tables in statement,
                                 # range 128-1024
geqo_selection_bias = 2.0       # range 1.5-2.0

# - Other Planner Options -

#default_statistics_target = 10 # range 1-1000
#from_collapse_limit = 8
#join_collapse_limit = 8        # 1 disables collapsing of explicit JOINs


#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------

# - Syslog -

syslog = 2                      # range 0-2; 0=stdout; 1=both; 2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

# - When to Log -

client_min_messages = error     # Values, in order of decreasing detail:
                                 #   debug5, debug4, debug3, debug2, debug1,
                                 #   log, info, notice, warning, error

log_min_messages = error        # Values, in order of decreasing detail:
                                 #   debug5, debug4, debug3, debug2, debug1,
                                 #   info, notice, warning, error, log,
fatal,
                                 #   panic

log_error_verbosity = terse   # terse, default, or verbose messages

#log_min_error_statement = panic # Values in order of increasing severity:
                                  #   debug5, debug4, debug3, debug2,
debug1,
                                  #   info, notice, warning, error,
panic(off)

#log_min_duration_statement = -1 # Log all statements whose
                                  # execution time exceeds the value, in
                                  # milliseconds.  Zero prints all queries.
                                  # Minus-one disables.

#silent_mode = false             # DO NOT USE without Syslog!

# - What to Log -

debug_print_parse = false
debug_print_rewritten = false
debug_print_plan = false
debug_pretty_print = false
log_connections = false
log_duration = false
log_pid = false
log_statement = false
log_timestamp = true
log_hostname = true
log_source_port = false


#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------

# - Statistics Monitoring -

log_parser_stats = false
log_planner_stats = false
log_executor_stats = false
log_statement_stats = false

# - Query/Index Statistics Collector -

#stats_start_collector = true
#stats_command_string = false
#stats_block_level = false
#stats_row_level = false
#stats_reset_on_server_start = true


#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '$user,public'   # schema names
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0          # 0 is disabled, in milliseconds

# - Locale and Formatting -

#datestyle = 'iso, mdy'
#timezone = unknown             # actually, defaults to TZ environment
setting
#australian_timezones = false
#extra_float_digits = 0         # min -15, max 2
#client_encoding = sql_ascii    # actually, defaults to database encoding

# These settings are initialized by initdb -- they may be changed
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

# - Other Defaults -

#explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000         # min 10


#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------

#deadlock_timeout = 1000        # in milliseconds
max_locks_per_transaction = 200 # min 10, ~260*max_connections bytes each


#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------

# - Previous Postgres Versions -

#add_missing_from = true
#regex_flavor = advanced        # advanced, extended, or basic
#sql_inheritance = true

# - Other Platforms & Clients -

#transform_null_equals = false

<--config-->


Thanks
--
---------------------------------------------------------------
Bryan Vest
ComNet Inc.
bright.net Network Administration/Network Operations
(888)-618-4638
net-admin@bright.net    Pager: pagenoc@bright.net
---------------------------------------------------------------