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

29 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

Anonymous 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...

Anonymous 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.

Anonymous 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).

Anonymous 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.

Anonymous 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! :)

Unknown 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

Unknown 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

Unknown 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

Unknown 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

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

Unknown 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

Unknown 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

Unknown 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

Unknown 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

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

Unknown 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

Unknown said...

WWW0425

michael kors outlet
coach outlet
canada goose outlet
red bottom
minnesota vikings jerseys
oakley glasses
adidas shoes
tods shoes
valentino
canada goose outlet

Obat Tradisional Benjolan di Pangkal Paha/Selangkangan said...

This article is interesting and useful. Thank you for sharing. And let me share an article about health that God willing will be very useful. Thank you :)

Obat Penghancur Benjolan di Leher
Obat Luka Berair yang Tak Kunjung Sembuh
Pengobatan efektif Menghilangkan Benjolan di Payudara
Cara Mengobati Sakit Dada secara Alami
Cara Mengobati sakit Telinga Secara Alami
Obat Gusi Bengkak Bernanah Tradisional

QQQQQQQQQ said...

air force 1
louboutin shoes uk
retro jordans
balenciaga shoes
cheap oakley sunglasses
nike air max 90
vibram fivefingers
cheap jordans
vans shoes
yeezy boost 350

QQQQQQQQQ said...

moncler jackets
fila
converse outlet
yeezy boost
birkin bag
hermes handbags
nike air max 2018
mlb jerseys
nike air huarache
air max 270

Unknown said...

qzz0712
jimmy choo sunglasses
undefeated clothing
belstaff jackets
oakley sunglasses wholesale
guess factory
balenciaga sneakers
golden state warriors jerseys
football shirts
gucci outlet
coach canada

Anonymous said...

Twenty first century kids (and their parents) will find themselves captivated by Dowley's modernized version of this seventeenth century classic. Illustrator Steve Smallman's brilliant artwork combines with Dowley's storytelling to create a fresh, vibrant Ray Ban Outlet effect. While some of Ray Ban Glasses the prose (and wardrobe) have been updated, this version of The Pilgrim's Coach Outlet Store Progress remains a wonderful way to emphasize morality and values with your children in a manner that will capture their attention and imagination..

AbstractThe Neoproterozoic strata of Coach Handbags Clearance the Sichuan Basin are a key target for oil and gas. To Yeezy Boost 350 evaluate the hydrocarbon evolution and its relationship with tectonic events in the Micang Shan uplift, New Jordan Shoes 2020 northernmost Coach Outlet Sichuan Basin, we Yeezy Discount apply solid bitumen geochemistry (bitumen reflectance and fluorescence) and Re Os geochronology. Geochemistry of the solid bitumen indicates that it is highly mature pyrobitumen that formed contemporaneously with dry gas generation during oil thermal cracking.

beautymach said...

Look for your most well-liked sport and study all about it so you can start half in} like a professional. Welcome bonuses are a typical practice and usually encompass a sum of money that you just get when joining a web-based casino. These welcome bonuses are sometimes free from any deposit requirement, meaning that you just wouldn't have to place down any money begin out|to begin} half in}. It is a good method to get conversant in the site verify out|and take a glance at} a few 카지노 of the the} video games with none funding. Last 12 months, the worldwide gaming market comprised three billion customers, with a total revenue of $180 billion; cell gaming revenue accounted for over 50% of the market share, based on Newzoo.