mysql server, open 'dead' connections

My basic question is what kind of impact does this have on the server?

Let's say, for example, there is an older program in my company that opens connections to a mysql database server at a high rate (everything they do with the application basically opens a server connections). However, this application was not designed in the way to dispose of the connections after they where created. A lot of the time the connections remain open but are never used again, open 'dead' connections I guess you could say.

They just remain connected until the server times them out, or until an admin goes in and removes the sleeping connections manually. I'm guessing this could be responsible for sometimes not able to connect errors, etc. that we receive from other systems that try to access the mysql database? (connections limit reached)

Could this slow down the server as well? Curious what all this could exactly cause.

Answer

You could play some games with the timeout values in MySQL.

For example, the default value for 'wait_timeout' and 'interactive_timeout' is 28800 (that's 8 hours)

You can see what they are set to by running this:

SHOW VARIABLES LIKE 'interactive_timeout';
SHOW VARIABLES LIKE 'wait_timeout';

If you want to lower these to, say, 1 minute, a MySQL restart is not required.

Run these as the root user:

SET GLOBAL interactive_timeout=60;
SET GLOBAL wait_timeout=60;

This will assure that any new MySQL connections will timeout in 60 seconds.

then add these lines to /etc/my.cnf under the [mysqld] section

interactive_timeout=60
wait_timeout=60

Of course, it is easier to restart mysql to remove the remaining sleeping connections. All connections, going forward from there, will timeout in 60 seconds.

Give it a try and let us know !!!