Home / server / Questions / 231677
Accepted
Jeff
Asked: 2011-02-06 13:52:49 +0800 CST

mysql server, open 'dead' connections

  • 772

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.

mysql

4 Answers

  1. Best 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';<BR>
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;<BR>
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 !!!

    • 3

  2. Unless youre running on a really limited server, this isnt likely to slow anything down. The application doing this will slowly mem leak though until the max connections is reached, but I doubt it'll be that much memory.

    The main problem youre likely to run into is the one you've already noticed, maxing out connections. Your best option is to fix the program to clean up after itself. If for some reason that really isnt an option, you may be able to put in a connection limit from the box running that application so that that box cant open more than X connections to the mysql server so that it cant monopolize it (I dont know what OS youre using to know if this is possible, but if linux, it'd be a simple iptables rule).

    • 1

  3. yes, interactive_timeout and wait_timeout. As long as the clean-up thread in mysql keeps running it will clean them out itself

    • 1

  4. It causes no problem until you run out of connections and legitimate clients can't connect.

    Of course you need to set max_connections low enough that you don't run out of memory or address space (MySQL uses one thread per connection). You should not run mysql on a 32-bit system, but if you do, this means that you need to (practically) have max_connections < 1000.

    If the app server is failing to close connections, it is probably leaking resources on its side too. Having the server time them out will fix the problem on mysql's end, but probably not free up memory (etc) on the other end. The faulty app server will eventually fail if left unchecked. It really needs fixing.

    Also, be very careful when setting timeouts low, some applications expect connections not to time out. The default is 8 hours, so changing it to 1 minute seems very drastic.

    • 1