pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
track_activity_query_size max practical size?
8+ messages / 2 participants
[nested] [flat]

* track_activity_query_size max practical size?
@ 2023-07-07 14:42 Ron <ronljohnsonjr@gmail.com>
  2023-07-07 14:51 ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Ron @ 2023-07-07 14:42 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

We've got some Very Large Queries that take a long time.  Even setting taqs 
to 10KB isn't adequate, so I want to significantly bump it, but am concerned 
about side effects of setting it to 48KB or even 64KB.

-- 
Born in Arizona, moved to Babylonia.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: track_activity_query_size max practical size?
  2023-07-07 14:42 track_activity_query_size max practical size? Ron <ronljohnsonjr@gmail.com>
@ 2023-07-07 14:51 ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-07 14:55   ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-07 14:56   ` Re: track_activity_query_size max practical size? Ron <ronljohnsonjr@gmail.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Adrian Klaver @ 2023-07-07 14:51 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org

On 7/7/23 07:42, Ron wrote:
> We've got some Very Large Queries that take a long time.  

An EXPLAIN(ANALYZE BUFFERS) would go a long way here.

>Even setting 
> taqs to 10KB isn't adequate, so I want to significantly bump it, but am

It is before coffee here, so you will need to spell out what taqs is?

> concerned about side effects of setting it to 48KB or even 64KB.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: track_activity_query_size max practical size?
  2023-07-07 14:42 track_activity_query_size max practical size? Ron <ronljohnsonjr@gmail.com>
  2023-07-07 14:51 ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-07-07 14:55   ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-07 14:58     ` Re: track_activity_query_size max practical size? Ron <ronljohnsonjr@gmail.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Adrian Klaver @ 2023-07-07 14:55 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org

On 7/7/23 07:51, Adrian Klaver wrote:
> On 7/7/23 07:42, Ron wrote:
>> We've got some Very Large Queries that take a long time. 
> 
> An EXPLAIN(ANALYZE BUFFERS) would go a long way here.
> 
>> Even setting taqs to 10KB isn't adequate, so I want to significantly 
>> bump it, but am
> 
> It is before coffee here, so you will need to spell out what taqs is?

Did I mention lack of coffee? I see  track_activity_query_size in the 
subject. So the issue is what with the current setting?

> 
>> concerned about side effects of setting it to 48KB or even 64KB.
>>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: track_activity_query_size max practical size?
  2023-07-07 14:42 track_activity_query_size max practical size? Ron <ronljohnsonjr@gmail.com>
  2023-07-07 14:51 ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-07 14:55   ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-07-07 14:58     ` Ron <ronljohnsonjr@gmail.com>
  2023-07-07 15:13       ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Ron @ 2023-07-07 14:58 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 7/7/23 09:55, Adrian Klaver wrote:
> On 7/7/23 07:51, Adrian Klaver wrote:
>> On 7/7/23 07:42, Ron wrote:
>>> We've got some Very Large Queries that take a long time. 
>>
>> An EXPLAIN(ANALYZE BUFFERS) would go a long way here.
>>
>>> Even setting taqs to 10KB isn't adequate, so I want to significantly 
>>> bump it, but am
>>
>> It is before coffee here, so you will need to spell out what taqs is?
>
> Did I mention lack of coffee? I see  track_activity_query_size in the 
> subject. So the issue is what with the current setting?

10KB isn't enough to capture the whole query.  Are there any bad side 
effects of increasing it from 10KB to 48KB or 64KB?

>
>>
>>> concerned about side effects of setting it to 48KB or even 64KB.
>>>
>>
>

-- 
Born in Arizona, moved to Babylonia.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: track_activity_query_size max practical size?
  2023-07-07 14:42 track_activity_query_size max practical size? Ron <ronljohnsonjr@gmail.com>
  2023-07-07 14:51 ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-07 14:55   ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-07 14:58     ` Re: track_activity_query_size max practical size? Ron <ronljohnsonjr@gmail.com>
@ 2023-07-07 15:13       ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-07 15:27         ` Re: track_activity_query_size max practical size? Ron <ronljohnsonjr@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Adrian Klaver @ 2023-07-07 15:13 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org

On 7/7/23 07:58, Ron wrote:
> On 7/7/23 09:55, Adrian Klaver wrote:
>> On 7/7/23 07:51, Adrian Klaver wrote:
>>> On 7/7/23 07:42, Ron wrote:
>>>> We've got some Very Large Queries that take a long time. 
>>>
>>> An EXPLAIN(ANALYZE BUFFERS) would go a long way here.
>>>
>>>> Even setting taqs to 10KB isn't adequate, so I want to significantly 
>>>> bump it, but am
>>>
>>> It is before coffee here, so you will need to spell out what taqs is?
>>
>> Did I mention lack of coffee? I see  track_activity_query_size in the 
>> subject. So the issue is what with the current setting?
> 
> 10KB isn't enough to capture the whole query.  Are there any bad side 
> effects of increasing it from 10KB to 48KB or 64KB?

So to be clear the text of the query is large and not all of it is being 
being shown in pg_stat_activity.query. I have never had the need to 
increase that setting so all I can do is speculate. From the docs 
'Specifies the amount of memory reserved to store the text of the 
currently executing command for each active session, ...'. So given a 
lot of active sessions with each query being a large text value there 
could be a side effect at some point. The bigger issue would seem to be 
'This parameter can only be set at server start'. So you are looking at 
starting/stopping the server to find the sweet spot.

> 
>>
>>>
>>>> concerned about side effects of setting it to 48KB or even 64KB.
>>>>
>>>
>>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: track_activity_query_size max practical size?
  2023-07-07 14:42 track_activity_query_size max practical size? Ron <ronljohnsonjr@gmail.com>
  2023-07-07 14:51 ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-07 14:55   ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-07 14:58     ` Re: track_activity_query_size max practical size? Ron <ronljohnsonjr@gmail.com>
  2023-07-07 15:13       ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-07-07 15:27         ` Ron <ronljohnsonjr@gmail.com>
  2023-07-07 15:53           ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Ron @ 2023-07-07 15:27 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 7/7/23 10:13, Adrian Klaver wrote:
> On 7/7/23 07:58, Ron wrote:
>> On 7/7/23 09:55, Adrian Klaver wrote:
>>> On 7/7/23 07:51, Adrian Klaver wrote:
>>>> On 7/7/23 07:42, Ron wrote:
>>>>> We've got some Very Large Queries that take a long time. 
>>>>
>>>> An EXPLAIN(ANALYZE BUFFERS) would go a long way here.
>>>>
>>>>> Even setting taqs to 10KB isn't adequate, so I want to significantly 
>>>>> bump it, but am
>>>>
>>>> It is before coffee here, so you will need to spell out what taqs is?
>>>
>>> Did I mention lack of coffee? I see  track_activity_query_size in the 
>>> subject. So the issue is what with the current setting?
>>
>> 10KB isn't enough to capture the whole query.  Are there any bad side 
>> effects of increasing it from 10KB to 48KB or 64KB?
>
> So to be clear the text of the query is large and not all of it is being 
> being shown in pg_stat_activity.query. I have never had the need to 
> increase that setting so all I can do is speculate. From the docs 
> 'Specifies the amount of memory reserved to store the text of the 
> currently executing command for each active session, ...'. So given a lot 
> of active sessions with each query being a large text value there could be 
> a side effect at some point. The bigger issue would seem to be 'This 
> parameter can only be set at server start'. So you are looking at 
> starting/stopping the server to find the sweet spot.

Restarting is something I can do during a quiet point after the bulk of the 
day's work is complete, but before the nightly backups.

>
>>
>>>
>>>>
>>>>> concerned about side effects of setting it to 48KB or even 64KB.
>>>>>
>>>>
>>>
>>
>

-- 
Born in Arizona, moved to Babylonia.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: track_activity_query_size max practical size?
  2023-07-07 14:42 track_activity_query_size max practical size? Ron <ronljohnsonjr@gmail.com>
  2023-07-07 14:51 ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-07 14:55   ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-07 14:58     ` Re: track_activity_query_size max practical size? Ron <ronljohnsonjr@gmail.com>
  2023-07-07 15:13       ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
  2023-07-07 15:27         ` Re: track_activity_query_size max practical size? Ron <ronljohnsonjr@gmail.com>
@ 2023-07-07 15:53           ` Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Adrian Klaver @ 2023-07-07 15:53 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org

On 7/7/23 08:27, Ron wrote:

> 
> Restarting is something I can do during a quiet point after the bulk of 
> the day's work is complete, but before the nightly backups.
> 

For completeness there is the auto_explain module:

https://www.postgresql.org/docs/current/auto-explain.html

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: track_activity_query_size max practical size?
  2023-07-07 14:42 track_activity_query_size max practical size? Ron <ronljohnsonjr@gmail.com>
  2023-07-07 14:51 ` Re: track_activity_query_size max practical size? Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-07-07 14:56   ` Ron <ronljohnsonjr@gmail.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Ron @ 2023-07-07 14:56 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 7/7/23 09:51, Adrian Klaver wrote:
> On 7/7/23 07:42, Ron wrote:
>> We've got some Very Large Queries that take a long time. 
>
> An EXPLAIN(ANALYZE BUFFERS) would go a long way here.

You can't run EXPLAIN(ANALYZE BUFFERS) if you don't have a query to run.  
That's what track_activity_query_size is for.

>
>> Even setting taqs to 10KB isn't adequate, so I want to significantly bump 
>> it, but am
>
> It is before coffee here, so you will need to spell out what taqs is?

track_activity_query_size

>
>> concerned about side effects of setting it to 48KB or even 64KB.
>>
>

-- 
Born in Arizona, moved to Babylonia.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2023-07-07 15:53 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-07 14:42 track_activity_query_size max practical size? Ron <ronljohnsonjr@gmail.com>
2023-07-07 14:51 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-07 14:55   ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-07 14:58     ` Ron <ronljohnsonjr@gmail.com>
2023-07-07 15:13       ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-07 15:27         ` Ron <ronljohnsonjr@gmail.com>
2023-07-07 15:53           ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-07 14:56   ` Ron <ronljohnsonjr@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox