On 9/8/06, Bruce Momjian <bruce@momjian.us> wrote:
> Alvaro Herrera wrote:
> > Statistics?
>
> Oh, interesting.
We build this type of report for our customers:
http://pgfouine.projects.postgresql.org/reports/sample_hourly.html
This one is a real one.
As you can see, we cannot tell the type of every query as we don't log
the text of all of them (we identified 138,788 of the 12,358,514
queries executed this day).
It helped to detect there was a weird behaviour at 11am which was not
a good idea as we have a lot of visits at 11am. We asked our customers
if there was any cronjob running at 11am we could move to another time
when the load was less intensive as it really slowed down the
database.
Sometimes we also detect that we suddenly have a big up in the number
of queries executed (and it can be small queries we cannot detect with
log_min_duration_statement = 500) without the same increase on the
HTTP side. We contact our customer to ask him what happens and if it's
normal. If it's not, they take a look at the changes they made to the
code the previous day. Sometimes, we can even give them a hint because
it's at a specific time and we can suspect it's a cronjob running.
If they find the problem, it's directly corrected and we can check the
next day if it's okay. If they don't find the problem, then we enable
full query logging for a short time and we analyze them to find the
offending queries.
And sometimes, in the case we just host the database without
developing the application, we just simply need it for contractual
reasons to demonstrate why the server is suddenly becoming slow.
I hope it helps to clarify how we use it.
--
Guillaume