PostgreSQL: You might need to increase max_locks_per

“out of shared memory”: Some of you might have seen that error message in PostgreSQL already. But what does it really mean, and how can you prevent it? The problem is actually not as obscure as it might seem at first glance. max_locks_per_transaction is the critical configuration parameter you need to use to avoid trouble.

Table of Contents

“out of shared memory”: When it happens

Most of the shared memory used by PostgreSQL is of a fixed size. This is true for the I/O cache (shared buffers) and for many other components as well. One of those components has to do with locking. If you touch a table inside a transaction, PostgreSQL has to track your activity to ensure that a concurrent transaction cannot drop the table you are about to touch. Tracking activity is important because you want to make sure that a DROP TABLE (or some other DDL) has to wait until all reading transactions have terminated. The trouble is, you have to store information about tracked activity somewhere-- and this point is exactly what you have to understand.

Let us run a simple script:

BEGIN;

SELECT 'CREATE TABLE a' || id || ' (id int);' 
       FROM generate_series(1, 20000) AS id;

gexec

BEGIN;

SELECT 'CREATE TABLE a' || id || ' (id int);'

FROM generate_series(1, 20000) AS id;

gexec

What this script does is to start a transaction and to generate 20.000 CREATE TABLE statements. It simply generates SQL which is then automatically executed (gexec treats the result of the previous SQL statement as input).

Let us see what the SELECT statement produced ...

BEGIN
          ?column?          
----------------------------
 CREATE TABLE a1 (id int);
 CREATE TABLE a2 (id int);
 CREATE TABLE a3 (id int);
 CREATE TABLE a4 (id int);
 CREATE TABLE a5 (id int);
...

BEGIN

?column?

----------------------------

CREATE TABLE a1 (id int);

CREATE TABLE a2 (id int);

CREATE TABLE a3 (id int);

CREATE TABLE a4 (id int);

CREATE TABLE a5 (id int);

...

And now let us see what PostgreSQL does:

...
CREATE TABLE
CREATE TABLE
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
...

...

CREATE TABLE

ERROR: out of shared memory

HINT: You might need to increase max_locks_per_transaction.

ERROR: current transaction is aborted, commands ignored until end of transaction block

...

After a few thousand tables, PostgreSQL will error out: “out of shared memory”. What you can see is that we created all those tables in a single transaction. PostgreSQL had to lock them and eventually ran out of memory. Remember: The database is using a fixed-size shared memory field to store those locks.

The logical question is: What is the size of this memory field? Two parameters come into play:

test=# SHOW max_connections;
 max_connections
-----------------
 100
(1 row)

test=# SHOW max_locks_per_transaction;
 max_locks_per_transaction
---------------------------
 64
(1 row)

test=# SHOW max_connections;

max_connections

-----------------

100

(1 row)

test=# SHOW max_locks_per_transaction;

max_locks_per_transaction

---------------------------

(1 row)

The number of locks we can keep in shared memory is max_connections x max_locks_per_transaction.

Keep in mind that row level locks are NOT relevant here. You can easily do a …

SELECT * FROM billions_of_rows FOR UPDATE;

1	SELECT * FROM billions_of_rows FOR UPDATE;

… without running out of memory because row locks are stored on disk and not in RAM. Therefore the number of tables is relevant – not the number of rows.

Inspecting pg_locks

How can you figure out what is currently going on? To demonstrate what you can do, I have prepared a small example:

test=# CREATE TABLE t_demo (id int);
CREATE TABLE

1 2	test=# CREATE TABLE t_demo (id int); CREATE TABLE

First of all, you can create a simple table.
As you might know, in PostgreSQL names are not relevant at all. Internally, only numbers count. To fetch the object ID of a simple table, try the following statement:

test=# SELECT oid, relkind, relname
		FROM 	pg_class
 		WHERE relname = 't_demo';
  oid   | relkind | relname
--------+---------+---------
 232787 | r       | t_demo
(1 row)

test=# SELECT oid, relkind, relname

FROM pg_class

WHERE relname = 't_demo';

