Friday, June 01, 2012

pgstatview: Visualize your PostgreSQL in a minute

As you all may know, understanding activities inside database management system is one of the important, but difficult and painful tasks for DBAs, not only for newbies, but also experienced DBAs. I saw many DBAs struggling with PostgreSQL because of such difficulties.

Today, I'd like to introduce a brand-new tool, pgstatview.
pgstatview, a new visualization tool for PostgreSQL, has been developed to help DBAs and make it easier to understand what is actually going on inside PostgreSQL.

pgstatview allows DBA to visualize database activities with using several metrics both inside and outside PostgreSQL: transaction commit/rollbacks, tuple inserts/updates/deletes, cache hit ratio, bgwriter writes, WAL writes, and so on. You can see some sample reports here: Sample 1, Sample 2.

pgstatview has its unique advantages.

(1) Easy to use, in a minute
To use pgstatview, DBA needs only a single script ( to run on the database server. script collects several statistics with using vmstat, mpstat, iostat and psql, and then, records all of them into log files.

Once you submit those log files through the pgstatview web service, a pgstatview report generated from the statistics data you submitted will be published with several colorful charts, and you can read it on your browser.

(2) No impact for the running system
pgstatview doesn't need any additional module for PostgreSQL or any special commands. It requires only a single script and ordinary tools you might already have on your system (vmstat, mpstat, iostat and psql).

So, you don't worry about any impact for your database system even in the production server.

(3) No need for maintaining tools, only the program DBA needs to use, is a small script, and you can always download the latest one from the pgstatview web site. And of course, the pgstatview web service must be maintained and updated continuously.

So, DBA doesn't need to care about maintaining the tool anymore, and no longer be frustrated with version dependency and/or compilation issues.

Are you interested in? It's very easy to use, so you can try it in a minute.

Download from the pgstatview web site, and run it as following (with 5 second interval):
$ ./
Usage: [ <INTERVAL> | stop ]
$ ./ 5
<Run your workload on PostgreSQL here.>
$ ./ stop
Then, submit `vmstat.log', `mpstat.log', `iostat.log' and `pgstat.log' files generated in the current working directory.

Please try it, and if you have any comments, requests or have found a bug (unfortunately), please tell me by e-mail or this comment form.



Лев Ласкин said...

and what sense? why dont use for example munin?

Satoshi Nagayasu said...

Of course, Munin is one of the possible options to monitor PostgreSQL. However, from DBA point of view, I think I need to observe PostgreSQL deeper and easier than general monitoring tools. Not implemented yet though.