From owner-freebsd-database@FreeBSD.ORG Tue Apr 2 23:03:58 2013 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.FreeBSD.org [8.8.178.115]) by hub.freebsd.org (Postfix) with ESMTP id A68B325F for ; Tue, 2 Apr 2013 23:03:58 +0000 (UTC) (envelope-from david.i.noel@gmail.com) Received: from mail-pa0-f43.google.com (mail-pa0-f43.google.com [209.85.220.43]) by mx1.freebsd.org (Postfix) with ESMTP id 8518021A for ; Tue, 2 Apr 2013 23:03:58 +0000 (UTC) Received: by mail-pa0-f43.google.com with SMTP id hz11so552473pad.30 for ; Tue, 02 Apr 2013 16:03:52 -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=nT3HenehaBJm4mM1id2amaEMONruv9w4uVDhkWmxVY0=; b=FOOxIixa4/OlzFQEe+ir1IRH4sgJXjwpIERASsbWdod6+kUID9r9m95PiPDC8XMOEP kgwQHONUDRqbHnPP5V/NEt8kD4rRWqqKD8kPh21Rn5sp5MWqu2tEyk+IVQCS9oyKm6vT 3O2YIGv9NsFO739ZbiIkiDeAnZK8xKNStDDueDO/GDzbvisj3Gb0qrT+0gCiyl+2DAnc gXHJuVoNZfIjPmKuCEOtRNUuG0lkSKzEQOccu2SiXyfifgDeV/t3UorMmN5hlRt55FWF RakRImg2/B26USAPyIrwcKd84ZygXaZGTCAUAKWguxZCDMjeHbWW6AUv5Mmt/xDN10/+ oMuw== MIME-Version: 1.0 X-Received: by 10.68.40.165 with SMTP id y5mr27237368pbk.218.1364943832626; Tue, 02 Apr 2013 16:03:52 -0700 (PDT) Received: by 10.68.90.100 with HTTP; Tue, 2 Apr 2013 16:03:52 -0700 (PDT) In-Reply-To: References: Date: Tue, 2 Apr 2013 18:03:52 -0500 Message-ID: Subject: Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100% From: David Noel To: Ian Lawrence Barwick 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: Tue, 02 Apr 2013 23:03:58 -0000 On 4/2/13, Ian Lawrence Barwick wrote: > 2013/4/3 David Noel : >> 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 Thanks for the feedback. I'll look into pg tunings. Hopefully the problem's there somewhere. explain analyze select * from ((select * from "crawlq" where "Active" = 'true' AND "TimeoutDate" <= now()) UNION (select * from "crawlq" where "Active" = 'false')) as RS order by "NextCrawlDate" asc limit 1 "Limit (cost=4092.39..4092.39 rows=1 width=203) (actual time=23.447..23.450 rows=1 loops=1)" " -> Sort (cost=4092.39..4096.34 rows=1583 width=203) (actual time=23.442..23.442 rows=1 loops=1)" " Sort Key: public.crawlq."NextCrawlDate"" " Sort Method: top-N heapsort Memory: 25kB" " -> HashAggregate (cost=4052.81..4068.64 rows=1583 width=236) (actual time=18.195..20.486 rows=877 loops=1)" " -> Append (cost=0.00..3997.41 rows=1583 width=236) (actual time=0.015..13.423 rows=877 loops=1)" " -> Seq Scan on crawlq (cost=0.00..1995.14 rows=18 width=236) (actual time=0.011..3.397 rows=49 loops=1)" " Filter: ("Active" AND ("TimeoutDate" <= now()))" " Rows Removed by Filter: 828" " -> Seq Scan on crawlq (cost=0.00..1986.43 rows=1565 width=236) (actual time=0.013..7.152 rows=828 loops=1)" " Filter: (NOT "Active")" " Rows Removed by Filter: 49" "Total runtime: 23.633 ms" Relevant rows from table crawlq: CREATE TABLE crawlq ( "URL" text NOT NULL, "LastCrawlDate" timestamp with time zone DEFAULT now(), "NextCrawlDate" timestamp with time zone, "Active" boolean DEFAULT false, "TimeoutDate" timestamp with time zone, CONSTRAINT crawlq_pkey PRIMARY KEY ("URL") )