Hosting – Machiine

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.

3 things I wish I had known before moving to Media Temple

Recently we migrated our server to Media Temple’s grid servers. I had heard good things about Media Temple from others before making the decision to migrate, however while migrating we started to encounter challenges that I was not forewarned about. So here are 3 things that I wish we had known before moving, I hope they will help you avoid the pitfalls we encountered.

1. Mysql Views are not allowed

We discovered this while attempting to install our database on our new MediaTemple database. I sent a support request about this, they replied that they are planning on allowing views in the future, but don’t presently. While most web applications do not use Mysql views ours did. As a result I spent needless hours rewriting code to work without views. It was frustrating to say the least.

2. Maximum 500 emails per hour and 60/min

Here’s another small fact to know before deciding to move over to the grid server. There is a limit of 500 emails per hour. 500 emails an hour sounds like plenty. It’s not. Our application communicates to our users via email, sending updates and notifications constantly. We now pay for a 3rd-party API to send emails to our users. This is unnecessary and MediaTemple should really look at changing this.

3. No static outbound IP Address

This proved to be the biggest frustration of them all, especially because I asked one of MediaTemple’s support people about this before migrating and they ensured me that they had offered static outbound IP addresses. They do not. MediaTemple will give you a static IP address for all incoming requests. However, it will not give you a static IP address for outgoing requests. This was a big deal for us because we were using a secure API that required a single static outgoing IP Address. However, MediaTemple was unable to provide us with one. When I asked if they would be able to NAT the outgoing IP Address range to one address, the response was “Uhhhhhh?? Let me ask our server team”. Not very reassuring. They eventually responded with “no”, but without any reason why. We did come up with a work-around solution, but it is not ideal to say the least.

Despite these unpleasant surprises there are definitely some strong pros to MediaTemple. Its grid server uses a very eloquent node sharing system. This allows server resources to be freed up and reallocated where needed. Also they offer mysql containers to ensure dedicated ram is always available for your database requests for an additional fee. There support isn’t the greatest especially when compared to some of the hosting companies I also use, like LiquidWeb.

Overall I would recommend MediaTemple’s Grid Server, just know what you are getting into.