How to limit active connections in MySQL or MariaDB

Limiting concurrent client sessions protects MySQL and MariaDB from connection storms, runaway application pools, and sudden resource exhaustion. A sensible cap keeps memory use and scheduler pressure predictable, and turns “too many clients” into a clear failure mode instead of a slow performance collapse.

The server enforces the cap with the global max_connections system variable. In the default thread-per-connection model, each authenticated client consumes a connection slot (plus per-session buffers) until the session ends, and once the limit is reached the server rejects new sessions until a slot becomes available.

Raising the cap increases concurrency but also increases RAM pressure and can amplify the impact of expensive queries. Changes made with SET GLOBAL apply immediately but do not survive a restart unless the startup configuration is updated as well.

1. Open the mysql client as an administrative user.

   $ mysql --user=root --password
   Enter password: ********
   mysql>

   On socket-auth installs, sudo mysql or sudo mariadb can open an administrative session without a password prompt.

2. Display the current max_connections value.

   mysql> SHOW VARIABLES LIKE 'max_connections';
   +-----------------+-------+
   | Variable_name   | Value |
   +-----------------+-------+
   | max_connections | 151   |
   +-----------------+-------+
   1 row in set (0.00 sec)

   When the limit is reached, new sessions fail with 1040 Too many connections until a slot frees up.

3. Display the current number of active client sessions.

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

   SHOW FULL PROCESSLIST; helps identify idle or long-running sessions that keep slots occupied.

4. Display the peak number of simultaneous sessions since the last server restart.

   mysql> SHOW GLOBAL STATUS LIKE 'Max_used_connections';
   +----------------------+-------+
   | Variable_name        | Value |
   +----------------------+-------+
   | Max_used_connections | 48    |
   +----------------------+-------+
   1 row in set (0.00 sec)

   Set the new cap with modest headroom above Max_used_connections, and treat unexpected growth as a sign of connection leaks or missing pooling.

5. Apply a new in-memory connection limit with SET GLOBAL.

   mysql> SET GLOBAL max_connections = 200;
   Query OK, 0 rows affected (0.00 sec)

   Administrative privileges are required, and setting max_connections far above available capacity increases RAM usage and can trigger OOM conditions during spikes.

6. Confirm the running server reports the updated max_connections value.

   mysql> SHOW VARIABLES LIKE 'max_connections';
   +-----------------+-------+
   | Variable_name   | Value |
   +-----------------+-------+
   | max_connections | 200   |
   +-----------------+-------+
   1 row in set (0.00 sec)

   Lowering max_connections does not disconnect existing sessions, but new sessions are refused once the limit is reached.

7. Exit the mysql shell.

   mysql> exit
   Bye

8. Persist the setting under the [mysqld] section in /etc/mysql/mysql.conf.d/mysqld.cnf.

   $ sudoedit /etc/mysql/mysql.conf.d/mysqld.cnf

   [mysqld]
   max_connections = 200

   MariaDB packages on Debian or Ubuntu commonly use /etc/mysql/mariadb.conf.d/50-server.cnf for server settings, and very high limits may also require raising OS file limits and the server open_files_limit.

9. Restart the database service to load the persistent configuration.

   $ sudo systemctl restart mysql

   A restart disconnects active clients and can interrupt applications that do not retry connections.

10. Confirm the systemd service reports as active.

    $ systemctl is-active mysql
    active

    Some distributions name the unit mariadb or mysqld instead of mysql.

11. Reconnect to the server after the restart.

    $ mysql --user=root --password
    Enter password: ********
    mysql>

12. Verify the persistent max_connections value is still active after restart.

    mysql> SHOW VARIABLES LIKE 'max_connections';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | max_connections | 200   |
    +-----------------+-------+
    1 row in set (0.00 sec)