MySQL Connection Usage

By default 151 is the maximum permitted number of simultaneous client connections in MySQL 5.5. If you reach the limit of max_connections you will get the “Too many connections” error when you to try to connect to your MySQL server – which means all available connections are in use by other clients.

MySQL permits one extra connection on top of the max_connections limit which is reserved for the database user having SUPER privilege in order to diagnose connection problems. Normally the administrator user has this SUPER privilege. You should avoid granting SUPER privilege to app users.error-establishing-a-database-connection

Usually a high number of concurrent connections executing queries in parallel can cause significant slowdown and increase chances for deadlocks. Prior to MySQL 5.5, it doesn’t scale well although MySQL is getting better and better since then – but still if you have hundreds of active connections doing real work (this doesn’t count sleeping [idle] connections) then the memory usage will grow. Each connection requires per thread buffers. Also implicit in memory tables require more memory plus memory requirement for global buffers. On top of that, tmp_table_size/max_heap_table_size that each connection may use, although they are not allocated immediately per new connection.

In cases where an application doesn’t close connections properly, wait_timeout is an important parameter to tune and discard unused or idle connections to minimize the number of active connections to your MySQL server – and this will ultimately help to avoid the “Too many connections”error.

On systems with small RAM or with hard number of connections control on the application side we can use small max_connections values like 100-300. Systems with 16G RAM or higher max_connections=1000 is a good idea, of course per-connection buffer should have good/default values while on some systems we can see up to 8k max connections, but such systems usually became down in case of load spikes.

How many connections can be opened concurrently against my MySQL database can be configured and checked with the following command:

SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 505   |
+-----------------+-------+

Check the total number of users connected including sleep connections :

SHOW GLOBAL STATUS LIKE 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 60    |
+-------------------+-------+
1 row in set (0.00 sec)

Check the total number of active connections :

SHOW GLOBAL STATUS LIKE 'Threads_running' ;
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_running | 3 |
+-----------------+-------+
1 row in set (0.00 sec)

If  max_connection limit was ever reached in the past can be checked with:

SHOW GLOBAL STATUS LIKE 'max_use%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 23    |
+----------------------+-------+

To set the value of max_user connections we can use below command :

SET GLOBAL max_connections = 600 ;

Please set the value in my.cnf  and restart the instance if we want to set this value permanently.

But on MySQL instances with many different applications (= databases/schemas) and thus many different users it is a bit more complicated to find out which of these users have connected how many times concurrently. We can configure how many connections one specific user can have at maximum at the same time with:

SHOW GLOBAL VARIABLES LIKE 'max_user_connections';
+----------------------+-------+
| Variable_name        | Value |       
+----------------------+-------+
| max_user_connections | 500   |
+----------------------+-------+

Further we can limit one specific user with:

GRANT USAGE ON *.* TO 'repl'@'%' WITH MAX_CONNECTIONS_PER_HOUR 100 MAX_USER_CONNECTIONS 10;

Check the max_user_connection details of all the users if specified :

SELECT User, Host, max_connections, max_user_connections FROM mysql.user;
+------+---------------+-----------------+----------------------+
| User | Host          | max_connections | max_user_connections |
+------+---------------+-----------------+----------------------+
| root | localhost     |               0 |                    0 |
| repl | %             |             100 |                   10 |
| repl | 192.168.1.139 |               0 |                    0 |
+------+---------------+-----------------+----------------------+

Check if the max_user_connection has reached to its limit after the server restart :

DROP TABLE IF EXISTS mysql.`max_used_connections`;

CREATE TABLE mysql.`max_used_connections` (
  `USER` char(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `MAX_USED_CONNECTIONS` bigint(20) NOT NULL,
  PRIMARY KEY (`USER`, `HOST`) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=utf8
;

DROP EVENT IF EXISTS mysql.gather_max_used_connections;

-- event_scheduler = on
CREATE DEFINER=root@localhost EVENT mysql.gather_max_used_connections
ON SCHEDULE EVERY 10 SECOND
DO
INSERT INTO mysql.max_used_connections
SELECT user, host, current_connections
  FROM performance_schema.accounts
 WHERE user IS NOT NULL
   AND host IS NOT NULL
    ON DUPLICATE KEY
UPDATE max_used_connections = IF(current_connections > max_used_connections, current_connections, max_used_connections)
;

SELECT * FROM mysql.max_used_connections;

+--------+-----------+----------------------+
| USER   | HOST      | MAX_USED_CONNECTIONS |
+--------+-----------+----------------------+
| root   | localhost |                    4 |
| zabbix | localhost |                   21 |
+--------+-----------+----------------------+

Because we used a MEMORY table those values are reset at every MySQL restart (as it happens with the PERFORMANCE_SCHEMA or the INFORMATION_SCHEMA).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: