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
=============================================
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
