Database optimization

That or you just throw old data away. If you aren't actively deriving revenue from having it accessible, why design your game such that you have to store it?

How many people actually actually appreciate that FaceBook has made all their posts searchable back to the beginning of time? Most of the data is noise, so you really only need a trailing average...

Throwing away old data is a good option as mentioned by swiftcoder. Knowing that a dead player built a swordsman in 2004 isn't very useful.

If you can't (or don't want to) delete old data, you can limit your data sets to only return the useful information. For example you could: only return the last two weeks worth of data by default. Or limit the data to things that are only relevant to you like your closest 10 neighbors. The only "tricks" to these are providing the parameters you need to the queries and writing them with that in mind.

...
where 
    newsItem.CreatedDate >= @beginDate
...

And general SQL optimizations would apply including: good data model design, indexes on foreign keys and commonly searched on queries, limiting your result set, etc.

SQL is pretty darn awesome at digging through truly MASSIVE datasets provided you do it right. :)

- Eck

That or you just throw old data away. If you aren't actively deriving revenue from having it accessible, why design your game such that you have to store it?

How many people actually actually appreciate that FaceBook has made all their posts searchable back to the beginning of time? Most of the data is noise, so you really only need a trailing average...

Actually, I consider a "brief" timeline and a communication part like FB posts, so throwing away is not first option. Maybe putting archaic data to another database makes things better?

Throwing away old data is a good option as mentioned by swiftcoder. Knowing that a dead player built a swordsman in 2004 isn't very useful.

If you can't (or don't want to) delete old data, you can limit your data sets to only return the useful information. For example you could: only return the last two weeks worth of data by default. Or limit the data to things that are only relevant to you like your closest 10 neighbors. The only "tricks" to these are providing the parameters you need to the queries and writing them with that in mind.

...
where 
    newsItem.CreatedDate >= @beginDate
...

And general SQL optimizations would apply including: good data model design, indexes on foreign keys and commonly searched on queries, limiting your result set, etc.

SQL is pretty darn awesome at digging through truly MASSIVE datasets provided you do it right.

- Eck

will use LIMIT for sure but if player wants to get exactly archaic data, seems options are limited.

Actually real question is, is there a cache like mechanism (DB in memory ?) making this worthy?

While throwing data away might be an option, from the perspective of games this usually falls into the category of splitting your data into hot and cold records. (I.e. who's actually playing? What items are actively in use, etc.) Basically you want a LRU (least recently used) cache layer between you and your database. High density web sites tend to use memcached, or related, in front of the the SQL servers as an effective method of implementing this without a lot of work. In order to implement it yourself you just have to provide an API in front of the data retrieval which first checks for items in the cache layer and uses that instead of sending off an SQL query. With a decent division of information in the DB tables, this can reduce the load on the Sql server by a hundredfold pretty easily such that the occasional queries against really old cold data are not a problem in the middle of regular hot data accesses. As with anything there are more details but this is a starting point.

In terms of scaling when applied to games work (should apply to just about anything), it scales very well and even dynamically. By simply adjusting the memory size of the cache you can increase/decrease load on SQL to the point that it makes the most sense for you. If things get really big, it scales horizontally also, you can move the cache onto a standalone box with basically nothing more than massive amounts of memory. If that ends up bottlenecking you can split it over multiple machines by breaking up which caches contain different portions of the access API. At the point the DB starts slowing down even with the cache on top you can subdivide the SQL servers into primary table access for each cache separately etc. This is basically an algorithmic optimization instead of arbitrarily throwing data away (which still may be valid) or just throwing bigger boxes at a problem hoping vertical scale can keep up.

SQL databases support range partitioning and partition pruning for that, although simply using an index on a timestamp is likely as efficient or more efficient than partition pruning when searching. Pruning doesn't need to calculate an index though, so inserts are cheaper. Also, range deletes become trivial.

Basically, you tell the database to put everything in some range into one bucket, and everything in some other range into another one, and so on.

The database does not need to search buckets that are outside the range, and if you want to throw away an entire year of old data, you just tell the database to drop the partition. You could also have the most recent "hot" partition on a solid state disk, and older partitions on a network store (so they're slow to access but you don't completely throw them away).

Heavy-traffic sites like Twitter or Facebook will likely have such a two-layer (or three/four layer) hierarchy implemented one way or the other, too, unless hey have unlimited money. They'd have all "hot" and "changing" data cached in RAM (either using a NoSQL database, or in a custom in-memory hashtable like Facebook does), so 99% of the requests can be served from RAM. But due to the sheer insane amount of data and the inherent finiteness of RAM, they'll have no choice but to store the rest on some form of disk.

Not all data needs to be stored "hot" either. You sure want to be certain that updates as well as queries on meta data like "who is friends with who" are lightning fast. But on the other hand, if someone uploads a photo which is 4-5MiB in size, nobody really cares if has to be loaded from disk when it's served. The user's internet connection isn't that fast, it will take several seconds to load anyway. Nobody notices the difference.

Actually real question is, is there a cache like mechanism (DB in memory ?) making this worthy?

You have to remember that SQL Server or whatever DBMS you use will have its trade offs, but more importantly you have to think about how your application uses and serves this data to its clients to truly nail down what those trade offs will be.

At my current job we use a 2 layer stack, the first and more permanent storage are SQL server database instances that are built on a CQRS (Command Query Read Segregation) model and the most recently/frequently accessed data is cached in RavenDB.

The cool thing that we implemented recently was segregating the data into "components" to get a higher probability of hitting something in our RavenDB store. An example of what I mean by this is, instead of storing the player's entire save in the cache, you store pieces of it (their items, pieces of their skill tree, etc) separately so when you go to fetch a different player's save, most of that information is already cached and can be rebuilt without even hitting the database. The power of permutations will play in your favor this way, allowing you to load 90% or more of players' information from the fast NoSQL cache instead of having to do costly queries on the SQL DB.

CQRS is basically a way to scale your database queries (reads) independently of your database write operations (writes/updates). Yes, tweets come in at large volumes, but 90% of the time a client is fetching data. This goes more in depth: http://martinfowler.com/bliki/CQRS.html

Hope this helps!

Well, for someone not went far from simple CRUD and have brief idea of NoSQL, seems this is slightly out of my scope atm not seem unachieveable though , so will keep this in mind but seems I'll postpone it until becomes a real issue ie worthing the trouble.

Thanks for caring to help though :)

