#1 SQL Resource Starvation

[Kevin Sonney]

There seems to be a condition on the server where the
various processes for updating and reading the data re
using up all available connections to the database.

The symptoms of this are a significant number of open
SELECT postgres processes on the database server, an
inability to import new data, and a series of
pg_pconnect errors when trying to view the current stats.

[Kevin Sonney]

Logged In: YES
user_id=317561

I beleive this can be fixed or at least addressed by some
tunings on the server. So far I have changed the following :

increased the max connections available from the database to 128
increased the buffer size on the database to 256
set a hard limit for persistant connections in php.ini to 64
set a hard limit for total connections in php.ini to 96
set the invalid connection detection in php.ini to on

This shoudl leave enough connections for the import process
to complete, keeping the incomming data updated. I do not
know if this will eliminate the web ui errors, but it should
help resolve things and keep the connections under control.

[Kevin Sonney]

Logged In: YES
user_id=317561

All that being said, it looks like the real issue is in the
script that generates the stats every 10 mins or so. I'm
going to reduce it to every 15 mins, and not 10 mins, which
should help (some).

Long term, I need to use a different method. I *THINK*
wget/curl is timing out on me, and causing php to leave open
connections on the table. The apache logs and hung sessions
in the process list on my production server seem to
corroborate this.

[Kevin Sonney]

Logged In: YES
user_id=317561

I have replaced the php based updater with a perl based one.
We'll see if this helps or not.

[Kevin Sonney]

Logged In: YES
user_id=317561

Still testing the perl tool. it wasn't updating the cache,
so that had to be fixed.