Fixing WordPress-MySQL issue with Swap

On KAPsNotes, we were facing a weird WordPress-MySQL issue. Our WordPress site was going down frequently. Here is a quick fix by introducing Swap.

Background

KAPsNotes was going down frequently. Although issue was automatically getting fixed in 10-15 minutes, it was annoying for two reasons:

• Site is down which is a huge issue in itself.
• While working on KAPsNotes, if site goes down, we get stuck for 10-15 minutes. That was happening too frequently, around 4-5 times a day.

Server details and root cause

We are using Digital Ocean basic $6 droplet for hosting KAPsNotes. It comes with 1 GM RAM and 25 GB disk space. It seems fine as we currently (October 2022) have only 20-30 users visiting site everyday. Based on some reading, it should be sufficient to support at least 5000/day.

Upgrading droplet is always the option but it is not a correct solution. I wanted to know the root cause and fix it. So I went through some research to find the root cause. Here are some observations which help me find the root cause.

• I connected the DO support, which is very good and replied within hours. The reason they told is, in case some SQL query is taking too much memory, there is a process which kills MySQL process to protect whole Droplet going down.
• I verified that, whenever server was down, actually MySQL server was restarted at that time.
• However, that was not very helpful, I still didn’t knew which query is causing MySQL taking so much memory. I had no idea as there are quite a few plug-ins installed.
• One solution proposed on the internet was to log slow query. However, I was not able to do that, MySQL was simply not starting if I made slow query log changed in my.cnf file.
• While checking server, I figured out that there is no SWAP partition available. It was suggested by the output of htop command. This seems a point, which could give immediate result.

What is Swap?

Most of us must know that Random Access Memory (RAM) is the temporary memory. It is the memory for the running programs/process to store data needed for running the programs.

However, most modern computers operating systems run lot of programs in the background and if we do not have sufficient memory (32 GB or more), our memory gets filled quickly.

If system runs out of memory, most Unix systems have Swap memory, which is used to move from RAM to Swap. Thus, at very high level, just to understand, we can say Swap is the extension of RAM on Unix systems. However, please note, at the end Swap partition is part of the hard-disk and not as fast as RAM.

This can be extremely useful on the systems with lower memory. For example basic Droplets from Digital Ocean which starts with memory limit of 1 GB and also the server for KAPsNotes (in October 2022).

Creating Swap file

Run the following command

dd if=/dev/zero of=/swapfile bs=1G count=2

dd is a command-line utility for Unix and Unix-like operating systems whose primary purpose is to convert and copy files. It take following options:

• if: Input file
• of: Output file
• bs: Block zise
• count: Count/multiplier of blocks

/dev/zero

In input file, we used /dev/zero. From Wikipedia, /dev/zero is a special file in Unix-like operating systems that provides as many null characters (ASCII NUL, 0x00) as are read from it. One of the typical uses is to provide a character stream for initializing data storage.

In output file, we are creating a new file, /swapfile, which act as our Swap space

In bs and count, we defined we want 2 blocks of 1 GB, that is 2 GB as our swap storage. With this command, we have a file ready to be used as Swap, let’s create it now. Please check the section Swap size at the end for the recommendation of the size of Swap you should have.

Activating Swap

sudo mkswap /swapfile

This command will make the file ‘/swapfile‘ usable as Swap.

sudo swapon /swapfile

And this second command will activate the Swap. Now open the /etc/fstab file in your favourite editor and add the following line at the end

/swapfile      none    swap    sw      0       0

After editing your fstab, you may run htop or top command to confirm your Swap is created, configured and is in use.

MySQL configuration

Now we have Swap enabled, we can give some more RAM to our MySQL. Open your my.cnf file (generally located at /etc/mysql/my.cnf) and add following line under [mysqld] section

[mysqld]
innodb_buffer_pool_size=64M

Swap size

The general recommendation is, if you have RAM of 2 GB or less, your Swap file size should be two times your RAM (RAM * 2). For example, if you have 1 GB RAM, you must create a 2 GB Swap file.

If your RAM is more than 2 GB, your Swap file size should be 2 GB more than your RAM (RAM + 2). For example, if you have 4 GB RAM, it is recommended to have a 6 GB Swap.

However, this is a general recommendation that fit most of the cases.

This fixes the issue of WordPress’ MySQL going down frequently on DO basic droplet.

This fixed the issue on my DO droplet. If you face any other issue, please add a comment below or get in touch with me at twitter @kapsnotes

Final thoughts

Above solution fixed the issue of site (MySQL server) going down frequently. If you are facing same issue, trying above solution will probably fix it.

If it fix the problem for you, please let us know in the comments below.