← Back to branch summary

~launchpad-pqm/launchpad/devel

« back to all changes in this revision

Viewing changes to utilities/pgstats.py

  • Committer: Canonical.com Patch Queue Manager
  • Date: 2005-06-28 08:26:02 UTC
  • mfrom: (unknown (missing))
  • Revision ID: Arch-1:rocketfuel@canonical.com%launchpad--devel--0--patch-1952
[r=spiv] report some PostgreSQL stats we need to see
Patches applied:

 * stuart.bishop@canonical.com/launchpad--pgstats--0--base-0
   tag of rocketfuel@canonical.com/launchpad--devel--0--patch-1904

 * stuart.bishop@canonical.com/launchpad--pgstats--0--patch-1
   Generate some basic database statistics

 * stuart.bishop@canonical.com/launchpad--pgstats--0--patch-2
   SIGINT only shutdown running queries

 * stuart.bishop@canonical.com/launchpad--pgstats--0--patch-3
   Updates from review

Show diffs side-by-side

added added

removed removed

Lines of Context:
utilities/pgstats.py
 
1
#!/usr/bin/env python
 
2
# Copyright 2004-2005 Canonical Ltd.  All rights reserved.
 
3
"""
 
4
Generate some statistics about a PostgreSQL database suitable for
 
5
emailing via cron
 
6
"""
 
7
 
 
8
__metaclass__ = type
 
9
 
 
10
import sys
 
11
import psycopg
 
12
 
 
13
def percentage(num, total):
 
14
    """Return a percentage string of num/total"""
 
15
    if total == 0:
 
16
        return 'Unknown'
 
17
    else:
 
18
        return '%3.2f%%' % ( (num * 100.0) / total, )
 
19
 
 
20
 
 
21
def print_row(key, value):
 
22
    print '%(key)-20s: %(value)s' % vars()
 
23
 
 
24
 
 
25
def main(dbname):
 
26
    con = psycopg.connect("dbname=%s" % dbname)
 
27
    cur = con.cursor()
 
28
 
 
29
    print 'Statistics for %s' % dbname
 
30
    print '===============' + '=' * (len(dbname))
 
31
 
 
32
    # Database level statistics
 
33
    cur.execute("""
 
34
        SELECT blks_hit, blks_read, numbackends,xact_commit, xact_rollback
 
35
            FROM pg_stat_database
 
36
            WHERE datname=%(dbname)s
 
37
        """, vars())
 
38
    hit, read, backends, commits, rollbacks = cur.fetchone()
 
39
 
 
40
    hit_rate = percentage(hit, hit + read)
 
41
 
 
42
    print_row("Cache hit rate", hit_rate)
 
43
    print_row("Number of backends", backends)
 
44
 
 
45
    commit_rate = percentage(commits, commits + rollbacks)
 
46
 
 
47
    print_row("Commit rate", commit_rate)
 
48
 
 
49
    # Unused indexes, ignoring primary keys.
 
50
    # TODO: We should identify constraints used to enforce uniqueness too
 
51
    cur.execute("""
 
52
        SELECT relname, indexrelname
 
53
            FROM pg_stat_user_indexes AS u JOIN pg_indexes AS i
 
54
                ON u.schemaname = i.schemaname
 
55
                    AND u.relname = i.tablename
 
56
                    AND u.indexrelname = i.indexname
 
57
            WHERE
 
58
                idx_scan = 0
 
59
                AND indexrelname NOT LIKE '%_pkey'
 
60
                AND indexdef NOT LIKE 'CREATE UNIQUE %'
 
61
        """)
 
62
 
 
63
    rows = cur.fetchall()
 
64
    if len(rows) == 0:
 
65
        print_row('Unused indexes', 'N/A')
 
66
    else:
 
67
        print_row('Unused indexes', rows[0][1])
 
68
        for table, index in rows[1:]:
 
69
            print_row('', index)
 
70
 
 
71
if __name__ == '__main__':
 
72
    if len(sys.argv) != 2:
 
73
        print >> sys.stderr, "Usage: %s [DBNAME]" % sys.argv[0]
 
74
        sys.exit(1)
 
75
    dbname = sys.argv[1]
 
76
    main(dbname)