Re: Out of Shared Memory: max_locks_per_transaction

> most likely possibility you have a transaction being left open and
> accumulating locks. of course, you have to rule out the fact that
> you simply have to increase max_locks_per_transaction: if you have a
> lot of tables, it might be reasonable to have to extend this on a
> stock config.
>
>
We allow 500 connections to the DB with 64 locks per transaction = 32,000
locks. During the stress testing, I had roughly 40 simultaneous operations
going through the test application. The test application uses a separate
set of threads for accessing the database along with a shared connection
pool and a FIFO queue attached to each connection. Queries are mostly
insert, update, and delete, so they are batched into transactions in blocks
of 100 - 1000 queries per transaction. At the start of the transaction, a
stored procedure is called which acquires locks on 8 tables in a specific
order to prevent triggers on the associated tables from deadlocking with
each other and with other things accessing the database. In total, there
might be 15 tables (at most) touched by the batch of queries.

Another process comes along and processes records which are being inserted
into the database. It pulls up to 10 records from a table, processes them,
and moves those records into a "processed" table. The processing of the
records is rather complex. To facilitate the processing, 6 temporary tables
are created during the processing of each record, and then dropped after
that record is completed. 8 additional tables are accessed in some way
during the processing of each record. Each call to the processing stored
procedure is run in its own transaction and handles only those 10 records
at a time. This is done to keep the length of the transaction short so it
does not block other activity in the database.

> one thing that can cause this unfortunately is advisory locks eating
> up exactly the amount of shared memory you have. that's another thing
> to rule out.
>

How would I rule this out?

>
> > How do I track down what is going on if I cannot even run psql to get
> into
> > the DB to run troubleshooting queries?
>
> it's a nasty problem. if shared memory is exhausted and stuck i think
> the only practical think to do is to restart the database or nuking
> attached clients. maybe try restarting the test, but keep an open
> session *with an open transaction* that has previously queried both
> pg_locks and pg_stat_activity. it's just a hunch, but perhaps this
> might allow you to query said views and gather some details.
>
>
That is an interesting suggestion. I will definitely give that a try.

Is the pg_locks table the table I would query to see what is eating up
those 32,000 locks? Is there some other table or query I could run which
might provide additional information about those 32,000 locks and who /
what is using them?

Thanks for the info and your suggestions!