From owner-freebsd-database@FreeBSD.ORG Thu Apr 4 23:17:37 2013 Return-Path: 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 D40C1B06 for ; Thu, 4 Apr 2013 23:17:37 +0000 (UTC) (envelope-from david.i.noel@gmail.com) Received: from mail-pd0-f177.google.com (mail-pd0-f177.google.com [209.85.192.177]) by mx1.freebsd.org (Postfix) with ESMTP id B1D0127B for ; Thu, 4 Apr 2013 23:17:37 +0000 (UTC) Received: by mail-pd0-f177.google.com with SMTP id u11so1679181pdi.22 for ; Thu, 04 Apr 2013 16:17:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:x-received:reply-to:in-reply-to:references:date :message-id:subject:from:to:cc:content-type; bh=pDQMOrAIjcidox6m9kv4awASMxp2+UGxJZobUGf8jfQ=; b=I1Islv6Ebf2kkaWoMp8LhAI6Ay+dGLqwv4jQYogBf+546qhpc0BEhewGf2KJ4xAtd1 ZupecdVHRbxTDxb/a4tJztt0VBW/f5yp8XVc/ZTRyHM5LfbXisxTuhmwW2VIimVfw7L5 6ErkKeiuSq3JIVjVbFBj9qrPRPET6hodj/LuusqVFscHuTIT0NMkA4sEqYA5Ack8UaW6 WOp8AbXhcKc0zFc1pK072KD4kpzDX16M493qlrgUgRbwy8b2ltiQ14tXJtV7rObIOFv0 +3Jwr4iDBBbDHNbxpTUmTAGnhCC4+V6uyav6gEvRJ6FoTc0ka+Bfp8HvsAcAEO4OjWf6 65rQ== MIME-Version: 1.0 X-Received: by 10.68.201.227 with SMTP id kd3mr11275562pbc.65.1365117457186; Thu, 04 Apr 2013 16:17:37 -0700 (PDT) Received: by 10.68.90.100 with HTTP; Thu, 4 Apr 2013 16:17:37 -0700 (PDT) In-Reply-To: <1365002752.81518.YahooMailNeo@web162905.mail.bf1.yahoo.com> References: <1364943147.84745.YahooMailNeo@web162902.mail.bf1.yahoo.com> <1365002752.81518.YahooMailNeo@web162905.mail.bf1.yahoo.com> Date: Thu, 4 Apr 2013 18:17:37 -0500 Message-ID: Subject: Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100% From: David Noel To: Kevin Grittner Content-Type: text/plain; charset=ISO-8859-1 Cc: "freebsd-database@freebsd.org" , PGSQL Mailing List X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list Reply-To: David.I.Noel@gmail.com List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 04 Apr 2013 23:17:37 -0000 On 4/3/13, Kevin Grittner wrote: > David Noel wrote: >> On 4/2/13, Kevin Grittner wrote: >>> David Noel wrote: >>> >>>> 'select * from pg_stat_activity' shows that the queries are not >>>> waiting, and are in the idle state. >>> >>> The process is idle or the process is running the query? If the >>> latter, what do you mean when you say "the queries ... are in the >>> idle state"? >> >> select * from pg_stat_activity returns a table containing a column >> labeled "state". When the postgres process is at 100% utilization and >> the application has hung, this query returns the value "idle" in that >> field. When things are running properly, as they are for the moment >> now, the value is "active". > > When a connection shows "idle", the related process should be > showing zero CPU usage. Are you seeing something different? If > so, is the transaction or query start time changing from one sample > to the next? The related process was maxed out at or near 100%. I've scanned chapter 18.4 of the documentation and along with some suggested values from a reply earlier have brought things to the point where it *seems* to be stable. I'm continuing to test though, so if the issue pops up again I'll check to see whether the query start time is changing or not. Thanks for the help, -David