The first thing to note would be that DigitalOcean wouldn’t be directly responsible for MySQL taking a dive on you as you’re in full control of your Droplet (i.e. VPS), so configuration, optimization, and so forth are 100% completely up to you. They do not provide managed services, or provide support for issues unless it’s truly server/hardware/network related.
So if the SSD is taking a drive, and it’s not do to your own I/O, they can help there. If network speeds are bad, skipping hops, dropping etc - they can help you there. But, if MySQL isn’t working for you, it falls under “third-party” software, and you need to be able to manage it or have a DBA do it for you.
That being said, MySQL won’t just crash without a valid reason. So the first thing you’d need to do is check your logs and see if they can give you some insight. You can also use a tool called
MySQL Tuner that you run as a
root user or a
sudo user with root privileges.
Simply log-in via SSH and:
To run MySQL Tuner, simply:
It’ll take about 3-4 seconds to run and the output will give you an idea of how your current installation is performing when it comes to reads & writes, slow queries, key buffer, query cache, temp tables & so forth.
At the bottom it’ll give you some general recommendations and variables that it suggests tuning. Keep in mind, this is not a magic fix-all tool. It provides suggestions to help, that’s it. It does nothing else for you and will not make changes to MySQL.
General suggestions would be to work with both PHP & MySQL at the same time as PHP is what’s passing data to MySQL, which is then choosing to store it. When PHP is called, it’s going to do one of two things, call on a cached set of data or query the database. If you’re not using Object Caching and/or Page Caching, you’re going to hit MySQL every single time you display a page.
So I’d look at a few plugins, such as:
W3TC is probably one of the most recommended but it can be overkill and time-consuming if you do not like a lot of options. WPSC is a bit more simple.
Beyond caching with WordPress, you should look to PHP for caching using OPCache which is included in newer versions of PHP by default (if you’re hitting repo’s i.e yum install php or apt-get install php etc). Enabling that forces PHP to turn on it’s own object cache.
A basic configuration for OPCache would look something like this (this goes inside of your php.ini file):
You can find your PHP ini file by doing a
whereis php at the CLI or by creating a PHP info page using:
You can stick that in any web-accessible directory and access it via browser to find out all there is to know about your PHP installation.
In regards to MySQL, enabling the query cache at the server level is something I would consider as well, though the query cache itself isn’t going to be a life-saver. You will also need to work with other variables such as the key, join, read and sort buffer, table cache, thread cache, max connections, etc.
If query cache is not enabled, you can enable it by adding the following to your
query_cache_limit = 512K
query_cache_size = 16M
query_cache_type = 1
That’ll get you started with query caching and what that translates in to is:
1). Don’t cache anything smaller that 512K
2). The maximum size of the cache is 16M
3). Query Cache is Enabled.
All that being said, when it comes to configuration, optimization, security etc - a VPS on an unmanaged provider probably is not the best option unless you know how to do it all yourself, or have someone on-hand to do it for you. It’s hard work sometimes and the images that most providers provide as “one-click” installations are just that, “one-click” installations. You still need to be able to manage the server on your own. They just shorten the amount of time it takes to get off the ground and on to a working installation.
If options are on the table, I would personally ditch Apache altogether, switch to NGINX, PHP-FPM and MariaDB (a fork of MySQL with better performance). Apache is a resource hog, whereas NGINX is relatively light and simple.