Mysql server full with idle connections

--001485f91ad23dd5ce0495cac6e7
Content-Type: text/plain; charset=ISO-8859-1

Hey,

Late last night, I got a call that one of our servers was down. Turns out
the machine had all 2500 connection slots in use, but none of them appeared
to be doing anything: hardly any CPU was being used, load 0.05, and tcpdump
confirmed that pretty much no data was being sent, either.

Disks were writeable - so not hanging write flood - and when I eventually
sent a kill, the daemon did a clean shutdown and came back up without a
hitch.

Unfortunately I couldn't log in while this was going on because I never
configured a few extra connections for super - that's been fixed by now.

Munin shows no discernable cause, no ramp-up on the load or whatever on
either the DB or the webservers; just a very sudden increase in open
connections and an equally sudden drop in network traffic.

Neither slowlog nor sys/errorlog shows anything out of the ordinary.

I can think of only two causes: bug in the app code, or bug in MySQL. This
is 5.0.32-Debian_7etch3-log (Debian package) on Debian Etch, running on
x86_64.

Does anyone know of similar things happening ?

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--001485f91ad23dd5ce0495cac6e7--

Re: Mysql server full with idle connections

--0016368322d4d0d2ca0495cc0ab4
Content-Type: text/plain; charset=ISO-8859-1

Seen this a lot with poorly written web apps which open connections but dont
close them when finished. Try setting wait_timeout and/or
interactive_timeout to close unused connections.

John



On 24 November 2010 11:27, Johan De Meersman wrote:

> Hey,
>
> Late last night, I got a call that one of our servers was down. Turns out
> the machine had all 2500 connection slots in use, but none of them appeared
> to be doing anything: hardly any CPU was being used, load 0.05, and tcpdump
> confirmed that pretty much no data was being sent, either.
>
> Disks were writeable - so not hanging write flood - and when I eventually
> sent a kill, the daemon did a clean shutdown and came back up without a
> hitch.
>
> Unfortunately I couldn't log in while this was going on because I never
> configured a few extra connections for super - that's been fixed by now.
>
> Munin shows no discernable cause, no ramp-up on the load or whatever on
> either the DB or the webservers; just a very sudden increase in open
> connections and an equally sudden drop in network traffic.
>
> Neither slowlog nor sys/errorlog shows anything out of the ordinary.
>
> I can think of only two causes: bug in the app code, or bug in MySQL. This
> is 5.0.32-Debian_7etch3-log (Debian package) on Debian Etch, running on
> x86_64.
>
> Does anyone know of similar things happening ?
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>



--
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.daisley@butterflysystems.co.uk

--0016368322d4d0d2ca0495cc0ab4--

Re: Mysql server full with idle connections

--000e0cd56b3e927f7e0495cc3b74
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Nov 24, 2010 at 1:13 PM, Nigel Wood wrote:

> Quick thought: what is your idle timeout set to on the MySQL server?
> Could you have configured it to reap these idle connections?
>

I could, probably, but the applications are generally well-behaved, and it's
not a recurring problem. I hope. I'm more interested in the cause, atm.


On Wed, Nov 24, 2010 at 1:58 PM, John Daisley wrote:

> Seen this a lot with poorly written web apps which open connections but
> dont
> close them when finished. Try setting wait_timeout and/or
> interactive_timeout to close unused connections.
>


Well, yes, but as far as we're aware nothing new has been deployed - this
setup is several years old. I suppose it's possible that one of those kind
of bugs is hiding somewhere in a forgotten corner of code, but given that
we're running Drupal and Wordpress, I'd be surprised at something like that
remaining unnoticed for so long.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--000e0cd56b3e927f7e0495cc3b74--

Re: Mysql server full with idle connections

--0016e6d7e9302c2cc50495fa23d7
Content-Type: text/plain; charset=ISO-8859-1

Hi,

to avoid this change ur "wait_timeout" value to 300 Secs or less, along
with this you can also write a script to kill those process (mysql process)
which are in sleep mode for more than certain time..
hope this will helpful..



On Wed, Nov 24, 2010 at 6:41 PM, Johan De Meersman wrote:

> On Wed, Nov 24, 2010 at 1:13 PM, Nigel Wood wrote:
>
> > Quick thought: what is your idle timeout set to on the MySQL server?
> > Could you have configured it to reap these idle connections?
> >
>
> I could, probably, but the applications are generally well-behaved, and
> it's
> not a recurring problem. I hope. I'm more interested in the cause, atm.
>
>
> On Wed, Nov 24, 2010 at 1:58 PM, John Daisley > >wrote:
>
> > Seen this a lot with poorly written web apps which open connections but
> > dont
> > close them when finished. Try setting wait_timeout and/or
> > interactive_timeout to close unused connections.
> >
>
>
> Well, yes, but as far as we're aware nothing new has been deployed - this
> setup is several years old. I suppose it's possible that one of those kind
> of bugs is hiding somewhere in a forgotten corner of code, but given that
> we're running Drupal and Wordpress, I'd be surprised at something like that
> remaining unnoticed for so long.
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>



