Kill inactive MySQL connections automaically

Question

I am trying to learn more on mysql connections.

Here are few steps I performed.

1) Hit the server API few times and checked.

mysql> SHOW STATUS WHERE variable_name = 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 15     |
+----------------------+-------+
1 row in set (0.00 sec)

2) Then I stopped calling server. And set following 2 rules for mysql.

SET GLOBAL interactive_timeout = 120;
SET GLOBAL wait_timeout = 120;

3) I waited for 2 mins and things do not change.

mysql> SHOW STATUS WHERE variable_name = 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 15     |
+----------------------+-------+
1 row in set (0.00 sec)

I don't understand how can I drop all the inactive connections after a certain period of time?

Answer 1

Your assumption of what "Max_used_connections" is, might be wrong.

According to this: https://dba.stackexchange.com/questions/28644/why-does-max-used-connections-status-not-get-refreshed-automatically-once-it-rea

max used connections is a count of the maximum number of connections that have been in use simultaneously since server start. This means that the value for max connections used will not go down just because you are manually forcing connections to be closed.

The next thing you need to address, is that you do not need to close all connections. As long as your application code is written correctly it will "close" a connection when it is finished. MySQL will keep that "closed" connection open in the pool so that it is reused the next time an application "opens" a connection to MySQL. Without this pooling and reuse, the act of actually opening connections quickly becomes the most expensive part of your application's operation.

Answer 2

See for details, https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout indicates thread_timeout is used to 'terminate connections'. To be effective, your my.cnf-ini must have the global values set for your requirements, then complete shutdown/restart will manage eliminating the connection after expiration of wait_timeout or interactive_timeout. A clue will be in SHOW GLOBAL STATUS LIKE 'aborted_%' results when aborted_connections has been incremented by at least 1. When you SET GLOBAL wait_timeout=120 and interactive_timeout=120, that is ONLY for future connections and the connections you are looking at were created with the timeout from my.cnf-ini;