1010433 - bouncer db too many connections

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Sheeri Cabral [:sheeri]]

11:55 < gozer> Hrm, seeing something annyoign with bouncer ATM, [Wed May 14 
               11:50:45 2014] [error] [client 10.8.81.219] SQLSTATE[08004] 
               [1040] Too many connections
11:56 < gozer> can someone have a look at the Bouncer DB and tell me about 
               connection limits / counts

Comment 1

[Sheeri Cabral [:sheeri]]

2:06 < gozer> sheeri: there were 2-3 bouncer web heads disabled in Zeus for no 
               reason, so we (I) turned them back on after verifying they were 
               healthy
12:06 < gozer> sheeri: so that must have caused an increase in client 
               connections
12:06 <@sheeri> ah
12:06 < gozer> sheeri: seeing that Tuxedo (the app) keeps one connection open 
               per httpd child
12:06 <@sheeri> OK
12:06 < gozer> at MaxClient of 200, 10 web nodes
12:06 < gozer> that's 2,000 connections theoritical maximum
12:06 <@sheeri> so we should have at least 2000
12:06 <@sheeri> well, plus a few admin ones
12:06 < gozer> sheeri: yeah, that's an oversight on our part there
12:06 <@sheeri> so something like 2005 or so
12:07 <@sheeri> let's call it 2100 for an even #?

Comment 2

[Sheeri Cabral [:sheeri]]

Sheeri-Cabral:nodes scabral$ svn commit -m "changing max_connections to 2100 for bouncer clusters"
Sending        databases.pp
Transmitting file data .
Committed revision 87436.

Comment 3

[Brandon Johnson [:cyborgshadow]]

Re-opening this. This did not solve the issue, and it appears there's more at root here which may require some work on the app end.

In addition to already maxing out the 2100 connections on bouncer1 (what should only be the write master), I seem to see no load on bouncer 2-4 (read slaves). After checking, max_used_connections on each of those has only reached 6 and they've done no work.

It appears that bouncer is not utilizing its read slaves and is doing 100% of it's load on the master.

Let's look into why bouncer isn't using the read slaves provided and see if we can correct that before moving forward. I suspect simply using the read slaves will solve this.

Adding gozer for needinfo.

Comment 4

[Philippe M. Chiasson (:gozer)]

Yes, this is a known Bouncer problem, see bug 1014990

Comment 5

[Jake Maul [:jakem]]

FYI this should be a whole lot better with memcache functional again... IIRC that is usable for a whole bunch of connections. IIRC that's one reason we never really bothered with using the slaves... between Zeus caching and memcache, there's not supposed to be very much database for it.

Comment 6

[Sheeri Cabral [:sheeri]]

So then we probably can kill some of the slaves - We only need 1 slave (for failover) if it's not using slaves. Currently we have 3 slaves.

Can we spin down 2 slaves? (they're vm's so we could always re-add them if necessary).

Comment 7

[Philippe M. Chiasson (:gozer)]

(In reply to Jake Maul [:jakem] from comment #5)
> FYI this should be a whole lot better with memcache functional again... IIRC
> that is usable for a whole bunch of connections. IIRC that's one reason we
> never really bothered with using the slaves... between Zeus caching and
> memcache, there's not supposed to be very much database for it.

Current status is that bouncer is now correctly using the slave pool, and there is another bug open to re-enable the memcache support in bouncer.

Comment 8

[Sheeri Cabral [:sheeri]]

Oh! Even better!