Re: PostgreSQL: CPU utilization creeping to 100%

On 4/2/13, Ian Lawrence Barwick <barwick(at)gmail(dot)com> wrote:
> 2013/4/3 David Noel <david(dot)i(dot)noel(at)gmail(dot)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

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")
)