Thread: Suggestion about logging only every n-th statement
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
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
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,
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
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
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
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