Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group