Select Page

How to fix MySQL ‘Too many connections’ error in your server

In our role as Server administration specialists for server owners, database errors are something that that we resolve for them. A commonly reported one is the MySQL ‘Too many connections’ error.

Many server owners who have their sites running on MySQL database server, often come across this error in the websites or server:

ERROR 1040 (HY000): Too many connections

In the case of PHP based websites with MySQL database server in the backend, the error message can be something like:

PHP Warning: mysqli_connect(): (HY000/1040): Too many connections

MySQL ‘Too many connections’ – The causes and fix

When a MySQL server is initially installed, it will have 150 as the default value for maximum permitted number of simultaneous client connections. This value can be adjusted further in servers.

An additional admin connection is also possible to monitor these client connections. This extra connection is reserved for use by accounts that have CONNECTION_ADMIN or SUPER user privilege.

The number of connections is defined by ‘max_connections’ value. When the number of client connections exceed this value, MySQL shows ‘Too many connections’ error.

This can happen in multiple scenarios, and the fix for the error would vary with the actual cause. Here, we’ll see the various causes that trigger MySQL ‘Too many connections’ error.

1. High traffic to sites

Many a times, the web traffic can go high due to peak sales or promotions. If there number of connections allowed for your MySQL server is not enough, users will get “Too many connections” error in websites.

In such scenarios, we first examine the MySQL processes in the server and analyze the web server traffic to figure out which site is causing the issue.

If the traffic is valid, we tweak the maximum number of connections to the database that are allowed, and increase it to a value that is high enough to meet the website requirements.

For resource-intensive websites, sometimes increasing the connection limit alone would not be sufficient. We do an in-depth analysis and tweak various service parameters to handle this high traffic.

2. Web server attacks

Often, it has been seen that a sudden increase in website traffic and intermittent ‘Too many connections’ errors denote a flooding attack to the web server.

If the connection limit is increased without noticing or preventing the attack, it will eventually crash your server, as a flooding attack is aimed at exhausting server resources with too many requests.

Bobcares’ engineers detect such attack attempts proactively, and immediately pinpoint the source of the attack and block those IP addresses, before it crashes the server.

We examine the access logs, network connections to the server, malicious scripts and processes, etc. in the server, to figure out the depth and extent of the attack.

3. Poorly coded applications

Many websites have custom code and applications running on them. Many often, this code may contain bugs or can lead to infinite loops of MySQL queries, leading to too many connections.

Too many persistent connections to MySQL server or the application not closing connections properly, can also lead to MySQL ‘Too many connections’ error.

If the timeout for idle connections is too high, it can cause too many sleeping connections, and thereby use up the allowable limit of MySQL connections in the server.

By examining the MySQL processes and queries that are running, we figure out the code that is causing issue, and optimise it further to fix ‘Too many connections’ error.

MySQL ‘Too many connections’ fix – What to keep in mind!

One common question that comes into the mind of server owners, is what exactly should be the value of max_connections parameter for MySQL. The fact is that, there is no such specific value.

The number of allowable connections depends on the amount of RAM available and memory usage for each connection. Increasing the value too much can use up the RAM and cause server to crash.

At Bobcares, we examine the available memory in the server and other parameters that require the RAM, before going ahead and increasing the connection limit.

Other parameters such as the timeout settings, cache and open tables can also influence the memory utilised for MySQL and the connections possible.

By doing an iterative tweaking and examining the MySQL server performance and memory usage after each iteration, we configure the MySQL server to serve connections with maximum efficiency.

Conclusion

MySQL ‘Too many connections’ is a commonly seen, but confusing database server error. The major hurdle involved in fixing it, is finding the actual root cause of the error. From attacks to database server settings, we’ve seen the various causes for the error and how to fix it permanently.

 


FIX SERVER ERRORS

Worried over server errors? Consult our 24/7 server experts to ensure seamless server functioning.

FIX YOUR SERVER ERRORS

Submit a Comment

Your email address will not be published. Required fields are marked *

Bobcares
Bobcares is a server management company that helps businesses deliver uninterrupted and secure online services. Our engineers manage close to 51,500 servers that include virtualized servers, cloud infrastructure, physical server clusters, and more.
MORE ABOUT BOBCARES

Privacy Preference Center

    Necessary

    Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

    PHPSESSID - Preserves user session state across page requests.

    gdpr[consent_types] - Used to store user consents.

    gdpr[allowed_cookies] - Used to store user allowed cookies.

    PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
    PHPSESSID
    WHMCSpKDlPzh2chML

    Statistics

    Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

    _ga - Preserves user session state across page requests.

    _gat - Used by Google Analytics to throttle request rate

    _gid - Registers a unique ID that is used to generate statistical data on how you use the website.

    smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

    _ga, _gat, _gid
    _ga, _gat, _gid
    smartlookCookie

    Marketing

    Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

    IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

    test_cookie - Used to check if the user's browser supports cookies.

    1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

    NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

    DV - Google ad personalisation

    IDE, test_cookie, 1P_JAR, NID, DV, NID
    IDE, test_cookie
    1P_JAR, NID, DV
    NID