Jump to content

Running a MMORPG - Would like optimising advice

BRUm

By BRUm
in MySQL Help

 Share

Recommended Posts

BRUm Newbie

BRUm

Posted
Posted

Hello everyone,

 

I run a small MMORPG and I was wondering if any gurus around here would be so kind so as to share some wisdom :)

 

We run a script which is executed hourly by CRON. This script cycles through all ~5,200 of our members and updates their various in-game statistics etc. The problem is, we're hosted on a good server, but it's still putting quite excessive load on its resources. At the moment, the script takes 160-170 seconds to complete, and we recently managed to reduce the execution time by 10 seconds after we added 

mysql_free_result();

 

Now, this isn't bad, a few months ago we began optimising and managed to get it from 10-20 minutes! Crazy.

 

Anyway, the script is posted below, I'd be very grateful if anyone could share some tips, tricks or other SQL functions to help us get the time down even more.

 

[Warning the script is very long]

 

   1.
      <?
   2.
      INCLUDE "db_connect.php";
   3.
      $time1=time();
   4.
       
   5.
      $ol_y = mysql_query("select * from overlords where place = 'yuaktz'");
   6.
      $y_t=mysql_result($ol_y,0,"tax");
   7.
       
   8.
      $ol_s = mysql_query("select * from overlords where place = 'shleincyrodil'");
   9.
      $s_t=mysql_result($ol_s,0,"tax");
  10.
       
  11.
      $ol_gp = mysql_query("select * from overlords where place = 'great_plains'");
  12.
      $gp_t=mysql_result($ol_gp,0,"tax");
  13.
       
  14.
      $ol_wp = mysql_query("select * from overlords where place = 'western_province'");
  15.
      $wp_t=mysql_result($ol_wp,0,"tax");
  16.
       
  17.
      $select_weather = mysql_query("select * from `weather`");
  18.
      while($weather=mysql_fetch_array($select_weather))
  19.
      {
  20.
      $y_w=$weather['yuaktz'];
  21.
      if($y_w== "rain"){$y_c= 1.1;}
  22.
      if($y_w== "sunny"){$y_c= 1.15;}
  23.
      if($y_w== "over cast"){$y_c= 1;}
  24.
      $s_w=$weather['shleincyrodil'];
  25.
      if($s_w== "rain"){$s_c= 1.1;}
  26.
      if($s_w== "sunny"){$s_c= 1.15;}
  27.
      if($s_w== "over cast"){$s_c= 1;}
  28.
      if($s_w== "frosty"){$s_c= 0.75;}
  29.
      $gp_w=$weather['great_plains'];
  30.
      if($gp_w== "sunny"){$gp_c= 1.15;}
  31.
      if($gp_w== "over cast"){$gp_c= 1;}
  32.
      $wp_w=$weather['western_province'];
  33.
      if($wp_w== "rain"){$wp_c= 1.1;}
  34.
      if($wp_w== "frosty"){$wp_c= 0.75;}
  35.
      }
  36.
       
  37.
      ######################                        SELECTING USERS                                 #######################################
  38.
      $select_users=mysql_query("SELECT * FROM data WHERE bann='0' AND active > '0' ");
  39.
      while($sel_users=mysql_fetch_array($select_users))
  40.
      {
  41.
      $username=$sel_users['username'];
  42.
      $user_hashed=$sel_users['username_hashed'];
  43.
      $user_net=$sel_users['netscore'];
  44.
      $user_pop=$sel_users['pop'];
  45.
      $user_xp=$sel_users['xp'];
  46.
      $user_money=$sel_users['money'];
  47.
      $user_pop=$sel_users['pop'];
  48.
      $user_place=$sel_users['place'];
  49.
      $user_turns=$sel_users['turns'];
  50.
      $user_food=$sel_users['food'];
  51.
       
  52.
      if($user_place=='yuaktz'){$tax=$y_t/100;}
  53.
      if($user_place=='shleincyrodil'){$tax=$s_t/100;}
  54.
      if($user_place=='great_plains'){$tax=$gp_t/100;}
  55.
      if($user_place=='western_province'){$tax=$wp_t/100;}
  56.
      ############            CIV UNREST             ##################################################################
  57.
      $select_user_units=mysql_query("SELECT * FROM units WHERE username_hashed='$user_hashed' ");
  58.
      while($sel_units = mysql_fetch_array($select_user_units))
  59.
      {
  60.
      $user_hi = $sel_units['heavy_infantry'];
  61.
      $user_mi = $sel_units['medium_infantry'];
  62.
      $user_li = $sel_units['light_infantry'];
  63.
      $user_ww = $sel_units['war_weariness'];
  64.
      $user_as = $sel_units['army_str'];
  65.
      }
  66.
       
  67.
      $army_req = ((sqrt($user_net) + $user_pop) - ($user_as + sqrt($user_xp)));
  68.
      if($army_req<0){$army_req=1;}
  69.
      if($user_as<$army_req){$civil_unrest =number_format(($user_as/$army_req),'2');}else{$civil_unrest=1;}
  70.
      if($user_as==0 || $user_as<0){$civil_unrest=0;}
  71.
      ###############################################################################################
  72.
       
  73.
      $select_user_labs=mysql_query("SELECT * FROM buildings WHERE username_hashed='$user_hashed' ");
  74.
      while($sel_lab = mysql_fetch_array($select_user_labs))
  75.
      {
  76.
      $user_lab = $sel_lab['lab'];
  77.
      $user_farms = $sel_lab['farms'];
  78.
      $user_homes = $sel_lab['home'];
  79.
      }
  80.
       
  81.
      $profit=$user_pop*100*(1-$user_ww);
  82.
      $costs=$user_hi*100+$user_mi*50+$user_li*10;
  83.
      $gain=($profit-$costs)*$civil_unrest;
  84.
      $gain_taxed=$gain*(1-$tax);
  85.
      $gain_over=$gain*$tax;
  86.
      ////////////   OVERLORD CASH AND WEATHER BONUSES
  87.
      if($user_place=='yuaktz'){$oy_money=$oy_money+$gain_over;$food_bonus=$y_c;}
  88.
      if($user_place=='shleincyrodil'){$os_money=$os_money+$gain_over;$food_bonus=$s_c;}
  89.
      if($user_place=='great_plains'){$ogp_money=$ogp_money+$gain_over;$food_bonus=$gp_c;}
  90.
      if($user_place=='western_province'){$owp_money=$owp_money+$gain_over;$food_bonus=$wp_c;}
  91.
      ///////////////////////////////////////////////
  92.
      ######################       FAILSAFE     CHECK     ##########################
  93.
      $second_select=mysql_query("SELECT money,turns,food FROM data WHERE username_hashed='$user_hashed'");
  94.
      while($sel_sec = mysql_fetch_array($second_select))
  95.
      {
  96.
      $user_money=$sel_sec['money'];
  97.
      $user_turns=$sel_sec['turns'];
  98.
      $user_food=$sel_sec['food'];
  99.
      }
100.
       
101.
      ################################################################
102.
      if($user_food<$user_pop){$dead=$user_pop-$user_food;}else{$dead=0;}
103.
      $new_pop=$user_homes*10-$dead;
104.
      $food_production=(($user_farms*100)*$food_bonus)*$civil_unrest;
105.
      $new_user_money=$gain_taxed+$user_money;
106.
      $new_turns = (($user_turns+$user_lab)+10);
107.
      $new_user_food=$user_food+$food_production-$new_pop;
108.
      if($new_user_food<0){$new_user_food=0;}
109.
       
110.
      mysql_query("UPDATE data SET money='$new_user_money',turns='$new_turns',food='$new_user_food',pop='$new_pop' WHERE username_hashed='$user_hashed'");
111.
      if($user_ww>0)
112.
      {
113.
      $new_user_ww=$user_ww-0.05;
114.
      if($new_user_ww<=0){$new_user_ww=0;}
115.
      mysql_query("UPDATE units SET war_weariness='$new_user_ww' WHERE username_hashed='$user_hashed'");
116.
      }
117.
      }
118.
      ##############################################################################################
119.
      ######################################            EXPLORATIONS                                               #########################
120.
      $select_voyage=mysql_query("SELECT * FROM voyages");
121.
      while($voyage=mysql_fetch_array($select_voyage))
122.
      {
123.
      $explorer=$voyage['username_hashed'];
124.
      $timer=$voyage['time'];
125.
      $timer2=$timer-1;
126.
      $l_gain=$voyage['land_expected'];
127.
      $force=$voyage['away'];
128.
      $id=$voyage['id'];
129.
      if($timer==0)
130.
      {
131.
      $select_explorer=mysql_query("SELECT * FROM data WHERE username_hashed='$explorer'");
132.
      while($explo=mysql_fetch_array($select_explorer))
133.
      {
134.
      $user_land=$explo['land'];
135.
      }
136.
      $select_unite=mysql_query("SELECT * FROM units WHERE username_hashed='$explorer'");
137.
      while($unite=mysql_fetch_array($select_unite))
138.
      {
139.
      $user_lip=$unite['light_infantry'];
140.
      }
141.
      $new_land=$user_land+$l_gain;
142.
      $new_lis=$user_lip+$force;
143.
      mysql_query("UPDATE data SET land='$new_land' WHERE username_hashed='$explorer'");
144.
      mysql_query("UPDATE units SET light_infantry='$new_lis' WHERE username_hashed='$explorer'");
145.
      mysql_query("delete from `voyages` where `id` = '$id'");
146.
      //MSG
147.
      $t=time();
148.
      $message = "<i>Your <b>".$force."</b> soldiers have arrived back from their voyage. They have discovered <b>".$l_gain."</b> Acres of new land for your empire! - from <b>Your Chief Messenger</b> on <b>".date("d/m/y",$t)."</b> at <b>".date("H:i:s",$t)."</b></i>";
149.
      mysql_query("INSERT INTO `news` (`username_hashed`,`latest`)VALUES('$explorer','$message')");
150.
      ///////
151.
      }
152.
      else
153.
      {
154.
      mysql_query("UPDATE voyages SET time='$timer2' WHERE id='$id'");
155.
      }
156.
      }