oid | relkind | relname

--------+---------+---------

232787 | r | t_demo

(1 row)

In my example, the object id is 232787. Let us figure out where this number pops up:

test=# BEGIN;
BEGIN
test=# SELECT * FROM t_demo;
 id
----
(0 rows)

test=# x
Expanded display is on.
test=# SELECT * FROM pg_locks WHERE relation = '232787';
-[ RECORD 1 ]------+----------------
locktype           | relation
database           | 187812
relation           | 232787
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 3/6633
pid                | 106174
mode               | AccessShareLock
granted            | t
fastpath           | t

test=# BEGIN;

BEGIN

test=# SELECT * FROM t_demo;

----

(0 rows)

test=# x

Expanded display is on.

test=# SELECT * FROM pg_locks WHERE relation = '232787';

-[ RECORD 1 ]------+----------------

locktype | relation

database | 187812

relation | 232787

page |

tuple |

virtualxid |

transactionid |

classid |

objid |

objsubid |

virtualtransaction | 3/6633

pid | 106174

mode | AccessShareLock

granted | t

fastpath | t

Since we are reading from the table, you can see that PostgreSQL has to keep an ACCESS SHARE LOCK which only ensures that the table cannot be dropped or modified (= DDL) in a way that harms concurrent SELECT statements.
The more tables a transaction touches, the more entries pg_locks will have. In case of heavy concurrency, multiple entries can become a problem.

PostgreSQL partitioning and how it relates to “out of shared memory”

If you are running a typical application, out of memory errors are basically rare because the overall number of relevant locks is usually quite low. However, if you are heavily relying on excessive partitioning, life is different. In PostgreSQL, a partition is basically a normal table-- and it is treated as such. Therefore, locking can become an issue.

Let us take a look at the following example:

BEGIN;

CREATE TABLE t_part (id int) PARTITION BY LIST (id);

SELECT 'CREATE TABLE t_part_' || id
	|| ' PARTITION OF t_part FOR VALUES IN ('
	|| id || ');'
FROM 	generate_series(1, 1000) AS id;

gexec

SELECT count(*) FROM t_part;

BEGIN;

CREATE TABLE t_part (id int) PARTITION BY LIST (id);

SELECT 'CREATE TABLE t_part_' || id

|| ' PARTITION OF t_part FOR VALUES IN ('

|| id || ');'

FROM generate_series(1, 1000) AS id;

gexec

SELECT count(*) FROM t_part;

First of all, a parent table is created. Then, 1000 partitions are added. For the sake of simplicity, each partition is only allowed to hold exactly one row-- but let’s not worry about that for now. Following that, a simple SELECT statement is executed—such a statement is guaranteed to read all partitions.

The following listing shows which SQL the script has generated to create partitions:

                              ?column?                              
--------------------------------------------------------------------
 CREATE TABLE t_part_1 PARTITION OF t_part FOR VALUES IN (1);
 CREATE TABLE t_part_2 PARTITION OF t_part FOR VALUES IN (2);
 CREATE TABLE t_part_3 PARTITION OF t_part FOR VALUES IN (3);
 CREATE TABLE t_part_4 PARTITION OF t_part FOR VALUES IN (4);
 CREATE TABLE t_part_5 PARTITION OF t_part FOR VALUES IN (5);
...

?column?

--------------------------------------------------------------------

CREATE TABLE t_part_1 PARTITION OF t_part FOR VALUES IN (1);

CREATE TABLE t_part_2 PARTITION OF t_part FOR VALUES IN (2);

CREATE TABLE t_part_3 PARTITION OF t_part FOR VALUES IN (3);

CREATE TABLE t_part_4 PARTITION OF t_part FOR VALUES IN (4);

CREATE TABLE t_part_5 PARTITION OF t_part FOR VALUES IN (5);

...

After running the

SELECT count(*) FROM t_part

1	SELECT count(*) FROM t_part

statement, the important observation is now:

SELECT 	count(*)
FROM 	pg_locks
WHERE 	mode = 'AccessShareLock';
 count
