Friday, January 4, 2013

MySQL : interactive_timeout v/s wait_timeout

Database administrator usually face problem while deciding what would be perfect value for wait_timeout and interactive_timeout. Lower value of wait_timeout boost the database performance and drop long waiting Sleep connections which can be reused when application required. When application sends a query to database it  create a connection on mysql server however mysql doesn't close/kill the connection even after query results transferred back to the application until the connection time reaches wait_timeout.

We can set wait_timeout after analysing slow query log and application requirements. However setting it to 300 seconds is reasonable.

wait_timeout is the amount of seconds during inactivity that MySQL will wait before it will close a connection on a non-interactive connection.
interactive_timeout is the same, but for interactive sessions (mysql shell)

How to set wait_timeout:
1. Editing /etc/mysql.cnf

2. Changing value at runtime

How to Kill all MySQL Sleep queries taking more than 600 seconds:

for i in $(mysql -e "show processlist"| grep Sleep | awk '$6 > 600' | awk '{print $1}'); do mysql -e "kill $i"; done

No comments:

Post a Comment