A better way to handle large mysql tasks?

[TEENFRONT]

Hi people, heres hoping you can offer some much needed advice.

 

We run a large online multiplayer flash game with >20,000 users and alot of data for each user. every few hours we have a cron to calculate players ratings, which means pulling each games played/won and updating the rating if its different to the current rating. While all this is happening, the game is still accessing and updating the players etc.

 

But still, running this task is a great server pull, im wondering if theres a way to stagger this update, like only do x % of the db each 30mins to keep the pull on the db to a minimum.

 

We also have a similiar more complex cron that runs every 4 hours which involves the same kinda thing but groups the users who are in a team, adds up the ratings, and updates the teams scores and ratings.

 

Again, looking if theres any way to stagger these updates?

[samshel]

1) Get a new hardware for running the crons seperately

2) Update scores/ratings at the end of game itself.

[TEENFRONT]

1) Get a new hardware for running the crons seperately

2) Update scores/ratings at the end of game itself.

 

1) we have a separate mysql box.

2) we tried that, but we have around 50,000 games played per day, so this added >100,000 queries to the server, moving it to a cron, freed up alot of resources but still when the cron does run it sucks the server dry.

 

Thanks for the suggestions though.

[samshel]

3) Optimize Cron queries, sometimes denormalizing database helps to gain performance. you may wish to distribute the operations between cron and end of game. Like instead of doing complete operation of rating, just do some calculations and store in fields in DB, Cron will refer to these fields and complete the operation.

4) If the system is already database/code optimized (indexes, less queries, de normalized) then you can go for MySQL replication.

[redarrow]

also sounds like a bandwidth issue, ever looked,

 

as the web site is growing, and more resources are being used, the bandwidth,

get less.

 

if you feel that the web site it self is lagging then it defiantly bandwidth issue.

 

(( dont tell me it all flash games ((scream lol