MySQL Connections

Configuration

host_cache_size

Check

SELECT @@host_cache_size;


SHOW GLOBAL VARIABLES LIKE "host_cache_size";

To view the contents of the host cache query the host_cache Performance Schema table.

Set

SET GLOBAL host_cache_size=200;

Requires the SYSTEM_VARIABLES_ADMIN privilege
Changing the cache size at runtime causes an implicit host cache flushing operation that clears the host cache, truncates the host_cache table, and unblocks any blocked hosts (7)
Setting host_cache_size to 0, either at server startup or at runtime, disables the host cache. With the cache disabled, the server performs a DNS lookup every time a client connects. (7)

Flushing the Host Cache

The cache can be flushed by changing the host_cache_size (see above), truncating the host_cache table, or executing the FLUSH HOSTS command.

TRUNCATE TABLE performance_schema.host_cache;

Requires the DROP privilige for the table.

FLUSH HOSTS;

Requires the RELOAD privilegeDeprecated as of MySQL 8.0.23 (8)
You may need to flush the cache if you get unexpected 'blocked' connections or if you change the IP addresses of any client hosts.

interactive_timeout

The number of seconds the server waits for activity on an interactive connection before closing it. (2)

Check

SELECT @@interactive_timeout;


SHOW GLOBAL VARIABLES LIKE "interactive_timeout";

Set

SET GLOBAL interactive_timeout=28800;

SET GLOBAL interactive_timeout=180;

SET GLOBAL interactive_timeout=3600;

# 8 Hours (Default)

# 3 Minutes

# 1 hour

To get a number of minutes expressed as seconds, multiply the minute value by 60.

max_connections

Defines the upper limit for the number of concurrent connections.

Check

SELECT @@max_connections;

+-------------------+| @@max_connections |+-------------------+|               300 |+-------------------+

SHOW GLOBAL VARIABLES LIKE "max_connections";

+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 300   |+-----------------+-------+

SELECT *

  FROM performance_schema.variables_info

 WHERE variable_name = 'max_connections'; 

+-----------------+-----------------+---------------+-----------+-----------+----------+----------+----------+| VARIABLE_NAME   | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |+-----------------+-----------------+---------------+-----------+-----------+----------+----------+----------+| max_connections | EXPLICIT        | /mysql/my.cnf | 1         | 100000    | NULL     | NULL     | NULL     |+-----------------+-----------------+---------------+-----------+-----------+----------+----------+----------+

SHOW STATUS LIKE 'MAX_USED_CONNECTIONS%';

+---------------------------+---------------------+| Variable_name             | Value               |+---------------------------+---------------------+| Max_used_connections      | 38                  || Max_used_connections_time | 2024-08-15 11:41:25 |+---------------------------+---------------------+
Max_used_connections shows the maximum number of concurrent connections since instance start (or status flush).
Max_used_connections_time shows the time when the maximum number of concurrent connections since instance start (or status flush) was reached.

Set

SET GLOBAL max_connections = 150;

Changes setting only until instance restart

SET PERSIST max_connections = 150;

Persists setting to mysqld-auto.cnf (which overrides my.cnf at restart)

The maximum value is: 4294967295

max_connect_errors

SELECT @@max_connect_errors;


SHOW GLOBAL VARIABLES LIKE "max_connect_errors";

max_execution_time

Specifies how long (in milliseconds) a query can execute for before it is automatically killed.

Check

SELECT @@max_execution_time;


SHOW GLOBAL VARIABLES LIKE "max_execution_time";

Set

SET GLOBAL max_execution_time=0;

SET GLOBAL max_execution_time=1800000;

SET GLOBAL max_execution_time=2700000;

SET GLOBAL max_execution_time=3600000;

# Disable

# 30 minutes

# 45 minutes

# 1 hour

To get a number of minutes expressed as milliseconds, multiply the minute value by 60000.

wait_timeout

The number of seconds the server waits for activity on a noninteractive connection before closing it. (1)

