i'm developing game , i'm using leaderboard keep track of player's score. there requirement keep track of 200 additional statistics. these stats things like: kills, deaths, time played, weapon used, achievements gained , on.
what players interested in is score,kills,deaths , time played. other stats not needed shown in game should accessible if want view them or compare them against other players. expected number of players stored in leaderboard table 2 million.
currently design store player id stats in 1 table, instance:
player_id,points,stat_1 .. stat_200,date_created,date_updated
if want show sorted leaderboard based on points have put index on points , sort on select query , limit results return 50 every time. there ideas able have player sort leaderboard on couple of other stats time played or deaths maximum of 5 sortable stats.
the number of expected users playing game 40k concurrently. maybe quarter of them, ballpark figure, actively browse leaderboard, rest play game , upload scores when finished.
i have number of questions approach below:
it seems, have doubts, consensus leaderboards millions of records should sortable on couple of stats don't scale in rdbms. correct ?
is sorting leaderboard on points through select query, assuming have index on it, going extremely slow , if how can work around ?
should split storing of additional stats not sorted in separate table or there better approach ?
is caching sorted results in memory or in separate table going needed, keeping expected load in mind, , if solutions or options should consider ?
if approach wrong , better of doing things in way please let me know, options nosql solutions in cloud hosting environments open considered.
cheers
1) multiple indexes become more costly update table. boils down how each player status written db.
2) fast long indexes small enough fit ram. after that, performance takes big hit.
3) can gain performance if add fields need index, cause dbms doesn't need access table @ all. approach has highest probability work if accessed fields small compared size of row.
4) oracle att doing caching you, if have massive load of users doing same query better run query regularly , store result in memory (or memory-mapped file). instance, if high-score list accessed 50 times/second can decrease load caused question 99% dumping every 2 seconds. advice on is: don't unless need it. measure performance first, , add if necessary.
Comments
Post a Comment