Обсуждение: postgres query log analysis?

What do you all use for query log analysis for Postgres. I feel/hope
like there must be something that I am missing.

I have tried PQA (http://pqa.projects.postgresql.org/) and it is very
problematic, at least with the kind of application we have. Some of the
problems:
  * not aware of prepared statements
(http://pgfoundry.org/forum/forum.php?thread_id=948&forum_id=24);
  * giving faulty stats in several situations (possibly related to
prepared statements, but maybe some others);
  * very slow on anything but "play" logs;
  * seems to not be maintained lately (since 8.0?).

Pglog-analyze (http://opensource.pearshealthcyber.cz/) is an admirable
attempt, but it is very problematic too:
  * incredibly memory-intensive -- I can at best process a few hours of
my production logs even though I have jacked up my PHP memory limit to
2.5GB
  * gives out stats that seem way off on several accounts (subsecond
queries sometimes show as taking over a minute);
  * seems to have similar to PQA's (if not worse) issues with prepared
statements.

Is there any other option? When you enable query logging in your PGSQL
logs what do you do with the output?

Thanks!

George

George Pavlov wrote:
> What do you all use for query log analysis for Postgres. I feel/hope
> like there must be something that I am missing.
>
> I have tried PQA (http://pqa.projects.postgresql.org/) and it is very
> problematic, at least with the kind of application we have. Some of
>   the problems: * not aware of prepared statements
> (http://pgfoundry.org/forum/forum.php?thread_id=948&forum_id=24);
>   * giving faulty stats in several situations (possibly related to
> prepared statements, but maybe some others);
>   * very slow on anything but "play" logs;
>   * seems to not be maintained lately (since 8.0?).
>
> Pglog-analyze (http://opensource.pearshealthcyber.cz/) is an
> admirable attempt, but it is very problematic too:
>   * incredibly memory-intensive -- I can at best process a few hours
> of my production logs even though I have jacked up my PHP memory
> limit to 2.5GB
>   * gives out stats that seem way off on several accounts (subsecond
> queries sometimes show as taking over a minute);
>   * seems to have similar to PQA's (if not worse) issues with
> prepared statements.
>
> Is there any other option? When you enable query logging in your
> PGSQL logs what do you do with the output?
>
> Thanks!
>


Look into pgfouine on pgFoundry. http://pgfoundry.org/projects/pgfouine/


--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 512-248-2683             E-Mail: ler@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893

> Look into pgfouine on pgFoundry.
> http://pgfoundry.org/projects/pgfouine/

thanks! definitely much better, but still not entirely believable, at
least on first try. e.g. i have a query with 4 conditions in the ORDER
BY. pgfouine reports show the query as having the last 3 of those
repeated 18 times! it also fails to normalize a lot of the queries
(seems related to prepared statements). the "queries by type" and the
"hourly reports" are way off (the "queries by type" table has no rows,
the total queries in the hourly statistics section do not match the
number of queries count in the "overall statistics" section and the
select and "write" queries parts all show as 0). all of these problems
are in syslog mode, stderr mode is probably a bit worse. i will play
with the tool some more and try to diagnose the various problems better.

george

George Pavlov wrote:
>> Look into pgfouine on pgFoundry.
>> http://pgfoundry.org/projects/pgfouine/
>
> thanks! definitely much better, but still not entirely believable, at
> least on first try. e.g. i have a query with 4 conditions in the ORDER
> BY. pgfouine reports show the query as having the last 3 of those
> repeated 18 times! it also fails to normalize a lot of the queries
> (seems related to prepared statements). the "queries by type" and the
> "hourly reports" are way off (the "queries by type" table has no rows,
> the total queries in the hourly statistics section do not match the
> number of queries count in the "overall statistics" section and the
> select and "write" queries parts all show as 0). all of these problems
> are in syslog mode, stderr mode is probably a bit worse. i will play
> with the tool some more and try to diagnose the various problems better.

Narrow down the problems it's having to some sample cases and post
bug-reports. That's the best way to contribute and "fix" the problem.

--
Postgresql & php tutorials
http://www.designmagick.com/