-------
  1004
(1 row)

SELECT count(*)

FROM pg_locks

WHERE mode = 'AccessShareLock';

count

-------

1004

(1 row)

When to change max_locks_per_transaction in regard to partitioning

PostgreSQL already needs more than 1000 locks to do this. Partitioning will therefore increase the usage of this shared memory field and make “out of memory” more likely. If you are using partitioning HEAVILY, it can make sense to change max_locks_per_transaction.

Finally …

In case you are interested in Data Science and Machine Learning, you can check out Kevin Speyer’s post on “Reinforcement Learning” which can be found here.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.

0 0 votes

Article Rating

4 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

bupathi laxman

3 years ago

We have this problem in our database. High lock, cpu and DB crash happens when we add more partitions for upcoming months. Each new monthly/weekly partition has sub-partitions(exactly 11 sub-partitions on each partition) . Recently after adding partitions for next 3 months with sub-partitions (Older partitions didn't have subpartitions. Sub-partitions are added for new partitions only).

SELECT queries on this causing high CPU consumption which saturates the CPU and slows down the query which leads to load pile-up. Side effect of this, we see too many locks, 10K times than normal when load is pile up.

Im planning to do a perf test to find a solution to improve the performance of the queries against this table. I have tried increasing the parameter "max_locks_per_transaction" few months back for a problem in different table in different DB to solve a similar CPU/slowness issues, but increasing this parameter didn't help, so we had to detach very old partitions which reduce the number of partitions in the table and CPU consumption came down.

Still I like to evaluate this parameter once again in a perf environment. Should I consider any other parameter to tune or is any other solution available to solve my problem?

Thanks in advance. It will be great if you could help on my problem.

Regards, Boopathi L

laurenz

3 years ago

Reply to bupathi laxman

The system can also run out of shared memory in parallel queries.

It is impossible to diagnose your problem with the information you gave; you might want to buy some consulting and have someone look at your problem in depth.

Seems like the underlying problem is the long query time, perhaps you can start working on that.

bupathi laxman

3 years ago

Reply to laurenz

Thanks laurenz, for the response.

There was no long running transactions in our case. Our problem is high concurrent workload on the table. When more partitions to be locked for every query/transaction, CPU increases and when CPU saturates, the queries gets slowed down and thats leading to pile up in the load.

What I like to sort out here is, how to reduce the CPU consumed by locks when a query execution has to lock many partitions. Right now we dont have the sub-partition's partition key column in the WHERE Clause of the query. Due to this, every execution has to take lock on multiple partitions.

We can solve this by having the sub-partition's partition key columns in the WHERE clause. But we can't do that for some reasons, so we need solve this by other way.

Alhashash Mohammad

4 years ago

It should be also noted that if the query touches only a few partitions, PostgreSQL locks these partitions only.

commit; begin;


# SELECT count(*) FROM t_part where id=11;

 count

-------

     0

(1 row)
# SELECT  count(*)  FROM    pg_locks WHERE   mode = 'AccessShareLock';

 count

-------

     3

(1 row)
# SELECT count(*) FROM t_part where id=12;

 count

-------

     0

(1 row)
# SELECT  count(*)  FROM    pg_locks WHERE   mode = 'AccessShareLock';

 count

-------

     4

(1 row)
# SELECT count(*) FROM t_part where id>=950;

 count

-------

     0

(1 row)

# SELECT count(*) FROM pg_locks WHERE mode = 'AccessShareLock'; count ------- 55 (1 row)

Stay tuned with our

PostgreSQL: You might need to increase max_locks_per_transaction

“out of shared memory”: When it happens

And now let us see what PostgreSQL does:

The number of locks we can keep in shared memory is max_connections x max_locks_per_transaction.

Inspecting pg_locks

PostgreSQL partitioning and how it relates to “out of shared memory”

When to change max_locks_per_transaction in regard to partitioning

Finally …

Hans-Jürgen Schönig

Blog Tags

NEWSLETTER

Articles by our PostgreSQL Experts