Thread: Improve MMO Game Performance

On Sat, Oct 13, 2012 at 1:52 PM, Arvind Singh <arvindps@hotmail.com> wrote:
>
> To understand my query, please consider the following scenario
>  we store game progress in a postgres table.
> A tournament starts with four players and following activity
>
> Each player starts with 100hitpoints
> player 1 makes a strike (we refer to a chart to convert blows to hitpoints
> with random-range %)
> player 2 has 92HP, and returns a light blow, so player1 has 98hp
>
> The above two round will now be in Game Progress Table, as
>
> ROW Player1HP Player2HP Strikefrom StrikeTo ReturnStrikeHP Round TimeStamp
> StrikeMethod
> 1      100       100         0        0            0          0
> 2       98        92        P1        P2           2          1
>
> There is a tremendous flow of sql queries,
>
> 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)
>

that's below 15 insert/s ... not something to worry about, on recent hardware.


> Are there any particular settings or methods available to improve Just
> insert_table operations

- avoid too many unused indexes
- keep your model normalized
- keep pg_xlog on separate device
- follow tuning advices from wiki
http://wiki.postgresql.org/wiki/Performance_Optimization



>
> thanks
> arvind
>
>
>
>

On Sat, Oct 13, 2012 at 5:52 AM, Arvind Singh <arvindps@hotmail.com> wrote:
> we are all aware of the popular trend of MMO games. where players face each
> other live.
>
> My area of concern, is storage of player moves and game results.
> Using Csharp and PostgreSql
> The game client is browser based ASP.NET and calls Csharp functions for all
> database related processing
>
>
> To understand my query, please consider the following scenario
>  we store game progress in a postgres table.
> A tournament starts with four players and following activity
>
> Each player starts with 100hitpoints
> player 1 makes a strike (we refer to a chart to convert blows to hitpoints
> with random-range %)
> player 2 has 92HP, and returns a light blow, so player1 has 98hp
>
> The above two round will now be in Game Progress Table, as
>
> ROW Player1HP Player2HP Strikefrom StrikeTo ReturnStrikeHP Round TimeStamp
> StrikeMethod
> 1      100       100         0        0            0          0
> 2       98        92        P1        P2           2          1
>
> There is a tremendous flow of sql queries,
>
> 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)

That's only about 13 inserts per second, and if you're batching them
up in transactions then you could easily be doing only one insert per
second or so.  My laptop could handle that load easily.

On 10/13/2012 07:52 PM, Arvind Singh wrote:
> we are all aware of the popular trend of MMO games. where players face
> each other live.
>
>     My area of concern, is storage of player moves and game results.
>     Using Csharp and PostgreSql
>     The game client is browser based ASP.NET and calls Csharp functions
>     for all database related processing
>
>
> To understand my query, please consider the following scenario
> /we store game progress in a postgres table./

I suspect that this design will scale quite poorly. As others have noted
it should work OK right now if tuned correctly. If you expect this to
get bigger, though, consider splitting it up a bit.

What I'd want to do is:

- Store data that must remain persistent in the main PostgreSQL DB;
   things like the outcomes of games that have ended, overall scores,
   etc.

- Use memcached or a similar system to cache any data that doesn't
   have to be perfectly up-to-date and/or doesn't change much, like
   rankings or player names;

- Use LISTEN / NOTIFY to do cache invalidation of memcached data
   if necessary; and

- Store transient data in `UNLOGGED` tables with `async_commit` enabled,
   a long `commit_delay`, etc. Possibly on a different DB server. You'll
   certainly want to use different transactions to separate your
   important data where durability matters from your transient data.

   I'd run two different Pg clusters with separate table storage and WAL,
   so the transient-data one could run with the quickest-and-dirtiest
   settings possible.


I might not even store the transient data in Pg at all, I might well use
a system that offers much weaker consistency, atomicicty and integrity
guarantees.

--
Craig Ringer

Arvind Singh wrote:
> Are there any particular settings or methods available to improve Just
insert_table operations

The obvious ones:
- Wrap several of them in a transaction.
- Increase checkpoint_segments.
- Set fsync=off and hope you don't crash.

Yours,
Laurenz Albe

On Mon, Oct 15, 2012 at 7:16 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> - Set fsync=off and hope you don't crash.

Ouch. I might consider that for a bulk import operation or something,
but not for live usage. There's plenty else can be done without
risking data corruption.

ChrisA

