Wednesday, October 24, 2007

Our Server Woes

Hey everybody -As you probably know, the engines of war have been grinding to a halt around 7 pm each night. As you probably know, this sucks. We’re chalking this up to the overwhelming popularity of both our games thus far, as over 40% of on-campus students at RPI and 10% of all Ivy undergrads are actively playing. This is awesome, but we unfortunately don’t have Facebook’s server farm.

For now, we suggest not waiting until last minute to place your armies or issue your orders. It could save you the possible disappointment of not getting your orders in before turn processing starts.

The situation is quite sad actually. Did you know that if you give a MySQL database too much work, it just refuses to continue! We get emails from it saying:
"MySQL server has gone away."
...on vacation, perhaps? We don't know. But we always pray that it come back quickly.

Just so everybody knows, we're working really hard to optimize our code. For example, the Flash map currently makes 800 MySQL queries on each page load [update: we've since gotten the number down to 18 queries, but are trying to make it even smaller]! Think about that happening for 5,000 visitors, all hitting refresh simultaneously, while our server is already struggling to find some RAM to start turn processing properly.

We've made some strides in optimization and cutting down the number of SQL queries our game page makes significantly (although we haven't published the changes yet). We've already started the process of researching how we want to incorporate more servers into our system architecture. Hopefully in the near future, all of you will see a noticeable improvement in site performance at peak times.

Needless to say, of course, this is a better problem to have than having no one visiting the site at all. So we thank everybody for being dedicated players and coming back consistently to participate!

And we really appreciate how understanding all of you have been during this whole experience. Thanks for bearing with us!

Very gratefully,
The GXC Development Team

34 comments:

Sparky said...

800 queries for just the map? It's a wonder that the system has lasted this long. I bet you could load an entire page with only 20 to 30 queries.

Anonymous said...

If it is to be useful, I recommend you change the team chat into a threaded message board.

Brian said...

You should consider the idea of a reporting database, which is separate from the actual game play database.

Have only the data you need to display the map in this database, just the territory and player stat information. When a user goes to update something that occurs on the actual game play db, and you could sync the two ever 30 mins or something.

I would also check out Mysql Persistant connections if you havent't already, reusing connections can save the runtime it takes to establish a new connection. Especially if you have lots of people viewing the page at the same time, why have them all open a new connection when chances are there is already a connection open that someone has just finished using.

MySQL clustering isn't too hard to do either, I'm sure Pair can figure it out.

~Brian

Elwin said...

800 queries to generate the map? If I understand the turn procedure correctly, the map should only change once per turn, so couldn't you generate it once at midnight and cache it for the rest of the day? Not that I actually know anything about SQL...

Matt said...

What you may want to try doing is have the server generate one map for players to see when the turn starts. Then, as players place armies, the map is updated. When someone clicks on a territory to get extra information, then the map queries the server for more data.

Jhon Adams said...

brian has the right sort of idea, I haven't bothered to decompile your flash map, but really the map should be reading an XML file that generates all the data. if you cache one file for each team (so other teams cannot see other teams' movement) and use fpsasthru or readfile you're golden. Especially if you're using session variables to hold user data you wouldn't even have to make a single query, updating the cached XML file with a cronjob every 10 (or so) minutes.

In response to 800 queries... WTF? You could've even just done a "SELECT * FROM" from each table and written a simple object ot pull the data you actually want and it would've been more efficient. I'd look into implementing smart database objects especially since those can cache themselves and result in you never querying the same data twice. (mm... $Users->findById($user_id) there are a couple interesting implementations of this lying around, basically being designed after the models used in ruby on rails).

Brian said...

I don't think you'll find any XML in there, last time I checked they relied heavily on http://www.amfphp.org/ but I might be wrong

Jhon Adams said...

I'd like to retract my previous statement regarding cronjobs. The job should actually be called when troops are placed or ordered. this would mean that if everyone has placed/ordered troops prior to 20:00, no matter how many people refresh the map, there will be no queries to the database. If 1 person does place troops, you have the troop placement queries and then the queries required to recache the map.

