The idea of doing an UPDATE on the custom levels table every time someone dies in order to increment the deaths counter terrifies the database administrator in me. That'll scale REAL well. Yikes.
The easiest way to have a counter that is both correct and fast is to split things up into multiple separate counters and then count them. So for a simple example: User ID mod 50 = 7 so lock row, 7 increment row, 7 unlock row 7. Want the total? sum (all rows).
You can also get fancy with lock row 1, lock total, update total = total + value of row 1, unlock total, set row 1 to 0, unlock row 1. Repeat row 2...50. The advantage being even lower latency access to a number that is withing X seconds of being correct.
PS: Both of the above can be split across multiple cores / machines. But, for a less accurate but still reasonable have the client a = (rand (0,100)), b = rand (0, 100 + a), if b == 7 update death count by (a + 100). This only increments the counter 1 time in ~150 times while avoiding counters that are always multiples of some number. Just be careful of off by one errors.
We do millions of operations like that a minute by queuing, aggregating and then committing. SQL Server's MERGE is particularly useful for it, although on our MongoDB stuff (and ironically, for player created levels) we do $incs.
I know next to nothing about this kind of stuff. If I wanted to create a stats + user generated level database system akin to Super Meat Boy (and I do) do you have any recommended resources to read?
It depends on how deep you want to go yourself. At its easiest you could just drop Playtomic [1] in, we have support for most gaming platforms.... obviously I'm quite biased towards this option. :)
If you want something more flexible and you're doing mobile you can check out Parse [2], they're a custom database with a REST, iOS and Android APIs. If you're using Flash, HTML5 or Unity3d we have a bridge that lets you use Parse through our own APIs.
If you want to get right down to the guts of it I would get a simple Heroku [3], PHPFog [4] or AppHarbor [5] account depending on what languages you're most comfortable with or learning and set up a MongoDB database over at MongoHQ [6], MongoDB lends itself very well to user created levels in my experience.
Basically you need:
1) Scripts to save, rate, count plays and list levels. You want to either authenticate the user, or more simply just obfuscate the data you're transmitting to make tampering harder
2) Some kind of logging or queueing system where you will store the plays
3) Something that will go through your logs or queues and perform the $inc operations on your levels in bulk batches rather than doing it all individually
4) Indexes on your database that match your listing requirements
On our platform the only problem has been having to perform count operations, the way we do it scores can be listed in unpredictable fashions and MongoDB is inherently bad at counts.
Aside from that the read:write ratio massively favors reading for us and caching makes that a negligible operation most of the time, and (unless like in our case you're providing leaderboards for games you don't control) MySQL and Memcache should carry you fine.
Yes, but redis is just so nice for certain applications that can be a pain in a RDBMS. Not that it can't be done, but the simplicity and performance of redis atomic counter increment/decrement is often enough for magical vertical scaling sauce. Redis is chock full of these little use cases, even when you're just using it as a "cache".
Off the top of my head, one way to do this without rearchitecting everything would be to insert a new record into a very simple in-memory temp table. Every minute, update the appropriate record in the real table by count(*) of the temp table and clear the temp table. As it's being used for a non-critical counter, data loss in the event of a MySQL restart isn't a concern.
That should be sufficient for something of this scale and only requires a minor update to the client SQL, ensuring the temp table is created when MySQL starts, and executing one stored procedure on a schedule.
If I had to use the database, I'd probably aggressively shard the data so that it's spread across multiple databases/tables. That way the load of counter updates could be spread across machines. I'd probably also store the counters in a separate table from the rest of the levels, for the same reason, but also to ensure that the rest of the information about a level isn't locked during every increment.
Of course, this is given that IIRC, MySQL uses row locking and used to even use table locks at times during writes. The last thing you want is for your spew of counter updates to lock out other, more important operations (like deleting a level). MySQL locking behavior might be better now.
The real 'right' solution to a high-traffic counter like a death count is to store the realtime counter(s) in something like memcached: High throughput, low overhead, and built in support for operations like increment.
As counter traffic increases, you could partition the counter itself (and approximate the actual value by taking partition_value * num_partitions) or only record a percentage of the time (say, +4 the counter on 25% of deaths). Both solutions reduce load and could produce 'good enough' numbers as long as you don't have wildly different behavior on each partition.
MySQL locking behavior depends on the table engine/type you use. The old MyISAM format is table-level locking. InnoDB is row-level and supports all the "big boy" RDBMS features you'd expect to be there (like foreign keys...).
Also ideal is to have the client not update the number of deaths on each attempt but only either on completion of the level or at a timed interval. No need for that date to be real-time, so it can be aggregated.
One is: don't use the database for this. Lock/read/write/unlock is very slow when you need a round-trip over the Internet for each phase. (Remember, with optimistic locking, many transactions are in the Lock/read state as the same time. When someone writes the new death count, all transactions in the lock/read state are rolled back and started from the beginning, as the data read is now invalid. And that's assuming MySQL is not in read uncommitted mode; then you just throw away data randomly.)
The correct way to count something in a relational database is to add a relation (row) for each event, and then COUNT them all. How do you count log message in log files; by having syslogd update a count file? Nope. wc -l.
(And no, I'm not a fan of auto-incrementing integer primary keys, because read/calculate/write is slow. Databases have ACID so that read/calculate/rewrite is safe. But that doesn't mean that type of workload is going to run fast. Of course, autoincrementing primary keys is specially optimized by most databases, so you never notice in this particular case.)
