we are all aware of the popular trend of MMO games. where players face each other live.
My questions are focussed on reducing load on Game database or Sql queries
a) How to control the surge of records into the GameProgress table. so that players get response quicker. The Server starts to lag at peak hours or when 1000 players are online
There is a tremendous flow of sql queries, for ex. even in the current game version
There are average/minimum 100 tournaments online per 12 minutes or 500 players / hour
In Game Progress table, We are storing each player move
a 12 round tourament of 4 player there can be 48 records
plus around same number for spells or special items
> a total of 96 per tourament or 48000 record inserts per hour (500 players/hour)
I am also considering using a background process in Csharp, that keeps moving expired tournament records from GameProgresstable to another Database where we have a server free of gameplay load.
b) How often should we run vaccum full of postgres .
c) can we set a table to be present in some kind of cache or quick buffer for quicker access, for ex. we often have to authenticate user credentials or lookup tournament status in Table
thanks
arvind