Leaderboards optimization

To be honest, I don't know how Guitar Hero scores, but I would assume it's based on skill and not on the popularity of the song played. I would develop metrics that measure how accurate the players timing is based on the input they provide and gauge everybody on the same scale regardless of the specific song used to "measure their skill".. I'm guessing that it's based on pressing the right key/button at the right time, so I would measure the milliseconds between when they should be pressing the key and when they actually do, and that should give you a number that you can add / subtract to a "score" value. Missed keys or keys over a certain millisecond window would subtract points and well timed appropriate input gets points added. Something like that is how I would approach it.

If you're just using a scoreboard type server, then this should be pretty easily done, tally the points and send them to the server to be placed in their position on the global score-board.

I'm not entirely sure though if that's what you had in mind from your question. Are you considering making this a type of server-authoritative simulation, where all the player input is sent over the network to a server to be checked and scored there? If that is the case, it will be adding a few more layers of complexity. If so, I would read this: https://gafferongames.com/post/introduction_to_networked_physics/ and replace the idea of a "physics simulation" with a "song simulation", you'll have all the same issues with timing and network lag that a 3D physics game will have and you should be able to solve it the same way.

Best of luck, sounds like a fun project.

It's really no different from any other band+song+player+score+date database.

You'll end up with a primary key of (band+song+player) and a secondary key of (player), so with only two keys in the table, any database engine should work fine. (Postgres is notoriously bad at too many keys per table.) Just make sure you have high performance storage and sufficient RAM in your database engine.

You can probably stuff them all in a single SQL database table, unless your game is super popular. At that point, physically shard by some key, or throw it all at Amazon DynamoDB or Google Firebase and pay them to manage it. Contrary to some popular belief, I don't think self-hosted NoSQL solutions like Redis or Scylla or Mongo would be any better for this use case. They're all bound on I/O performance and indexing, and the workload is exceedingly regular, perfectly mapping to a SQL table.

There's also the question of how you map band + song name to a particular score, if the spelling isn't consistent (as when users encode their own files from CDs and such.) ContentID or other content fingerprint services may help you come up with a reasonable key for each track, then.

Hi Septopus,

Thanks for the reply, it helps to know what you understood from my question :).

I am interested in how the scores register on the database of the server.

Let's say you can play songs from youtoobe and people will play the same song, but the remix done by their favorite artist. Imagine that 100k people playing a lot of songs every day will create a long list of songs your client must check every time for the highest score.

Now, imagine 1000 concurrent users are doing queries on the server for the highscore.

My question is how can you avoid a high load on to your database server.

LE: @hplus0603

Yes, you kinda nailed it, but how do I calculate the processing power needed? Any pointer to that?

And in the case described above, what kind of optimization options do I have to reduce data entry and/or cpu power needed for the query?

Sorry, misunderstood the question.

Sounds like @hplus0603 addressed some of that though.

What you described isn't that big of a database hit. Even if you have 100,000 people per day, they should not be constantly probing the entire high score list.

These requests are both simple and easily cached. If they're cached they have no real work, just the network requirements of sending data each way. If they require a lookup, there is no join or anything, just a single key lookup on a single table. Modern database servers can handle several thousand concurrent requests like that every second on a single machine. You're more likely to hit networking bottlenecks before database limits with those queries.

how do I calculate the processing power needed? Any pointer to that?

Implement the use case, fill it with synthetic data, and write some parallel benchmarking code that runs the queries.

Spin up as many benchmark clients as you can, until the database starts backing up. (You'll likely need more machines for running all the clients than the one for the database, for robustness.)

That being said, a song is 3 minutes, and a player updates and sees the highscore once per song (approximately.) This means that you get 1 query per 180 seconds per active user. You need 180,000 simultaneous active users ("concurrent users" or CCU) to get to 1000 queries per second. With sufficient RAM and SSDs, 1000 queries per second isn't hard for a typical database.

You can rent high-end hardware by the hour from Amazon so you don't have to actually buy it just to benchmark it.