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

Great, thanks. I'll get those tunables modified and see if that
smooths things out.

> 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.

I won't tell them you said that. Feelings might get hurt.

> 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.

RAID10 under ZFS. Yes, JBOD. ZFS is neat!

> Those are consumer grade SSD's, are they even qualified for use
> with that Highpoint controller?

Consumer grade SSD's, indeed. They've held together so far though.
Fingers crossed.

Thanks again,

-David