Hi, recently I was suspended again for high resource usage, though the scripts being viewed by visitors don't use much resources, the scripts that update the cached data use a lot.
I'm in this situation:
There's an online game called "Tribal Wars", it has 13 English servers and I would like to support 11 of them already, preferably all servers, even the none English ones, but that might be later.
The game provides the map data, points of players, number of villages they own etc to the players who wish to use it to create an application, it is formatted like this:
Line 1: field1,field2,field3,urlencode(field4)
Line 2: field1,field2,field3,urlencode(field4)
I have to urldecode field 4, change it's character encoding from UTF-8 to ISO8859-1 (UTF-8 sometimes gives odd problems in PHP due to lack of multibyte support), replace ' by \\' and mysql_escape_string it. Then I have to insert those fields into a database. Best would be to just load it directly into the database using LOAD DATA, but MYSQL can't urldecode a field, and I don't know how to replace those ' by \\' in MYSQL either. So to insert them, I have to open a stream with the server, parse each line in PHP using fgets and then insert them line by line into a database. This isn't efficient especially for the large servers with 300000 villages, 100000 players and 20000 tribes, that would make 420000 MYSQL queries for such a server. If the memory limit was higher, I could load the data into the memory and create an appropriate INSERT query using PHP, which would be far more efficient.
Does anyone have an idea how I can efficiently load the data from such a file into the database, changing one of the fields as I described without loading it into the memory? Currently I am only able to support one server updating each 8 hours, and 7 others updating daily. Normally it would be best to update each server's data at least each 4 hours, so the script would need to be way more efficient than it is currently. This is the source of the script that updates the database for a specific server:
This is the script that updates the "0" "1" "2" "3" "4" "5" and "6" fields, they are used to track the point grow of a village for a week (numbers representing the days), it is run by a cron job each server morning. It doesn't need to be optimized I believe, as it is pretty efficient already.
I ommited the connect and select database lines, as they are unnecessary in this example.
I'm in this situation:
There's an online game called "Tribal Wars", it has 13 English servers and I would like to support 11 of them already, preferably all servers, even the none English ones, but that might be later.
The game provides the map data, points of players, number of villages they own etc to the players who wish to use it to create an application, it is formatted like this:
Line 1: field1,field2,field3,urlencode(field4)
Line 2: field1,field2,field3,urlencode(field4)
I have to urldecode field 4, change it's character encoding from UTF-8 to ISO8859-1 (UTF-8 sometimes gives odd problems in PHP due to lack of multibyte support), replace ' by \\' and mysql_escape_string it. Then I have to insert those fields into a database. Best would be to just load it directly into the database using LOAD DATA, but MYSQL can't urldecode a field, and I don't know how to replace those ' by \\' in MYSQL either. So to insert them, I have to open a stream with the server, parse each line in PHP using fgets and then insert them line by line into a database. This isn't efficient especially for the large servers with 300000 villages, 100000 players and 20000 tribes, that would make 420000 MYSQL queries for such a server. If the memory limit was higher, I could load the data into the memory and create an appropriate INSERT query using PHP, which would be far more efficient.
Does anyone have an idea how I can efficiently load the data from such a file into the database, changing one of the fields as I described without loading it into the memory? Currently I am only able to support one server updating each 8 hours, and 7 others updating daily. Normally it would be best to update each server's data at least each 4 hours, so the script would need to be way more efficient than it is currently. This is the source of the script that updates the database for a specific server:
PHP:
if ($_GET['server']&&$_GET['server']<=11&&$_GET['server']>=1) {$server = $_GET['server'];
if ($villages = fopen('http://en'.$server.'.tribalwars.net/map/village.txt', 'r')) {
mysql_query("TRUNCATE TABLE village".$server); while (!feof($villages)) {$vil = explode(",", fgets($villages));
$vil[1] = mysql_real_escape_string(str_replace("'", "\\\\'", mb_convert_encoding(urldecode($vil[1]), "ISO-8859-1", "UTF-8")));
mysql_query("INSERT INTO `village$server` SET `village`='$vil[1]', `id`='$vil[0]', `x`='$vil[2]', `y`='$vil[3]', `player`='$vil[4]', `points`='$vil[5]'");}; fclose($villages);
mysql_query("UPDATE `village".$server."`,`village".$server."temp` SET `village".$server."`.`0`=`village".$server."temp`.`0`, `village".$server."`.`1`=`village".$server."temp`.`1`, `village".$server."`.`2`=`village".$server."temp`.`2`, `village".$server."`.`3`=`village".$server."temp`.`3`, `village".$server."`.`4`=`village".$server."temp`.`4`, `village".$server."`.`5`=`village".$server."temp`.`5`, `village".$server."`.`6`=`village".$server."temp`.`6` WHERE `village".$server."`.`id`=`village".$server."temp`.`id`");}
if ($villages = fopen('http://en'.$server.'.tribalwars.net/map/tribe.txt', 'r')) {
mysql_query("TRUNCATE TABLE player".$server); while (!feof($villages)) {$vil = explode(",", fgets($villages));
$vil[1] = mysql_real_escape_string(str_replace("'", "\\\\'", mb_convert_encoding(urldecode($vil[1]), "ISO-8859-1", "UTF-8")));
mysql_query("INSERT INTO `player$server` SET name='$vil[1]', id='$vil[0]', tribe='$vil[2]', villages='$vil[3]', points='$vil[4]'");}; fclose($villages);}
if ($villages = fopen('http://en'.$server.'.tribalwars.net/map/ally.txt', 'r')) {
mysql_query("TRUNCATE TABLE tribe".$server); while (!feof($villages)) {$vil = explode(",", fgets($villages));
$vil[1] = mysql_real_escape_string(str_replace("'", "\\\\'", mb_convert_encoding(urldecode($vil[1]), "ISO-8859-1", "UTF-8")));
$vil[2] = mysql_real_escape_string(str_replace("'", "\\\\'", mb_convert_encoding(urldecode($vil[2]), "ISO-8859-1", "UTF-8")));
mysql_query("INSERT INTO `tribe$server` SET name='$vil[1]', tag='$vil[2]', id='$vil[0]', members='$vil[3]', points='$vil[6]'");};}}
echo $_GET['server'].' Done';
PHP:
for ($i=1; $i<12; $i++) {$server = $i;
mysql_query("UPDATE `village$server` SET `".date('w')."`=`points`");
mysql_query("TRUNCATE TABLE village".$server."temp");
mysql_query("INSERT INTO `village".$server."temp` SELECT `id`, `0`, `1`, `2`, `3`, `4`, `5`, `6` FROM `village$server`");}
echo 'updated ',date('w');
Last edited: