Re: suggestion: log_statement = sample

hi,

thanks for your comments on this.

On Thursday 16 July 2009 15:05:58 you wrote:
> In response to Janning Vygen <vygen@kicktipp.de>:
> > hi,
> >
> > http://archives.postgresql.org/pgsql-general/2009-03/msg00581.php
> >
> > This was my suggestion about introducing a statment to get a sample of
> > SQL statements. Nobody answered yet. Why not? i think my suggestion would
> > help a lot. Or was it kind of stupid?
>
> For my part, I don't think this would be useful.
>
> Since most of your queries are run by software, you're going to see a
> fairly predictable pattern to the queries, which means your sampling isn't
> going to be anywhere near random, thus it will still be inaccurate and
> incomplete.

I dont think so. In my use case i will get a good sampling of queries as I
could keep my log_sample running over long period of time. The sampling is in
any case much better than with log_minduration while logging all statement is
not acceptable in production.

> In my experience, I've found that enabling full logging for a short time
> (perhaps a few hours) gathers enough data to run through tools like
> pgFouine and find problem areas.

It is not possible for us. Logging millions of statements take too much time.

> Also, we have development servers that
> run automated tests, and since it's not critical that they be performant,
> we can run full query logging on them all the time.

But you dont run the real use cases with automated tests. There so many
factors involved in real time: caching, concurrency, data, peaktime,
deadlocks, doubleclicks, robots etc. that you just can't reproduce it on a
development system without lots of effort.

> Additionally, we make
> sure our production systems have enough hardware behind them that we can
> add additional tasks without it affecting production use.

that's nice, but not everybody can afford it. Of course i would love to log
every statement. But do you really log every statement in production? I guess
not.

> All of these are (in my opinion) better approaches to the problem than
> yet another arbitrary query filtering technique.  I mean, logging only
> the most time-consuming queries is already arbitrary enough (as you
> already stated).

With log_min duration i get only most time-consuming queries.
With log sample i can detect if there is a fast query which is called to
often. This is impossible today.

Again: for my use case it makes sense to have a log_sample feature.

kind regards
Janning