MySQL is one of the most popular free, open-source databases used by millions of websites, online stores and blogs. It is capable of handling high number of connections, making it suitable for supporting high-traffic websites and blogs. But sometimes, you may need to increase max connections in MySQL database. In this article, we will learn how to do this.
Why Increase Max Connections in MySQL
There are several reasons why you may need to increase max connections allowed by MySQL server. Let us look at some of the common ones.
- Error messages – When your MySQL server reaches connection limit, it will start throwing all sorts of error messages such as connection timed out, insufficient memory, etc. These messages may even appear on front end web pages, confusing both users as well as system administrators.
- Dropped Connections – If there are too many connections in MySQL server, then it may start dropping some of the existing connections to accommodate new ones. Sometimes, it may simply drop new connection requests without any response.
- Connection Time out – If MySQL server is already maxed out with respect to connections, then any new connection will have to wait for a long time, until any of the existing connections closes. While doing so, these new connection requests may get timed out.
- Slow Processing Queries – If your MySQL server has reached connection limit, then it means your server will be consuming a lot of resources. This will slow down all queries until some of the connections terminate and free up resources. If there are some long running queries among them, then they will cause other queries in the queue to slow down.
- Concurrency – Generally, MySQL manages queries from all users in a single queue or pool. If it is maxed out, then all your concurrent users will face the consequences of this problem. This will also reduce the number of concurrent users that your system can handle.
All these are compelling reasons to increase connection limit for MySQL database.
How to Increase Max Connections in MySQL
Next, let us look at how to increase max connections in MySQL server. You can do it temporarily for specific session, or you can do it permanently that persists across sessions. We will look at both these approaches one by one.
Temporary Change
In this case, we simply update the value of global variable max_connections using SQL query. Here is its syntax.
set global max_connections = new_value;
Replace new_value with the increased number of connections. Just run the following query to increase max MySQL connections to 5000.
set global max_connections = 5000;
This approach does not require you to reboot MySQL server. But when you create a new session, these changes will be lost. This solution is useful if you want to programmatically increase max connections, without having to reboot MySQL server. It is also useful if you only want to change max connections for specific queries and revert it back after the query is executed.
Permanent Change
If your website/application consistently hits max connections, then you may want to permanently increase this limit so that it is persistent across server reboots. For this purpose, open MySQL configuration file in text editor. It is my.cnf in Linux and my.ini in Windows.
sudo vi /etc/mysql/my.cnf
Next, add or modify the max_connections setting. If it is already present, update it as per your requirement, else add it in [mysqld] section. Here is an example to increase max connections to 5000.
max_connections = 5000
Now, whenever you reboot MySQL, it will read the configuration file and update connection limit automatically. This approach is useful if you have determined that you need to increase max connections for good.
Restart MySQL Server
You need to restart MySQL Server to apply the above mentioned persistent changes. Run the following command to restart MySQL server.
sudo service mysql restart
# OR
sudo systemctl restart mysql
Verify Change
When you change the max connections in MySQL, it will change the value of global variable max_connections. You can verify the change by checking the value of this variable. Open MySQL console and run the following query to get its present value.
mysql> SHOW VARIABLES LIKE "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 5000 |
+-----------------+-------+
You will see that this variable’s value has been updated. Please note, if you have temporarily changed its value, then it will be reset to default or previous value when you end the present MySQL session. If it has been changed persistently, then it will continue to remain changed even after you end this session or start a new session.
Points to Remember
It is also important to keep the following points in mind while changing max connections in MySQL.
- Open connections – It is always good to make sure that your application properly closes all database connections, when not required. Many times, applications hit max connections simply because they keep connections open even after processing queries. If you do not close connections after query execution, then your system will quickly hit the limit once again. It is only a matter of time. So evaluate application logic and code where database querying happens.
- Max_user_connections – If your database is used by multiple users, and you do not want most of the available connections to be hogged by specific users, then you can set the value of max_user_connections to restrict the number of concurrent connections for individual database users.
- Upstream Changes – If the number of database connections is also controlled by your application, or web server, then you will also need to propagate the increase in max connections to these places.
- Resource Monitoring – When you increase max connections in MySQL, it will start consuming more resources such as memory and CPU. So be prepared for that by monitoring these resources regularly using log management and other tools. This will prevent your server from crashing.
Conclusion
In this article, we have learnt a couple of simple ways to easily increase max connections in MySQL. You can use any of these methods as per your requirement. In most cases, the default number of max connections is enough to support even high traffic sites and blogs. If you need to increase MySQL connections, then make sure to check how your application/site is handling these connections before you increase the limit.
Also read:
How to Unpivot Table in MySQL
How to Alter Column from NULL to NOT NULL
How to Import SQL File in MySQL
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.