pgstat.
pgstat is a utility that shows various PostgreSQL statistics on the command line similar to other unix utilities like iostat.
The purpose of this utility is to echo various PostgreSQL data to the command line in a format similar to iostat, or vmstat. This is useful to see ‘at a glance’ what the DB is doing, and how much throughput it’s providing at any given point. The utility can be run by hand or perhaps run in the background spooling to a log file. The utility uses python and psycopg2 to connect to the database, and while every attempt to keep it’s memory footprint small has been made, it’s recommended to restart it periodically to clean up.
Download:
pgstat is hosted over at pgfoundry, and can downloaded from there.
Requirements:
- python 2.5.2+ (untested on older versions)
- psycopg2 2.06+
Installation:
Copy this file into any directory. You may need to change the first line of the file to match your Python binaries location. Also, be sure to put the PostgreSQL lib directory in your LD_LIBRARY_PATH. Thats it.
Feedback:
You can email it to kenny [ at ] kennygorman [dot] org.
Usage:
./pgstat -h
Usage: pgstat [options]
Options:
-h, --help show this help message and exit
-d DATABASE, --database=DATABASE
database name to connect to
-n HOSTNAME, --hostname=HOSTNAME
hostname to connect to
-u USERNAME, --username=USERNAME
username
-p PASSWORD, --password=PASSWORD
passwordThe output looks like this:
$>./pgstat -d largedb time commits rollbks blksrd blkshit bkends seqscan seqtprd idxscn idxtrd ins upd del locks hit load 23-10-2008.16:40:01 15960 11 19471 120060 1259 42 2902 14093 43632 1249 884 7 361 0.0 13.81 23-10-2008.16:40:11 17171 11 20051 145053 1262 43 3214 12722 52371 1485 1072 5 352 0.0 14.24 23-10-2008.16:40:21 17234 13 21034 400247 1263 40 2954 10281 1224424 1611 1145 6 414 0.0 15.17 23-10-2008.16:40:31 17338 12 21778 126037 1265 40 2984 10569 45095 1444 1009 4 366 0.0 15.46 23-10-2008.16:40:41 16300 11 19071 120190 1265 40 3010 9381 38011 1517 1131 2 360 0.0 15.51 23-10-2008.16:40:51 16812 11 18832 140303 1265 40 3028 9852 69111 1490 1051 5 381 0.0 15.67 23-10-2008.16:41:01 15828 12 18176 104550 1266 41 2740 9001 36234 1396 1011 11 387 0.0 15.48 23-10-2008.16:41:11 16883 10 20706 129345 1266 42 3370 9817 43890 2060 1619 7 351 0.0 15.55 23-10-2008.16:41:21 17493 12 19679 120780 1268 40 3082 10045 43568 1634 1204 6 368 0.0 15.70 23-10-2008.16:41:31 17037 13 20738 402062 1268 40 3114 10021 1236268 1540 1149 3 371 0.0 14.84 23-10-2008.16:41:41 16122 12 17789 123511 1268 38 2828 9492 41573 1817 1439 5 341 0.0 14.87 23-10-2008.16:41:51 17112 11 20396 102981 1268 42 3486 9760 41443 1535 1147 6 330 0.0 15.27 23-10-2008.16:42:01 17010 11 20573 105587 1268 40 3188 9915 38743 1458 1060 4 355 0.0 15.51 23-10-2008.16:42:11 17151 12 24610 126935 1267 40 3206 10214 52086 1969 1511 4 345 0.0 15.90 23-10-2008.16:42:21 17764 10 20245 123182 1258 40 3224 10582 42139 1699 1317 10 348 0.0 16.31 23-10-2008.16:42:31 17384 12 20389 111437 1255 42 3242 10129 44530 1497 1037 8 353 0.0 16.73 23-10-2008.16:42:41 17835 11 22282 117775 1259 40 3274 10977 52354 1423 961 4 342 0.0 17.35 23-10-2008.16:42:51 16466 13 25360 399588 1262 40 3292 10058 1258398 1384 989 7 358 0.0 16.71 23-10-2008.16:43:02 17044 10 20122 106590 1262 40 3320 10778 46185 1288 892 7 369 0.0 17.00 23-10-2008.16:43:12 16855 12 24605 118533 1262 42 3093 16618 40749 1214 792 1538 375 0.0 16.14
Changelist:
0.5b: initial release
0.7b: fixed bug with commits count, and bug with showing realtime hit ratio
