> Number of logical CPUs: 16 (4x Quadcore Xeon E5520 @ 2.27GHz)
> RAM: 16GB
> Concurrent connections (according to our monitoring tool): 7 (min), 74
> (avg), 197 (max)
Your current issue may be IO wait, but a connection pool isn't far off
in your future either.
> max_connections = 200
> work_mem = 256MB
That is a foot-gun waiting to go off. If 32 queries manage to
simultaneously each need 256MB to sort, your cache is blown out and
the server is out of RAM. If your application is like most, you need a
huge work_mem for, maybe, 1% of your queries. You can request it high
on a per connection/per query basis for the queries that need it, and
set the default to a low, safe figure.
> HDD: 2x 120 GB OCZ Vertex 2 SSD; RAID 1
> random_page_cost = 2.0
I thought these drives were a lot better at random IO than this gives
them credit for. The are certainly no better at sequential IO than
(good) conventional drives. You might have a lot of room to turn this
down even smaller.