{"id":6324,"date":"2016-10-12T11:27:36","date_gmt":"2016-10-12T03:27:36","guid":{"rendered":"http:\/\/rmohan.com\/?p=6324"},"modified":"2016-10-12T11:28:02","modified_gmt":"2016-10-12T03:28:02","slug":"how-and-why-you-should-change-mysqls-wait_timeout-and-interactive_timeout-variables","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=6324","title":{"rendered":"How and Why You Should Change MySQL’s wait_timeout and interactive_timeout Variables"},"content":{"rendered":"

How and Why You Should Change MySQL’s wait_timeout and interactive_timeout Variables<\/strong><\/h1>\n

wait_timeout variable represents the amount of time that MySQL will wait before killing an idle connection.<\/em>
\nThe default wait_timeout variable is 28800 seconds, which is 8 hours. That’s a lot.<\/em><\/p>\n

I’ve read in different forums\/blogs that putting wait_timeout too low (e.g. 30, 60, 90) can result in MySQL has gone away error messages.
\nSo you’ll have to decide for your configuration.<\/p>\n

Requirement: You will need admin\/root access to the server.<\/p>\n

Step 1) Edit your \/etc\/my.cnf file and enter the following 2 values.<\/p>\n

[mysqld]
\ninteractive_timeout=300
\nwait_timeout=300<\/p>\n

Step 2) run the command and enter your root password
\nmysql -uroot -p -e”SET GLOBAL wait_timeout=300; SET GLOBAL interactive_timeout=300;”<\/p>\n

If you are connected from the mysql console
\ne.g. mysql> you can run this command which will show you global and session variables.<\/p>\n

SELECT @@global.wait_timeout, @@global.interactive_timeout, @@session.wait_timeout, @@session.interactive_timeout;<\/p>\n

+———————–+——————————+————————+——————————-+
\n| @@global.wait_timeout | @@global.interactive_timeout | @@session.wait_timeout | @@session.interactive_timeout |
\n+———————–+——————————+————————+——————————-+
\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 300 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 300 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28800 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28800 |
\n+———————–+——————————+————————+——————————-+
\n1 row in set (0.00 sec)<\/p>\n

To see the the current values you can run this command
\nmysql> show global variables like ‘%timeout%’;<\/p>\n

+—————————-+———-+
\n| Variable_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Value\u00a0\u00a0\u00a0 |
\n+—————————-+———-+
\n| connect_timeout\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 10\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |
\n| delayed_insert_timeout\u00a0\u00a0\u00a0\u00a0 | 300\u00a0\u00a0\u00a0\u00a0\u00a0 |
\n| innodb_lock_wait_timeout\u00a0\u00a0 | 50\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |
\n| innodb_rollback_on_timeout | OFF\u00a0\u00a0\u00a0\u00a0\u00a0 |
\n| interactive_timeout\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 300\u00a0\u00a0\u00a0\u00a0\u00a0 |
\n| lock_wait_timeout\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 31536000 |
\n| net_read_timeout\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 30\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |
\n| net_write_timeout\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 60\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |
\n| slave_net_timeout\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 3600\u00a0\u00a0\u00a0\u00a0 |
\n| wait_timeout\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 300\u00a0\u00a0\u00a0\u00a0\u00a0 |
\n+—————————-+———-+
\n10 rows in set (0.00 sec)<\/p>\n

Credits<\/strong>:
\nStep1 instructions from RolandoMySQLDBA
\nhttp:\/\/serverfault.com\/questions\/355750\/mysql-lowering-wait-timeout-value-to-lower-number-of-open-connections<\/p>\n

Step 2) Eliot Kristan’s blog http:\/\/www.eliotk.net\/12\/21\/mysql-wait_timeout-default-is-set-too-high\/<\/p>\n","protected":false},"excerpt":{"rendered":"

How and Why You Should Change MySQL’s wait_timeout and interactive_timeout Variables <\/p>\n

wait_timeout variable represents the amount of time that MySQL will wait before killing an idle connection. The default wait_timeout variable is 28800 seconds, which is 8 hours. That’s a lot.<\/p>\n

I’ve read in different forums\/blogs that putting wait_timeout too low (e.g. 30, 60, 90) […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6324"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6324"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6324\/revisions"}],"predecessor-version":[{"id":6325,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6324\/revisions\/6325"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6324"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6324"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6324"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}