I’m running Ghost (a publishing software) version 3.14.0, self-hosted on a DigitalOcean droplet via Docker containers (ghost and mysql in separate containers). I’ve been experiencing sudden spikes in CPU usage that brings down the database, as shown in the graphs.

You can view all related logs and memory usage statistics here.

https://imgur.com/a/Mg8yrc4

You can see how the CPU spikes suddenly to 100% even though it usually barely uses 1%. I’ve narrowed down the problem to be caused by the MySQL container, bringing down the db.

It seems to be an out of memory error, but I don’t know why the droplet is using up so much memory. It has 1GB RAM, and from the statistics, it’s barely using 30-40% of that, yet the graphs show a consistent 90% RAM usage.

Not sure why the graphs show so much memory is used even though I’m only using at beast 40-50% memory. Should I add swap or some other thing?

Does anyone have any idea how to fix this issue? The server randomly crashes due to this (luckily, thanks to Docker, it restarts immediately). I can’t pinpoint the exact reason either - running top doesn’t show anything, and I can’t access the server through ssh during the flareup. Thank you!

1 comment
  • Now I’m thinking of adding swap to solve this problem somewhat, but the DigitalOcean guide for adding swap seems to discourage this. I don’t think I need to be upgrading to a higher tier based on my usage, so should I just add swap?

    Does swap affect anything if I’m running everything in Docker containers?

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
2 answers

Hi @chenningg,

Let’s start with your comment first. You should add swap but not more than 1 GB. If your server is reaching that swap it means either you have to upgrade or you have to find the source of this load and resolve it.

As for the load. Have you actually done any troubleshooting while this load has been experienced according to the graphs? I’m asking as this might be crucial to how you approach this problem. If you haven’t, it’s alright, I’ll post some things to check now.

Firstly, when there is load check how many connections you have to your server. To do that, you can try and use the netstat command. SSH to your droplet and type in

netstat -punta

It will post all connections to your server at the moment. If they seem a lot, I’ll recommend checking the current load with the tool htop, it will show you which process is generating the load. If it’s MySQL, then run the command

mysqladmin proc

It will show you how much queries you have at the moment that are being executed. It’s possible due to the high number of connections, your website generates too many queries which can’t be closed at time.

Another problem might be MySQL by itself. It’s possible your application’s queries need optimizing and are just taking too long to finish. Again this can be checked with

mysqladmin proc

or alternatively, you can add slow-query-log to your server and see the queries that are being executed there.

Once you know what’s the root problem, you should be able to proceed in that direction to resolve it.

Regards,
KDSys

  • Hi, thanks for the reply.

    Regarding the swap, I’ll try to add it after I do a backup.

    Unfortunately, I’m unable to check what’s wrong during the load itself, as I’m unable to SSH in. When the issue is occurring, the CPU usage spikes to 100% as well, making the droplet basically unusable (even for SSH and console use). It won’t even load.

    Additionally, I’m running MySQL in a Docker container. I’m not sure if the commands you listed above works in this scenario. I’m also running my server through a Cloudflare routing, so the load shouldn’t be that bad of an issue… I hope.

Hello, all

What you can also do is to use the MySQLTuner script.

The MySQLTuner is a script written in Perl and allows you to quickly test your MySQL configuration and it gives you suggestions for adjustments to increase performance and stability.

According to the official GitHub page, it supports 300 indicators for MySQL/MariaDB/Percona Server in this last version.

To run the script you could do the following:

  • SSH to your Droplet
  • Download the script:
wget http://mysqltuner.pl/ -O mysqltuner.pl
  • Then execute it:
perl mysqltuner.pl

The script would run multiple checks against your MySQL instance, all checks done by MySQLTuner are documented here.

Also as stated in the official documentation, it is still extremely important for you to fully understand each change you make to a MySQL database server. If you don’t understand portions of the script’s output, or if you don’t understand the recommendations, you should consult a knowledgeable DBA or system administrator that you trust.

As a good practice make sure to always test your changes on staging environments before implementing them on your production database.

On the same note, if you want to have a worry-free MySQL hosting and focus on your application, I would recommend trying out the DigitalOcean Managed Databases:

https://www.digitalocean.com/products/managed-databases-mysql/

This was mini tutorial was posted from bobbyiliev in this question in our community: https://www.digitalocean.com/community/questions/how-to-tweak-mysql-mariadb-configuration-for-increased-performance-and-stability

Hope that this helps!
Regards,
Alex

Submit an Answer