Understanding max_locks_per_transaction - Mailing list pgsql-general
| From | Craig McIlwee |
|---|---|
| Subject | Understanding max_locks_per_transaction |
| Date | |
| Msg-id | CAGqBcTY9N_=ChgGMVkfm=2gMMZPOBCViSTmRkvPhRC0jCVg5Gw@mail.gmail.com Whole thread Raw |
| Responses |
Re: Understanding max_locks_per_transaction
|
| List | pgsql-general |
We're using PostgreSQL 13.10, installed on CentOS 7 from PGDG RPMs.
Most discussions regarding the lock table say that the size of the lock table determines how many locks can be held. The documentation for max_locks_per_transaction [3] reads slightly different though, and in particular this phrases stands out to me:
> no more than this many distinct objects can be locked at any one time
To me, that seems to be saying that multiple locks for the same object (e.g. for a single table) would only consume a single lock table entry. Finally on to my first question: Am I interpreting the documentation correctly, that multiple locks for the same object only consume a single lock table entry, or am I reading too much into this and the size of the lock table really does dictate the total number of locks regardless of whether those locks point to the same object? If my interpretation is correct, then I can size the lock table to be slightly above the total number of database objects and I should be safe.
Recently we've run into "out of shared memory" issues with a hint at increasing max_locks_per_transaction. The problem is well described in the PostgreSQL documentation and various blog posts found around the internet, and the solution is straightforward - touch fewer objects per transaction or increase the size of the lock table. The error occurs when joining partitioned tables, and changing the query structure is something we'd like to avoid, so we are going the route of increasing the size of the lock table by increasing max_locks_per_transaction.
Many blog posts suggest against using an arbitrarily large value for max_locks_per_transaction to avoid excess memory usage by the lock table. Contrary to that is an email from Tom Lane [1] indicating that a lock table with several million slots wouldn't be so bad. A SO answer from Laurenz Albe [2] tells us that a lock entry consumes 168 bytes so a table with 1m slots would consume somewhere around 160MB (assuming we are on the same architecture, but if not then at least still probably within an order of magnitude). I suppose I could set the max_locks_per_transaction value really high, but, on the other hand, if I can make a pretty good estimate about how many locks are actually needed then I can set a lower value and let some of that RAM be used for other things like disk caching instead. So here I am, trying to estimate the amount of locks that would actually be needed but have some questions and am running into some behavior that I do not expect.
> no more than this many distinct objects can be locked at any one time
To me, that seems to be saying that multiple locks for the same object (e.g. for a single table) would only consume a single lock table entry. Finally on to my first question: Am I interpreting the documentation correctly, that multiple locks for the same object only consume a single lock table entry, or am I reading too much into this and the size of the lock table really does dictate the total number of locks regardless of whether those locks point to the same object? If my interpretation is correct, then I can size the lock table to be slightly above the total number of database objects and I should be safe.
I tried to test my understanding by creating two database connections and in each connection issuing a query that touched a large number of tables (enough to almost reach the size of the lock table). My theory was that if multiple locks for the same object only consume a single lock table entry, then multiple connections could each issue the same big query that used a large number of locks and no issue would arise. For example, with the default settings (max_connections = 100, max_prepared_statements = 0, max_locks_per_transaction = 64) I should have a lock table with 6400 slots so two connections that both touch 6000 database objects should be able to run concurrently. That test succeeded, but led me to the next observation...
To my surprise, during my testing I was able to issue queries that used well above the maximum number of locks that I was expecting and those queries completed without issue. Below are the relevant database settings and the results of a statement that locks many objects.
archive=# show max_connections ;
max_connections
-----------------
100
(1 row)
archive=# show max_prepared_transactions ;
max_prepared_transactions
---------------------------
0
(1 row)
archive=# show max_locks_per_transaction ;
max_locks_per_transaction
---------------------------
64
(1 row)
archive=# begin;
BEGIN
archive=*# explain <large select statement that joins many partitions>
max_connections
-----------------
100
(1 row)
archive=# show max_prepared_transactions ;
max_prepared_transactions
---------------------------
0
(1 row)
archive=# show max_locks_per_transaction ;
max_locks_per_transaction
---------------------------
64
(1 row)
archive=# begin;
BEGIN
archive=*# explain <large select statement that joins many partitions>
<explain output omitted for brevity>
archive=*# select count(*) as total, count(*) filter (where granted) as granted from pg_locks;
total | granted
-------+---------
7568 | 7568
(1 row)
archive=*# select count(distinct relation) from pg_locks ;
count
-------
7576
(1 row)
total | granted
-------+---------
7568 | 7568
(1 row)
archive=*# select count(distinct relation) from pg_locks ;
count
-------
7576
(1 row)
According to the documentation, I should have a lock table with 6400 entries but somehow have been able to obtain 7576 locks. So my second and last question: how is this possible - shouldn't I have received an "out of shared memory" error since I exceeded 6400 distinct locked objects?
Craig
pgsql-general by date: