From owner-freebsd-database@FreeBSD.ORG  Tue Apr  2 22:44:57 2013
Return-Path: <owner-freebsd-database@FreeBSD.ORG>
Delivered-To: freebsd-database@freebsd.org
Received: from mx1.freebsd.org (mx1.freebsd.org
 [IPv6:2001:1900:2254:206a::19:1])
 by hub.freebsd.org (Postfix) with ESMTP id 289ECD7E
 for <freebsd-database@freebsd.org>; Tue,  2 Apr 2013 22:44:57 +0000 (UTC)
 (envelope-from barwick@gmail.com)
Received: from mail-ie0-x236.google.com (mail-ie0-x236.google.com
 [IPv6:2607:f8b0:4001:c03::236])
 by mx1.freebsd.org (Postfix) with ESMTP id 0075DE4
 for <freebsd-database@freebsd.org>; Tue,  2 Apr 2013 22:44:56 +0000 (UTC)
Received: by mail-ie0-f182.google.com with SMTP id at1so1036321iec.27
 for <freebsd-database@freebsd.org>; Tue, 02 Apr 2013 15:44:56 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=mime-version:x-received:in-reply-to:references:date:message-id
 :subject:from:to:cc:content-type;
 bh=IpSZxtPEcH0APZzLst2MtkIsUzsBDQ+tDDPRARDTWPA=;
 b=EhMesYp+Ikf/IyOvG5cyKu/Uoxtvs3Zgg49UFoDZynXbozF5Q2PhkbiWP+Nby/qBIs
 uoCUgxIkHWG/4MZbbgb6gX9zgRYNNYssJ+1BeFAloAaBcAdnBqbeAhYtlqyJiqMQoM41
 nTdA74gNGrSsIl5DTqfkRhFbtd/30PWsX7DkQm7K29VdU5VCSyfsc09LDbH0jPDkLPmF
 gh1rmyJYa69DWuTOunDbL+UM6loIZ8kPAd1ie+tSyOU0hWEd43uOrbPdMV9Cx7r/Y3/f
 dYhRKHxqqEOdK2SmqmftjjwG9RYn6mxp/aPzuW7ywy/y7Jyuw4D1G3T7Zyb9eNSuqQUf
 PGvw==
MIME-Version: 1.0
X-Received: by 10.50.236.100 with SMTP id ut4mr5635129igc.86.1364942696599;
 Tue, 02 Apr 2013 15:44:56 -0700 (PDT)
Received: by 10.50.106.166 with HTTP; Tue, 2 Apr 2013 15:44:56 -0700 (PDT)
In-Reply-To: <CAHAXwYDg7ijxaG8K8vHMQci552txe94U4KKKJ4cZbUHT5UGXcQ@mail.gmail.com>
References: <CAHAXwYDg7ijxaG8K8vHMQci552txe94U4KKKJ4cZbUHT5UGXcQ@mail.gmail.com>
Date: Wed, 3 Apr 2013 07:44:56 +0900
Message-ID: <CAB8KJ=h6MJV2ndETgD1zYwPxWj1rtzUbyPf3swRyQ8vBc=z92g@mail.gmail.com>
Subject: Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100%
From: Ian Lawrence Barwick <barwick@gmail.com>
To: David.I.Noel@gmail.com
Content-Type: text/plain; charset=UTF-8
Cc: freebsd-database@freebsd.org,
 PGSQL Mailing List <pgsql-general@postgresql.org>
X-BeenThere: freebsd-database@freebsd.org
X-Mailman-Version: 2.1.14
Precedence: list
List-Id: Database use and development under FreeBSD
 <freebsd-database.freebsd.org>
List-Unsubscribe: <http://lists.freebsd.org/mailman/options/freebsd-database>, 
 <mailto:freebsd-database-request@freebsd.org?subject=unsubscribe>
List-Archive: <http://lists.freebsd.org/pipermail/freebsd-database>
List-Post: <mailto:freebsd-database@freebsd.org>
List-Help: <mailto:freebsd-database-request@freebsd.org?subject=help>
List-Subscribe: <http://lists.freebsd.org/mailman/listinfo/freebsd-database>, 
 <mailto:freebsd-database-request@freebsd.org?subject=subscribe>
X-List-Received-Date: Tue, 02 Apr 2013 22:44:57 -0000

2013/4/3 David Noel <david.i.noel@gmail.com>:
> I'm running into a strange issue whereby my postgres processes are
> slowly creeping to 100% CPU utilization. I'm running
> postgresql-server-9.2.3, FreeBSD 8.3-RELEASE-p6, and using the
> postgresql-9.2-1002.jdbc4 driver.

(...)
> postgresql.conf, all standard/default except for:
> max_connections = 256

It's very likely the default settings are woefully inadequate for your
server; some basic
tuning (especially the value of shared_buffers and other
memory-related parameters)
should help.

> Any thoughts? What other information can I provide?

Sample EXPLAIN/EXPLAIN ANALYZE output from the query in question, and
if possible relevant table definitions etc. would certainly be useful.

Regards

Ian Barwick