Re: Sudden slow down and spike in system CPU causes max_connections to get exhausted

On Tue, Jan 7, 2014 at 9:06 AM, Anand Kumar, Karthik <
Karthik(dot)AnandKumar(at)classmates(dot)com> wrote:

> We do typically have a lot of idle connections (1500 connections total,
> over a 1000 idle at any given time). We're in the midst of installing
> pgbouncer to try and mitigate the problem, but that still doesn't address
> the root cause.
>

1500 connections in total (is that the number you are using for your
max_connections too)? How many CPUs/CPU threads you have on your server?
How many concurrent transactions are happening in your database? 1000 idle
at any give time? Whoa! Is that by design or a defect which you have
decided to live with? 1000 idle connection are idle or idle in transaction?

What pooling mode are you using in pgbouncer? Going by your description, I
will suggest that you use transaction mode. Since you either have bugs or
deliberately (why? why?) keep connections open/idle.

So in sessions mode you may not get much benefits from pgbouncer.

While you use pgbouncer you can set max_clients to a higher number and try
to set timeouts for your clients (note that it will timeout even those
clients who are in middle of transaction but are idle for long).

Best Regards,
*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD. *101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M : *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

[image: email patch]

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).