pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Practical maximum max_locks_per_transaction?
3+ messages / 2 participants
[nested] [flat]

* Practical maximum max_locks_per_transaction?
@ 2022-11-25 23:45 Ron <ronljohnsonjr@gmail.com>
  2022-11-25 23:56 ` Re: Practical maximum max_locks_per_transaction? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Ron @ 2022-11-25 23:45 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

v9.6.24 (being retired next year)

https://www.postgresql.org/docs/9.6/runtime-config-locks.html

"The default, 64, has historically proven sufficient, but you might need to 
raise this value if you have queries that touch many different tables in a 
single transaction, e.g., query of a parent table with many children."

How "raised" is too raised?  I just had to increase 
max_locks_per_transaction from 640 to 1024 on an instance with many child 
tables and against which is run many large reports.  Another instance has 
zero children, but had to increase the value to 1280 to prevent reports from 
failing.

-- 
Angular momentum makes the world go 'round.

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

* Re: Practical maximum max_locks_per_transaction?
  2022-11-25 23:45 Practical maximum max_locks_per_transaction? Ron <ronljohnsonjr@gmail.com>
@ 2022-11-25 23:56 ` Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-26 07:33   ` Re: Practical maximum max_locks_per_transaction? Ron <ronljohnsonjr@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2022-11-25 23:56 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

Ron <ronljohnsonjr@gmail.com> writes:
> How "raised" is too raised?  I just had to increase 
> max_locks_per_transaction from 640 to 1024 on an instance with many child 
> tables and against which is run many large reports.  Another instance has 
> zero children, but had to increase the value to 1280 to prevent reports from 
> failing.

max_locks_per_transaction in isolation means nothing.  What counts
is the product max_locks_per_transaction * max_connections, which is
(more or less) the number of slots allocated in the shared lock table.

Having said that, you can probably make it as big as you want
on any reasonably modern machine.  I wouldn't blink at a few
million locktable entries, at least not on 64-bit hardware.

			regards, tom lane





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

* Re: Practical maximum max_locks_per_transaction?
  2022-11-25 23:45 Practical maximum max_locks_per_transaction? Ron <ronljohnsonjr@gmail.com>
  2022-11-25 23:56 ` Re: Practical maximum max_locks_per_transaction? Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-11-26 07:33   ` Ron <ronljohnsonjr@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Ron @ 2022-11-26 07:33 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 11/25/22 17:56, Tom Lane wrote:
> Ron <ronljohnsonjr@gmail.com> writes:
>> How "raised" is too raised?  I just had to increase
>> max_locks_per_transaction from 640 to 1024 on an instance with many child
>> tables and against which is run many large reports.  Another instance has
>> zero children, but had to increase the value to 1280 to prevent reports from
>> failing.
> max_locks_per_transaction in isolation means nothing.  What counts
> is the product max_locks_per_transaction * max_connections, which is
> (more or less) the number of slots allocated in the shared lock table.
>
> Having said that, you can probably make it as big as you want
> on any reasonably modern machine.  I wouldn't blink at a few
> million locktable entries, at least not on 64-bit hardware.

Really good to know.  I wonder how that can be worded in the documentation.

-- 
Angular momentum makes the world go 'round.





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


end of thread, other threads:[~2022-11-26 07:33 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-25 23:45 Practical maximum max_locks_per_transaction? Ron <ronljohnsonjr@gmail.com>
2022-11-25 23:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-26 07:33   ` 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