Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

up vote 3 down vote accepted

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 !!!

share|improve this answer
    
thanks for this information, really appreciate –  Jeff Feb 8 '11 at 19:20

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).

share|improve this answer

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

share|improve this answer

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.

share|improve this answer
    
thanks for your feedback. im in the process of re-writing the applications. none of them expect connections to open. they open the connection, do what they need to do, and close the connection. i keep track of them by using different names for different apps, to see when and where i might have a leak. the system that the server is running on is a 32bit MAC, purchased awhile back. Until i can get it upgraded, i will set the < 1000. I beleive the guy i work with currently has it set to 8,000. What problems could this cause? –  Jeff Feb 21 '11 at 19:17
    
Each thread needs a stack, which allocates some address space, typically 256k or 1M. Each thread stack needs to be contiguous and not crash into another thread's stack. This consumes and fragments address space, until the server cannot function any more and starts failing. Do not use 32-bit systems for production servers. No hardware which is still in warrantee exists which does NOT run a 64-bit OS. –  MarkR Feb 21 '11 at 22:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.