Re: Use case for enabling log_duration other than benchmarking

[Julien Rouhaud]

Le mer. 11 janv. 2023 à 00:28, Ron  a écrit :

> If your application *requires* subsecond response, and you're only
> getting subsecond response some of the time, then you obviously want to
> know why.  Part of that is checking to see if the database and queries are
> doing their job.
>

now that log_line_prefix can display the query_id, it could also be used to
get a limited version of what log_min_duration_statements does (you can
know what query was but not the exact parameters), but in a way cheaper
way. of course it assumes that you have something like pg_stat_statements
installed and that you need some more granularity than regular snapshots of
its information.

>

Disallow execution of shell commands from psql

[Wiwwo Staff]

Hi!
Happy new (gregorian calendar) year!

Somehow related to the proposal of having a `psql --idle` option, is there
a way to disallow the command `\!` (and anything of the likes in psql?

Sure, I can set the SHELL env var at run-time, but I still want to have
postgres user to be a normal user, with its shell etc, which means it can
change this SHELL setting somewhere.

Any tip?

Re: Use case for enabling log_duration other than benchmarking

[Ron]

On 1/10/23 09:57, Alicja Kucharczyk wrote:

wt., 10 sty 2023 o 14:57 Ron  napisał(a):

On 1/10/23 07:14, Alicja Kucharczyk wrote:

Do you know any use case for enabling log_duration? Like 3rd party
tools for instance.
I find this parameter pretty much useless (in opposite to
log_min_duration_statement) as it does not show the query text, so
besides having just the timing logged it is of no use in
troubleshooting and often causes huge overhead. Am I missing something?


https://www.postgresql.org/docs/current/runtime-config-logging.html


  Note

The difference between enabling|log_duration|and
settinglog_min_duration_statement

to
zero is that exceeding|log_min_duration_statement|forces the text of
the query to be logged, but this option doesn't. Thus,
if|log_duration|is|on|and|log_min_duration_statement|has a positive
value, all durations are logged but the query text is included only
for statements exceeding the threshold. *This behavior can be useful
for gathering statistics in high-load installations.*


thank you Ron.
My question is a bit more practical - Does anyone really find it useful?
What value brings the info that 20% of my query are under 1ms and 10% over 
1 minute


If your application /*requires*/ subsecond response, and you're only getting 
subsecond response some of the time, then you obviously want to know why.  
Part of that is checking to see if the database and queries are doing their job.


- If just checked once and then turned off - I can understand to have more 
visibility into the overall characteristics. But let say someone have it 
enabled on a production system all the time - what could be the reason for 
that?


--
Born in Arizona, moved to Babylonia.

Re: Use case for enabling log_duration other than benchmarking

[Alicja Kucharczyk]

wt., 10 sty 2023 o 14:57 Ron  napisał(a):

> On 1/10/23 07:14, Alicja Kucharczyk wrote:
>
> Do you know any use case for enabling log_duration? Like 3rd party tools
> for instance.
> I find this parameter pretty much useless (in opposite to
> log_min_duration_statement) as it does not show the query text, so besides
> having just the timing logged it is of no use in troubleshooting and often
> causes huge overhead. Am I missing something?
>
>
> https://www.postgresql.org/docs/current/runtime-config-logging.html
>
> Note
>
> The difference between enabling log_duration and setting
> log_min_duration_statement
> 
>  to zero is that exceeding log_min_duration_statement forces the text of
> the query to be logged, but this option doesn't. Thus, if log_duration is
> on and log_min_duration_statement has a positive value, all durations are
> logged but the query text is included only for statements exceeding the
> threshold. *This behavior can be useful for gathering statistics in
> high-load installations.*
>

thank you Ron.
My question is a bit more practical - Does anyone really find it useful?
What value brings the info that 20% of my query are under 1ms and 10% over
1 minute - If just checked once and then turned off - I can understand to
have more visibility into the overall characteristics. But let say someone
have it enabled on a production system all the time - what could be the
reason for that?

Best opensource Postgresql monitoring tool

[Vikas Sharma]

Hi there,

Could you please advise on the best opensource monitoring tool for
Postgresql?

Thanks & Best Regards
Vikas

Re: Use case for enabling log_duration other than benchmarking

[Ron]

On 1/10/23 07:14, Alicja Kucharczyk wrote:
Do you know any use case for enabling log_duration? Like 3rd party tools 
for instance.
I find this parameter pretty much useless (in opposite to 
log_min_duration_statement) as it does not show the query text, so besides 
having just the timing logged it is of no use in troubleshooting and often 
causes huge overhead. Am I missing something?


https://www.postgresql.org/docs/current/runtime-config-logging.html


 Note

The difference between enabling|log_duration|and 
settinglog_min_duration_statement 
to 
zero is that exceeding|log_min_duration_statement|forces the text of the 
query to be logged, but this option doesn't. Thus, 
if|log_duration|is|on|and|log_min_duration_statement|has a positive value, 
all durations are logged but the query text is included only for statements 
exceeding the threshold. *This behavior can be useful for gathering 
statistics in high-load installations.*




--
Born in Arizona, moved to Babylonia.

Re: pg_multixact_member file limits

[Ron]

On 1/9/23 22:51, Martin Ritchie wrote:
Are there any limits on the number of records in 
the postgresql/12/main/pg_multixact/members directory? We have a database 
that has grown to tens of thousands of files in this directory during an 
autovacuum after a large data purge. It shrank after the autovacuum completed.


Wouldn't that be a function of how much filesystem performance degrades as 
the number of files in a directory increases?


--
Born in Arizona, moved to Babylonia.

Use case for enabling log_duration other than benchmarking

[Alicja Kucharczyk]

Do you know any use case for enabling log_duration? Like 3rd party tools
for instance.
I find this parameter pretty much useless (in opposite to
log_min_duration_statement) as it does not show the query text, so besides
having just the timing logged it is of no use in troubleshooting and often
causes huge overhead. Am I missing something?

pozdrawiam,
best regards,
Alicja Kucharczyk