Amazon Aurora MySQL and wait timeout

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 wait_timeout or 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.