Intermittent slowdowns, connection delays

Hi All,

We have a Postgres 7.4.1 server running on FreeBSD 5.2. Hardware is a Dual
Xeon 2.6 (HT enabled), 2 GB Memory, 3Ware SATA RAID-5 w/ 4 7200 RPM Seagate
disks and gigabit Intel Server Ethernet. The server is dedicated to serving
data to our web-based CMS.

We have a few web servers load balanced, and we do around 1M page
impressions per day. Our website is highly personalized, and we've
optimized it to limit the number of queries, but we still see between 2 and
3 SELECT's (with JOIN's) and 1 UPDATE per page load, selectively more - a
fair volume.

The single UPDATE per page load is updating a timestamp in a small table
(about 150,000 rows) with only 1 index (on the 1 field that needs to be
matched).

We're seeing some intermittent spikes in query time as actual connection
time. I.e., during these seemingly random spikes, our debug output looks
like this (times from start of HTTP request):

SQL CONNECTION CREATING 'gf'
0.0015 - ESTABLISHING CONNECTION
1.7113 - CONNECTION OK
SQL QUERY ID 1 COST 0.8155 ROWS 1
SQL QUERY ID 2 COST 0.5607 ROWS 14
.. etc.. (all queries taking more time than normal, see below)

Refresh the page 2 seconds later, and we'll get:

SQL CONNECTION CREATING 'gf'
0.0017 - ESTABLISHING CONNECTION
0.0086 - CONNECTION OK
SQL QUERY ID 1 COST 0.0128 ROWS 1
SQL QUERY ID 2 COST 0.0033 ROWS 14
.. etc.. (with same queries)

Indeed, during these types, it takes a moment for "psql" to connect on the
command line (from the same machine using a local file socket), so it's not
a network issue or a web-server issue. During these spurts, there's nothing
too out of the ordinary in vmstat, systat or top.

These programs show that we're not using much CPU (usually 60-80% idle), and
disks usage is virtually nil. I've attached 60 seconds of "vmstat 5".
Memory usage looks like this (constantly):

Mem: 110M Active, 1470M Inact, 206M Wired, 61M Cache, 112M Buf, 26M Free

I've cleaned up and tested query after query, and nothing is a "hog". On an
idle server, every query will execute in < 0.05 sec. Perhaps some of you
veterans have ideas?

Thanks,

Jason Coene
Gotfrag eSports
585-598-6621 Phone
585-598-6633 Fax
jcoene(at)gotfrag(dot)com
http://www.gotfrag.com