Thread: Game Server Lags
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
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
On 10/02/2012 10:02 PM, Arvind Singh wrote: > 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 In most cases the answer is the same as for any other bursty application: Cache aggressively in the layers between the app front end and the database. memcached is a popular choice. For really harsh environments use dynamically scaling clusters where you start up new replicas as load goes up, and stop them when load goes down again. > 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 Some combination of async commit, commit delay, etc depending on how critical the data is. > b) How often should we run vaccum full of postgres . On any modern version you shouldn't need to. Turn autovacuum up so it runs very aggressively and let it do the work. > 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 Use a front-end cache like memcached. -- Craig Ringer
On 2 October 2012 15:02, Arvind Singh <arvindps@hotmail.com> wrote: > 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 It sounds like this data is not that valuable, so a small window of data loss in the event of a crash might be acceptable for a gain in performance. If that is the case, consider turning off synchronous_commit. Note that this is something that you can do at the transaction granularity, so, for example, if there are financial transactions, they need not make this trade-off. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services