This can result in some clobbering if done incorrectly (player 1 submits their placement, queries the database, thread is suspended. Player 2 submits their placement, queries and writes the cache. Player 1's thread resumes, writes the cache.) but this can be resolved with either file locking or timestamps.

Jhon Adams said...

@brian: Well, there's there first mistake... XML and flash is very easy and lightweight, all the features to do it are built into both languages.

Sparky said...

Really, even cutting down the number of queries would be a good start. The start to doing that is good DB design.

CREATE TABLE territories
{
territory_id INTEGER UNSIGNED,
owner_army_id INTEGER UNSIGNED,
user_id INTEGER UNSIGNED,
army_count INTEGER UNSIGNED,
KEY territory_id,
KEY owner_army_id,
KEY user_id
};

SELECT territory_id, SUM(army_count) AS army_sum FROM territories GROUP BY territory_id;

CREATE TABLE commands
{
territory_id INTEGER UNSIGNED,
user_id INTEGER UNSIGNED,
command_id INTEGER UNSIGNED,
target_territory INTEGER UNSIGNED,
army_count INTEGER UNSIGNED,
KEY territory_id,
KEY target_territory,
KEY user_id
};

SELECT territory_id, user_id, command_id, target_territory, army_count FROM commands, territories WHERE commands.territory_id = territories.territory_id AND territories.owner_army_id = %i;

There, 2 commands that pull the bulk (if not all of) of the map data. All that's really left is the query to pull the physical map data.

That's a total of 3 queries. That is an acceptable level.

Forgive any mistakes in the SQL. It's been a while since I've done with with it and I threw this together quickly as an example.

This of course excludes much of the other data on the page. For example, the turn statistics should be stored in a table and aggregated as needed, same with the chat, and the user information.

----

Another good trick that has been mentioned is caching. Depending on how the data is retrieved from the server, it could be really easy to cache targeted data.

However, given the minuscule mount of data and the minuscule number of queries, that probably isn't needed for the time being.

---

SQL clustering is another performance improvement. However this only addresses the short term affects and not the underlying cause of the problem.

---

Sharding the game servers is another option. Instead of hosting multiple games on each server, host only a small handfull of games on each server.

---

But what ever happens, all I know is that at 7:00PM today, the servers because unresponsive. that's 1 1/2 hours earlier than yesterday.

Sean Mehra said...

Hey Everybody,

Thanks for your comments. To address you comments very briefly, our map is dynamic, which means it updates the placed armies in each territory on each page load. Of course, we could publish a static map at the beginning of each turn, but we feel like that reduces some of the strategy involved in when teams should place their armies (which reveals their battle plans to opponents), and also leaves too many Commanders and strategy planners in the dark.

Why 800? Because we just never hard to time optimize it after the first time we coded it. One of our team members has been able to reduce this number to 18 queries! that's a 98% drop! We're really excited about the improvements this makes.

Sean Mehra said...

Also, any of you guys looking for a job or a summer internship? You all seem to be really into this stuff and savvy on the tech side of things. We're hiring, and we're looking for fun people to work with that love the game as much as we do. If so, contact us at contact@gocrosscampus.com! :)

Coach Factory said...

hollister, karen millen, soccer shoes, herve leger, lululemon, celine handbags, new balance shoes, ferragamo shoes, vans, nike air max, abercrombie and fitch, hogan, wedding dresses, mcm handbags, ghd, converse shoes, ugg boots, reebok outlet, oakley pas cher, ray ban pas cher, rolex watches, louis vuitton, ugg, soccer jerseys, insanity, louis vuitton, yoga pants, bottega, lancel, ugg boots, mont blanc, marc jacobs, air max, juicy couture outlet, p90x3, birkin bag, hermes, mulberry, north face outlet, supra shoes, jimmy choo, nfl jerseys, valentino, timberland, asics gel, chi flat iron, montre pas cher, ralph lauren, nike free, beats by dre

Coach Factory said...

