Monday, July 15, 2019

MySQL database server on a VPS: can it handle the spike in traffic I'm anticipating?

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?

No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...