Running 100+ MySQL Update Queries. Optimization Tips Please :)

Question

I'm working on this game titled "Fortress", located at http://www.joemajewski.com/fortress. Anyways, it's one of those browser-based role-playing games where players build an army and upgrade their stats to get a high ranking on the leaderboard.

Every 30 minutes a cron job is going to execute, which makes some updates. It loops through each player of the game (which will continue to rise naturally over time as more people join), and updates certain stats. It gives everyone 1 extra turn, gives them gold based on their income, food, wood, copper, iron, etc..., plus it updates their ranking.

That's all mumbo-jumbo, I know, but the point is that each player is going to have their own update query. I timed the script with just three members:

execution time with mysql_query() function commented out for the updates: ~ 3.5 ms

execution time with mysql_query() function working properly: ~ 14 ms

It is clearly the update queries that are going to cause my cron job to get very slow once the game has 100+ registered members. I am hoping to have 100 active players within two weeks of the game's launch, but many players create multiple accounts, so I expect at least 200 members after a couple weeks. With 200 update queries, plus the extra time to sort members and calculate rankings, it will likely take over 2 seconds to run the cron job.

My question to you. Is there a faster way to do something like this? I tried adding a "START TRANSACTION" and "COMMIT" queries before and after the loop, respectively, but it's hard to gauge speed with just 3 members. Will transactions make the script better or worse? Any other way around this? Any help would be appreciated.

Thank you very much for taking the time to listen to my plea.

Answer 1

Based on the limited details in the question, it sounds like you may be able to do all of the player updates in one query or a few queries, rather than looping through all of them individually.

Consider adding turns, and updating their gold based on a formula, all in one query that affects the whole table.

i.e. as a basic oversimplification:

update Players set turns = turns + 1, gold = (gold + (income * 100) + (wood * 25) + (copper * 25))

Does this help?.

As for the ranking, how are you calculating it right now?

Answer 2

My suggestion would be to create a test environment. Then populate that test environment with thousands of rows of user data. For an idea on how much data to put in, I use the rule of thumb of ((What You Expect) + (What You Hope For)) * 2. So if you expcect 100 users, and hope (a realistic hope) to get 5000, you should test for at least 10200 users...

So create the large database, and try to be as realistic as possible, but don't spend too much energy on that. Once you're there, try your queries one at a time. Optimize each. Then build a simple PHP script to execute a number of queries at different delays (query1, sleep 50ms, query2, sleep 100ms, query3, query4, sleep 100ms, etc). Then use a tool like ab (Apache Bench) to concurrently execute that script and look for issues in the db (remember, the task here is to stress the DB, so if it's not stressed out, keep pushing until it is).

Answer 3

You are doing wrong.

Calculate resource amount whenever someone accesses user resource data. You don't need to calculate those data when no one accesses them.

Store last time when data were calculated (lt).

Resource amount now is: r2=r+dr*(now-lt) where
dr - resource change in time now - time now lt - time when last r save to database was made. r - resource amount stored in db r2 - resource amount now

You even dont need to update table every time, just read r, dr, lt from database, calculate r2 now.

Doing this way you will get real time amounts of resources and you no corn jobs.