Thread: Comment on max_locks_per_transaction
Folks,
Way it is now:
===============
max_locks_per_transaction (integer)
The shared lock table tracks locks on max_locks_per_transaction *
(max_connections + max_prepared_transactions) objects (e.g., tables);
hence, no more than this many distinct objects can be locked at any one
time. This parameter controls the average number of object locks
allocated for each transaction; individual transactions can lock more
objects as long as the locks of all transactions fit in the lock table.
This is not the number of rows that can be locked; that value is
unlimited. The default, 64, has historically proven sufficient, but you
might need to raise this value if you have clients that touch many
different tables in a single transaction. This parameter can only be set
at server start.
Increasing this parameter might cause PostgreSQL to request more
System V shared memory than your operating system's default
configuration allows. See Section 17.4.1 for information on how to
adjust those parameters, if necessary.
When running a standby server, you must set this parameter to the
same or higher value than on the master server. Otherwise, queries will
not be allowed in the standby server.
================
The way it should be:
max_locks_per_transaction (integer)
The shared lock table tracks locks on max_locks_per_transaction *
(max_connections + max_prepared_transactions) objects (e.g., tables);
hence, no more than this many distinct objects can be locked at any one
time. This parameter controls the average number of object locks
allocated for each transaction; individual transactions can lock more
objects as long as the locks of all transactions fit in the lock table.
This is not the number of rows that can be locked; that value is
unlimited. This parameter can only be set at server start.
The default, 64, has historically proven sufficient for most databases,
but you might need to raise this value if you have clients that touch
many different tables in a single transaction. Databases with several
tables with many partitions each can require raising this setting. The
PostgreSQL activity log will contain a fairly clear error message
suggesting raising max_locks_per_transaction if needed.
Increasing this parameter might cause PostgreSQL to request more
System V shared memory than your operating system's default
configuration allows. See Section 17.4.1 for information on how to
adjust those parameters, if necessary.
When running a standby server, you must set this parameter to the
same or higher value than on the master server. Otherwise, queries will
not be allowed in the standby server.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
On Fri, 2012-06-15 at 11:05 -0700, Josh Berkus wrote:
> The default, 64, has historically proven sufficient for most databases,
> but you might need to raise this value if you have clients that touch
> many different tables in a single transaction. Databases with several
> tables with many partitions each can require raising this setting.
Is "partition" defined somewhere else in the docs?
Maybe it should say something like: "Extensive use of table inheritance
is the most common reason to increase this value from the default",
assuming that's what you meant.
Regards,
Jeff Davis
On 6/15/12 12:25 PM, Jeff Davis wrote: > On Fri, 2012-06-15 at 11:05 -0700, Josh Berkus wrote: >> The default, 64, has historically proven sufficient for most databases, >> but you might need to raise this value if you have clients that touch >> many different tables in a single transaction. Databases with several >> tables with many partitions each can require raising this setting. > > Is "partition" defined somewhere else in the docs? > > Maybe it should say something like: "Extensive use of table inheritance > is the most common reason to increase this value from the default", > assuming that's what you meant. Hmmm. I think we should also say "partitioning", as well as "inheritance". Maybe: "Extensive use of table inheritance, such as for tables with many partitions, may require raising this setting." Works? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On Fri, 2012-06-15 at 12:37 -0700, Josh Berkus wrote: > Hmmm. I think we should also say "partitioning", as well as > "inheritance". Maybe: > > "Extensive use of table inheritance, such as for tables with many > partitions, may require raising this setting." http://www.postgresql.org/docs/9.2/static/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION Looks like we do define it, so that's fine with me. Regards, Jeff Davis
On fre, 2012-06-15 at 11:05 -0700, Josh Berkus wrote: > Folks, > > Way it is now: > The way it should be: This would be easier to process if you had sent a diff.
> This would be easier to process if you had sent a diff. Do you agree with the changes? I can send a diff. I personally find SGML impossible to read, though, so I never propose changes that way. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On ons, 2012-06-20 at 14:47 -0700, Josh Berkus wrote: > > This would be easier to process if you had sent a diff. > > Do you agree with the changes? I can send a diff. I personally find > SGML impossible to read, though, so I never propose changes that way. I don't know, because I can't see what's changed if you don't send a diff. It would be fine if you sent a diff between plain text, to illustrate what you mean, even if it's not the actual source code.
> I don't know, because I can't see what's changed if you don't send a > diff. It would be fine if you sent a diff between plain text, to > illustrate what you mean, even if it's not the actual source code. Odd; nobody else seems to have had trouble understanding my post. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Excerpts from Josh Berkus's message of jue jun 21 18:53:06 -0400 2012: > > > I don't know, because I can't see what's changed if you don't send a > > diff. It would be fine if you sent a diff between plain text, to > > illustrate what you mean, even if it's not the actual source code. > > Odd; nobody else seems to have had trouble understanding my post. It takes a lot more effort, though -- I, for one, would read both versions in parallel to see what differs (and I'm not Leonardo). If you send a patch the differences are immediately obvious (assuming you don't do anything silly like reflowing the whole paragraph). -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
I have applied the attached patch to document this issue. --------------------------------------------------------------------------- On Fri, Jun 15, 2012 at 11:05:30AM -0700, Josh Berkus wrote: > Folks, > > Way it is now: > > =============== > > max_locks_per_transaction (integer) > > The shared lock table tracks locks on max_locks_per_transaction * > (max_connections + max_prepared_transactions) objects (e.g., tables); > hence, no more than this many distinct objects can be locked at any one > time. This parameter controls the average number of object locks > allocated for each transaction; individual transactions can lock more > objects as long as the locks of all transactions fit in the lock table. > This is not the number of rows that can be locked; that value is > unlimited. The default, 64, has historically proven sufficient, but you > might need to raise this value if you have clients that touch many > different tables in a single transaction. This parameter can only be set > at server start. > > Increasing this parameter might cause PostgreSQL to request more > System V shared memory than your operating system's default > configuration allows. See Section 17.4.1 for information on how to > adjust those parameters, if necessary. > > When running a standby server, you must set this parameter to the > same or higher value than on the master server. Otherwise, queries will > not be allowed in the standby server. > > ================ > > The way it should be: > > max_locks_per_transaction (integer) > > The shared lock table tracks locks on max_locks_per_transaction * > (max_connections + max_prepared_transactions) objects (e.g., tables); > hence, no more than this many distinct objects can be locked at any one > time. This parameter controls the average number of object locks > allocated for each transaction; individual transactions can lock more > objects as long as the locks of all transactions fit in the lock table. > This is not the number of rows that can be locked; that value is > unlimited. This parameter can only be set at server start. > > The default, 64, has historically proven sufficient for most databases, > but you might need to raise this value if you have clients that touch > many different tables in a single transaction. Databases with several > tables with many partitions each can require raising this setting. The > PostgreSQL activity log will contain a fairly clear error message > suggesting raising max_locks_per_transaction if needed. > > Increasing this parameter might cause PostgreSQL to request more > System V shared memory than your operating system's default > configuration allows. See Section 17.4.1 for information on how to > adjust those parameters, if necessary. > > When running a standby server, you must set this parameter to the > same or higher value than on the master server. Otherwise, queries will > not be allowed in the standby server. > > > -- > Josh Berkus > PostgreSQL Experts Inc. > http://pgexperts.com > > -- > Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-docs -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +