CODEWITHSUNDEEP
mysqlruby-on-railsamazon-ec2mysql2
concept47
concept47

Reputation: 31776

mysql not respecting wait_timeout setting in my.cnf

I've set a wait_timeout in my.cnf and restarted the server, but the time on idle connections continues to grow larger than the default 100s I set. Any ideas why this is happening?

PS: I'm running ubuntu 12.04 and Mysql Server 5.5. Using Rails 3 with the mysql2 gem.

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY TIME DESC LIMIT 20;
+------+------------------+-------------------------------------+--------------------------+---------+------+-------+------+
| ID   | USER             | HOST                                | DB                       | COMMAND | TIME | STATE | INFO |
+------+------------------+-------------------------------------+--------------------------+---------+------+-------+------+

|  760 | user | ip-xxx-xxx-xxx-xxx.ec2.internal:45852 | x | Sleep   | 4202 |       | NULL |
|  912 | user | ip-xxx-xxx-xxx-xxx.ec2.internal:40929 | y  | Sleep   | 4194 |       | NULL |
|  976 | user | ip-xxx-xxx-xxx-xxx.ec2.internal:40970 | y  | Sleep   | 4193 |       | NULL |
|  836 | user | ip-xxx-xxx-xxx-xxx.ec2.internal:49903   | z | Sleep   | 4189 |       | NULL |
| 1743 | user | ip-xxx-xxx-xxx-xxx.ec2.internal:44585  | z | Sleep   | 4157 |       | NULL |
| 1778 | user | ip-xxx-xxx-xxx-xxx.ec2.internal:48714    | a  | Sleep   | 4154 |       | NULL |

Upvotes: 3

Views: 4249

Answers (2)

concept47
concept47

Reputation: 31776

Turns out the problem is in the mysql2 gem. It does this really awesome thing ...

Essentially it substitutes a default wait_timeout value of about 25 days (2147483s) of its own as the wait_timeout value (probably the session version of wait_timeout) that the connection uses, basically overriding whatever setting we specify on the server.

By setting a wait_timeout: value in config/database.yml the timeout works as it should. When the connection gets killed, however, you get "mysql server has gone away" errors.

By specifying an accompanying reconnect: true option in database.yml, everytime the connection is reused the connection's timer is reset, BUT after that it uses the GLOBAL wait_timeout setting from mysql directly

Hope this helps someone

Upvotes: 8

spencer7593
spencer7593

Reputation: 108460

One possibility is that the wait_timeout for a session is being set as the value of the interactive_timeout system variable. 

SHOW VARIABLES LIKE 'interactive_timeout'

And the default value for that is 8 hours.

Upvotes: 2

Related Questions