How to change limits in MariaDB, max_connections

Hello colleagues.

In today's article, I will tell you about how to change client connection limits in MariaDB. The max_connections parameter is responsible for the number of external connections in MariaDB. You need to increase or decrease it not when you want, but when you know exactly how many connections to the database you will have. There is another indicator when you need to increase the connection limit, this is when you get a "too many connections" error in the logs or in the application, it means that the connection limit to the database has been exceeded.

This article is suitable for the database administrator, programmer and engineer.

The content of the article:

1. What is the current value of the max_connections parameter.
2. How many active connections are there now.
3. Change the max_connections parameter.
4. Results.

1. What is the current value of the max_connections parameter.

So, for starters, before changing the values of max_connections, let's see what values it currently stores. To see this, use the command in the console:

mysql> SHOW VARIABLES LIKE 'max_connections';

So, we see the values ​​of 151, these are the default values, and we will change it a little later.

2. How many active connections are there now.

Now that we know the maximum value of the max_connections parameter, now let's see how many active sessions there are at the moment. To view the number of active sessions, use the command:

mysql> SHOW STATUS WHERE variable_name = "max_used_connections";

The max_used_connections parameter indicates that we currently have only one active session.

3. Change the max_connections parameter.

Now let's change the values of the max_connections parameter. There are two ways to do this. The first is through the SET GLOBAL function, but when you reload the database your values will reset back to the default value. The second way is to change it in the configuration file. And so we first use the first option.

mysql> SET GLOBAL max_connections = 500;

This was the first option.

Now let's change this parameter, but through the configuration file. The server.cnf file, which is located in the /etc/my.cnf.d directory, is responsible for MariaDB configuration.

$. vim /etc/my.cnf.d/server.cnf

And add the parameter max_connections = 500. In this case, we indicate that the maximum number of client connections is 500.

Save and close.

4. Results.

As a result, today we have learned how to change the maximum number of client connections in MariaDB. The best practice is to not reload the database, for this we first change the max_connections values in the console through the SET GLOBAL function, and then add this parameter to the configuration file so that if your database is reloaded, then the max_connections value will not be reset to default values, and will be taken from the server.cnf configuration file.

Thank you all, I hope my article was of some help to you.