157.
      ##################################################################################################
158.
      ###################################                           WEATHER            ###########################################
159.
      $weather1 = RAND(1,2);
160.
      $weather2 = RAND(1,2);
161.
      $weather3 = RAND(1,3);
162.
      $weather4 = RAND(1,4);
163.
      IF($weather1 == 1){$weath1 = "sunny";}
164.
      IF($weather1 == 2){$weath1 = "over cast";}
165.
       
166.
      IF($weather2 == 1){$weath2 = "frosty";}
167.
      IF($weather2 == 2){$weath2 = "rain";}
168.
       
169.
      IF($weather3 == 1){$weath3 = "rain";}
170.
      IF($weather3 == 2){$weath3 = "sunny";}
171.
      IF($weather3 == 3){$weath3 = "over cast";}
172.
       
173.
      IF($weather4 == 1){$weath4 = "rain";}
174.
      IF($weather4 == 2){$weath4 = "sunny";}
175.
      IF($weather4 == 3){$weath4 = "over cast";}
176.
      IF($weather4 == 4){$weath4 = "frosty";}
177.
      mysql_query("update `weather` set `great_plains` = '$weath1',`western_province` = '$weath2',`yuaktz` = '$weath3',`shleincyrodil` = '$weath4'");
178.
      ##################################################################################################
179.
       
180.
      $war_sel=mysql_query("select * from war_timer ");
181.
      while($war=mysql_fetch_array($war_sel))
182.
      {
183.
      $username_has = $war['username_hashed'];
184.
      $target = $war['target'];
185.
      $time1s=$war['time'];
186.
      $time2s=$time1s-1;
187.
      mysql_query("update `war_timer` set `time` = '$time2s'  WHERE username_hashed='$username_has' AND target='$target'");
188.
      }
189.
      mysql_query("delete from `war_timer` where `time` = '0'");
190.
      mysql_query("delete from `war_timer` where `time` < '0'");
191.
      #######################################               OVERLORD PAY              #############################################
192.
      $o_y = mysql_query("select * from overlords where place = 'yuaktz'");
193.
      $y_o=mysql_result($o_y,0,"username");
194.
      if($y_o!='-')
195.
      {
196.
      $sel_o=mysql_query("SELECT * FROM data WHERE username='$y_o'");
197.
      $y_m=mysql_result($sel_o,0,"money");
198.
      $new_money=number_format(($oy_money+$y_m),'0','','');
199.
      mysql_query("update `data` set `money` = '$new_money'  WHERE username='$y_o' ");
200.
      //MSG
201.
      $receiver=md5($y_o);
202.
      $message = "Sire You have received &#163; <b>".number_format($oy_money,'0')."</b> as your overlord pay.";
203.
      mysql_query("INSERT INTO `news` (`username_hashed`,`latest`)VALUES('$receiver','$message')");
204.
      ///////
205.
      }
