Database Concurrent Connections Limit Explained
Every hosting company can announce to provide unlimited databases but no one can guarantee unlimited concurrent connections. Why is that? This article provides side by side reviews of database concurrent connections and guide on how to use it properly. We'll also guide on how to fix the popular errors caused by concurrent connection limits.
About Database Concurrent Connections
Concurrent connection refers to the number of concurrent open connections to database. We can think in this way to understand this theory, for example there're 100 people opened phpmyadmin on their computers and all of them connected to the same database, here the database concurrent connections are 100. Concurrent connection can be opened manually via database tools or from script connections.
As we know, each connection will use up some server resources. Hence if the number of connections increase then more server resources will be used. If all databases being connected like this on hosting server then performance can not be guaranteed. That's why every hosting provider set a reasonable limit of database concurrent connections.
But please note the limitation is only for mysql database not mssql. There's no such limitation for mssql database.
How to configure Mysql concurrent connection limit?
Mysql concurrent connection limit can be setup during installation. You can also make the change after setup. Take windows server mysql setup. The installer will guide to configure each parameter. Concurrent connection is specified from the last step as showed below
You can also ignore it directly to have mysql running first then define all parameters based your requirements. All mysql configuration is stored in my.ini file from installation directory. For linux server, the file is provided as my.cnf which is normally located at /etc/my.cnf. In this file there's a parameter called "max_connections". You can give the appropriate number based your requirement.
What's the limitation on hosting server? We personally have been working with 10 hosting providers. From our examination, most providers offer 10 or 15 connections per database. One hosting service provides up to 20.
How many concurrent connections we need?
Because concurrent connection is strictly limited and it seems so little, how many we need exactly to power our website? Actually, if our website is programmed properly, we need very few connections to database. A single connection will handle most tasks. What we need to understand is the number of database concurrent connection is not the same as website visits. Your website might be browsed by 1000 users concurrently but it might doesn't require database connection at all. Database connection is opened when your website has to read/write data from/to database such as artice editing, user registration etc. You don't have to open multiple connections to complete those tasks.
But please remember to close the connection after use. Or else it will be always opened there so after you opened connection multiple times the concurrent connections will be huge. Once you reached the server limits, you will get database connection failed error such as "Error establishing a database connection". In order to fix this error, you must contact server admin and run "flush hosts" command to clear out the connections.
In order to operate a healthy database driven website, it's very important to choose a well coded platform no matter if you use cms software or program by yourself. Always remember to close connection after use it. Normally, a popular cms software is pretty reliable because many users have used the platform so the project team could find various bugs then apply fixes quickly.