Thread: Suggestion about logging only every n-th statement

Suggestion about logging only every n-th statement

From
Janning Vygen
Date:
Back in 2009 I made a suggestion which is not implemented yet but would 
still be very valuable and easy to implement in my opinion (not for me 
as I am not speaking C):

https://www.postgresql.org/message-id/flat/200903161426.56662.vygen%40kicktipp.de#200903161426.56662.vygen@kicktipp.de

We still can't afford to log each and every statement as we have too 
many and we don't want to afford more disk performance/space. Therefore 
we log only statements slower than 50ms.

But for analyzing usage patterns it would be very nice to have this 
combined with a sample_rate for logging.

   logging_sample_rate = n

So each n-th statement will get logged regardless of execution time.

What do you think?

regards
Janning



Re: Suggestion about logging only every n-th statement

From
Geoff Winkless
Date:
On Wed, 20 Jun 2018 at 12:51, Janning Vygen <vygen@kicktipp.de> wrote:
But for analyzing usage patterns it would be very nice to have this
combined with a sample_rate for logging.

   logging_sample_rate = n

So each n-th statement will get logged regardless of execution time.

I think you would need to introduce a randomizing element ​if you wanted to make it statistically valid. Logging every n'th statement could (depending on your usage pattern) be catastrophically inaccurate.

Geoff

Re: Suggestion about logging only every n-th statement

From
Adrien NAYRAT
Date:
On 06/20/2018 01:51 PM, Janning Vygen wrote:
> Back in 2009 I made a suggestion which is not implemented yet but would 
> still be very valuable and easy to implement in my opinion (not for me 
> as I am not speaking C):
> 
>
https://www.postgresql.org/message-id/flat/200903161426.56662.vygen%40kicktipp.de#200903161426.56662.vygen@kicktipp.de
 
> 
> 
> We still can't afford to log each and every statement as we have too 
> many and we don't want to afford more disk performance/space. Therefore 
> we log only statements slower than 50ms.
> 
> But for analyzing usage patterns it would be very nice to have this 
> combined with a sample_rate for logging.
> 
>    logging_sample_rate = n
> 
> So each n-th statement will get logged regardless of execution time.
> 
> What do you think?
> 
> regards
> Janning
> 
> 

Hello Janning,

FYI in made this patch which seems do what you want :

https://www.postgresql.org/message-id/flat/c30ee535-ee1e-db9f-fa97-146b9f62caed%40anayrat.info#c30ee535-ee1e-db9f-fa97-146b9f62caed@anayrat.info


I will add an entry in september's commit fest.

Regards,


Re: Suggestion about logging only every n-th statement

From
Janning Vygen
Date:
Am 20.06.2018 um 14:53 schrieb Adrien NAYRAT:
> On 06/20/2018 01:51 PM, Janning Vygen wrote:
>> Back in 2009 I made a suggestion which is not implemented yet but 
>> would still be very valuable and easy to implement in my opinion (not 
>> for me as I am not speaking C):
>>
>>
https://www.postgresql.org/message-id/flat/200903161426.56662.vygen%40kicktipp.de#200903161426.56662.vygen@kicktipp.de
 
>>
>
> FYI in made this patch which seems do what you want :
>
https://www.postgresql.org/message-id/flat/c30ee535-ee1e-db9f-fa97-146b9f62caed%40anayrat.info#c30ee535-ee1e-db9f-fa97-146b9f62caed@anayrat.info

>
>
> I will add an entry in september's commit fest.

excellent. Exactly what I want!

regards
Janning



Re: Suggestion about logging only every n-th statement

From
Andreas Kretschmer
Date:
On 20 June 2018 13:51:25 CEST, Janning Vygen <vygen@kicktipp.de> wrote:
>Back in 2009 I made a suggestion which is not implemented yet but would
>
>still be very valuable and easy to implement in my opinion (not for me
>as I am not speaking C):
>
>https://www.postgresql.org/message-id/flat/200903161426.56662.vygen%40kicktipp.de#200903161426.56662.vygen@kicktipp.de
>
>We still can't afford to log each and every statement as we have too
>many and we don't want to afford more disk performance/space. Therefore
>
>we log only statements slower than 50ms.
>
>But for analyzing usage patterns it would be very nice to have this
>combined with a sample_rate for logging.
>
>   logging_sample_rate = n
>
>So each n-th statement will get logged regardless of execution time.
>
>What do you think?
>
>regards
>Janning

I would suggest using of pg_stat_statements for that.

Regards, Andreas


--
2ndQuadrant - The PostgreSQL Support Company


Re: Suggestion about logging only every n-th statement

From
Adrien Nayrat
Date:
On 06/20/2018 03:08 PM, Andreas Kretschmer wrote:
> I would suggest using of pg_stat_statements for that.

I agree, but sometimes it is not sufficient. For example, if you need query's
parameters.

--
Adrien NAYRAT



Attachment

Re: Suggestion about logging only every n-th statement

From
Adrien Nayrat
Date:
On 06/20/2018 03:06 PM, Janning Vygen wrote:
>> FYI in made this patch which seems do what you want :
>>
https://www.postgresql.org/message-id/flat/c30ee535-ee1e-db9f-fa97-146b9f62caed%40anayrat.info#c30ee535-ee1e-db9f-fa97-146b9f62caed@anayrat.info
>>
>>
>> I will add an entry in september's commit fest.
>
> excellent. Exactly what I want!

FYI, I added an entry in next commit fest (september is not open) :
https://commitfest.postgresql.org/18/1691/

Feel free to review it ;)

Regards,

--
Adrien NAYRAT


Attachment