Online game / 16,000+ users / DB woes!

Anon

We have written an online web based resource management game that

currently has in excess of 16,000 players and as many as 1,000+ players
online at any given time. We are having some serious performance issues
at this point (we COULD have more than 1,000 players but we had to limit
it because of the performance). Just about everything the user does goes
through this logic:

1) Open the database.
2) Perform a SELECT on the database to verify the player identity.
3) Close the database
4) Depending on the action the player requested:
a) Open the database
b) Perform a SELECT/UPDATE/INSERT depending on the players action.
c) Close the database.
5) Exit

Now, the questions are:

1) Does PHP/Pear DB cache the opening/closing of databases? Is there a
performance hit by having multiple database opens/closes?

2) Will PHP/Pear DB close an open database connection when my script exits
if I forget to?

3) Is there a way to have a persistant database connection for each user?
What would happen if the player just closed their browser instead of
logging off? Would the connection stay open occupying db handles?

4) Is there a way to have PHP open a bunch of persistant db connections
and have all of the scripts (players) use the master db connections?
Would this be more of a performance help, or a performance hit?

5) We are currently using MySql, but as we've built the game using Pear DB
we have the ability to easily switch to Postgresql. Which one performs
better under loads like this? What are the benefits/restrictions of
each?

6) Any suggestions on a way to optimize this scenario without compromising security?

7) Kind of off the topic, but we're using templates for everything (Xtemplate
to be specific, I love it!). Is there anyway to PHP process a template?
For example: We load the template, parse it, and then we want to have
PHP interpret the results of our template... 🙂

kparker

For starters, if your description really is describing a single http transaction, then you can get a big boost by changing this:

  1) Open the database.
  2) Perform a SELECT on the database to verify the player identity.
  3) Close the database
  4) Depending on the action the player requested:
    a) Open the database
    b) Perform a SELECT/UPDATE/INSERT
    c) Close the database.
  5) Exit

to this:

  1) Open the database.
  2) Perform a SELECT on the database to verify the player identity.
  3) Depending on the action the player requested:
    a) Perform a SELECT/UPDATE/INSERT
  4) Close the database.
  5) Exit

I.e. there's never any reason to close the database and reopen it during the execution of one script.

As for some of the rest:

2) Will PHP/Pear DB close an open database connection if I forget to

Yes.

3) Is there a way to have a persistant database connection for each user?

No, due to the stateless nature of http. Persistent connections can still help, but they help by keeping the connection open for the same httpd child instance, and thereby reducing overhead in general. There's no way to know or control what connections that a given instance of httpd is going to handle.

4) Is there a way to have PHP open a bunch of persistant db connections
and have all of the scripts (players) use the master db connections?

That's sort of what happens anyway with persistent connections and httpd child processes. You can tell apache how many children to start with, how soon to spawn more, and how many requests each child should service before it dies. Upping any of these numbers means fewer db opens/closes per db transaction

5) We are currently using MySql... [can] ...switch to Postgresql.
Which one performs better under loads like this?

You can't know for sure without benchmarking it both ways with your actual load, but Postgresl is know to perform better doing updates for multiple user than does MySql. Another thing to think about here is maybe your disk is simply too slow, or the server needs more RAM. Lots of possible places to tweak things...

6) Any suggestions on a way to optimize this scenario without
compromising security?

Retrieving session variables might be faster than querying the database, so you might try querying the database for user authentication only the first time, and store the results in session variables.