Help with database update script to increase efficiency

Status
Not open for further replies.

Tomcenc

New Member
Messages
57
Reaction score
0
Points
0
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:
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';
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.
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');
I ommited the connect and select database lines, as they are unnecessary in this example.
 
Last edited:

Corey

I Break Things
Staff member
Messages
34,553
Reaction score
204
Points
63
I thought the memory limit was quite high for advanced PHP. How much memory do you need?
 

Tomcenc

New Member
Messages
57
Reaction score
0
Points
0
The largest server is currently accounting for 264.699 villages, but this number rises every day. The raw txt file is 12MB (12.601.961 bytes) large. Loading this amount of data into the memory as an array requires more than 32MB ram. The memory limit used to be 128MB, but I had to change my script because it ran out of memory using the limit of 32MB, so it seems it was lowered somehow during the recent changes. It might have been a bug when I was reset to PHP V2 temporarily like all version 3 users, but I'm not going to try to test the current limit as I don't want to get suspended again.

If the memory limit was higher, I could insert the data into the database in one large query, I'm almost certain that would be faster than inserting them individually.

You are right though, a memory limit of 32MB is very large already, and should be more than sufficient for normal use. I suppose an array of the data would result in 48Mb or ram, 96MB at maximum if the server increases in size even more before being reset. I don't see how increasing the limit with the high resource usage program on would cause any harm, as abusing it would result in quick suspension.
 
Last edited:

Corey

I Break Things
Staff member
Messages
34,553
Reaction score
204
Points
63
Max memory on advanced is 128M
 

Tomcenc

New Member
Messages
57
Reaction score
0
Points
0
You are right (of course you are), I must've had this error when I was reset to version 2 then. Now that I am testing it however, it seems there is a limit on the size of the mysql query.
http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html
The default maximum packet size is one MB, so a query larger than one megabyte will return an error.

The server's default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns). For example, to set the variable to 16MB, start the server like this:
shell> mysqld --max_allowed_packet=16M
You can also use an option file to set max_allowed_packet. For example, to set the size for the server to 16MB, add the following lines in an option file:
[mysqld]
max_allowed_packet=16M
It is safe to increase the value of this variable because the extra memory is allocated only when needed.
So I have to split up my query into several smaller ones (not too hard, but still requires more resources than necessary, I still need +/- 30 queries that way), if possible could you increase the limit of a query (16-32MB)? You are already generous with the features you supply to your users with this great service and I'm certainly happy with this service, as it gives me nearly everything I need. Except for a large mysql query size that is.
 
Last edited:

Corey

I Break Things
Staff member
Messages
34,553
Reaction score
204
Points
63
With advanced PHP I believe you might be able to set this setting on your account.

Try creating a file called php.ini in the same directory that your script is in and put just max_allowed_packet=16M in it.
 

Slothie

New Member
Messages
1,429
Reaction score
0
Points
0
What about dumping it to a file and 'exec'-ing the queries?
 

Tomcenc

New Member
Messages
57
Reaction score
0
Points
0
I followed your advice and created a file "php.ini", inserted the lines
[mysqld]
max_allowed_packet=16M
and uploaded it to the same directory but it failed to work, I tried again with just the line you said
max_allowed_packet=16M
and that didn't work either.

Edit @ slothie: I can try that but according to the mysql manual the max_allowed_packet parameter limits that too if I understand correctly:
When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.
 
Last edited:

Slothie

New Member
Messages
1,429
Reaction score
0
Points
0
Code:
[mysqld]
max_allowed_packet=16M

This should be inserted into my.cnf not php.ini.


Also at 12MB, I don't see why you can load a CSV file into mysql. Even if its set at 16MB, you've still got 14 to spare.
Code:
LOAD DATA LOCAL INFILE '/somefile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);
 
Last edited:

Tomcenc

New Member
Messages
57
Reaction score
0
Points
0
I already cited why that is not possible (for me):
One of the values that I have to insert, has to be:
1: urldecoded (not available in mysql)
2: character set converted from UTF-8 to iso-8859-1 (available in mysql)
3: ' replaced by \\' (available in mysql but I'm not sure how to utilize it in a load data statement)

Edit: Just tried using a my.cnf file and that does not work either
 
Last edited:

Slothie

New Member
Messages
1,429
Reaction score
0
Points
0
You don't have access to my.cnf, that's root.

You COULD write out the data AFTER processing it in PHP to a CSV file
 

Tomcenc

New Member
Messages
57
Reaction score
0
Points
0
Processing data, writing it to a file, process the file and then delete it again doesn't seem more efficient than using multiple large queries. I can't monitor the resource usage but it doesn't seem very efficient. I'm trying to find the most efficient way to update my database.

The most efficient way to do it as far as I know, would be to write a custom mysql urldecode function which incorporates the replacement of ' by \\' and define it each time the script has to be run and use LOAD for the data. Though I haven't been able to get any I found on the Internet to work. I just learned Mysql so I can't write something like that myself yet.
 

Slothie

New Member
Messages
1,429
Reaction score
0
Points
0
A stored procedure will probably take care of that.

urldecoding / encoding is pretty easy and so would string replacement.

You can either google it, code it yourself, or pay someone to do it for you :)
 

Tomcenc

New Member
Messages
57
Reaction score
0
Points
0
You can either google it, code it yourself,
I already looked it up on the internet but couldn't get it to work (I just learned mysql, the terminology is new to me and I don't understand the errors yet). That's why I'm asking for help here.

The easiest and not so inefficient way (and my preferable too, as I can change it myself whenever the need occurs) would be having a higher mysql packet size limit but Corey hasn't responded yet whether he would be willing to increase it in the configuration.
 

Bryon

I Fix Things
Messages
8,149
Reaction score
101
Points
48
I increased the max_allowed_packet size for MySQL on the server that you're on to 16 MB. Will that work for you?
 

Tomcenc

New Member
Messages
57
Reaction score
0
Points
0
Thank you, it works perfectly. The Mysql query is 13.74MB (14407852 bytes) large for the largest server so it'll do (at least for now).

It's really great how many features you guys offer for add-free free hosting. If I ever move to paid hosting I'm sure to pick you guys. Support is worth a lot too, many companies fail to realise that.
 

Bryon

I Fix Things
Messages
8,149
Reaction score
101
Points
48
Alright, it's good to hear that it is working for you now. If you need any further help regarding this, re-open the thread or create a new one.

Thank you for the compliment. :)
 
Last edited:
Status
Not open for further replies.
Top