Jump to content

Running a MMORPG - Would like optimising advice

BRUm

By BRUm
in MySQL Help

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.

Archived

This topic is now archived and is closed to further replies.

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.