Categories
MySQL PHP

What to do when you have the error “too many connections”

Recently our servers went down. The error we were getting was “Too many connections”. This meant that we needed to increase the limit on the number of available connections within our mysql config file. The setting is called

max_connections

Before you go upping the number of max user connections there are two things to consider.

1. Is your code opening and closing the connections efficiently?

According to the mysql “using mysql_close() isn’t usually necessary, as non-persistent open links are automatically closed at the end of the script’s execution.” However, there are rare occasions where the connections do not close properly, so it is always a good rule of thumb to include mysql_close() at the end of your scripts.

Another mistake that can occur is that you may be unintentionally opening a new connection for each query, rather than opening one connection and then closing it once all your queries are completed. This will definitely increase the number of user connections.

Also, using persistent connections are generally frowned upon these days. For php using mysql_connect has actually proven to be more efficient.

2. Do you have enough memory (RAM) available to handle your increased connections.

Once you know your code is clean and you are still running out of available user connections the next step will be to increase your number of max_connections on your mysql config file.

It is important to calculate your available memory before upping your connection limit however, other wise you will encounter this error:

Connect Error (1135) Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

This error is simply telling you that you are using too much memory. To avoid this error when upping your connection number use the following formula to calculate your number of available connections.

Key_buffer_size + ((read_buffer_size + sort_buffer_size)*max_connections) = memory needed

If you know your server’s ram, calculating the number of max_connections is straight forward.

I hope this helps you if you are encountering this problem

Note: Your current settings for Key_buffer_size, read_buffer_size, sort_buffer_size and max_connections can all be found in your mysql config file.