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.-