PostgreSQL perfomance monitoring tool

Question

I'm setting up an web application with a FreeBSD PostgreSQL back-end. I'm looking for some database performance optimization tool/technique. Does someone know one?

Answer 1

pgfouine works fairly well for me. And it looks like there's a FreeBSD port for it.

Answer 2

Well, the first thing to do is try all your queries from psql using "explain" and see if there are sequential scans that can be converted to index scans by adding indexes or rewriting the query.

Other than that, I'm as interested in the answers to this question as you are.

Answer 3

I've used pgtop a little. It is quite crude, but at least I can see which query is running for each process ID.

I tried pgfouine, but if I remember, it's an offline tool.

I also tail the psql.log file and set the logging criteria down to a level where I can see the problem queries.

#log_min_duration_statement = -1        # -1 is disabled, 0 logs all statements
                                        # and their durations, > 0 logs only
                                        # statements running at least this time.

I also use http://www.sqlmanager.net/products/postgresql/manager)">EMS Postgres Manager to do general admin work. It doesn't do anything for you, but it does make most tasks easier and makes reviewing and setting up your schema more simple. I find that when using a GUI, it is much easier for me to spot inconsistencies (like a missing index, field criteria, etc.). It's only one of two programs I'm willing to use VMWare on my Mac to use.

Answer 4

Check out Lightning Admin, it has a GUI for capturing log statements, not perfect but works great for most needs. http://www.amsoftwaredesign.com

Answer 5

Munin is quite simple yet effective to get trends of how the database is evolving and performing over time. In the standard kit of Munin you can among other thing monitor the size of the database, number of locks, number of connections, sequential scans, size of transaction log and long running queries.

Easy to setup and to get started with and if needed you can write your own plugin quite easily.

Check out the latest postgresql plugins that are shipped with Munin here:

http://munin-monitoring.org/browser/branches/1.4-stable/plugins/node.d/