pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDetermining server load from client
5+ messages / 4 participants
[nested] [flat]
* Determining server load from client
@ 2007-03-21 00:47 Dan Harris <fbsd@drivefaster.net>
0 siblings, 4 replies; 5+ messages in thread
From: Dan Harris @ 2007-03-21 00:47 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Determining server load from client
@ 2007-03-21 01:15 Craig A. James <cjames@modgraph-usa.com>
parent: Dan Harris <fbsd@drivefaster.net>
3 siblings, 0 replies; 5+ messages in thread
From: Craig A. James @ 2007-03-21 01:15 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
Dan Harris wrote:
> I've found that it would be helpful to be able to tell how busy my
> dedicated PG server is ...
>
> 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.
I'd write a simple pg-perl function to do this. You can access operating-system calls to find out the system's load. But notice that you need "Untrusted Perl" to do this, so you can only do it on a system where you trust every application that connects to your database. Something like this:
create or replace function get_stats()
returns text as '
open(STAT, "</proc/stat");
my @stats = <STAT>;
close STAT;
return join("", @stats);
' language plperlu;
See http://www.postgresql.org/docs/8.1/interactive/plperl-trusted.html
Craig
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Determining server load from client
@ 2007-03-21 01:22 Jim Buttafuoco <jim@contactbda.com>
parent: Dan Harris <fbsd@drivefaster.net>
3 siblings, 0 replies; 5+ messages in thread
From: Jim Buttafuoco @ 2007-03-21 01:22 UTC (permalink / raw)
To: 'Dan Harris' <fbsd@drivefaster.net>; pgsql-performance
Dan
Use the following plperlu function
create or replace function LoadAVG()
returns record
as
$$
use Sys::Statistics::Linux::LoadAVG;
my $lxs = new Sys::Statistics::Linux::LoadAVG;
my $stats = $lxs->get;
return $stats;
$$
language plperlu;
select * from LoadAVg() as (avg_1 float,avg_5 float,avg_15 float);
The Sys::Statistics::Linux has all kind of info (from the /proc) file
system.
Jim
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Dan Harris
Sent: Tuesday, March 20, 2007 8:48 PM
To: PostgreSQL Performance
Subject: [PERFORM] Determining server load from client
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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Determining server load from client
@ 2007-03-21 01:24 Joe Healy <joe@omc-international.com.au>
parent: Dan Harris <fbsd@drivefaster.net>
3 siblings, 0 replies; 5+ messages in thread
From: Joe Healy @ 2007-03-21 01:24 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
(forgot to send to list)
Dan Harris wrote:
> 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 have installed munin (http://munin.projects.linpro.no/) on a few
systems. This lets you look at graphs of system resources/load etc. I
have also added python scripts which do sample queries to let me know if
performance/index size is changing dramatically. I have attached an
example script.
Hope that helps,
Joe
------------------------------------------------------------------------
#! /usr/bin/python
import psycopg
import sys
def fixName(name):
return name[:19]
if len(sys.argv) > 1 and sys.argv[1] == "config":
print """graph_title Postgresql Index Sizes
graph_vlabel Mb"""
con = psycopg.connect("host=xxx user=xxx dbname=xxx password=xxx")
cur = con.cursor()
cur.execute("select relname, relpages from pg_class where relowner > 10 and relkind='i' and relpages > 256 order by reltuples desc;")
results = cur.fetchall()
for name, pages in results:
print "%s.label %s" % (fixName(name), name)
else:
con = psycopg.connect("host=xxx user=xxx dbname=xxx password=xxx")
cur = con.cursor()
cur.execute("select relname, relpages from pg_class where relowner > 10 and relkind='i' and relpages > 256 order by reltuples desc;")
results = cur.fetchall()
for name, pages in results:
print "%s.value %.2f" % (name[:19], pages*8.0/1024.0)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Determining server load from client
@ 2007-03-21 01:27 Dan Harris <fbsd@drivefaster.net>
parent: Dan Harris <fbsd@drivefaster.net>
3 siblings, 0 replies; 5+ messages in thread
From: Dan Harris @ 2007-03-21 01:27 UTC (permalink / raw)
To: pgsql-performance
Dan Harris wrote:
> 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.
..snip
Thank you all for your great ideas! I'm going to try the perl function
as that seems like a very elegant way of doing it.
-Dan
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-03-21 01:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-21 00:47 Determining server load from client Dan Harris <fbsd@drivefaster.net>
2007-03-21 01:15 ` Craig A. James <cjames@modgraph-usa.com>
2007-03-21 01:22 ` Jim Buttafuoco <jim@contactbda.com>
2007-03-21 01:24 ` Joe Healy <joe@omc-international.com.au>
2007-03-21 01:27 ` Dan Harris <fbsd@drivefaster.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox