List:General Discussion« Previous MessageNext Message »
From:Eric Bergen Date:April 12 2009 3:18am
Subject:Re: flush-host problem
View as plain text  
You can set max_connect_errors=999999999 to "disable" the host
blocking. It's common to do this with load balancers because tcp/ip
health checks count as connection errors.

-Eric

On Sat, Apr 11, 2009 at 1:01 PM, Shawn Green <Shawn.Green@stripped> wrote:
>
> Hello Gary,
>
> Gary Smith wrote:
>>
>> 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?
>>> --
>
> ...
>
> The server generates those "Aborted connection" messages under the following
> circumstances:
>
> 1) The server was trying to pass information to the client and the client stopped
> being there.
>
> 2) The client sat idle beyond the wait_timeout setting.
>
> 3) There was some kind of networking interference (such as a VPN or proxy server
> closing an idle connection)  or bad connections.
>
> Look at your SHOW PROCESSLIST report. If it shows many connections that have been
> idle for more than 30 seconds or so, then you need to change your client software
> behavior. If these are pooled connections, make sure your idle connection timeout for the
> pool (part of the pool configuration) is set to a value smaller than wait_timeout.
>  Otherwize you need to audit your client code to ensure that it is calling the
> appropriate close() function for the library it is using to connect to the MySQL server.
> That will release the connection and allow the server to reallocate those resources to
> handling the commands for the live (and not idle) connections.
>
> For additional reasons for these errors, please peruse:
> http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html
>
> Warmest regards,
>
> --
> Shawn Green, MySQL Senior Support Engineer
> Sun Microsystems, Inc.
> Office: Blountville, TN
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>



--
Eric Bergen
eric.bergen@stripped
http://www.provenscaling.com
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