High Traffic Database Access

Anon

Hi all,

I am developing a simple online multiple quiz that runs during a specific hourlong time period.

There is a login system that users will be able to setup anytime before the quiz occurs. I will be keeping record of each user's answers, and would like live stats of the current best score for having completed the quiz, as well as current stats for each question's answers.

The catch here is that the traffic is going to be HUGE. We expect 20,000 users all trying to login and start the quiz at the same time.

The actual pages of the quiz will be extremely simple, with almost no graphics, and we will be having around 10 questions per page. Furthermore, the "bandwidth" itself will be virtually unlimited.

Which finally brings me to my question -- I would like to do this in PHP/MySQL...is this unrealistic? It's mainly that first minute when lots of people are trying to login, when the problems will occur.

Am I crazy to imagine being able to do this on a single server (to keep things simple), with a single mySQL database.
If I need to use more than one machine, how would you recommend doing it?

I am open to all suggestions, requests for clarifications, etc, etc.

Thanks for any help,
michael

smarlowe

Well, if there's gonna be 20,000 people accessing this thing at once, you want to minimize ALL database interaction to an absolute minimum. I.e. don't build pages from the database, but harvest them every couple of minutes to a static page that the users see (i.e. one harvest -> 2,000 reads of static page code)

You may or may not be able to run this on one box. For something this big, you're gonna HAVE to write test scripts to test the load handling of the system before going live. apache bench (ab) and wget are nice testing tools you can use to build scripts to abuse your server with.

I'm guessing that you'll be doing a lot of writing to the database, and therefore, mySQL is probably not a great choice, unless the latest versions have gotten a LOT better.

You need a database with row level locking and very fast performance to pull this off, and while I'm a big fan of postgresql, I don't think it's up to the task either really. I'd test it to see if it could do it, but am willing to bet that even on big iron, postgresql 7.1beta3 would have problems with that big a load. (i.e. response times could be really long, like 30 seconds or something).

Look at some of the big commercial dbs, like sybase or informix or DB2 for this kind of load.

Lastly, if it's possible to "partition" this application to several database servers for performance and then aggregate your data into one server for output, you might be able to keep up.

Anon

You wrote: "It's mainly that first minute when lots of people are trying to login, when the problems will occur. "

Careful! Remember that the http protocol is connectionless -- you'll have to do database lookups each time your each of your users posts a page. I don't think you'll actually have extra processing during the first minute -- I think you'll have a tremendously high processing load throughout your hour.

You NEED to load-balance this across multiple servers, to handle the processing load. If this quiz is time-limited you might really annoy your students if it's taking a long time to post stuff.

Load-balancing for this isn't hard, conceptually. Set up a bunch of servers like this:

www.quiz.com
www1.quiz.com
www2.quiz.com
www3.quiz.com
and so forth.

Put a script on www.quiz.com which randomly chooses one of the other servers, and redirects each incoming user (via a Location: HTTP directive) to one of the other servers. If you can figure out something more intelligent than pure random redirection, that's even better.

Thereafter, run each user's session on just one machine. Run a separate mySQL or postgreSQL instance on each machine, and gather that user's anwers locally to the machine. That way your database won't be a bottleneck.

Make sure you use mod_php/apache, and make sure you use persistent database connections.

smarlowe

I don't mean log in in the usual sense. If you're running a program where 10,000 people show up between 9:58 and 10:02, they are, in effect, logging in. They're either creating a temporary account/object in a database, or accessing their personal record for viewing. For apache, and many other web servers this can be a problem case if the machines aren't configured properly.

I'd say this is a candidate for 1+N machines. 1 machine to run the database if possible, and N machines to run the huge number of processes/tasks/threads needed to handle all those people that will be clicking at the same time.

First, off, build your apache/php boxes lean, without a lot of crapola running on them, and give them lots of memory. A typical Dual III box will not hit 50% CPU usage until it has well over 300 or so active database connections going at once.

You might want to look at disabling keep alive and configuring apache for a max number on each box that keeps it from starving the database server.

If you can go up to a Quad PIII Xeon or Dual AMD TBird, or multiway SGI/IBM/HP/Sun iron, then it make make more sense for long term maintainability to put it on one very hardy box. Stay away from frills, go for memory and I/O bandwidth appropriately.

With apache configured to NOT have keep alive on, it should allow everyone to get a daemon almost all the time, since most people only click every few seconds.

Have you (the original author) modeled your user actions over time, average time between page views and such? That's really necessary to see if you can handle the load on one machine. If you click a page every three seconds or every twenty seconds, that's a big difference.

[deleted]

20k in one minute, that's 333 connections/queries per second.
Can MySQL survive that kind of beating?

More realistically, will all 20k come in the first minute?

I doubt it.
I'd at least split up the db server and the webserver.
and fit both machine with MEMORY and lots of it. Did I mention you'd need to fit lots of memory?

The main thing here is: experiment. Setup a few machines that can simulate 20k users (go for 30k to be safe) and beat your machine repeatedly to see if it holds out, and check what is used most. How much CPU usage, how much memory, HD activity, network utilization. (your bandwidth may be "unlimited" but that doesn't mean your server can fill the bandwidth. Sending data takes CPU cycles :-)

Oh, and fit your machines with some extra memory.

szii28

If you DO go with MySQL, make sure you get the .23 release, a
2.4 kernel, and either thttpd or Apache with SGI's performance
patches. (www.sgi.com)

-Szii