Re: flush-host problem
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 [mo...@fastmail.fm] Sent: Tuesday, April 07, 2009 9:18 AM To: mysql@lists.mysql.com 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=arch...@jab.org
Re: flush-host problem
You can set max_connect_errors=9 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.gr...@sun.com 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 [mo...@fastmail.fm] Sent: Tuesday, April 07, 2009 9:18 AM To: mysql@lists.mysql.com 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=eric.ber...@gmail.com -- Eric Bergen eric.ber...@provenscaling.com http://www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
flush-host problem
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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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=arch...@jab.org
RE: flush-host problem
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 [mo...@fastmail.fm] Sent: Tuesday, April 07, 2009 9:18 AM To: mysql@lists.mysql.com 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=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
