How Long Will MariaDB Wait Before Closing an Inactive Connection?

• When your application connects to MariaDB but isn't actively sending any queries, the connection goes idle.
• MariaDB manages these idle connections with two timeout settings:
  • wait_timeout: This is the maximum time (in seconds) MariaDB will wait for an idle connection to become active before closing it. By default, this is set to 28,800 seconds (8 hours).
  • interactive_timeout: This is similar to wait_timeout but applies specifically to interactive connections, like those used by command-line tools. The default is also 28,800 seconds.

So, How Long Does it Take to Close?

• An idle connection won't be closed automatically until it reaches the configured wait_timeout or interactive_timeout (depending on the connection type).

Customizing Timeout Values:

• You can adjust these timeouts in the MariaDB configuration file (my.cnf) or directly on the server using SET GLOBAL commands.
• Be cautious when lowering these values too much, as it could lead to frequent connection closures and disrupt your application.

Key Points:

• MariaDB manages idle connections, not "sleep connections."
• Idle connections are closed after a specific timeout (wait_timeout or interactive_timeout).
• You can customize these timeouts in the configuration.

SHOW GLOBAL VARIABLES LIKE '%timeout%';

This query will display all global variables containing "timeout" in their name, including wait_timeout and interactive_timeout.

Modifying Timeouts (using my.cnf):

The recommended way to change timeouts is by editing the my.cnf configuration file (location may vary depending on your system). Add or modify the following lines:

wait_timeout = [desired_timeout_in_seconds]
interactive_timeout = [desired_timeout_in_seconds]

If you can't access my.cnf, you can temporarily change timeouts on the server itself using commands:

SET GLOBAL wait_timeout = [desired_timeout_in_seconds];
SET GLOBAL interactive_timeout = [desired_timeout_in_seconds];

Remember: These changes are temporary and will reset upon server restart.

1. Connection Pooling:

• Connection pooling helps reuse existing connections instead of creating new ones every time your application needs to interact with MariaDB. This reduces the number of idle connections overall.
• Popular connection pooling libraries for various programming languages can handle this task.

1. Client-side Logic:

• You can implement logic within your application to handle idle connections. For instance, your application can close connections after a period of inactivity or before going idle itself.
• This approach requires modifying your application code and might add complexity.

1. Database Monitoring and Alerting:

• Monitor the number of idle connections in your MariaDB server. If you see a consistently high number of idle connections, it might indicate that your application logic or connection management needs improvement.
• Many database management tools offer monitoring and alerting functionalities.