Apache2, MySQL Optimization (for Wordpress)
Created at 2016-04-27T07:01:41.000Z

UPDATE: 2016/05/25

It turned out the problem of memory shortage is because of famous XML-RPC Attacks. I followed this article digitalocean: how-to-protect-wordpress-from-xml-rpc-attacks-on-ubuntu-14-04. My change is here.


Today, I came across "Error establishing a database connection" for the first time. If I checked on the server and run sudo service mysql status, it was not running. Then, I restarted manually by sudo service mysql start, but the same error happened in a moment.

This wordpress is run on AWS EC2 micro instance (which only has 1GB memory). As a temporary solution, I made 1GB of swap memory and I went to figure out the cause of the problem.

Simply put, it was a memory shortage and that came from Apache2 and MySQL. These changes are solution I found from some of the references:

Apache2

For the file /etc/apache2/mods-available/mpm_prefork.conf, I changed from this:

<IfModule mpm_prefork_module>
    StartServers             5
    MinSpareServers       5
    MaxSpareServers      10
    MaxRequestWorkers     150
    MaxConnectionsPerChild   0
</IfModule>

to this:

<IfModule mpm_prefork_module>
    StartServers        1
    MinSpareServers     1
    MaxSpareServers     5
    MaxRequestWorkers   20
    MaxConnectionsPerChild   0
</IfModule>

MySQL

For the file /etc/mysql/my.cnf, I changed from this:

...
key_buffer        = 16M
max_allowed_packet    = 16M
thread_stack        = 192K
thread_cache_size       = 8
...
myisam-recover         = BACKUP
max_connections        = 100
...

to this:

...
key_buffer        = 16M
max_allowed_packet    = 16M
thread_stack        = 192K
thread_cache_size       = 1     # from 8 to 1
...
myisam-recover         = BACKUP
max_connections        = 10     # from 100 to 10
...

Results from htop and ps

I'll show screenshots of htop and the number of child processes obtained by ps. Here is Before__ and __After:

Before

2016-04-27_2257

2016-04-27_2258

# show the number of child processes by `ps` command
$ ps -e -T | grep apache2 | wc
    151     755    5738
$ ps -e -T | grep mysqld | wc
    167     835    6179

After

2016-04-28_0016

# show the number of child processes by `ps` command
$ ps -e -T | grep apache2 | wc
      6      30     228
$ ps -e -T | grep mysqld | wc
     17      85     629

Of course, those changes are applied by using ansible. I extended my scripts accordingly. Here is a diff from my public repository.

References