Re: When should I worry?

On Mon, 11 Jun 2007, Steve Crawford wrote:

> In my experience the more common situation is to "go off a cliff."

Yeah, I think the idea that you'll notice performance degrading and be
able to extrapolate future trends using statistical techniques is a
bit...optimistic.

Anyway, back to the original question here. If you're worried about
catching when performance starts becoming an issue, you need to do some
sort of logging of how long statements are taking to execute. The main
choice is whether to log everything, at which point the logging and
sorting through all the data generated may become its own performance
concern, or whether to just log statements that take a long time and then
count how many of them show up. Either way will give you some sort of
early warning once you get a baseline; it may take a bit of tweaking to
figure out where to draw the line at for what constitutes a "long"
statement if you only want to see how many of those you get.

There are two tools you should look at initially to help process the
logging information you get back: pgFouine and PQA. Here are intros to
each that also mention how to configure the postgresql.conf file:

http://pgfouine.projects.postgresql.org/tutorial.html
http://www.databasejournal.com/features/postgresql/article.php/3323561

As they're similar programs, which would work better for you is hard to
say; check out both and see which seems more practical or easier to get
running. For example, if you only have one of PHP/Ruby installed, that
may make one tool or the easier preferred.

If you can get yourself to the point where you can confidently say
something like "yesterday we had 346 statements that took more then 200ms
to execute, which is 25% above this month's average", you'll be in a
positition to catch performance issues before they completely blindside
you; makes you look good in meetings, too.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD