Mysql – How long is “too long” for MySQL Connections to sleep

MySQLmysql-5

I logged onto my server to check the status of the database and noticed on the SHOW PROCESSLIST; that there are a large number of connections sleeping that are very old.

enter image description here
What duration should the connection time limit be before the connection should be killed?

How do I set the time limit in MySQL 5.0.51a-3ubuntu5.4?

Note:

I am using PHP 5.2.x and symfony framework 1.2.

Best Answer

mysqld will timeout database connections based on two server options:

Both are 28,800 seconds (8 hours) by default.

You can set these options in /etc/my.cnf

If your connections are persistent (opened via mysql_pconnect) you could lower these numbers to something reasonable like 600 (10 minutes) or even 60 (1 minute). Or, if your app works just fine, you can leave the default. This is up to you.

You must set these as follows in my.cnf (takes effect after mysqld is restarted):

[mysqld]
interactive_timeout=180
wait_timeout=180

If you do not want to restart mysql, then run these two commands:

SET GLOBAL interactive_timeout = 180;
SET GLOBAL wait_timeout = 180;

This will not close the connections already open. This will cause new connections to close in 180 seconds.

Related Solutions

Mysql – different login credentials based on user

You typically have to weight the cost of doing so vs. the benefits ... but benefit in risk management is difficult to quantify.

Basically, it comes down to what the cost of an exploit would be, and what the likihood of it happening are.

So, having to restore from backup because someone managed to drop a table which creates a denial of service, and being down for a day has a cost to the company in terms of what profit they'd have made in that given time, but there's also an issue of reputation loss (ie, customers/users who stop doing business with you, or potential users who are less likely to do busines in the future) ... but we have to balance this by the likehood of someone successfully attacking the site and causing this.

If you're not storing credit cards, and you're not a big target (the type of site people would brag about taking out), you're less likely to be hacked ... although, if you're running commonly distributed software, you still risk attacks by script kiddies who are just looking for people running software with a known exploit.

...

What our security folks don't seem to understand is that it's a balancing act -- some changes for security will create a burden on your users. And sometimes, the security itself will cause outages (eg, one of our external partners moves IP ranges ... but the new holes in the firewall weren't made, and due to a "network hold" we can't get any changes made for over a week) or just performance degredation.

Sometimes it's just that it takes longer to code, or more headaches to maintain, etc.

But it's something you have to answer for yourself -- is the cost worth the benefit of having made the change? (and sometimes, if the cost is just in man-power, was there an opportunity cost; ie, could you have been doing something else that would derive even more benefit with your time?)

MySQL – Does Column Order in WHERE Clause Affect Query Performance?

Traditionally ubuntu supported a fairly blunt method of suspend and hibernate. Neither would integrate well with other apps and sometimes not even work on some machines. This new method doesn't require root and notifies all applications listening for power events.

Systemd Method

Starting with Ubuntu 16.04, systemctl call must be used (See Suspend command in Ubuntu 16.04)

systemctl suspend

and

systemctl hibernate

New Method (obsolete)

Obsolete circa Ubuntu 16.04; use systemctl instead, as above.

See the answer here on this page from Adam Paetznick regarding the use of dbus. Ideally you would create a ~/bin/suspend shortcut/script that makes the use of this action easy.

For use over ssh, you should modify policykit rules as outlined by Peter V. Mørch

Old Method

According to the Ubuntu Forum you can use the following commands:

pmi action suspend

and

pmi action hibernate

This requires that you install the powermanagement-interface package (not tested).

sudo apt-get install powermanagement-interface

I have also found the commands sudo pm-suspend and sudo pm-hibernate to work on my netbook.