Tweaking your server to not get the “Error establishing a database connection”

mysql
mysql

For months now my server has been running ok, I only had a small issue with a misconfigured varnish server, that caused the PHP session cookies to not work.

In the past I have had issues with my Ubuntu 14.04 Server, almost on a daily basis the server crashed with the error “Error establishing a database connection”, as my host is Digital Ocean there were others who experienced similar problems, it seems that MySQL is running out of RAM and crash, that could be due to a traffic spike.

There are several solutions available, the simplest solution is to add swap file on the server, or upgrade your droplet to the next plan so that there’s more RAM available for MySQL. I took both steps, I added swap file and I also upgraded to the next plan.

This worked tills a few days ago when the server starts crashing again with “Error establishing a database connection”, so I started to look into how to add performance to the server, I came across an article on how to tweak the Apache  Mpm prefork values, it seems on my server it was set a bit too high. So I changed it to a more suitable number for the traffic I have.

The Mpm prefork values should be located in /etc/apache2/apache2.conf file or /etc/apache2/mods-enabled/mpmprefork.conf file. If you don’t have these values in apache2 file and you don’t have mpmprefork.conf file, then you have to install mpmprefork module and restart apache.

Here are the values that I defined, seems to work fine now:

StartServers          2
MinSpareServers       3
MaxSpareServers       5
MaxClients           100
ServerLimit          100
MaxRequestsPerChild 3000

What do these values means, here are some explanations:

  • StartServers – this is how many apache instances should start at the very beginning when apache is started. I set it to 2, and it works fine for me.
  • MinSpareServers – the minimum number of spare servers that should be running waiting for potential requests. I set it to 3, works fine for me.
  • MaxSpareServers – the maximum number of spare servers that should be running waiting for potential requests, obviously >= MinSpareServers. In my working example MaxSpareServers=5.
  • MaxClients – You need to figure out how much memory is consumed by one Apache process when you know the average amount of memory consumed by Apache and the total amount of memory of your server, it is possible to calculate the value to be used for MaxClients setting. For example, if on average one Apache process consumes 50MB RAM and server RAM is 2GB, and you want to leave 512MB for the rest processes, then: MaxClients = (2GB – 512MB)/50MB = 30.72 ~ 30.
  • ServerLimit value is MaxClients should always be <= ServerLimit. To make it easy, I set ServerLimit = MaxClients to the same value. For new ServerLimit value to take effect Apache restart is required.
  • By default MaxRequestsPerChild = 0, which means that httpd process will never expire. However, it can happen that there are some memory leaks in your PHP scripts (especially if you are using lots of third-party contributed modules). To protect yourself from accidental memory leaks, you can set how many requests should be processed by the Apache process before it dies. In my example, I used MaxRequestsPerChild = 3000 and it works fine.

If you are running WordPress on your server, then you may also want to take a look at MySQL database and make some changes, WordPress itself is installed with the InnoDB engine, I have noticed that some plugins are using MyISAM engine, which means that you will run two engines. you can fix this with PhpMyAdmin and repair your tables and switched all tables from MyISAM to InnoDB. Before any work on the database, do a backup.

I hope this will help someone who has a problem, please comment below.

Leave a reply:

Your email address will not be published.