hollister, karen millen, soccer shoes, herve leger, lululemon, celine handbags, new balance shoes, ferragamo shoes, vans, nike air max, abercrombie and fitch, hogan, wedding dresses, mcm handbags, ghd, converse shoes, ugg boots, reebok outlet, oakley pas cher, ray ban pas cher, rolex watches, louis vuitton, ugg, soccer jerseys, insanity, louis vuitton, yoga pants, bottega, lancel, ugg boots, mont blanc, marc jacobs, air max, juicy couture outlet, p90x3, birkin bag, hermes, mulberry, north face outlet, supra shoes, jimmy choo, nfl jerseys, valentino, timberland, asics gel, chi flat iron, montre pas cher, ralph lauren, nike free, beats by dre

Coach Factory said...

tiffany and co, longchamp, michael kors, louis vuitton outlet online, kate spade outlet, ray ban, chanel handbags, true religion outlet, polo ralph lauren outlet, nike shoes, oakley sunglasses, coach outlet store, polo ralph lauren, jordan shoes, louis vuitton handbags, louboutin, coach outlet, ray ban sunglasses, tory burch outlet, toms shoes, air max, ray ban sunglasses, coach factory, oakley sunglasses, nike air max, oakley sunglasses, burberry outlet online, true religion, nike free, air max, louis vuitton, coach purses, ralph lauren, prada outlet, coach factory outlet, louboutin, michael kors outlet, burberry, longchamp outlet, oakley sunglasses, true religion jeans, gucci outlet, air jordan, michael kors outlet, oakley sunglasses, louboutin, burberry outlet online, nike air max, nike free, prada handbags

Coach Factory said...

tiffany and co, longchamp, michael kors, louis vuitton outlet online, kate spade outlet, ray ban, chanel handbags, true religion outlet, polo ralph lauren outlet, nike shoes, oakley sunglasses, coach outlet store, polo ralph lauren, jordan shoes, louis vuitton handbags, louboutin, coach outlet, ray ban sunglasses, tory burch outlet, toms shoes, air max, ray ban sunglasses, coach factory, oakley sunglasses, nike air max, oakley sunglasses, burberry outlet online, true religion, nike free, air max, louis vuitton, coach purses, ralph lauren, prada outlet, coach factory outlet, louboutin, michael kors outlet, burberry, longchamp outlet, oakley sunglasses, true religion jeans, gucci outlet, air jordan, michael kors outlet, oakley sunglasses, louboutin, burberry outlet online, nike air max, nike free, prada handbags

Coach Factory said...

ray bans, cheap oakley sunglasses, oakley sunglasses cheap, gucci, canada goose, moncler outlet, moncler, oakley vault, oakley sunglasses outlet, cheap oakley sunglasses, raybans, canada goose, oakley sunglasses outlet, canada goose, oakley outlet, cheap sunglasses, cheap oakley sunglasses, oakley sunglasses outlet, ralph lauren, ray ban, oakley sunglasses cheap, oakley sunglasses cheap, moncler, moncler, cheap sunglasses, oakley vault, canada goose, cheap sunglasses, ray ban, oakley sunglasses outlet, oakley sunglasses cheap, ray ban outlet, ugg boots, oakley outlet, oakley sunglasses cheap, oakley vault, oakley vault, oakley sunglasses outlet, canada goose outlet, oakley outlet, cheap sunglasses, oakley vault, oakley, louboutin,

Coach Factory said...

ray bans, cheap oakley sunglasses, oakley sunglasses cheap, gucci, canada goose, moncler outlet, moncler, oakley vault, oakley sunglasses outlet, cheap oakley sunglasses, raybans, canada goose, oakley sunglasses outlet, canada goose, oakley outlet, cheap sunglasses, cheap oakley sunglasses, oakley sunglasses outlet, ralph lauren, ray ban, oakley sunglasses cheap, oakley sunglasses cheap, moncler, moncler, cheap sunglasses, oakley vault, canada goose, cheap sunglasses, ray ban, oakley sunglasses outlet, oakley sunglasses cheap, ray ban outlet, ugg boots, oakley outlet, oakley sunglasses cheap, oakley vault, oakley vault, oakley sunglasses outlet, canada goose outlet, oakley outlet, cheap sunglasses, oakley vault, oakley, louboutin,

