Message-ID: <46008931.8060605@omc-international.com.au>
Date: Wed, 21 Mar 2007 12:24:01 +1100
From: Joe Healy <joe@omc-international.com.au>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
To: Dan Harris <fbsd@drivefaster.net>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Determining server load from client
References: <460080A2.20800@drivefaster.net>
In-Reply-To: <460080A2.20800@drivefaster.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

(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)