Flash/PHP/MySQL bottleneck

[der_dinosaurier@yahoo.de]

Dear Forum:I am developing a Flash game for two players which must present the illusion of sync. Both Flash pages call a PHP script, which UPDATES the player's data (position, score, etc) and then reads (SELECT) the opponent's data back to the respective players. Player 1 "sees" Player 2, and vice-versa. The problem is that, though both players are able to connect, only one actually updates its data in MySQL. Is MySQL bottlenecking? Is there some maximum allowed number of queries per user per second that I have neglected to consider? This UPDATE/SELECT cycle is executed by the main game loop in Flash; am I choking the DB with so many calls?Many thanks!

[justsomeguy]

There may be too many calls, depending how often you send requests. If you don't have error logging enabled for the PHP script, you should. All PHP errors should go to a log that you can check later. You can set that up like this, to send all errors to a file called error.log in the same directory as the script:

error_reporting(E_ALL);ini_set('error_log', dirname(__FILE__) . DIRECTORY_SEPARATOR . 'error.log');ini_set('html_errors', 0);ini_set('log_errors', 1);ini_set('display_errors', 0);

Additionally, you should also be checking for MySQL errors if any query fails. You can use the error_log function to send your own messages to the error log, so check for MySQL errors and send them to the error log if there are any.It may be a table locking issue. The MyISAM storage engine implements table locking, so any time an operation is performed on a table it will lock the entire table until it's finished. The InnoDB engine uses row locking instead, so it only locks the rows it's working on instead of the entire table.

[der_dinosaurier@yahoo.de]

Hi Justsomeguy,Thanks for writing me back so quickly. My PHP code has conditionals for every aspect of failure, and returns custom error codes to Flash where I would see them in the IDE. My current suspicion is that whichever Flash player page (Player 1 or Player 2) enters the game loop first locks that table or row with a query-bombardment. I don't know if you know ActionScript, but my query engine is in an EnterFrame handler function which fires off queries at about 30/sec. By the time the late-comer signs on, I think he or she is simply shut out. I'll try implementing a sort of "loop token", whereby every 2nd or 3rd loop iteration abstains from calling the script, hopefully allowing the opponent a chance to reach the DB. And I guess I would have tested this hypothesis sooner, except I was surprised to consider that MySQL could be so easily jammed up. Isn't it used to book airline reservations and whatnot, taking, maybe 30,000 transactions/second? How could my little game cause it so much trouble?Do you think this is the problem, or am I mis-speculating?Thanks again for your correspondence.

[justsomeguy]

My PHP code has conditionals for every aspect of failure, and returns custom error codes to Flash where I would see them in the IDE.

Does that mean you're not going to use an error log also? You may be surprised what shows up there.

My current suspicion

The idea with the error logging is to remove suspicion and get answers. If MySQL is timing out waiting for a lock, it's going to send an error message which will show up in the log. That how I found out about MyISAM locking tables, the first time I sent my application live it immediately became apparent that there were a lot of things happening a lot slower than they should, and the error log and a little research told me why. Even so, 30 queries per user per second is a lot. Each of those has the overhead of an HTTP connection going to and from the server. With any ajax application like a chat application I always try to get people to send no more than one request every two seconds. The combination of the user agent's request limit, the web server's connection limit, and the database server's connection limit makes a lot of requests in a small time problematic. HTTP wasn't designed as a real-time protocol, it's a request/response model with one connection per request, not a single connection which stays open where you can send and receive data at any time.

Isn't it used to book airline reservations and whatnot, taking, maybe 30,000 transactions/second? How could my little game cause it so much trouble?

Because you're probably using a single machine to handle both the web server and database server, instead of a cluster of web servers communicating with a cluster of database servers. You can throw more hardware at this to solve the problem by implementing a load-balancing database cluster, but if that's not an option then you need to find a way to use the database more efficiently. Depending on your queries, using database indexes can also drastically speed things up. If you're doing a lot of select queries with where conditions on various fields, add indexes to cover those fields.

[der_dinosaurier@yahoo.de]

Thanks, Justsomeguy. You've given me a lot to look into.

[der_dinosaurier@yahoo.de]

Hi Justsomeguy,I've added your error checking code and enabled mysql error reporting, too, and to my surprise, nothing showed up in an error log. I also implemented the query token I'd mentioned and set it as high as 60, which, at 30 frames/second in Flash, will abstain from querying until every 2nd second. The database still seems to be locked to whomever signs in later. I tried changing the engine type from MyISAM to ARCHIVE ("InnoDB" was not an option on my host's installation), and that seems to have locked the entire table even though the MySQL specs say that "ARCHIVE" only locks the row (http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html). You seem to understand what I am trying to develop with this project, and I understand that I am attempting to use the protocol in a way for which it was never intended. But I am curious to see how close a reproduction of real time this setup can achieve, and I accept that even the best results will be "stuttered" by the protocol exchanges. Can you recommend a configuration for this goal? There is no deadline to miss, no job to ruin, and no end-client to worry about. I really just want to see how much flex this rig has. Many thanks.

[justsomeguy]

I would say to remove Flash as a variable and do a test with Javascript. You can run ajax requests in a loop and gather statistics about each one, like how long the response took to come back, etc, and if any failed.Even with Flash, you should be able to run this in Firefox with the Firebug extension, and keep an eye on the Net tab. That will show you all of the requests going out. Even requests originating in Flash will show up there. You can use the Net tab as a log to look at how often the requests go out, how long it takes each to complete, if they complete, how many are pending, etc. That will be a good indication of whether or not you're sending requests too fast for the server to process. It may also be useful to run several instances of Firefox with Firebug at the same time and monitor each one.You may also want to look into using persistent database connections, which don't close after the script ends. You can either use mysql_pconnect with the mysql extension, or prepend "p:" to the host name with the mysqli extension.