--
Best Regards,

Prabhat Kumar
MySQL DBA

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

--0016e6d7e9302c2cc50495fa23d7--

Re: Mysql server full with idle connections

--0003255750fec5a92404962f31f6
Content-Type: text/plain; charset=ISO-8859-1

300 seconds is 5 minutes, while this whole thing happened probably under a
minute. I'm looking for possible causes, not workarounds :-)

On Fri, Nov 26, 2010 at 8:57 PM, Prabhat Kumar wrote:

> Hi,
>
> to avoid this change ur "wait_timeout" value to 300 Secs or less, along
> with this you can also write a script to kill those process (mysql process)
> which are in sleep mode for more than certain time..
> hope this will helpful..
>
>
>
> On Wed, Nov 24, 2010 at 6:41 PM, Johan De Meersman > >wrote:
>
> > On Wed, Nov 24, 2010 at 1:13 PM, Nigel Wood wrote:
> >
> > > Quick thought: what is your idle timeout set to on the MySQL server?
> > > Could you have configured it to reap these idle connections?
> > >
> >
> > I could, probably, but the applications are generally well-behaved, and
> > it's
> > not a recurring problem. I hope. I'm more interested in the cause, atm.
> >
> >
> > On Wed, Nov 24, 2010 at 1:58 PM, John Daisley <
> daisleyjohn@googlemail.com
> > >wrote:
> >
> > > Seen this a lot with poorly written web apps which open connections but
> > > dont
> > > close them when finished. Try setting wait_timeout and/or
> > > interactive_timeout to close unused connections.
> > >
> >
> >
> > Well, yes, but as far as we're aware nothing new has been deployed - this
> > setup is several years old. I suppose it's possible that one of those
> kind
> > of bugs is hiding somewhere in a forgotten corner of code, but given that
> > we're running Drupal and Wordpress, I'd be surprised at something like
> that
> > remaining unnoticed for so long.
> >
> >
> > --
> > Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
> >
>
>
>
> --
> Best Regards,
>
> Prabhat Kumar
> MySQL DBA
>
> My Blog: http://adminlinux.blogspot.com
> My LinkedIn: http://www.linkedin.com/in/profileprabhat
>



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0003255750fec5a92404962f31f6--

Re: Mysql server full with idle connections

Johan De Meersman writes:


Hi,

You may check the network at that time.

We had have a similar problem caused by broken network-card, use
`/sbin/ifconfig | grep errors' to check errors/dropped counter. for
example, broken network-card may cause many connections, since normal
tcp connect open/close can't be completed.

-peter

> Hey,
>
> Late last night, I got a call that one of our servers was down. Turns out
> the machine had all 2500 connection slots in use, but none of them appeared
> to be doing anything: hardly any CPU was being used, load 0.05, and tcpdump
> confirmed that pretty much no data was being sent, either.
>
> Disks were writeable - so not hanging write flood - and when I eventually
> sent a kill, the daemon did a clean shutdown and came back up without a
> hitch.
>
> Unfortunately I couldn't log in while this was going on because I never
> configured a few extra connections for super - that's been fixed by now.
>
> Munin shows no discernable cause, no ramp-up on the load or whatever on
> either the DB or the webservers; just a very sudden increase in open
> connections and an equally sudden drop in network traffic.
>
> Neither slowlog nor sys/errorlog shows anything out of the ordinary.
>
> I can think of only two causes: bug in the app code, or bug in MySQL. This
> is 5.0.32-Debian_7etch3-log (Debian package) on Debian Etch, running on
> x86_64.
>
> Does anyone know of similar things happening ?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Mysql server full with idle connections

--000325558202e805190496317345
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Nov 29, 2010 at 2:21 PM, Peter Wang wrote:

> We had have a similar problem caused by broken network-card, use
> `/sbin/ifconfig | grep errors' to check errors/dropped counter. for
> example, broken network-card may cause many connections, since normal
> tcp connect open/close can't be completed.
>

Ahh, an interesting suggestion. However, 4017 dropped RX packets over an
uptime of 1088 days isn't a lot :-) I guess that kind of behaviour would
also have shown up in Nagios and Munin; nor would killing and restarting
mysqld be likely to have fixed the issue.

Still, I'd never have thought of that - thanks.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--000325558202e805190496317345--