Re: PostgreSQL: CPU utilization creeping to 100%

On 4/2/13, John R Pierce <pierce(at)hogranch(dot)com> wrote:
> On 4/2/2013 3:35 PM, David Noel wrote:
>> The hardware is a Dell PowerEdge 1420, dual Xeon Nocona's, 3.2ghz,
>> 16gb ram. The disks are 4 Kingston HyperX SATA3's attached to a
>> HighPoint RocketRAID 2721 controller, ZFS, RAID10.
> .....
>> postgresql.conf, all standard/default except for:
>> max_connections = 256
>
> A) use a connection pool so you don't NEED 256 active database connections.
>
> B) shared_buffers, work_mem, and maintenance_work_mem all need to be
> tuned. I'd suggest 4gb, 16mb, 1gb respectively as a starting point on
> a 16GB ram system. if you can, shrink your max_connections by using a
> connection pooler (my target is generally no more than 2-4 active
> queries per CPU core or hardware thread). Ouch, Xeon Nocona was a
> single core, dual thread CPU, with rather poor performance, essentially
> just a Pentium-4... 3Ghz on a P4 is like 2Ghz on other CPUs.
>
> when you said raid10, do you mean zfs mirrored, or are you doing
> hardware raid10 in the Highpoint? I would have configured the raid
> card for JBOD, and done ZFS mirroring in the OS, so you can take
> advantage of ZFS's data integrity features. Those are consumer
> grade SSD's, are they even qualified for use with that Highpoint
> controller ?
>
>
> --
> john r pierce 37N 122W
> somewhere on the middle of the left coast

It looks like you guys were spot on, thanks. I've incorporated some of
the suggested values, done a little RTFM'ing (chapter 18.4), made a
few additional tweaks, and have brought things to a seemingly stable
state. Still testing, but so far so good. Glad it was such a simple
"fix".

Many thanks,

-David