Signs of Trouble

While we as software developers prepare for production releases as much as possible, the right sequence of events can still translate into something very wrong. This past week, visitors to this blog were greeted with the standard WordPress database error connection message, and I was unaware until I decided to share this blog with a contact.

Needless to say, I was embarrassed. My monitoring tool was supposed to alert me if the site went down. Why didn't it? It turns out that an HTTP status code of 500 was deemed acceptable by the tool. I've since reached out to the vendor to see if this can be corrected.

But what happened? Admittedly, this blog is a fairly standard WordPress instance and currently receives few visitors. Had the server been compromised? It was unlikely as I update automatically to avoid WordPress exploits and use few plugins. It was still possible, however.

I logged into the server and first checked the status of the MySQL service:

    $ sudo service mysql status
    mysql stop/waiting

Well, that isn't right. Let's at least turn it back on and see what happens:

    $ sudo service mysql start
    mysql start/running, process 17894

Thankfully that worked without issue, but I still didn't know why the database service had stopped. I could have looked into the logs, but I already had a suspicion as to what happened.

mysqltuner

The VPS tier that I hosted on only offers 512 MB of memory. After looking at the server configuration, I noticed I had opted to go without a swap partition. It turns out that I had incorrectly assumed what the memory requirements were for hosting a lightly used WordPress blog.

After some Googling, I found the perfect tool to check potential resource consumption - mysqltuner. As a bonus, it was available in my distro's package manager:

    sudo apt-get install mysqltuner

After running the tuner, I received a very interesting warning:

    [!!] Maximum possible memory usage: 597.8M (122% of installed RAM)

That is obviously an issue. It does not take an expert to see that I am using more resources (122%) than I have (100%). What is worse is that I have no swap partition to fall back on.

The Database Configuration

It is obviously a time to look at the database configuration.

    less /etc/mysql/my.cnf

I quickly noticed that on my single core VPS that no limit on threads was specified! Not only that, but the default connection pool in MySQL depending on the version can be between 100-151 connections. In addition to that, many caching values were much higher than I would expect a blog post to be.

I decided to cut back on the number of threads, the number of connections, and lowered a few cache values by half or three-fourths. Probably the most important were the max_connections and thread_concurrency settings:

    max_connections        = 30
    thread_concurrency     = 2

After restarting the database service...

    sudo service mysql restart

...I saw much better results from mysqltuner:

    [OK] Maximum possible memory usage: 206.9M (42% of installed RAM)

And, this blog has been up and stable since!

In closing, I would like for everyone out there who reads this to learn a lesson from my mistake. When working with these virtualized environments (Vagrant, Linode, Digital Ocean) or container formats (Docker) take the time to tune and benchmark your applications. I underestimated my resource requirements, and you might too.