How to setup a connection timeout depending of the

问题:

I have currently more than 100 connections in Sleep state.

Some connection must stay in Sleep state (and don't close) because it's permanent connection but some others (with a different user name) are from some php script and I want them to timeout very fast.

Is it possible to setup a wait_timeout per user? and if yes, How?

回答1:

There's no per-user timeout configuration, but you can set the wait_timeout value dynamically. That is, after you make a connection as a given user, you can issue a statement to change the timeout value to what you want it to be for that user's session.

Try the following experiment in the mysql command-line client:

mysql> SHOW VARIABLES LIKE 'wait_timeout';

...shows 28800 (i.e. 8 hours), which is the default wait_timout.

mysql> SET SESSION wait_timeout = 60;
mysql> SHOW VARIABLES LIKE 'wait_timeout';

...shows 60.

Then you can quit the session, reconnect, and again the default wait_timeout is 28800. So it's limited to the scope of the current session.

You can also open a second window and start a separate mysql client session, to prove that changing the wait_timeout in one session does not affect other concurrent sessions.

回答2:

You should set the following variables in your my.conf:

[mysqld]
interactive_timeout=180
wait_timeout=180

wait_timeout is a timeout for automated connections (in my opinion more than 30 on a web server is too much).
interactive_timeout is a console interaction timeout for idle session.

回答3:

Another possibility: MySQL supports two different timeout variables, wait_timeout for non-interactive clients, and interactive_timeout for interactive clients.

The difference between interactive and non-interactive clients seems to be simply whether you specified the CLIENT_INTERACTIVE option when connecting.

I don't know if this helps you, because you need to somehow make mysql_real_connect() pass that option in its client_flag parameter. I'm not sure what language or interface you're using, so I don't know if it permits you to specify this connection flag.

Anyway if you can pass that client flag, and you only need two different types of users, then you could configure wait_timeout and interactive_timeout differently in the MySQL server config, and then use the one with the shorter value when you want a given session to time out promptly.

回答4:

If you use Connector/J, you can use sessionVariables in the client's JDBC URL like so: jdbc:mysql://hostname:3306/schema?sessionVariables=wait_timeout=600

Other connectors for other languages will probably allow the same.

回答5:

I checked the mysql.user table and it doesn't look like there is a setting there for it:

+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     |         |       |
| x509_issuer           | blob                              | NO   |     |         |       |
| x509_subject          | blob                              | NO   |     |         |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.00 sec)

Depending on whether you're using MySQLi or PDO, your PHP MySQL connections should either hang up when the request does, or be shared in a pool for the Apache process.

For example, with PDO, to turn off persistent connections (I think this is the default), connect to your DB with:

$pdo = new PDO($dsn, $user, $pass, Array(PDO::ATTR_PERSISTENT => false));

If you want your scripts to use persistent connections, but you have too many connections open to your database in sleep mode, you should think about configuring your Apache's MaxServers, MaxSpareServers, MinSpareServers and StartServers so that not so many hang around when they aren't needed.

回答6:

http://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html

It is possible to kill connections per user with pt-kill. You can schedule this or set up a background job to handle this.

回答7:

init_connect will be executed whenever a user logs in, so we can write small case statement and set the value based on user. Please note that the init_connect won't be executed for super user.

mysql> SET GLOBAL init_connect="SET @@wait_timeout = CASE WHEN CURRENT_USER() LIKE 'app1@%' THEN '30' ELSE @@wait_timeout END";