co wendy said...

christian louboutin shoes, cheap jordans , Discount LV Handbags, Christian Louboutin Bois Dore, cheap christian louboutin, ugg soldes, cheap nike jordan shoes, uggs outlet, Bags Louis Vuitton, ugg pas cher, ugg australia, ugg boots, christian louboutin, Discount Louis Vuitton, wholesale jordan shoes, Cheap Louis Vuitton Handbags, Cheap LV Handbags, discount nike jordans, ugg, uggs on sale, discount christian louboutin, christian louboutin remise 50%, Christian Louboutin Daffodile

林磊 said...

2015-10-23leilei
Ray-Ban Sunglasses
Jordan 3 Retro For Cheap
Michael Kors Handbags Outlet Online
michael kors outlet
Canada Goose Outlet Online
nike shorts,nike hoodies,nike town,new nike shoes,nike tennis shoes,air max 97,nike shop,nike basketball
Jordan Retro 13 Hot Sale
louis vuitton outlet stores
michael kors outlet online
Coach Factory Outlet
ray-ban sunglasses,ray ban sunglasses,ray bans,rayban,ray ban wayfarer,raybans,ray ban glasses,ray ban aviators,ray ban clubmaster,ray ban eyeglasses,cheap ray bans,ray bans sunglasses,ray ban aviator,ray bands,fake ray bans,ray ban prescription glasses,ray ban outlet,ray ban canada,ray ban sunglasses sale,ray ban sale
cheap uggs
michael kors uk
mihchael kors bag
fitflops sale clearance
louis vuitton outlet online
Abercrombie Outlet Store Online
authentic louis vuitton handbags
jordan shoes
michael kors outlet
oakley sunglasses cheap
tory burch sale
michael kors uk
uggs outlet
cheap ugg boots
cheap ugg boots
Louis Vuitton Handbags Official Site
Michael Kors Outlet Online Sale
ralph lauren uk
Air Jordan 8

林磊 said...

2015-10-23leilei
Ray-Ban Sunglasses
Jordan 3 Retro For Cheap
Michael Kors Handbags Outlet Online
michael kors outlet
Canada Goose Outlet Online
nike shorts,nike hoodies,nike town,new nike shoes,nike tennis shoes,air max 97,nike shop,nike basketball
Jordan Retro 13 Hot Sale
louis vuitton outlet stores
michael kors outlet online
Coach Factory Outlet
ray-ban sunglasses,ray ban sunglasses,ray bans,rayban,ray ban wayfarer,raybans,ray ban glasses,ray ban aviators,ray ban clubmaster,ray ban eyeglasses,cheap ray bans,ray bans sunglasses,ray ban aviator,ray bands,fake ray bans,ray ban prescription glasses,ray ban outlet,ray ban canada,ray ban sunglasses sale,ray ban sale
cheap uggs
michael kors uk
mihchael kors bag
fitflops sale clearance
louis vuitton outlet online
Abercrombie Outlet Store Online
authentic louis vuitton handbags
jordan shoes
michael kors outlet
oakley sunglasses cheap
tory burch sale
michael kors uk
uggs outlet
cheap ugg boots
cheap ugg boots
Louis Vuitton Handbags Official Site
Michael Kors Outlet Online Sale
ralph lauren uk
Air Jordan 8

Cran Jane said...

Tory Burch Flats Marc Jacobs Handbags Jimmy Choo Shoes Jimmy Choos True Religion Outlet ED Hardy Outlet
Burberry Belt Tory Burch Boots Louis Vuitton Belt Ferragamo Belt Marc Jacobs Handbags Lululemon Outlet Christian Louboutin Shoes True Religion Outlet Tommy Hilfiger Outlet
Michael Kors Outlet Coach Outlet Red Bottoms Kevin Durant Shoes New Balance Outlet Adidas Outlet Coach Outlet Online Stephen Curry Jersey Vans Outlet Ralph Lauren Outlet
Ugg Boots Sale UGGS For Women Skechers Go Walk Adidas Yeezy Boost Adidas Yeezy Adidas NMD Coach Outlet North Face Outlet Ralph Lauren Outlet Puma Outlet Polo Outlet