206.
       
207.
      $o_s = mysql_query("select * from overlords where place = 'shleincyrodil'");
208.
      $s_o=mysql_result($o_s,0,"username");
209.
      if($s_o!='-')
210.
      {
211.
      $sel_o=mysql_query("SELECT * FROM data WHERE username='$s_o'");
212.
      $s_m=mysql_result($sel_o,0,"money");
213.
      $new_money=number_format(($os_money+$s_m),'0','','');
214.
      mysql_query("update `data` set `money` = '$new_money'  WHERE username='$s_o' ");
215.
      //MSG
216.
      $receiver=md5($s_o);
217.
      $message = "Sire You have received &#163; <b>".number_format($os_money,'0')."</b> as your overlord pay.";
218.
      mysql_query("INSERT INTO `news` (`username_hashed`,`latest`)VALUES('$receiver','$message')");
219.
      ///////
220.
      }
221.
       
222.
      $o_gp = mysql_query("select * from overlords where place = 'great_plains'");
223.
      $gp_o=mysql_result($o_gp,0,"username");
224.
      if($gp_o!='-')
225.
      {
226.
      $sel_o=mysql_query("SELECT * FROM data WHERE username='$gp_o'");
227.
      $gp_m=mysql_result($sel_o,0,"money");
228.
      $new_money=number_format(($ogp_money+$gp_m),'0','','');
229.
      mysql_query("update `data` set `money` = '$new_money'  WHERE username='$gp_o' ");
230.
      //MSG
231.
      $receiver=md5($gp_o);
232.
      $message = "Sire You have received &#163; <b>".number_format($ogp_money,'0')."</b> as your overlord pay.";
233.
      mysql_query("INSERT INTO `news` (`username_hashed`,`latest`)VALUES('$receiver','$message')");
234.
      ///////
235.
      }
236.
       
237.
      $o_wp = mysql_query("select * from overlords where place = 'western_province'");
238.
      $wp_o=mysql_result($o_wp,0,"username");
239.
      if($wp_o!='-')
240.
      {
241.
      $sel_o=mysql_query("SELECT * FROM data WHERE username='$wp_o'");
242.
      $wp_m=mysql_result($sel_o,0,"money");
243.
      $new_money=number_format(($owp_money+$wp_m),'0','','');
244.
      mysql_query("update `data` set `money` = '$new_money'  WHERE username='$wp_o' ");
245.
      //MSG
246.
      $receiver=md5($wp_o);
247.
      $message = "Sire You have received &#163; <b>".number_format($owp_money,'0')."</b> as your overlord pay.";
248.
      $timeris=time();
249.
      mysql_query("INSERT INTO `news` (`username_hashed`,`latest`,`time`)VALUES('$receiver','$message','$timeris')");
250.
      ///////
251.
      }
252.
      ############################     RESETING ELECTION TABLE
253.
      $check_votes_num2=mysql_query("SELECT * FROM `voting`");
254.
      $num_checkings=mysql_num_rows($check_votes_num2);
255.
      $t=time();
256.
      $date=date("D",$t);
257.
      #echo"$date";
258.
      if($date=='Sun' && $num_checkings>=0)
259.
      {
260.
      mysql_query("TRUNCATE TABLE `voting`");
261.
      mysql_query("UPDATE `election_nominees` SET `votes`='0'");
262.
      }
263.
      ########################################################################################################
264.
      $time2=time();
265.
      $time3=$time2-$time1;
266.
      #echo"Execution time : $time3 seconds";
267.
      #echo"<br>Taxes : y=$y_t , s=$s_t , gp=$gp_t , wp=$wp_t";
268.
      #echo"<br>Overlord money: y=$oy_money , s=$os_money , gp=$ogp_money , wp=$owp_money";
269.
      mysql_query("INSERT INTO `execution` (`time`,`exec`)VALUES('$time2','$time3')");
270.
      echo"$num_checkings";
271.
      ?>

 

Thanks!

 

Lee.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.