List:General Discussion« Previous MessageNext Message »
From:Gary Smith Date:April 7 2009 4:28pm
Subject:RE: flush-host problem
View as plain text  
Mike, 

It's not a connection pooling issue per say.  We have several boxes running spam assassin,
sqlgrey and postfix (via mysql).  Normally these components work great.  SA and sqlgrey
both have a fixed number of connections, around 16, that they are generally actively
using unless we get a burst of email, at which time they will increase by a few.  The
problem is that Postfix has been receiving a higher level than normal of emails as we
have taken 50% of our servers offline at this location (setting them up at a new
location).  We're also have this bouncing across a couple different firewalls, so for
some reason, the conneciton to mysql is generating a larger number of these:

090407 12:26:42 [Warning] Aborted connection 972479 to db: 'db' user: 'user' host: 'host'
(Got an error reading communication packets)

We do know the network isn't optimal right now and are working to fix the issues but we
are hoping to get by just for the short term.

But that leads back to the original question about increase the connection error cutoff
before banning a host.

We are using 5.1.32 with INNODB tables.

________________________________________
From: mos [mos99@stripped]
Sent: Tuesday, April 07, 2009 9:18 AM
To: mysql@stripped
Subject: Re: flush-host problem

At 10:39 AM 4/7/2009, Gary Smith wrote:
>I have system that is generating a larger than normal number of connection
>errors.  We know why the errors are occuring and are working to resolve
>them (connectivity and load issue on the client).  The question is, how
>can I tweak mysql to tolerate a higher level than normal of bad
>connections before banning the host.
>
>What happens is that when we have 300-500 connections a few random ones
>will get mucked up during a heavier than normal load on the client.  I
>have set the max connections to 3000 (which we never get close to).
>
>So, if there a config/startup setting to tweak to ease the banning of bad
>connetions thus reducing the need for me to continually "mysqladmin
>flush-host" on the server?
>--

What do you mean "mucked up"? This is a technical term I'm not familiar
with. :-)
Do you mean a few of the queries are taking too long to complete? You could
do a "Show ProcessList" every 15 seconds and kill the process for the low
level user that is taking too long, say over 30 seconds. I'm sure there are
also monitoring tools that could do this for you.

If you are using transaction-less MyISAM tables, can you not use connection
pooling? We've done that in Delphi and MySQL and the connections have
dropped considerably. I'm not sure what language you're using or if this is
a webserver, but a Google search on your development language and "MySQL
connection pooling" should get you pointed in the right direction.

Also you did not say what version of MySQL you're using.

Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
Thread
flush-host problemGary Smith7 Apr
  • Re: flush-host problemmos7 Apr
    • RE: flush-host problemGary Smith7 Apr
Re: flush-host problemShawn Green11 Apr
  • Re: flush-host problemEric Bergen12 Apr