From: Eric Bergen Date: April 12 2009 3:18am Subject: Re: flush-host problem List-Archive: http://lists.mysql.com/mysql/217073 Message-Id: <11b1bd990904112018g73c09865re51be0813f77348@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable You can set max_connect_errors=3D999999999 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 wrote: > > Hello Gary, > > Gary Smith wrote: >> >> Mike, >> It's not a connection pooling issue per say. =A0We have several boxes ru= nning spam assassin, sqlgrey and postfix (via mysql). =A0Normally these com= ponents work great. =A0SA and sqlgrey both have a fixed number of connectio= ns, around 16, that they are generally actively using unless we get a burst= of email, at which time they will increase by a few. =A0The problem is tha= t Postfix has been receiving a higher level than normal of emails as we hav= e taken 50% of our servers offline at this location (setting them up at a n= ew location). =A0We're also have this bouncing across a couple different fi= rewalls, 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: 'u= ser' host: 'host' (Got an error reading communication packets) >> >> We do know the network isn't optimal right now and are working to fix th= e issues but we are hoping to get by just for the short term. >> >> But that leads back to the original question about increase the connecti= on 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 connect= ion >>> errors. =A0We know why the errors are occuring and are working to resol= ve >>> them (connectivity and load issue on the client). =A0The question is, h= ow >>> 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. =A0= 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 b= ad >>> connetions thus reducing the need for me to continually "mysqladmin >>> flush-host" on the server? >>> -- > > ... > > The server generates those "Aborted connection" messages under the follow= ing 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) =A0or bad connections. > > Look at your SHOW PROCESSLIST report. If it shows many connections that h= ave 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 i= dle connection timeout for the pool (part of the pool configuration) is set= to a value smaller than wait_timeout. =A0Otherwize you need to audit your = client code to ensure that it is calling the appropriate close() function f= or the library it is using to connect to the MySQL server. That will releas= e the connection and allow the server to reallocate those resources to hand= ling 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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Deric.bergen@g= mail.com > -- Eric Bergen eric.bergen@stripped http://www.provenscaling.com