Good attitude. An important part of building a scalable system is knowing what is necessary now, and what you can improve later. None of the services that you mentioned started out needing to work at their current scale, and none of them would be here now if they tried to solve that problem initially. Generally, the biggest risk for newer projects is lack of players, not too many.

That or you just throw old data away. If you aren't actively deriving revenue from having it accessible, why design your game such that you have to store it?

I deeply disagree with this. Keep everything. There are a million things you might use the data for and once it's gone it's gone forever. You can always just ignore or archive old data you don't need. You can't fabricate data you threw away or never collected.

You can use this to collect important gameplay information on levels that's difficult to collect manually. You can use this to detect misbehaving players (and after, identify behaviors that help you detect them earlier by mining their history). You can analyze trends to further improve the game or marketing.

I highly suggest you read a text on data mining. It is essential in the games market; many game companies have entire teams of people whose job is just managing their data-mining needs.

I highly suggest you read a text on data mining. It is essential in the games market; many game companies have entire teams of people whose job is just managing their data-mining needs.

I'm not suggesting one shouldn't accrue value from data mining - I'm just doubting the OP is doing so currently.

will use LIMIT for sure but if player wants to get exactly archaic data, seems options are limited.

Actually real question is, is there a cache like mechanism (DB in memory ?) making this worthy?

You are confusing two different issues: achieving good performance on important and/or common queries and dealing with data accumulation in a cost effective way. The same tools, particularly partitioning and using multiple database types, can be applied for either purpose.

Table partitioning can allow parallel query execution for performance, or segregate old and new data for managing data accumulation (either to keep old data online, but on cheaper and slower storage, or to systematically purge archived old data).

Caching data in memory is obviously a performance-only technique: in the best possible case your database reads from disk very sparingly because you are blessed with a small enough, slowly changing and slowly growing working set of actually important data, but the influx of new data remains the same and it is going to fill up disk space at the same rate.

will use LIMIT for sure but if player wants to get exactly archaic data, seems options are limited.

Actually real question is, is there a cache like mechanism (DB in memory ?) making this worthy?

You are confusing two different issues: achieving good performance on important and/or common queries and dealing with data accumulation in a cost effective way. The same tools, particularly partitioning and using multiple database types, can be applied for either purpose.

Table partitioning can allow parallel query execution for performance, or segregate old and new data for managing data accumulation (either to keep old data online, but on cheaper and slower storage, or to systematically purge archived old data).

Caching data in memory is obviously a performance-only technique: in the best possible case your database reads from disk very sparingly because you are blessed with a small enough, slowly changing and slowly growing working set of actually important data, but the influx of new data remains the same and it is going to fill up disk space at the same rate.

Indeed, seems I was confused. Caching data in memory wasn't what I am looking for, actually doubt it is meaningful in a Twitter / FB comments like environment.

But for rest of game , it may be effective though.

Caching data in memory wasn't what I am looking for, actually doubt it is meaningful in a Twitter / FB comments like environment.

That may be very meaningful (and even necessary), depending on what you do and on how big your userbase is and what they do.

On a no-special machine with a no-special conventional, spinning harddisk, a typical MySQL InnoDB database will do around 2000 INSERTs per second if you disable flushing the transaction log (that is, without ACID!) and fewer than 100 otherwise.

If you have enough users or if your users are active enough so you come close to only a few dozen updates per second, you will feel that SELECTs get significantly slower, too. SELECTs interfere with the performance of INSERTs as well, obviously.

One or several readonly slaves would be able to serve a few thousand SELECTs independently, but they eventually still have to process the INSERTs, too (albeit somewhat more efficiently). MySQL Cluster (pure in-memory DB) is said to be around 1000 times faster, but I have never tried it, so can't tell if that's true at all or only marketing.

A typical redis server running on the same machine does 100,000 inserts (with ACID) and needs no additional slaves. A simple memcached can deliver 200,000 hits per second and can reduce the pressure on SELECTs by 90-95% with little or no visible difference (merely updates being delayed a few seconds), and with almost no change to how your system works.

If you run, like many games, a RESTful service where players can query their character highscores etc. to be displayed in banners at the forum, or on fansites, and players are hammering this service with automated scripts (which they will do!), then running memcached makes the difference between a functional service and no service.

A hand-crafted in-memory hashtable can, depending on what concessions you make (such as e.g. no deletes in Facebook's atomic hash), deliver tens of millions of reads and hundred thousands to millons of inserts per second.