Message-ID: <460080A2.20800@drivefaster.net>
Date: Tue, 20 Mar 2007 18:47:30 -0600
From: Dan Harris <fbsd@drivefaster.net>
User-Agent: Thunderbird 2.0b2 (Macintosh/20070116)
MIME-Version: 1.0
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Determining server load from client
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I've found that it would be helpful to be able to tell how busy my 
dedicated PG server is ( Linux 2.6 kernel, v8.0.3 currently ) before 
pounding it with some OLAP-type queries.  Specifically, I have a 
multi-threaded client program that needs to run several thousand 
sequential queries.  I broke it into threads to take advantage of the 
multi-core architecture of the server hardware.  It would be very nice 
if I could check the load of the server at certain intervals to throttle 
the number of concurrent queries and mitigate load problems when other 
processes might be already inducing a significant load.

I have seen some other nice back-end things exposed through PG functions 
( e.g. database size on disk ) and wondered if there was anything 
applicable to this.  Even if it can't return the load average proper, is 
there anything else in the pg_* tables that might give me a clue how 
"busy" the server is for a period of time?

I've thought about allowing an ssh login without a keyphrase to log in 
and capture it.  But, the client process is running as an apache user. 
Giving the apache user a shell login to the DB box does not seem like a 
smart idea for obvious security reasons...

So far, that's all I can come up with, other than a dedicated socket 
server daemon on the DB machine to do it.

Any creative ideas are welcomed :)

Thanks

-Dan