Question about locking and pg_locks
От | Moreno Andreo |
---|---|
Тема | Question about locking and pg_locks |
Дата | |
Msg-id | c79938be-3a1f-8959-be02-e924429be0a4@evolu-s.it обсуждение исходный текст |
Ответы |
Re: Question about locking and pg_locks
Re: Question about locking and pg_locks |
Список | 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.-
В списке pgsql-general по дате отправления: