Re: Improve MMO Game Performance

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, asROW 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)
Are there any particular settings or methods available to improve Just insert_table operations thanks arvind

On Sat, Oct 13, 2012 at 1:52 PM, Arvind Singh <arvindps(at)hotmail(dot)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(at)hotmail(dot)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

Oh no, it is average. I am sorry , i did not mention that Average was calculated PeakUsage+MinimumUsage/PeriodOfUsage

it is not that 500 users are always per hour,

in real game scenario there are peak times within a hour, so it means there can be 500 users making simultaneous move,
and there could be a surge of 500 inserts in an instant , and if there are 4000 users logged in the figure could be 4000 inserts in a millisecond. and at LowTide when there are 100 users, the number could be as low as 10 insert / ms

thanks

> Date: Sat, 13 Oct 2012 10:18:28 -0600
> Subject: Re: [GENERAL] Improve MMO Game Performance
> From: scott(dot)marlowe(at)gmail(dot)com
> To: arvindps(at)hotmail(dot)com
> CC: pgsql-general(at)postgresql(dot)org; laurenz(dot)albe(at)wien(dot)gv(dot)at
>
> On Sat, Oct 13, 2012 at 5:52 AM, Arvind Singh <arvindps(at)hotmail(dot)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.

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(dot)albe(at)wien(dot)gv(dot)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

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

> Date: Thu, 18 Oct 2012 11:24:00 -0300
> From: alvherre(at)2ndquadrant(dot)com
> To: laurenz(dot)albe(at)wien(dot)gv(dot)at
> CC: arvindps(at)hotmail(dot)com; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Improve MMO Game Performance
>
> 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

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

the cyclic change idea is really marvellous , thank you

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

returnstrikeHP is a random number in range, that is calculated when a player strikes back. although as you write,
we can calculate the difference between rows, but that may increase the number of selects.


yours truly
arvind

> Date: Fri, 19 Oct 2012 13:54:02 +0200
> From: foo(at)meisterderspiele(dot)de
> To: arvindps(at)hotmail(dot)com
> Subject: Re: Improve MMO Game Performance
>
> 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

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