“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 happensMost 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:
|
1 2 3 4 5 6 |
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 ...
|
1 2 3 4 5 6 7 8 9 |
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); ... |
|
1 2 3 4 5 6 7 8 9 10 |
... 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 ... |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
test=# SHOW max_connections; max_connections ----------------- 100 (1 row) test=# SHOW max_locks_per_transaction; max_locks_per_transaction --------------------------- 64 (1 row) |
Keep in mind that row level locks are NOT relevant here. You can easily do a …
|
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.
How can you figure out what is currently going on? To demonstrate what you can do, I have prepared a small example:
|
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:
|
1 2 3 4 5 6 7 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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 |
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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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:
|
1 2 3 4 5 6 7 8 |
?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
|
1 |
SELECT count(*) FROM t_part |
statement, the important observation is now:
|
1 2 3 4 5 6 7 |
SELECT count(*) FROM pg_locks WHERE mode = 'AccessShareLock'; count ------- 1004 (1 row) |
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.
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 Facebook or LinkedIn.
+43 (0) 2622 93022-0
office@cybertec.at
You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
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)
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
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.
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.