Server architecture for a read-heavy website

Question

The site is a custom written PHP application with a nearly 3NF (highly relational, many foreign keys) Mysql database. This website is about 90% read / 10% write.

Currently, we are on a VPS with 2Gig's of memory and with around 1000 unique visitors per day, and we are finding that this host is failing.

For several reasons, website stability being only one reason (other reasons include shoddy customer support, but that's neither here nor there), we are moving hosts. The top three priorities for this business when selecting a server option is:

1. Stability
2. Price
3. Scalability

I am considering an EC2 deployment. One option that looks extremely attractive to me is starting with two micro EBS instances of a MySQL server and Apache server. While this puts my total memory below my current VPS' 2G, this seems like a really good way to separate database and web serving needs. Would I be better with a higher memory single instance? What other server configurations are out there that I should consider before this?

On a side note, there are many possible avenues of optimization. Some pages (the heavily hit ones) hit 1000+ database queries per page. MySQL is also not as fully tuned as it could be. APC is installed but not utilized in any code.

Answer 1

If you have a read-intensive site that's failing, you need to look at your database caching strategy. Before doing anything with the hardware, examine your MySQL cache usage in detail and see if you can tune it better. It's also worth checking that all your joins are using indexes. 1000 unique visitors per day is very low traffic, and you really should be able to handle it from your current server.

If you do need more hardware, you will almost certainly find that you need more RAM so that you can have a bigger database cache, and that you will do better with a single server with more RAM rather than two servers with less RAM. On Amazon, I'd look at a single Large instance, which would nearly quadruple your memory -- although it would cost a lot more than two micro instances, 34 cents per hour instead of 2 x 2 cents per hour.

Answer 2

Personally I wouldn't ever deploy any database (no matter the platform) on a server with 2 Gigs of RAM (much less the 613 Megs that a micro instance comes with). When it comes to databases more RAM is better. You wan to get as much of the database into memory as possible that you aren't waiting for disk.

Answer 3

A load of 1000 unique visitors a day sounds like hardly any load. Although for performance you might not want to look at unique visitors but page views. But just 1000 unique visitors is like less than 1 visitor a minute. Your server has hardly any concurrency and is idle most of the time.

That means you can test your application on any PC or laptop and see how it performs. If you have no performance problems during this single user test, it should run on your server.

In general terms, I would say that a simple web based CRUD application should run on a webserver with 2 GB. If the database is only 100MB I think it should fly. But you might want to check if you setup your my.cnf settings correctly, especially if you use INODB (although a smallish 100MB db should easily fit in the standard file system cache).

But I am not a fan of shared hosting and VPS, as you are never sure what you get:

• Not always the memory specified, is the memory guaranteed (overselling)
• Sharing resources means you depend on the other loads that are on your shared server
• With a VPS your main memory is not always used as a file system cache (bye bye performance!)
• Some VPS solutions are very descent in scheduling processing power, sharing IO is a different story (bye bye performance).
• Although they call it a SAN, it might just be a NAS (higher latencies, thus bye bye performance)
• Although SAN sounds good, you have no idea with what kind of workload it has to handle in total, thus it could be much slower than expected.

Thus if you want reliable performance, you can better go for a dedicated host IMO.