Moving website images to remote server vs moving database to remote server

Question

I have a wordpress website that has gotten too big for a single server. I need to decide whether I should host the database externally i.e. purchase another server for the DB or if i should host the images on a CDN.

Moving the DB externally is the quickest and easiest solution. It will lower I/O and RAM usage which will help a lot. On the other side moving all the images to another server (i am thinking about using AWS S3 bucket) will mean a lot less requests to the core server and I wont have to separate the site and DB. This is of course a highly complicated procedure since there are other community based tools on the site that allow people to upload images.

It would seems as though moving the images over to S3 would give the best benefit since there are 15+ images on most pages when you account for "recent posts" and stuff in the sidebar. However, since browser caching is enabled, this might not be as significant as it might initially seem. Due to the dynamic, user driven nature of this site, i am unable to cache DB queries since most contain a join to the users table where ID = session value. For this, i think that the DB may benefit best. The drawback of course being that moving the DB away from localhost means I need to make a request across the internet. It is not possible right now to store the DB in a cluster or any internal network. The DB will be on a remote server and i will use its public IP to communicate.

Generally speaking, for the average website, what will provide the best benefit if you can only choose 1. Moving all images to a remote location and serving them via a subdomain e.g. images.mysite.com or should i move the DB to its own dedicated server?

I currently have a dedicated standalone server with a 2TB disk, 8 core xeon @2.7ghz and 16GB ram. I have unlimited bandwidth on a 100mb connection. The cost of upgrading to something bigger is crazy money, i could more multiple of the same server i have now for the same price. It appears that apache and mysql are hogging most of the CPU, but apache serves all the images, so this is where my issue lies.

Answer 1

There's no reason that this has to be one way or the other. I would say to optimize both. If you have a large number of images, a CDN makes sense, regardless of what you do on the server. Also, even if that takes care of the problem, better database performance is helpful in general, so I would recommend tackling both options.

I really do love AWS tools. They are great for the money you pay, and they are pay only for what you use. Also, for connections when downloading and uploading backups, they have great performance. However, for the few times I have seen S3 buckets being used as a CDN, they didn't seem to perform as well as other CDN, so you may want to test out different CDN options. If you have a lot of user-uploaded images, then having some sort of plugin integration is a must.

For the database, I don't recommend having a separate database server over the internet if it's a slow connection. The slower your database connections, the more resources you're going to have tied up and the slower your pages will load. But that doesn't mean that you don't have any options. If you can't add another server, I would recommend adding an SSD drive. You didn't specify if your drive was SSD or not, but it sounds like it's not. MySQL can be very I/O intensive, and that's exactly what SSDs are good fore. You can mount a separate SSD drive that you use only for MySQL to boost your performance. I've made that change for many clients to watch it make a big impact.

Additionally, tune your MySQL installation. It's a commonly forgotten step that can yield big improvements. You can pick up the tool here:

https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl

Here's an article that might help use it:

https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/

It never hurts to check for excessive transient records in the WordPress options table, which can sometimes slow things down.

That all being said, don't assume that these are the problems. Test to see where your bottlenecks are. The above are just good, generic recommendations, but testing is necessary to know the problem. If you haven't already, install sysstat and become familiar with using it. That should give you more details on what your CPU is actually doing. Here's an article that might help:

http://www.thegeekstuff.com/2011/03/sar-examples/

I like to run sar by itself, no options, to get an idea of where my big usage has been. Here's a personal line I use to check usage for the week:

(sar -u; for d in {1..7} ; do sar -u -f /var/log/sa/sa$(date -d"-$d day" +%d);done)|awk '$4~/20[1-2][0-9]/{d=$4}$9~/[0-9]/{print d" "$0}'|sort -nrk8|sort -nrk10|awk -v t="50" 'BEGIN{n="\033[0m";c="\033[1;31m";w="\033[1;33m"}$10~/[0-9]/{us=$5+$7;io=$8;id=$10;if (us>io){ioc=n;if (us>t){usc=c}else{usc=w}}else{usc=n;if (io>t){ioc=c}else{ioc=w}}printf "%s\tUser/System: %s%4.2f%s\tI/O: %s%4.2f%s\tIdle: %4.2f\n",$1" "$2" "$3,usc,us,n,ioc,io,n,id}'|tail -10 

I divide the usage up by System/User and I/O because I find that in general, if it's the former, it's an issue better resolved by addressing the PHP scripts, and if it's the later, I tend to look at MySQL first. If it's just CPU bound, then I would focus on optimizing the scripts themselves, like maybe removing plugins, etc. If it's I/O, then I might check iotop to confirm the culprit. If it's purely RAM based (check with sar -q), then maybe add RAM, otherwise, and SSD can help. Of course, YMMV.

Answer 2

The first step will be to check the server to see what is utilising the most system resources by way of memory and CPU cycles. A MySQL database server depending on the number of connections can drain system resources so some things you can take...

1. Transfer Database to a Separate Server
   By doing this you will reduce the load on the server imparted by the database service. If possible with your provider attempt to go for a cloud-based database platform as it should be burstable to handle sudden but short lived spikes.

2. Transfer Images
   By transfering the images to another platform such as an S3 bucket you will reduce the number of connections to your server which in turn will help reduce system load overall as well.

Answer 3

What others said. Find out where your system resources are going. Then correct that problem. If you want to be on a sustainable budget, you can't blindly throw general resources at the problem.

It feels odd that a single WordPress site is bogging down this way. It could be a specific module or, as others have mentioned, excessive db calls, instead.

You reference "localhost" at one point. That should not be part of any public web service--it's something that should only come up when using a sandbox to which one has logged in directly for development purposes. I presume you just mean that references to images currently assume that all are present on the same server?

Answer 4

Just something to note*

If you plan out which equipment and/or cloud recources you need for your project on a excel sheet, and send it off to AWS. They WILL match your rates and likely drop them lower to match your "cost effective" plan. All it takes is a email and a phone call. Plus this way, you can get dynamically growing recources for your project.

Answer 5

How many requests per second do you see? The hardware is pretty good and if it's hosting only a single WordPress site, it should be able to handle a lot of traffic if everything is set up correctly, before thinking about scaling out to more servers.

You mentioned apache takes a lot of resources. Maybe consider switching to nginx. From my experience, it reduces resource usage for static file requests significantly and is easier to configure for high loads.

You should also check what db queries are using the most resources.