MySQL on Low Memory VPS

Quick tip for those of you spinning up small droplets on Digital Ocean or instances elsewhere. In MysQL 5.6.*, Performance Shema is on by default. In previous version of MySQL, the defualt was off. This matters because performance schema allocates all of the RAM it requires at start up. On a Digital Ocean droplet with 1GB of RAM, that can amount to as much as 500KB or half your total allocation. As soon as you run a query with a large number of inserts or have some other usage spike, MySQL will fall over. If you don’t have swap allocated, your whole droplet will fall over and you will be sad.

Take heart, you can safely turn performance schema off. If you don’t know what it is, you don’t need it. You do this by editing your my.cnf to include:

[mysqld]
performance_schema=off

Then restart MySQL (on Ubuntu sudo service mysql restart). If you want to see the memory usage difference, watch top -u mysql before you turn it off and after.

If you use Laravel Forge, the server it provisions for you will be MySQL 5.6.*. I’m guessing a lot of users will run into things like ‘PDO exception mysql server has gone away’ and be stumped. Now, this isn’t a cure all for managing memory on a server with little memory allocated, but it will go a long way to helping you run your side project in Laravel or a few WordPress installs.

2 Comments

  • man, this is one of the most effective single tweaks I’ve found so far.
    thank you from the bottom of my hart!

  • Thanks with this solution. I was getting “The following exception is caused by a lack of memory or swap, or not having swap configured” when trying to install a Drupal module locally. This solved the problem. Thanks!

Leave a Comment

Your email address will not be published. Required fields are marked *