Check

SELECT @@wait_timeout;


SHOW GLOBAL VARIABLES LIKE "wait_timeout";

Note that using:SHOW VARIABLES LIKE "wait_timeout"might give a seemingly confusing result since the "session wait_timeout value is initialized from the global wait_timeout or from the global interactive_timeout, depending on the type of client"

Set

SET GLOBAL wait_timeout=28800;

SET GLOBAL wait_timeout=180;

SET GLOBAL wait_timeout=3600;

# 8 Hours (Default)

# 3 Minutes

# 1 hour

To get a number of minutes expressed as seconds, multiply the minute value by 60.

Status Counters

SHOW GLOBAL STATUS;

Aborted_clients

"The number of connections that were aborted because the client died without closing the connection properly." (3)

Common Causes

Aborted_connects

"The number of failed attempts to connect to the MySQL server." (4)

These should correlate to entires like this in the mysql.log (if log_warnings parameter is set to 2)...

2023-01-11T03:24:06.161568Z 3067 [Note] Aborted connection 3067 to db: 'mydb' user: 'mysuer' host: 'myhost' (Got timeout reading communication packets)

Common Causes

Connections

"The number of connection attempts (successful or not) to the MySQL server." (6)

Connection_errors_accept

"The number of errors that occurred during calls to accept() on the listening port." (5)

Connection_errors_internal

"The number of connections refused due to internal errors in the server, such as failure to start a new thread or an out-of-memory condition." (5)

Connection_errors_max_connections

"The number of connections refused because the server max_connections limit was reached." (5)

Connection_errors_peer_address

"The number of errors that occurred while searching for connecting client IP addresses." (5)

Connection_errors_select

"The number of errors that occurred during calls to select() or poll() on the listening port. (Failure of this operation does not necessarily means a client connection was rejected.)" (5)

Connection_errors_tcpwrap

"The number of connections refused by the libwrap library." (5)

Bibliography & References

https://dev.mysql.com/doc/refman/5.6/en/communication-errors.htmlhttps://www.percona.com/blog/2016/05/16/mysql-got-an-error-reading-communication-packet-errors/
COUNTERSAborted_clients(3) https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Aborted_clients
Aborted_connects(4) https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Aborted_connectshttps://dba.stackexchange.com/questions/154531/how-do-i-determine-the-cause-of-high-mysql-aborted-connects-counthttps://stackoverflow.com/questions/23689803/mysql-aborted-connections-how-to-track-down-cause
Connection_errors_xxx(5) https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Connection_errors_xxx
Connections(6) https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Connections
CONFIGURATIONhttps://dev.mysql.com/blog-archive/mysql-8-0-persisting-configuration-variables/
host_cache_size(7) https://dev.mysql.com/doc/refman/8.0/en/host-cache.html(8) https://dev.mysql.com/doc/refman/8.0/en/flush.html#flush-hosts
interactive_timeout(2) https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_interactive_timeouthttps://orbisius.com/blog/change-mysqls-wait_timeout-interactive_timeout-variables-p1699
max_connectionshttps://stackoverflow.com/questions/22297773/how-to-increase-mysql-connectionsmax-connectionshttps://stackoverflow.com/questions/16584061/mysql-max-user-connections-recommended-settingshttps://dba.stackexchange.com/questions/233886/configuring-the-max-connections-setting-in-mysqlhttps://stackoverflow.com/questions/730953/how-to-fix-mysql-connect-too-many-connections
max_used_connectionshttps://dev.mysql.com/doc/refman/8.4/en/server-status-variables.html#statvar_Max_used_connections
max_execution_timehttps://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time
wait_timeout(1) https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_wait_timeouthttps://orbisius.com/blog/change-mysqls-wait_timeout-interactive_timeout-variables-p1699https://serverfault.com/questions/355750/mysql-lowering-wait-timeout-value-to-lower-number-of-open-connectionshttps://dba.stackexchange.com/questions/189129/mysql-wait-timeout-variable