Chris Angelico wrote:
>> - Set fsync=off and hope you don't crash.
>
> Ouch. I might consider that for a bulk import operation or something,
> but not for live usage. There's plenty else can be done without
> risking data corruption.

I didn't mean that to be an alternative that anybody should
consider for production use.

I don't think that there are so many ways to speed up
INSERTs.  Can you think of some that I have missed?

Yours,
Laurenz Albe

Albe Laurenz wrote:
> Arvind Singh wrote:
> > Are there any particular settings or methods available to improve Just
> insert_table operations
>
> The obvious ones:
> - Wrap several of them in a transaction.
> - Increase checkpoint_segments.
> - Set fsync=off and hope you don't crash.

I think it would work to set asynchronous_commit=off for the
transactions that insert moves.  That way, the fsync flushes happen in
the background and are batched.  Raising wal_buffers is probably a good
idea, and keep an eye on how the walwriter is doing.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Alvaro Herrera wrote:
>>> Are there any particular settings or methods available to improve
Just
>>> insert_table operations
>>
>> The obvious ones:
>> - Wrap several of them in a transaction.
>> - Increase checkpoint_segments.
>> - Set fsync=off and hope you don't crash.
>
> I think it would work to set asynchronous_commit=off for the
> transactions that insert moves.  That way, the fsync flushes happen in
> the background and are batched.  Raising wal_buffers is probably a
good
> idea, and keep an eye on how the walwriter is doing.

I guess you mean synchronous_commit=off :^)

Yes, I forgot that one.

Yours,
Laurenz Albe

Hello Arvind,

i worked on MMO (and especially their performance) some time ago. I
really like(d) them. :)

> we are all aware of the popular trend of MMO games. where players face

> To understand my query, please consider the following scenario
> /we store game progress in a postgres table./
> A tournament starts with four players and following activity
>
>  1. Each player starts with 100hitpoints
>  2. player 1 makes a strike (we refer to a chart to convert blows to
>     hitpoints with random-range %)
>  3. player 2 has 92HP, and returns a light blow, so player1 has 98hp

That is a very simple description or a very simple system.

> The above two round will now be in Game Progress Table, as
>
> |ROWPlayer1HP  Player2HP  Strikefrom  StrikeTo  ReturnStrikeHP  Round  TimeStamp  StrikeMethod
> 1       100        100          0         0             0           0
> 2        98         92         P1        P22           1
> |

At this point you should reconsider if you need this much data. It is
faster to store less data.

For example you do not need the colum "ReturnStrikeHP". You can
calculate the damage from the difference between the rows.

If there is a cyclic change of the attacker (P1->P2->P1->P2->P1->P2 ...)
you just need to store the starting player and could drop the "strik
from" and "strike to" column.
If you need it and there are just 2 players in one fight, reduce the
column to "attacker" and store if it is player one or two. The one which
is not in the column is the defender.

Also why do you store each round? Most time there are just reports,
charts or animations about the fight. You can generate them in a more
compact form and just store 1 row with this information. In this way you
will reduce the number of needed INSERT-operations a lot. And you will
speed-up the SELECT because less data must be read.

I hope this will help you. Greetings from Germany,
Torsten

Arvind Singh wrote:
>
> ok,
>
> thanks i will look into walbuffers
>
> asynchronous_commit=off is a doubt for responsive environment (pg manual). for ex.
>
> would it help if,
>
> a game player , has to perform next move on basis of his/her previous move plus other players move.
> all is sent to table and for each player a particular set is retrieved with a minimalistic  data portfolio

The database will see all data as committed, regardless of commit being
synchronous or asynchronous (note the setting is actually called
synchronous_commit).  There is only a doubt about it being committed
after a database crash.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hello Arvind,

> the cyclic change idea is really marvellous , thank you

You're welcome. :)

> but we store each round, because we need player actions for further
> analysis about game trends

Normally the different analyze-forms and goals are known from the
beginning. You could use a more compact format for the analysis, which
is optimized for this.

Also: if you just need this data for further analysis: don't store them
together with the actual game data! Big Performance NO GO!

Set up a second database-server! It can be a simple and even slow
machine. Cache the round-data compressed in RAM or another fast location
and import them from there in a steady flow into the second
database-server. Their you can do your heavy analyze at any time without
annoying your users!
(You can even use more than a second server to do the analyze in
parallel and sum up the aggregated data. Most the analyze must not be
actual, so it doesn't bother if it takes some hours more. It is a big
win for your user-performance)

Greetings from Germany,
Torsten