Re: PostgreSQL: CPU utilization creeping to 100%

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