Vinícius Rezende

I had my databases down twice in a month with the following error message:

[ERROR] mysqld: Out of memory

Compared to shared hosting services that we used to have, the tiny and cheap cloud VPSs are amazing. I am a user and big fan of the service as I don’t have any personal projects that require AWS by now.

These servers are convenient and comfortable as you can customize, install and configure as you wish. When everything is to your liking you can take snapshots as backup or to create duplicates of your server.

The only problem is that some of the softwares needed to run a fully web server comes with predefined configuration files. They run fine on their own but you need to take in mind that your VPS have a limited RAM to work with. MySQL and MariaDB by default will allocate around 400MB, which is reasonable to a normal server, but not to a limited virtual instance running 24/7.

I will comment on the measures it took to get around this problem. As usual the following steps are for Ubuntu 14.04, your steps may vary depending on your distro and version. You may find a my.cnf over the internet that fits your server’s specifications.

Disable Performance Schema

In my opinion, performance schema only has a use if you or a DBA wants to monitor the database activity. To disable it edit the default MySQL configuration file, in this case /etc/mysql/my.cnf, and edit the following line under the [mysqld] block (if it doesn’t exist, create the line).

performance_schema = off

MyISAM tables

InnoDB tables have a huge impact in performance and memory usage, if you don’t use its resources you may consider to use MyISAM. To do so you need edit the following variables under the [mysqld] block of the /etc/mysql/my.cnf file:

default-storage-engine=MyISAM
default-tmp-storage-engine=MyISAM

If you don’t need InnoDB resources at all you can disable it adding the following line to your my.cnf:

skip-innodb

You may need to change your existing tables to MyISAM. The following ShellScript can do that for you.

#!/bin/bash

MYSQLCMD=mysql

for db in `echo show databases | $MYSQLCMD | grep -v Database`; do
        for table in `echo show tables | $MYSQLCMD $db | grep -v Tables_in_`; do
                TABLE_TYPE=`echo show create table $table | $MYSQLCMD $db | sed -e's/.*ENGINE=\([[:alnum:]\]\+\)[[:space:]].*/\1/'|grep -v 'Create Table'`
                if [ $TABLE_TYPE = "InnoDB" ] ; then
                        mysqldump $db $table > $db.$table.sql
                        echo "ALTER TABLE $table ENGINE = MyISAM" | $MYSQLCMD $db
                fi
        done
done

Swap

Swap isn’t recommended, at least by DigitalOcean that uses SSDs, but I use it as a fail-safe measure. The following commands will create a swapfile and use it as swap space in your machine, run as root:

fallocate -l 256M /swapfile
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile

To keep your changes permanent don’t forget to add the following line to your /etc/fstab file:

/swapfile   none    swap    sw    0   0

Conclusion

Cloud VPSs are amazing considering their cost, reliability and convenience, keep in mind that you will need to tweak one thing or other. If you need a more robust solution I recommend Amazon’s RDS or upgrading your instance plan.

Don’t forget to restart your MySQL/MariaDB service after any changes to the my.cnf file.

service mysql restart