Fangyaya said...

hollister kids
asics running shoes
oakley outlet
cheap jordans
christian louboutin wedges
designer handbags
rolex submariner price
air jordan shoes
michael kors outlet online
cartier watches outlet
louis vuitton handbags
gucci outlet
basketball shoes
ralph lauren polo shirts
adidas yeezy
timberlands
toms shoes uk
adidas yeezy 350
louis vuitton bags
celine outlet
michael kors handbags
kate spade outlet
michael kors outlet online
cheap oakley sunglasses
polo ralph lauren
true religion jeans
louboutin femme
ray ban sunglasses outlet
fitflop shoes
louis vuitton handbags
oakley sunglasses outlet
timberland shoes
polo shirts
louis vuitton
ray ban sunglasses
cheap oakleys
ralph lauren clearance outlet
coach outlet
michael kors outlet clearance
ralph lauren
20168.3chenjinyan

Meiqing Xu said...

canada goose parka
ray ban sunglasses
cheap nhl jerseys
white converse
oakley sunglasses
christian louboutin
ralph lauren
ralph lauren
coach factory outlet
reebok shoes
tiffany jewelry
toms shoes
celine outlet
oakley vault
adidas pure boost
christian louboutin shoes
coach factory outlet
ed hardy
moncler outlet
air max 90
christian louboutin shoes
canada goose outlet
cheap nike shoes
coach outlet online
ugg boots sale
uggs on sale
coach outlet
coach outlet
cheap oakley sunglasses
jimmy choo outlet store
longchamp outlet
ray ban sunglasses
louboutin uk
adidas nmd
canada goose jackets
zhuo20160816

mmjiaxin said...

christian louboutin outlet
fitflops
kobe shoes,kobe zoom,nike zoom kobe
http://www.rayban-sunglasses.uk
tiffany and co uk
ralph lauren online
michael jordan shoes
ugg outlet
michael kors outlet online
lacoste outlet
gg

eric yao said...

Coach Outlet http://www.coachstoreoutlet.us.org

Christian Louboutin Shoes http://www.christianlouboutinshoe.eu.com

Valentino Shoes http://www.valentinoshoes.eu.com

Michael Kors Outlet http://www.michaelkorsoutlet.us.org

Coach Factory Outlet http://www.coachfactoryoutlet.us.org

Coach Outlet Online http://www.coachoutletonlinestore.us.org

Coach Purses http://www.coachpurse.us.com

Kate Spade Outlet http://www.katespadefactoryoutlet.us.com

Toms Shoes http://www.toms-shoes.name

Hermes Belts http://www.hermesbelts.us.org

Louis Vuitton http://www.louisvuitton-outletstores.us.com

Fendi Handbags http://www.fendibelt.us.com

Giuseppe Shoes http://www.giuseppezanotti.eu.com

Michael Kors Outlet http://www.michael-kors-outlet.us.com

Stephen Curry Shoes http://www.stephencurryshoes.us.com

Salomon Shoes http://www.salomonshoes.eu.com

North Face Outlet http://www.northfacesoutlet.in.net

Coach Outlet http://www.outletcoach.in.net

North Face Outlet http://www.northfacesoutlet.us.com

Burberry Outlet http://www.burberryoutletstore.us.org

North Face Outlet http://www.north-faceoutlets.in.net

North Face Jackets http://www.outletnorthface.in.net

Skechers Shoes http://www.skechersshoes.us.com

Toms Outlethttp://www.tomsoutletstore.us.org

North Face Outlet http://www.northfacestoreoutlet.us.org

Nike Outlet http://www.nikeoutlet.us.org

Nike Hoodies http://www.nikehoodies.us.com

