0

I have VPS service with Hostican.com. 2.7GHz CPU, 2GB RAM (level 5, if you look at the site). I have a smartphone app that makes requests to PHP forms, which query the MySQL DB. I'm anticipating a huge surge of activity in a single day, due to an upcoming advertising campaign. Success of the campaign is pretty critical; I can't afford for the server to go down when the surge of activity hits.

Information:

  • I'm anticipating about 20,000 new accounts in a single day. To create an account takes 2 SELECTs, 2 INSERTs, and then 11 GRANT statements in a START/COMMIT block. The SELECTs and INSERTs are simple, with no joins.
  • Once a user has created a new account, they can perform a variety of functions, all of which access a PHP form and query the database. Most of these forms do 1 or 2 simple queries (i.e. a SELECT and an INSERT) and return a result. Some may do as many as 4, but none of the queries are complex. Most are single table, with a few two-table JOINs.
  • Data fields are all relatively small; no query is returning more than a few rows of text.
  • All tables are InnoDB. I've indexed fields wherever I thought it would be helpful.
  • All requests to the server are HTTPS.

The bulk of the traffic will probably be from account creation.

Can I rely on this VPS setup to handle that sort of traffic?

Also, assuming this single day is a success, I may be fielding this many account requests (and an appropriately increasing number of requests to the other PHP forms) every day thereafter. Potentially 20K new accounts each day. I don't imagine the VPS I have can handle that for very long, but I'm not sure roughly at what point it wouldn't be handle it, and what I should shoot for in terms of an upgrade.

Update

I'm looking into MySQL and PHP optimizations, and my options for upgrading to a dedicated server. I'd certainly be willing to spend the money on a dedicated server if that's what it would take (and it sounds like that's going to be the case, sooner or later).

Two points I forgot about and wanted to add:

  • Does the fact that all HTTP REQUESTS to the server use SSL add much processing overhead? From what I've read, it doesn't sound like I should worry about it too much...
  • n important point that I forgot to mention: The application that the server is talking to is a messaging application, where incoming messages are stored in the DB until users retrieve them (hence the write-heavy SQL statements). Outgoing messages are through email; so, I also have an email server running (exim, I believe, though I can change it) and sending emails very frequently. Once the campaign is in full swing, emails probably will be going out 1 per second or more. Generally speaking, is this going to add a lot of processing/memory overhead?

So in all, the server uses Apache, MySQL, PHP, SSL, and exim (or another email server) to serve this smartphone application. Are the SSL and email going to be much of a concern, compared to PHP and MySQL?

1
  • Automated testing is your friend.
    – Paul
    Apr 3, 2011 at 7:37

1 Answer 1

3

I'll start by saying that your best bet will be to benchmark the entire application (MySQL/PHP, not just one or the other) and see what the capabilities of your server is. This will probably involve some custom scripts that simulate your user's actions. This will give you a pretty good answer as to what amount of traffic you can serve and whether a more powerful server is needed.

Your queries average out to around 4 queries/sec which is pretty low so I would guess that even a minimal MySQL setup should be able to handle it. As an example, I have a low-mid end dedicated MySQL server averaging 100 queries/sec (MediaWiki) with the CPU 95% idle. My application, however, is read heavy while yours appears to be write heavy which changes things considerably.

It may also be dangerous to assume 20k accounts will be spread out over 24 hours. You may get most of the traffic in the first hour or two in which case you may need to serve 100s of queries/sec. Unless you happen to have a backup database already setup once your existing server becomes overloaded it will be too late to do anything.

Another thing to think about is not the performance of MySQL but of the PHP scripts. From experience using PHP for websites I would worry about this a lot more than 4 queries/sec. Even small PHP scripts can eat up a CPU in a hurry.

Ask yourself how much is this campaign worth and how much can you afford to lose if your database goes down 1 hour after it starts? You can get a decent dedicated server for a month for $100. It may be even worth it to get two (or more) servers, one as a backup ready to go in case the first server gets overloaded or goes down unexpectedly.

Some questions you can ask yourself:

  • What is the cost of failure?
  • How much am I willing/able to spend to prevent failure?
  • What happens if we get more, or a lot more, hits than anticipated?
  • What is the measured capacity of my servers in hits/sec, clients/sec?
  • What is the current bottleneck of my server/application architecture?

Edit: Answer Updated Question

I don't have any experience with using SSL/email at this scale. The additional processes will definitely consume CPU and, more importantly, RAM. Since this application is relatively complex it underlines the need to do some form of benchmarking. It is much harder to guess performance when you're running multiple server processes on a single server as the inter-process interaction gets very complex and performance issues more subtle. Without testing of some sort you really don't know if your server can handle 10 users or 10,000.

The fact that you have a very short window where traffic will increase quickly makes benchmarking even more important. In a situation where your traffic would steadily increase over weeks/months you can get away with a low-end server to start and slowly upgrade/scale as needed.

1
  • edit: didn't mean to leave a comment
    – JustinP
    Apr 2, 2011 at 14:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .