I'm trying to monitor an application that uses PostgreSQL (13) to identify what can cause this error (occasionally):
ERROR: out of shared memory
Hint: You might need to increase max_pred_locks_per_transaction.
I've increase the max_pred_locks_per_transaction
(and max_locks_per_transaction
), but I'm trying to find the potential cause in the application itself, to see if something better can be done about it.
When I monitor the locks, there are sometimes SIReadLock
that seem to be running for a relatively long time, all hung up on COMMIT
(although they do finish eventually).
A number of the queries executed are likely to involve after-insert/update triggers.
Is there a way to monitor exactly which queries are creating those locks (as opposed to just seeing COMMIT
at the end)?
(Is there a general recommended way to investigate this sort of problems?)
Here is the query to monitor the locks:
SELECT pg_database.datname AS database,
pg_namespace.nspname AS schema,
pg_class.relname AS table,
pg_locks.mode AS lock_mode,
pg_locks.page AS lock_page,
pg_locks.tuple AS lock_tuple,
pg_locks.locktype AS lock_type,
pg_locks.virtualxid AS virtual_xid,
pg_locks.virtualtransaction AS virtual_transaction,
pg_locks.transactionid AS transaction_id,
AGE(now(), pg_stat_activity.query_start) AS time_running,
pg_stat_activity.query_start AS time_started,
pg_stat_activity.query
FROM pg_class
INNER JOIN pg_locks ON pg_locks.relation = pg_class.oid
INNER JOIN pg_database ON pg_database.oid = pg_locks.database
INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
INNER JOIN pg_stat_activity ON pg_stat_activity.pid = pg_locks.pid
WHERE pg_class.relkind = 'r'
ORDER BY 11 DESC;
Here is a sample output (more rows not displayed):
table | lock_mode | lock_page | lock_tuple | lock_type | virtual_xid | virtual_transaction| transaction_id | time_running | time_started | query
---------------+-----------------+-----------+------------+-----------+-------------+--------------------+----------------+-----------------+-------------------------------+--------------
test_table_a | SIReadLock | 8410 | | page | | 25/298509 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8416 | | page | | 25/295398 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_b | SIReadLock | | | relation | | 25/299949 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8411 | | page | | 25/296128 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_c | SIReadLock | | | relation | | 25/297437 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8413 | | page | | 25/300961 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8405 | | page | | 25/294361 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8403 | 110 | tuple | | 25/300599 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8419 | | page | | 25/298137 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_d | SIReadLock | | | relation | | 25/296086 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8413 | | page | | 25/294361 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8421 | | page | | 25/298137 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8422 | | page | | 25/296692 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_b | SIReadLock | | | relation | | 25/300080 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8405 | | page | | 25/299590 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_c | SIReadLock | | | relation | | 25/294222 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_b | SIReadLock | | | relation | | 25/299148 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8407 | | page | | 25/296692 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_d | SIReadLock | | | relation | | 25/295640 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8431 | | page | | 25/298779 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8426 | | page | | 25/294976 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8410 | | page | | 25/297792 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8416 | | page | | 25/294361 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8426 | | page | | 25/300585 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8417 | 1 | tuple | | 25/297792 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_c | SIReadLock | | | relation | | 25/295787 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_e | SIReadLock | | | relation | | 25/297764 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8421 | | page | | 25/295893 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8427 | | page | | 25/294719 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_d | SIReadLock | | | relation | | 25/297663 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_c | SIReadLock | | | relation | | 25/294139 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8431 | | page | | 25/300947 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_d | SIReadLock | | | relation | | 25/295370 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8422 | | page | | 25/297792 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_c | SIReadLock | | | relation | | 25/297064 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8410 | | page | | 25/299949 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8415 | | page | | 25/296128 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8428 | | page | | 25/300080 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8431 | | page | | 25/297423 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8417 | 1 | tuple | | 25/298509 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8408 | | page | | 25/297064 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8427 | | page | | 25/295384 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8425 | | page | | 25/294236 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
test_table_a | SIReadLock | 8415 | | page | | 25/294361 | | 00:45:46.929129 | 2021-09-06 12:13:31.525553+01 | COMMIT
(There's generally fewer than 30 connections to this database server, 10 to this database.)