[Christo Du Preez - Wed at 12:25:20PM +0200]
> Is there some kind of performance testing utility available for
> postgresql Something I can run after installing postgresql to help me
> identify if my installation is optimal.
>
> I've been battling for days now trying to sort out performance issues
> and something like that may just identify issues I'm not even aware of
> or considering at this stage.
If you are really having performance problems, my general experience is
that you should look into the queries and usage patterns rather than the
configuration. The server configuration can only give marginal
benefits, compared to query and usage tuning.
It often a good idea to turn on the stats collector, even if it slows
down postgres a bit.
One of the things the stats collector gives is the pg_stat_activity
view, where you can find everything the server is working on exactly
now; checking up this view while you are actually experiencing
problems can give a lot of information.
Another thing I've noticed, is that the activity from our applications
often can come in bursts; the server can be like 70% idle most of the
time, but when the server is struck by 4-5 IO-heavy queries at the
same time in addition to the steady flow of simple queries, it can
easily get trashed. I've made up an algorithm to stop this from
happening, before running a transaction which is either heavy or not
considered very important, the activity view will be scanned, and if
the server is already working with many queries, the application will
sleep a bit and try again - and eventually return an error message
("please try again later") if it's doing interactive stuff.
Another thing that we've experienced - be aware of pending
transactions! It's important to commit or roll back every transaction
within reasonable time - if (i.e. due to a programming mistake or a
DBA starting a transaction in psql) a transaction is pending for
several hours or even ays, it is really very bad for the performance.
Another experience we have is that autovacuum can be quite naughty
when one has some really huge tables. This can be tweaked by
disabling autovacuum at those tables, and running a nightly vacuum
instead.
Apologies for not replying to your question, though ;-)
