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