Amazon Aurora MySQL and wait timeout
This is one of the few things that Aurora MySQL behaves differently than community MySQL, and made me struggling for few hours till I understood how it works. I didn't find this documenting anywhere and I resolved it with try and error.
In MySQL you can set a timeout for idle connections. An Idle connection wait time is the time the server waited for the client to send a new command.
During a new connection initialization, MySQL copies the global value of
interactive_timeout to the session variable
wait_timeout. After connection initialization MySQL will consider only the session value of
wait_timeout and will automatically close the connection whenever the connection is kept idle for more than the
wait_timeout. You can increase the timeout of a single connection by changing the session value of wait_timeout.
Aurora MySQL deviates from MySQL behavior as following:
- Aurora considers the session values of wait_timeout and interactive_timeout (not just wait_timeout). So to have a longer timeout period, increasing wait_timeout is not enough, you need also to increase the value of interactive_timeout. This applies for both interactive and non-interactive connections.
- It seems Aurora has a monitoring thread that checks the timeouts every 1 minute or so. So idle connections are not killed immediately after passing the timeout. They can remain idle for up to another 1 minute.
This can cause surprises if you set low values for wait_timeout and interactive_timeout instance wide, but would like to have a larger timeout for few connections by increasing the session value of wait_timeout after connection. In this case you need also to increase the session value of interactive_timeout.