pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
to many locks held 4+ messages / 4 participants [nested] [flat]
* to many locks held @ 2013-07-30 10:52 Jeison Bedoya <jeisonb@audifarma.com.co> 0 siblings, 2 replies; 4+ messages in thread From: Jeison Bedoya @ 2013-07-30 10:52 UTC (permalink / raw) To: pgsql-performance Hi everybody, In recent days, we have seen many processes in reaching the lock held 5000. 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. 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: memory ram: 128 GB cores: 32 max_connections: 900 shared_buffers = 2048MB work_mem = 1024MB maintenance_work_mem = 1024MB temp_buffers = 512MB checkpoint_segments = 103 regrads -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: to many locks held @ 2013-07-30 14:48 bricklen <bricklen@gmail.com> parent: Jeison Bedoya <jeisonb@audifarma.com.co> 1 sibling, 1 reply; 4+ messages in thread From: bricklen @ 2013-07-30 14:48 UTC (permalink / raw) To: Jeison Bedoya <jeisonb@audifarma.com.co>; +Cc: pgsql-performance On Tue, Jul 30, 2013 at 3:52 AM, Jeison Bedoya <jeisonb@audifarma.com.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? ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: to many locks held @ 2013-07-31 05:18 Michael Paquier <michael.paquier@gmail.com> parent: bricklen <bricklen@gmail.com> 0 siblings, 0 replies; 4+ messages in thread From: Michael Paquier @ 2013-07-31 05:18 UTC (permalink / raw) To: bricklen <bricklen@gmail.com>; +Cc: Jeison Bedoya <jeisonb@audifarma.com.co>; pgsql-performance On Tue, Jul 30, 2013 at 11:48 PM, bricklen <bricklen@gmail.com> wrote: > On Tue, Jul 30, 2013 at 3:52 AM, Jeison Bedoya <jeisonb@audifarma.com.co>wrote: > 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. > Yeah that's a lot. pgbouncer might be a good option in your case. 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)? > work_mem is a per-operation setting for sort/hash operations. So in your case you might finish with a maximum of 900GB of memory allocated based on the maximum number of sessions that can run in parallel on your server. Simply reduce the value of work_mem to something your server can manage and you should be able to solve your problems of OOM. -- Michael ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: to many locks held @ 2013-08-02 21:03 Kevin Grittner <kgrittn@ymail.com> parent: Jeison Bedoya <jeisonb@audifarma.com.co> 1 sibling, 0 replies; 4+ messages in thread From: Kevin Grittner @ 2013-08-02 21:03 UTC (permalink / raw) To: Jeison Bedoya <jeisonb@audifarma.com.co>; pgsql-performance Jeison Bedoya <jeisonb@audifarma.com.co> wrote: > memory ram: 128 GB > cores: 32 > > max_connections: 900 > temp_buffers = 512MB In addition to the other comments, be aware that temp_buffers is the limit of how much RAM *each connection* can acquire to avoid writing temporary table data to disk. Once allocated to a connection, it will be reserved for that use on that connection until the connection closes. So temp_buffers could lock down 450 GB of RAM even while all connections are idle. If the maximum connections become active, and they average one work_mem allocation apiece, that's an *additional* 900 GB of RAM which would be needed to avoid problems. Reducing connections through a pooler is strongly indicated, and you may still need to reduce work_mem or temp_buffers. http://wiki.postgresql.org/wiki/Number_Of_Database_Connections -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2013-08-02 21:03 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2013-07-30 10:52 to many locks held Jeison Bedoya <jeisonb@audifarma.com.co> 2013-07-30 14:48 ` bricklen <bricklen@gmail.com> 2013-07-31 05:18 ` Michael Paquier <michael.paquier@gmail.com> 2013-08-02 21:03 ` Kevin Grittner <kgrittn@ymail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox