Being somewhat unaware of the capacity of MySQL I pose this inquery: Is MySQL fast enough to be used as a server for a multiplayer flash game. Out of sheer laziness I would like to have my flash client application query the SQL server every half a second or so to acquire information about other players (i.e. their position). If I were to have one thousand players connected to the server simeltaniously would MySQL handle the 2000 queries per second load? I seriously doubt that it will... but then again my laziness is overwhelming.

Its fast enough to host some of yahoo's web services and google's adwords. I can probably do what you want.

If you run into performance problems with your database check out memcached.

It can be your friend.

Cheers
Chris

It could happen, if the query runs in RAM. You'd probably have to turn off synchronous transaction flush, though, as 2000 writes per second is a bit too much for PC-type MySQL servers. Thus, if the server crashed, you wouldn't recover a "consistent" database state.

You should do a little math on the networking bandwidth needed for those 1,000 users, though :-)

So which would be the most advantageous solution?

1. Flash client w/ MySQL --> PHP Scripts Performing Server-Side logic
2. Flash client w/ Java/C++ Server Performing Database Management and Server-Side Logic

Obviously going with PHP and a generic web server will save you a good deal of development time. However a custom made C++ server would certainly be more efficient. It really comes down to how optimized you want your server to be? Let me say this: at 1k players your first concern should be bandwidth availability and cost – getting extra hardware for a moderately inefficient server is not a big pocket stretcher.

Lets take this one step at a time:

Quote:

Out of sheer laziness I would like to have my flash client application query the SQL server every half a second or so to acquire information about other players (i.e. their position). If I were to have one thousand players connected to the server simeltaniously would MySQL handle the 2000 queries per second load?

This is what we scientists call batshit dumbfuck crazy. A database is not your heap or stack. Databases, even poorly designed ones, are not remotely intended or optimized for the purpose you have planned (which is to act as a real time data store). While there are complications that make this not a 100% accurate statement, I'm going to go out on a limb and say: "Neither MySQL, nor any other database, can handle your 2000 batshit dumbfuck crazy queries per second".

To use a database to store the real time positions of players mean you need to be updating those positions in real time too. Even though I could recommend server setups that would handle 2000 simple read queries per second, adding 1000 write operations (that directly conflict and thus block those 2000 read queries) per second would require a cluster, and would still likely melt the web server acting a conduit for all this. Most online games run only about 500-2000 people per server, and that's without massive database overhead.

I get the feeling that the reason you are trying to use a database, instead of something sane like an actual server program running on the machine with the data on the heap, is that you think you can run your 1000 player game on some a or relatively low cost LAMP hosting package instead of having to write your own server and find a dedicated server host. Sorry, MySQL, or any other database, does not grant speed from nothing. You'll find all such hosts pack many websites (sometimes hundreds) onto the same server. If you overtax the CPU for your site by trying to use it for real time operations, your host will quickly kick you off, paid customer or not. To follow that up, you'll get kicked off before you even pass the 100 player mark - 100 queries per second average is a big chunk of traffic - that's about the same as 8 million page hits per day. And to follow that up, you'll find that they have every right to because CPU usage is included in their terms of service.

Quote:

1. Flash client w/ MySQL --> PHP Scripts Performing Server-Side logic

This won't work for a real time game, period. Even HTTP/1.1 persistent connections (which I'm not sure flash will actually utilize) won't make up for the massive overhead created by such a Frankenstein setup. Additionally such an interface would make cheating (by creating a bot client) very, very easy. Attempts to curtail this would only add to the server load.

Quote:

Is MySQL fast...

This is a huge question, and depends heavily on "what for" and "compared to what". In your case for example the "compared to what" has already been covered in detail and is the biggest factor in whether MySQL is "fast" for you.

MySQL has two primary table types, the default MyISAM and optional InnoDB (which is now controlled by Oracle). When people talk about MySQL being fast, they are normally basing this off MyISAM performance; in fact they are basing it off MyISAM performance in a very specific situation.

MyISAM performs well when asked to do simple read operations (such as SELECT [field names] FROM [table] WHERE [simple field compared to constant condition or neighboring simple field] ORDER BY [field list]) while few or no write operations are taking place, on relatively small (a few thousand rows) tables. The reason for this is that MyISAM is basically a flat file system warped and hacked until it begins to look like a SQL relational database, in much the same way you can use a knife and glue to make a dead Gorilla resemble a horse. The major downfalls of this approach is that MyISAM does not run complex queries well (until version 4 it couldn't even do a UNION, and features like complex joins and sub queries have only recently been introduced) as the underlying structure was never designed for relational database operations, and hacking it on is well, a hack job at best. Additional to this is the way MySQL locks records. When you write to a database, you need to be sure that you don't affect read operations being done at the same time (say you ran an update that changed the date on all records, at the same time you where querying all records. Half would have the old value, half would have the new). MyISAM, as we've discussed, is not really designed the way a proper database is, leaving record locking, like most other advanced operations, as an afterthought to be hacked on. The result is table level locking (the entire table cannot be read from while a write is in progress), the worst type of lock known to DBAs. In your situation, even if you got those 2000 reads to run fast (which could be done without too much difficulty if you ignored the interface concerns), as soon as you introduced the writes it would kill the server. Given the way locks must be established you'd end up dealing with an exponential slowdown for every write after a certain trivial number of simultaneous write attempt.

InnoDB is distinctively slower then MyISAM, but exists for a very good reason. In the database world there is a core concept called ACID (Atomicity, Consistency, Isolation, and Durability). These are basically a set of requirements that ensure that data is properly stored, read and modified (we skimmed this with the concept of locks). Simply put, in the world of real databases, data integrity is god (what good is a database if it loses/corrupts your bank transaction or newegg purchase on a whim). The problem is that MyISAM isn't remotely ACID (heck, MySQL has a habit of destroying data through silent modification before it even gets to the table!). While storing your blog in a MyISAM table is perfectly fine (in fact I've got a rusty hard drive that regularly catches fire which is "perfectly fine" for storing most "blogs"), storing anything you can't afford to lose in one is just criminally negligent. So you end up with a trade off: If you want fast performance for reading off mostly static, unimportant data, MyISAM is the key. If you need a MySQL compatible table that supports data integrity, you'll need to give up some performance to get it (off course this doesn't affect the zealots much – they tend to combine the best traits of both into a single argument about MySQLs superiority, completely ignoring the fact that you can't have both at the same time).

Overall, you really need to rethink your idea of using a database for a real time data store. Databases are not meant or suited for this purpose, even screwed up ones like MySQL, regardless of what any raw performance figures might trick you into thinking.

Quote:

This is what we scientists call batshit dumbfuck crazy.

Sorry, but I have to rate you down for language. Seeing as this is a public forum accessible by minors, the "seven words" from public broadcasting apply in this forum.

You are absolutely correct in your technical analysis, though :-)

Quote:

6) your part. make sure the db scheme is well prepared. also do not write

If you look at the original requester, he wanted to select out the position of the other players, 2 times a second per player. This means that, everytime you change position (which I assume is often), you have to update the table.

Hence my recommendation that, if you want to try it, you should make sure it all runs in memory, and turn off transactions (run as unsafe as you can).

Writing a custom server, however, is going to remove the write restriction, as it can store everything in RAM and can had-code the queries in the implementation langauge. Then, the bottleneck becomes Flash itself. In general, Flash isn't intended to be warped in these ways.

Quote:

Original post by jfoster Out of sheer laziness I would like to have my flash client application query the SQL server every half a second or so to acquire information about other players (i.e. their position).

I just want to say, that if your clients can send SQL queries directly to the database then your game is going to be very very vulnerable to hackers.

Much better to have the clients send requests to some kind of server, which can then validate/authenticate the requests, then transform them into SQL itself and query the DB and then pass the results back to the clients.


As for performance - the web-site im building at the moment uses PHP (which is a slow language) to construct SQL queries which are then used to construct some HTML. Each page builds and executes about a dozen SQL queries then writes out 5-10kb of HTML code in about 10ms.
Most of this time is spent constructing HTML, not processing the SQL, so yeah, its pretty fast. I dont know about thousands of users querying it every 500ms though, that might be a bit too much. Maybe a few hundred would be a better target.

Quote:

Original post by Anonymous Poster Quote: Original post by DogCity Obviously going with PHP and a generic web server will save you a good deal of development time. However a custom made C++ server would certainly be more efficient. It really comes down to how optimized you want your server to be? Let me say this: at 1k players your first concern should be bandwidth availability and cost – getting extra hardware for a moderately inefficient server is not a big pocket stretcher. ofcourse using php and a generic webserver together with a flash client isn't extremely bandwidth effective either due to the nasty "&varname=value&othervar=othervalue" format used by loadVariables. (can be used to send data by adding it to the URL ...somepage.php?var=value&....etc) the only other option i know of for a flash client is the xml socket. (also very inefficient when it comes to bandwidth usage unless ofcourse you abuse it by sending non-xml data, though it will always add a terminating 0 at the end (this is generally unnecessary for games since the packetsize is known in advance)).

I presume that's how AMFPHP works, as they claim to be doing data compression on packets between Flash and PHP.

I'm currently using PHPObject which sacrifices packet-efficiency and Flash Comm Server compatibility in favor of simplicity. I looked at AMFPHP, but I just wasn't seeing much advantage over PHPObject for my situation.