server performance issues - suggestions for tuning

From: Kevin Kempter <kevin(at)kevinkempterllc(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: server performance issues - suggestions for tuning
Date: 2007-08-28 04:13:14
Message-ID: 200708272213.14277.kevin@kevinkempterllc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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
=============================================

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Huxton 2007-08-28 07:50:54 Re: server performance issues - suggestions for tuning
Previous Message Kevin Kempter 2007-08-27 22:06:00 Re: significant vacuum issues - looking for suggestions