Re: to many locks held

On Tue, Jul 30, 2013 at 3:52 AM, Jeison Bedoya <jeisonb(at)audifarma(dot)com(dot)co>wrote:

> Hi everybody,
>
> In recent days, we have seen many processes in reaching the lock held 5000.

Do you know what queries are holding locks? Is that behaviour expected?

> At that time my machine will become sluggish and no response from the
> database. I tried to change configuration parameters, but have not found
> anything satisfactory. further in meeting log messages like the following:
> COTidleERROR: out of memory
> COTidleDETAIL: Can not enlarge string buffer container containing 0 bytes
> by 1476395004 more bytes.
>

I've never come across that message before, so someone wiser will need to
comment on that.

> COTidleLOG: incomplete message from client
> COTUPDATE waitingLOG: process 20761 still waiting for ShareLock on
> transaction 10,580,510 1664,674 ms after
>
> My machine is on linux postgres version 9.2.2, and the following settings:
>

You will want to upgrade to the latest point release (9.2.4) as there was a
serious security vulnerability fixed in 9.2.3. Details:
http://www.postgresql.org/about/news/1446/

>
> memory ram: 128 GB
> cores: 32
>
> max_connections: 900
>

I would say you might be better off using a connection pooler if you need
this many connections.

work_mem = 1024MB
>

work_mem is pretty high. It would make sense in a data warehouse-type
environment, but with a max of 900 connections, that can get used up in a
hurry. Do you find your queries regularly spilling sorts to disk (something
like "External merge Disk" in your EXPLAIN ANALYZE plans)?

Have you looked at swapping and disk I/O during these periods of
sluggishness?