Marc Jacobs Handbags http://www.marcjacobshandbags.name

Marc Jacobs Outlet http://www.marcjacobsoutletstore.us.com

Jimmy Choo Shoes http://www.jimmy-chooshoes.us.com

Jimmy Choos http://www.jimmychoos.in.net

Burberry Belt http://www.burberrybelt.us.com

Louis Vuitton Belt http://www.louisvuitton-belt.com

Salvatore Ferragamo http://www.ferragamo-belt.us.com

Marc Jacobs Handbags http://marcjacobshandbagsoutlet.blog.com

Lululemon Outlet http://www.lululemonsoutlet.us.com

True Religion Outlet http://www.truereligion-outlets.in.net

Tommy Hilfiger http://www.tommyhilfigeroutlet.us.com

Michael Kors Outlet http://www.outletmichaelkors.eu.com

Coach Outlet http://www.outletcoach.eu.com

Red Bottoms http://www.redbottom.in.net

Kevin Durant Shoes http://www.kevindurantshoes.us.org

New Balance Outlet http://www.newbalanceoutlet.in.net

Adidas Outlet http://www.adidasoutlet.in.net

Coach Outlet Online http://www.coachoutlet-online.eu.com

Stephen Curry Jersey http://www.stephencurryjersey.us.com

Vans Outlet http://www.vansoutlet.us.com

Ralph Lauren Outlet http://www.ralphlauren-outletstore.in.net

True Religion Outlet http://www.true-religionoutlets.in.net

ED Hardy Outlet http://www.edhardyoutlet.in.net

North Face Outlet http://www.northfaceoutlet.eu.com

UGG Outlet http://www.uggoutlet.us.org

UGG Outlet http://www.outletugg.in.net

North Face Outlet http://www.northface--outlet.us.com

Ugg Boots Sale http://www.uggbootssale.in.net

UGGS For Women http://www.uggsforwomen.us.com

Skechers Go Walk http://www.skechersgowalk.us.com

Adidas Yeezy Boost http://www.adidasyeezyboost.in.net

Adidas Yeezy http://www.adidasyeezy.us.com

Adidas NMD http://www.adidas-nmd.us.org

Coach Outlet http://www.coachoutletinc.us.com

Unknown said...

oakley sunglasses
ecco
jets jersey
giants jersey
ugg boots
chicago bulls
coach outlet online
michael kors handbags wholesale
oakley sunglasses
abercrombie and fitch

dong dong23 said...

yeezy boost 350 v2
converse shoes
polo ralph lauren
ralph lauren polo shirts
coach factory online
nike air force 1
nike air max uk
ralph lauren
columbia outlet
michael kors
20174.26wengdongdong

龙大猫 said...

nike mercurial vapor
ferragamo sale
nike roshe one
longchamp handbags
michael kors handbags sale
yeezy shoes
adidas neo
basketball shoes
light up shoes
timberland boots

GIL BERT said...

ugg boots
ugg outlet
oakley sunglasses wholesale
air jordan 8
ugg boots
nba jerseys
oakley sunglasses
ray ban sunglasses
washington redskins jerseys
los angeles clippers

GIL BERT said...

chargers jerseys
michael kors
michael kors handbags
yeezy boost 350 black
falcons jersey
bengals jersey
oakley sunglasses
michael kors uk
coach outlet online
yeezy shoes

林磊 said...

20170612 leilei3915
adidas nmd r1
true religion jeans
cheap jordans for sale
oakley sunglasses
ralph lauren uk
canada goose
coach outlet online
coach factory outlet
ralph lauren
michael kors outlet

raybanoutlet001 said...

polo ralph lauren
ugg boots
ugg outlet
michael kors outlet
jordan shoes
tennessee titans jersey
polo ralph lauren
coach factory outlet
yeezy boost 350 white
ugg boots

shengda xu said...

tory burch outlet online
yeezy shoes
coach factory outlet
pandora charms outlet
air max shoes
jordan retro
yeezy boost 350
nike uk
clarks shoes outlet
mulberry
xsd7.28