Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 48BEB9F9F14 for ; Tue, 28 Aug 2007 01:15:09 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 40360-09 for ; Tue, 28 Aug 2007 01:15:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from outbound-mail-49.bluehost.com (outbound-mail-49.bluehost.com [69.89.18.18]) by postgresql.org (Postfix) with SMTP id C44919F9EDC for ; Tue, 28 Aug 2007 01:15:01 -0300 (ADT) Received: (qmail 18769 invoked by uid 0); 28 Aug 2007 04:14:58 -0000 Received: from unknown (HELO box54.bluehost.com) (69.89.20.54) by mailproxy3.bluehost.com with SMTP; 28 Aug 2007 04:14:58 -0000 Received: from vdsl-151-118-147-91.dnvr.qwest.net ([151.118.147.91] helo=new-host.home) by box54.bluehost.com with esmtp (Exim 4.66) (envelope-from ) id 1IPsTh-0000K0-Kq for pgsql-performance@postgresql.org; Mon, 27 Aug 2007 22:14:57 -0600 From: Kevin Kempter To: pgsql-performance@postgresql.org Subject: server performance issues - suggestions for tuning Date: Mon, 27 Aug 2007 22:13:14 -0600 User-Agent: KMail/1.9.7 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200708272213.14277.kevin@kevinkempterllc.com> X-Identified-User: {840:box54.bluehost.com:kevinkem:kevinkempterllc.com} {sentby:bopbeforesmtp 151.118.147.91 authed with ossfusion.com} X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/333 X-Sequence-Number: 26463 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 =============================================