This package includes three useful scripts aimed to help to pinpoint performance issues on systems with PostgreSQL as database backend.
All scritps are written in Python. Requirements:
- Python 2.6+
- psycopg2 module (http://initd.org/psycopg/)
All scripts require a connect to PostgreSQL database. They take traditional set of DB credentials: host address, port (5432 by default), database name, database user and password.
Note: you may need to modify pg_hba.conf file to allow the scripts to connect to the server. Please refer to official documentaion: http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html
For detailed list of command line options use --help / -h option.
pg-top.py uses pg_stat_user_tables system table to get current statistics of server activity. The information is represented in 'top'-like screen, per table and total for the server, and updated dynamically.
The following data is reported:
- total number of inserted/updated/deleted rows per sec
- number of inserted rows per second
- number of updated rows per second
- number of deleted rows per second
- number of rows updated with index update
- number of index scans per second
- number of sequential scans per second
- number of rows per second fetched by seq scans
- number of processes waiting for lock
- approximate number of rows in table
pg-stat.py is a command-line tool to get advanced server statistics in real-time. The information is represented in tabular form, similar to 'vmstat' output. By default, new data row is printed each 2 seconds.
The following data is reported:
- size of database in kilobytes
- write operations: number of rows inserted/updated/deleted (into user tables)
- total number of index scans
- total number of sequential scans
- percentage of sequential scans
- total number of live rows fetched by seq scans
- cache: total number of shmem block hit/miss
- IO: percent of time spent on waiting to read/write the device (>=9.2)
- number of processes waiting for lock
- total number of deadlocks (>= 9.2)
- number of transactions committed/rolled back
- total number of 'idle in transaction' processes
- total number of live processes
pg-info.py script gathers static performance-related information from the pg_stat_xxx tables and tries to identify potential problem sources.
In particular, the following data is reported:
- Size of entire database on disk
- Top tables sorted by size on disk
- Large tables with missing indexes
- Less frequently accessed indexes ordered by size
- Most frequently modified tables
These scripts were created as inhouse tools at Parallels (www.parallels.com), by Alexander Andreev (aandreev@odin.com).
Do not hesitate to send your patches, issues and proposals!
Released under GPLv2 License