Question about locking and pg_locks

From: Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it>
To: PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org>
Subject: Question about locking and pg_locks
Date: 2016-09-08 11:30:33
Message-ID: c79938be-3a1f-8959-be02-e924429be0a4@evolu-s.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi folks! :-)

This morning I was woken up by a call of a coworker screaming "Help, our
Postgres server is throwing strange errors!"
Not the best way to start your day...

OK, to the serious part.

"Strange errors" were (in postgresql-9.1-main.log)
WARNING: out of shared memory
ERROR: out of shared memory
HINT: you may need to increase max_locks_per_transaction

Restarting Postgresql solved the issue (for now), but that's what I'm
wondering:
- the greatest part of this locks are used by rubyrep (that we use to
replicate users' databases), no new users since 3 weeks, first time
error show up in almost 2 years
- I read this: https://wiki.postgresql.org/wiki/Lock_Monitoring but
still I can't figure out what to do if I need to know if I have to be
worried or not :-)
- I have
OS: Ubuntu 12.04 (won't upgrade because we are leaving this server to a
new one with Debian Jessie)
PG: 9.1.6 (same as above, in new server ve have 9.5.4)
RAM: 32 GB
shared_buffers = 2GB
max_connections=800
max_locks_per_transaction=64 (default value)
max_prepared_transactions = 0

so, I should be able to manage 800*64 = 5120 locks, right?

Now my pg_locks table has more than 6200 rows, but if I reorder them by
pid I see that one of them has 5800 of them, and it keeps on eating locks.
If I dig more and get pid info, its state is "<IDLE> in transaction"

ATM there are no locks that have granted = false.

Now, question time:
- Is there a number of pg_locks rows to be worried about? At more than
6000 I'm still not facing out of shared memory again
- Is there a way to release locks of that pid without pg_terminate() it?

I tried to give most of the details, if you need more, just ask...
Thanks
Moreno.-

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stefan Keller 2016-09-08 11:31:29 Re: Do parallel queries work with only dblink not with fdw?
Previous Message Martijn Tonies (Upscene Productions) 2016-09-08 11:14:00 ANN: Database Workbench 5.2 now includes PostgreSQL support