Skip site navigation (1) Skip section navigation (2)

Re: Determining server load from client

From: Joe Healy <joe(at)omc-international(dot)com(dot)au>
To: Dan Harris <fbsd(at)drivefaster(dot)net>
Cc: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Determining server load from client
Date: 2007-03-21 01:24:01
Message-ID: 46008931.8060605@omc-international.com.au (view raw or flat)
Thread:
Lists: 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)


In response to

Responses

pgsql-performance by date

Next:From: Dan HarrisDate: 2007-03-21 01:27:12
Subject: Re: Determining server load from client
Previous:From: Jim ButtafuocoDate: 2007-03-21 01:22:52
Subject: Re: Determining server load from client

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group