CODEWITHSUNDEEP
mysqlquery-optimizationleaderboard
Dave
Dave

Reputation: 427

Mysql leaderboard with heavy use

I have a bunch of mobile apps that send data to a mysql server. Each app has it's own table and it can easily reach 100,000+ records in just a few weeks. I have around 10 million calls to the database currently per day. The way my system is currently setup is every 5 minutes a cron will run a bunch of queries for each table and cache html into a file. Then when the user calls the leaderboard I run 1 query to fetch their current rank and then I show the cached html as well as their rank I pulled from the query.

While this works, I'm looking to change this for 2 main reasons. I want to server the leaderboards up on the client side instead of on my server. I would like to send json and then parse it on their device. Also I would like to add daily, weekly, monthly and all time leaderboards for each app. Currently the way I'm storing the data wont allow for that.

My questions are

  1. What would be the best way to send the json to the user? Would it be wise to cache the json like I do with the html in a file and then have the user call that file? The only gripe about this is that I will need to create 4 json files for each app (daily, weekly, monthly, all-time).

  2. What would the db structure look like for wanting to add daily, weekly, monthly and all-time scores? I've added daily scores to some of my apps but I stopped doing it because it seemed like a terrible way of doing it. I was essentially keeping a large list of all the users and what they started the day with and everytime they updated their score I would then compare it to their stored score and get the difference. That table quickly grew very large for jus the daily, if I had weekly and monthly it would be millions and millions of records easily.

My table structure per app is pretty simple really

CREATE TABLE `app_test1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deviceID` varchar(50) NOT NULL,
  `username` varchar(25) NOT NULL,
  `score` int(11) unsigned NOT NULL
  PRIMARY KEY (`id`),
  UNIQUE KEY `deviceID_2` (`deviceID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Usually I would just make the changes and tweak them as I see fit, but with so many calls I've found out quite a few times a simple little change can easily crash the server so now I like to get ideas before I even attempt anything.

Upvotes: 0

Views: 901

Answers (1)

SolarBear
SolarBear

Reputation: 4619

1) Regardless if the best way is to cache the JSON file, it does have the benefit of being consistent with the data you cache in HTML. Generating that JSON would likely reuse the same code you use(d) with HTML so it's less work for you in a way.

2) That part is easy, at least at first glance : add a timestamp to every score you register for your users. Make sure to add an index (or more) including the timestamp : you'll be performing a lot of data manipulation based on time.

Another possibility is to have new tables for daily, weekly, monthly and all-time scores. The downside is that this adds a bit of complexity to your server-side code and, quite frankly, it's a bit disgusting... however, you would only need to query on that particular tables for the related high scores. So if you keep, say, the 50 highest scores, you would only have 50 x your number of apps rows in those tables. When a user registers a new score, you check if it's higher than the lowest daily score among the 50 : in that case, insert it in the table and drop the lowest score. If it beat a daily score, check against weekly ones, if it beat a weekly check against monthly and so on. Wehen you insert a new high score, recreate your cached high score file (HTML or JSON). Run a cron to clean-up the daily table at the end of the day, etc. Reads and writes on those tables would be faster than full table searches.

Just some ideas for you, I hope I make sense.

EDIT : I forgot another possiblity, namely to split your data among different servers. For instance, having the data for games A, B and C on server 1 and games D, E, F and G on server 2. This would enable you to handle the load more evenly. This would require a bit of fiddling (you don't want a server to receive 80% of the traffic while the other only gets the remaining 20%) but it's an easy solution to implement since all games seem to use the same schema ; the downside is that it would be more expensive to maintain two servers, obviously.

Upvotes: 1

Related Questions