How to limit active connections in MySQL or MariaDB

Limiting concurrent client sessions keeps MySQL and MariaDB responsive when application pools, ETL jobs, or misbehaving clients open more sessions than the host can serve comfortably. A sensible cap turns a connection surge into a controlled refusal instead of letting the database drift into memory pressure and scheduler thrash.

The server enforces this ceiling with the global max_connections variable. Each authenticated client consumes a connection slot until it disconnects, so comparing Threads_connected with Max_used_connections shows whether the current cap is already close to real peak demand or whether the real problem is a connection leak or oversized pool.

Raising the limit increases potential concurrency, but it also increases per-session memory demand and can magnify the impact of expensive queries. SET GLOBAL changes the running value immediately for new sessions, while persistent changes still depend on the server family: MySQL 8.0+ supports SET PERSIST, but MariaDB does not, so the option-file method remains the portable path across both.

1. Open the mysql or mariadb client as an administrative user.

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

   On socket-auth installs, sudo mysql or sudo mariadb can open a local 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)

   New client sessions usually fail with 1040 Too many connections once the normal connection pool is full.

3. Compare the current and peak connection counts before choosing a new cap.

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

   Leave modest headroom above the observed peak rather than treating a higher limit as a substitute for fixing connection leaks or right-sizing the application pool.

4. Apply the new in-memory limit for new sessions.

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

   Use an account permitted to change global server variables.

   Raising max_connections too aggressively increases per-connection memory usage and can push the host toward swapping or the OOM killer during spikes.

5. Confirm the running server reports the updated value.

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

   Existing sessions stay connected, and the new limit applies to future connections that arrive after the change.

6. Exit the client when the runtime change is confirmed.

   mysql> exit
   Bye

7. Edit the server option file or a dedicated override file under the active mysqld include directory.

   $ sudoedit /etc/mysql/conf.d/99-max-connections.cnf

   [mysqld]
   max_connections = 200

   On Debian or Ubuntu, this is commonly /etc/mysql/mysql.conf.d for MySQL or /etc/mysql/mariadb.conf.d for MariaDB, while many other distributions use /etc/my.cnf or /etc/my.cnf.d.

   MySQL 8.0+ also supports SET PERSIST max_connections = 200, but MariaDB does not, so the option-file method remains the portable path across both servers.

   Related: How to locate and modify MySQL and MariaDB configuration files

8. Restart the database service so the persistent setting loads on the next daemon start.

   $ sudo systemctl restart mysql

   A restart disconnects active clients and can interrupt applications that do not retry or fail over cleanly.

   Use the installed unit name such as mysql, mariadb, or mysqld.

   Related: How to manage MySQL or MariaDB service in Linux

9. Verify the persisted value from a new client session after the restart.

   $ mysql --table --user=root --password --execute "SHOW VARIABLES LIKE 'max_connections';"
   +-----------------+-------+
   | Variable_name   | Value |
   +-----------------+-------+
   | max_connections | 200   |
   +-----------------+-------+

   If the value reverts after restart, another later-loaded option file is overriding it.