0

I have a large game engine which serves mobile phone clients and a web site. The DB is MSSQL2008 and the engine is written using C#.

The web site is built with ASP.NET MVC and the web service for the mobile phones is also built on ASP.NET MVC (probably will be migrated to WCF or pure socket server).

The web site and the web service are located on an IIS 7 server and the DB is located on a dedicated server. Both are connected on a local fast LAN.

The game requires real-time response (under 1 sec) for each user. When I did some load testing on the service - it seemed that on ~250 users it reached 1 second response (at 50 it was about 200ms). It should support well over 10000 users connected. (Server replication I guess).

I thought about adding one more layer - a dedicated real-time server for the game web service. I've heard that Python could be used to build very high performance services - would that be a clever idea to add this layer? (the layer should have in-memory temporary database to serve the real time players and then dump it all to the back-end DB every X seconds).

Is my architecture any good? How could that be improved?

Ben Pilbrow
  • 12,041
  • 5
  • 36
  • 57
Bob
  • 3
  • 1

4 Answers4

6

It seams to me that you should ask yourself where the bottleneck is first.

  • Most of the time SQL server is a hard consumer of disk IO; By itself this is a whole topic of optimization (Database Schema, SQL Query vs StoreProc etc.). You may want to use the SQL profiler.
  • Are you using any state mechanism using the Web Service if so are you using SQL server to preserve that state this maybe a performance issue.
  • Have you tried to profile your .Net code and your SQL Query code?
  • Are you sending binary chunk over SOAP, binary content need to be encoded in base64 this may cause some overhead (Network and Bandwith) same as for SSL;
  • Have you ask you’re self about using a less verbal protocol other than WebService/SOAP; maybe an HTTP/JSON protocol or even a custom binary one.
  • Building a python proxy is an interesting thing but I’m pretty sure you could achieve a similar concept using .Net and Windows service but you may also ask yourself at what price since, if you ever need to cluster the whole thing you will need to put more synchronisation and robustness (crash proof) feature on that.
Caleb
  • 11,813
  • 4
  • 36
  • 49
Eric.Lacroix
  • 61
  • 2
2

I can speak to the SQL Server part.

What is the hardware specs you have for this? What's the SQL Server edition you are using? While you can add more memory to the box and keep the data in memory but that solution won't scale after a while. You need to use lot of good practices here if you want to scale for many users.

The usual bottlenecks are Memory, IO, CPU.

  1. Having more memory helps.
  2. Having good disk system [RAID 10] really helps. Separating data and transaction logs onto separate spindles is a good practice. Depending on the amount of IO, move tempdb onto its own spindles and look for bottlenecks. Is it bound by IO or allocation bottleneck. using Trace Flag TF 1118 and many tempdb data files helps.
  3. Pre-allocate data and log files so that it won't auto grow during normal business operations.
  4. Start with good schema + good code after doing some due diligence. Crappy code is crappy code and garbage in is garbage out irrespective of what platform you use.
  5. Understand Index structures very well and have a good index maintenance plan ( update stats)
  6. Load test your sql code with large amounts of data.
  7. Look at identifying the bottlenecks and performance problems in SQL Server. Wait statistics and virtual file stats are very important here. Identify which procedures are expensive based on cu, logical IO & high execution count. Look at missing indexes DMV. Learn to read data from SQL Server Plan Cache.
  8. Look at Instant File Initialization
  9. And there are so many other things here and its hard to list them all. This is a good starting point but its NOT an exhaustive list though.

Ref: http://dl.dropbox.com/u/13748067/SQL%20Server%202008%20Diagnostic%20Information%20Queries%20%28April%202011%29.sql

http://www.sqlskills.com/BLOGS/KIMBERLY/category/Indexes.aspx

http://www.sqlskills.com/BLOGS/PAUL/category/Indexes-From-Every-Angle.aspx

http://www.datamanipulation.net/sqlquerystress/

Sankar Reddy
  • 1,374
  • 8
  • 8
2

The game requires real-time response (under 1 sec) for each user.

And these are mobile phone users, with varying qualities of service (3G/3Gs/4G/wi-fi), reception, latency, jitter, fragmentation, etc. and you're expecting < 1s response time? Have you tested this with one phone let alone hundreds?

gravyface
  • 13,957
  • 19
  • 68
  • 100
1
  1. Game servers usually work on custom protocol based on UDP protocol
  2. There is need to make this server for this protocol from the low levels, as far as I know there is no public framework or something to help
  3. For storing state use memory or some cache, not SQL DB
mixal
  • 11
  • 1