Definition of MariaDB Max Connections
- MariaDB Max connection by default is configured for connections up to 150, also additionally one more connection is operated for the root access but in case if not used already, thus it becomes 151 connections.
- The system variable named max_connections controls the number of connections to be allowed. 100 is the default value and for more, we need to configure the big value of the system variable.
- Max connections are enhanced on MariaDB SkySQL for allowing a distinct number of concurrent database connections on the basis of a per-instance and per-service size.
- By means of the SkySQL Configuration Manager, the setting of max connections will not be customer-tunable. Thus, we may be contacted by the SkySQL Power Tier customers for chatting traditional tuning necessities.
How to work?
The number of connections that can be accepted by MariaDB/MySQL is determined by the max_connections system variable. For this, initially, we need to confirm what will be the new maximum value that the user requires to use for max_connections. When the user wants to increase the number of MariaDB/MySQL connections, several considerations should be taken into account listed as follow since the system supported maximum number depends on them:
• Availability of RAM quantity
• How much RAM will be taken by every connection (as easy query commands will need less RAM than more connections for labour-intensive ones.)
• The response time that will be acceptable.
MySQL Error Too many connections
- In MySQL 5.5, the maximum allowed number of concurrent client connections by default is 151. But when the limit is reached for the max_connections, then the error of “Too Many Connections” will be received if anyone tries to connect the MySQL server. When you try to associate the server mysqld, then you might receive an error i.e. Too many connections, which means that all existing connections are in usage by another client.
- MySQL authorizes one additional connection on the top level of the limit of max_connections that is kept for the database consisting of SUPER privilege so that the connection problems can be identified. Basically, this SUPER privilege holds the administrator level user privilege. Hence, we need to escape the app users admitting SUPER privilege.
- MySQL implements a single thread per client connection, as well as several active threads, which is considered a performance killer. Typically, a great number of synchronized connections running query commands in parallel may origin major slowdowns with upturn chances for deadlocks.
- Though improvements on MySQL is going on every day but still the growth in memory usages will be caused due to active connections in hundreds where every connection needs per-thread buffers. Moreover, implicit in the server memory tables need more memory in addition to memory necessity for global buffers. Overall this, every connection may apply max_heap_table_size/tmp_table_size, even though they are not assigned instantly per new connection.
- It is influenced by the amount of memory usage and RAM present for every connection. But if a connection pool on the application side is applied, then the max_connections must be greater than max connections. This connection pooling is considered as a good substitution when it is projected a great number of connections.
MariaDB max connections Examples
We need to follow the below procedures to configure the max_connections:
- Updating the file my.cnf, with location /etc/my.cnf, to edit using the command as follows:
Sudo nano /etc/my.cnf
Beneath the first line openly:
[mysqld]
We will add a line as:
Max_connections = [preferred new max digits]
Example: For setting max_connections supposed to 210, then the initial two lines of the file will be read as:
[mysqld]
Max_connections =210
After this save the file and you can exit now.
Configuring GLOBAL
We need to be logged in to the command-line client in MariaDB/MySQL writing the query as:
Mysql –u root -p
Setting the new value for max_connections as follows:
SET GLOBAL max_connections = [preferred new max digits];
Suppose for setting max_connections nearly to 210, and then query command will be written as:
SET GLOBAL max_connections =210;
Now, we will leave the MariaDB/MySQL typing the command as:
Quit;
• Now, we will execute the SHOW command which lists all the variables that are configured for MariaDB/MySQL where on scrolling up the user may get the value the variable i.e. max_connections as:
SHOW variables;
Output:
The suggested action for varying the value is the determined approach and then resuming MariaDB that will destroy all the determined connections. Therefore, let us view the query to modify the max_connections value in the configuration file:
Max_connections = 210;
After restarting the server MariaDB, you can view the new value delivered on executing the command as shown below:
SET GLOBAL max_connections = 210;
Output:
How to increase the MariaDB max connections?
To achieve more connections than 100k and go even further, there are three modules to attain this max_connections:
- Proper training of network limits
- Thread pool in Percona Server (resources are managed proficiently with enhanced response times.)
- By means of multiple IP addresses on the server box (single IP address per 60k connections approximately.)
The systems which get too busy may provide a too_many_connections error, therefore when the quantity of threads_connected exceeds the server variable i.e. max_connections, then think of making a modification. This error symbolizes a symptom that occurred due to slow queries or any bottlenecks, nevertheless, if the server is executing smoothly then it can be addressed by rising the same system variable’s value. This changes need to be made in two locations:
1. The my.cnf file should be updated, so that the fresh value can be implemented when the server MariaDB/MySQL is resumed.
2. Writing the command SET GLOBAL for modifying the value on the executing MariaDB/MySQL server.
Coincidentally, with this technique, MariaDB/MySQL will not require to resume MariaDB/MySQL, so also any downtime may not be practised.
Conclusion
- Depending on workload, we can set the applicable value for the max_connections as there is no static rule for this.
- Most Linux systems, without any difficulty, must be capable to support about 500-1000 connections, according to the MySQL documentation.
- We can gain more info about Max Connections on the link ‘Max Connection Tuning with MariaDB Xpand’, with MariaDB Platform for the Distributed SQL.
Recommended Articles
This is a guide to MariaDB Max Connections. Here we discuss the definition, how to work with examples for better understanding. You may also have a look at the following articles to learn more –
