How to calculate max connections in mysql

Table of Contents Show

What causes MySQL Too Many Connections?
How Many Connections can MySQL handle?
1. Check the default max connections
2. Increase Max Connections
3. Restart MySQL Server
Related posts:
How do I find the maximum DB connections in MySQL?
What is Max connections in MySQL?
How many max connections can MySQL handle?
How many connections MySQL 8 can handle?

B.3.2.5 Too many connections

If clients encounter Too many connections errors when attempting to connect to the mysqld server, all available connections are in use by other clients.

The permitted number of connections is controlled by the max_connections system variable. To support more connections, set max_connections to a larger value.

mysqld actually permits max_connections + 1 client connections. The extra connection is reserved for use by accounts that have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege). By granting the privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. See Section 13.7.7.29, “SHOW PROCESSLIST Statement”.

The server also permits administrative connections on a dedicated interface. For more information about how the server handles client connections, see Section 5.1.12.1, “Connection Interfaces”.

Sometimes MySQL server may give “Too many connections” error message when you try to connect to a MySQL database. Here’s how to increase max connections in MySQL to fix this problem.

What causes MySQL Too Many Connections?

If you get “Too many connections” error message while connecting to MySQL database it means all available connections have been used by the various clients and your MySQL Server cannot open any new connections until any of the existing connections is closed.

How Many Connections can MySQL handle?

By default, MySQL 5.5+ can handle up to 151 connections. This number is stored in server variable called max_connections. You can update max_connections variable to increase maximum supported connections in MySQL, provided your server has enough RAM to support the increased connections.

Here are the steps to increase max connections in MySQL.

1. Check the default max connections

Log into MySQL command line tool and run the following command to get the current default max connections supported by your database server.

mysql> show variables like "max_connections";

You will see the following output.

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

2. Increase Max Connections

There are two ways to increase MySQL connections. Here’s the command to update max connections to 200 via MySQL command line tool without restart.

mysql> set global max_connections = 200;

The above command will increase max connections without restart but once the server is restarted, the changes will be gone. This method is useful to increase available connections in a production system such as a website/app, without having to restart your database server.

To permanently increase max connections, open my.cnf file,

$ sudo vi /etc/my.cnf

Depending on your Linux distribution and type of installation, my.cnf file may be located at any of the following locations.

/etc/my.cnf
/etc/mysql/my.cnf
$MYSQL_HOME/my.cnf
~/.my.cnf

Add the following line under [mysqld] section.

max_connections = 200

Now if you restart MySQL server, the changes will persist.

3. Restart MySQL Server

Restart MySQL Server to apply changes

$ sudo service mysql restart

Hopefully, the above tutorial will help you increase max connections in MySQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it for free!

About Author

max_connections = (Available RAM – Global Buffers) / Thread Buffers

For getting the available RAM in bytes.

free -b
               total        used        free      shared  buff/cache   available
 Mem:     8253255680  6625886208   172158976   931852288  1455210496   409915392

For getting the details about Global buffers, access MySQL and run the following command.

SHOW VARIABLES LIKE '%buffer%';

Output be like this:

mysql> SHOW VARIABLES LIKE '%buffer%';
 +------------------------------+-----------+
 | Variable_name                | Value     |
 +------------------------------+-----------+
 | bulk_insert_buffer_size      | 8388608   |
 | innodb_buffer_pool_instances | 1         |
 | innodb_buffer_pool_size      | 134217728 |
 | innodb_change_buffering      | all       |
 | innodb_log_buffer_size       | 8388608   |
 | join_buffer_size             | 131072    |
 | key_buffer_size              | 8388608   |
 | myisam_sort_buffer_size      | 8388608   |
 | net_buffer_length            | 16384     |
 | preload_buffer_size          | 32768     |
 | read_buffer_size             | 131072    |
 | read_rnd_buffer_size         | 262144    |
 | sort_buffer_size             | 2097152   |
 | sql_buffer_result            | OFF       |
 +------------------------------+-----------+
 14 rows in set (0.00 sec)

Ram: 8253255680

Global Buffers = key_buffer_size + innodb_buffer_pool_size +innodb_log_buffer_size + innodb_additional_mem_pool_size +net_buffer_length +query_cache_size

Thread Buffers: sort_buffer_size + myisam_sort_buffer_size + read_buffer_size + join_buffer_size + read_rnd_buffer_size+ thread_stack

It puts the data together for the calculation.

Global Buffers = 8388608+134217728+8388608+0+16384+0 =151011328

Thread Buffers = 8388608+2097152+131072+131072+262144+0 = 11010048

max_connections = (Available RAM – Global Buffers) / Thread Buffers

                = (8253255680 - 151011328 ) / (11010048)

                = 735.89

Mainly max_connections will be in the range of 200 to 320 for small websites and for large websites it vary from 700 to 1024. It mainly depends upon the ram that you have. Setting the value higher tends to use the MySQL resource concurrently by the connections and leads to MySQL down.

Post Views: 562

How do I find the maximum DB connections in MySQL?

To check the current number of max_connections log in to the MySQL/MariaDB command line client with the following command:.

mysql -u root -p..

SHOW variables;.

sudo nano /etc/my.cnf..

[mysqld].

max_connections=[desired new maximum number].

[mysqld] max_connections=200..

mysql -u root -p..

What is Max connections in MySQL?

MySQL Connection Limits At provision, Databases for MySQL sets the maximum number of connections to your MySQL database to 200. You can raise this value by Changing the MySQL Configuration.

How many max connections can MySQL handle?

Each database user is limited to 38 simultaneous MySQL connections.

How many connections MySQL 8 can handle?

mysqld actually permits max_connections + 1 client connections. The extra connection is reserved for use by accounts that have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege).