server performance issues - suggestions for tuning - Mailing list pgsql-performance

Hi List;

I've just inherited multiple postgres database servers in multiple data
centers across the US and Europe via a new contract I've just started.

Each night during the nightly batch processing several of the servers (2 in
particular) slow to a crawl - they are dedicated postgres database servers.
There is a lot of database activity going on sometimes upwards of 200
concurrent queries however I just dont think that the machines should be this
pegged. I am in the process of cleaning up dead space - their #1 fix for
performance issues in the past is to kill the current vacuum process.
Likewise I've just bumped shared_buffers to 150000 and work_mem to 250000.

Even at that I still see slow processing/high system loads at nite.I have
noticed that killing the current vacuum process (autovacuum is turned on)
speeds up the entire machine significantly.

The servers are 4-CPU intel boxes (not dual-core) with 4Gig of memory and
attached to raid-10 array's

Any thoughts on where to start?

Below are the current/relevant/changed postgresql.conf settings.

Thanks in advance...

/Kevin




============== postgresql.conf (partial listing)========================
#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '$user,public'        # schema names
#default_tablespace = ''        # a tablespace name, '' uses
                    # the default
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#statement_timeout = 0            # 0 is disabled, in milliseconds

# - Locale and Formatting -

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

# These settings are initialized by initdb -- they might 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 = on
#dynamic_library_path = '$libdir'


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

#deadlock_timeout = 1000        # in milliseconds
#max_locks_per_transaction = 64        # min 10
# note: each lock table slot uses ~220 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.


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

# - Previous Postgres Versions -

#add_missing_from = off
#backslash_quote = safe_encoding    # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = off
#regex_flavor = advanced        # advanced, extended, or basic
#sql_inheritance = on

# - Other Platforms & Clients -

#transform_null_equals = off


#---------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#---------------------------------------------------------------------------

#custom_variable_classes = ''        # list of custom variable class names
=============================================