- Joined
- Jan 4, 2009
- Messages
- 14
- Reaction score
- 0
Forward
The purpose of this release is to start moving towards storing all of the XML data files in the MySQL database, for the sake of performance, centralized storage, and availability. One of the things that I noticed when I initially set up TitanMS, was the amount of time that it took to 'initialize' the data files, which basically reads and parses them, every time you start the server. Of course, you wouldn't normally stop and start a server so often as you do while debugging it, but it is disadvantageous to be storing the data in this manner.
Parsing strings is very expensive, especially when they are not indexed in any way, and are in a bulky markup such as XML. Also there is a very large overhead from storing 9,969 xml files that are only several kilobytes a piece (this comes from the chunking of the filesystem to the high seek times to read from the MFT after reading a few kilobytes, and then back again.)
Goal:
To utilize the database to eliminate overhead, provide data accessibility, and increase server performance, all of which are lost when storing the game data into XML files.
Implementation:
Here is what I've implemented so far. It utilizes the preexisting MySQL Database, and requires very little server code changes. Unfortunately, I've only implemented Mobs Initialization in this manner, which is one of the least intensive XML loading procedures. I am currently working on implementing Maps Initialization, which is by far one of the longest running parsing functions. (I've counted it's execution time to be upwards of 40-50 seconds, on a AMD Athlon 64bit 3200+, with 2.25GB of ram)
My first step to doing this was designing the database tables in a normalized, yet efficient, fashion, as to promote easy data manipulation and fast access. For Mobs, this required breaking the information into two tables, dataMobs, and dataMobSummons. (the prefix data is what I'm using to differentiate the Data tables from the others)
In your database manager (Navicat, phpmyadmin), enter SQL query mode and execute these two statments.
Please note that this database table has been modified for better performance (and naming conventions), and has not been updated here. Please wait until we post the updated design before using this! -- zinmirai 4-17-2008
dataMobs
Note that we are indexing MobNumber, as it may be used for selecting against, and is a pseudo ID. (non-enforced expected uniqueness)
dataMobSummons
Again, note that we index MobNumber, as this is guarenteed to be selected against in our code.
The next step is to import the data from the pre-existing XML files. I wrote a php script for this that you can run using CLI (php -f), which allowed me to quickly import all of the Mob XML files into the database. (unfortunately this is also where I'm having a little bit of trouble.. The XML parsing for Maps gets a little complex, due to it requiring 5 different tables, and involving nesting with many duplicate element names)
The script is as follows:
Just edit the configuration to match yours. Note that this script will delete all of the information in those two tables! (dataMob, dataMobSummon)
mobinsert.php
Just execute php -f mobinsert.php
If it fails, it will most likely throw an error.. But it really shouldn't, it's very straight forward. (just possible connection problems, etc)
Note that you will need PHP to execute this script, which can be downloaded for free at
After you have successfully imported the data, you just have to make the necessary code changes.
For Mobs, we are going to replace the function void Initializing::initializeMobs()
Please note that this source has been modified for better performance (and the updated table naming conventions), and has not been updated here. Please wait until we post the updated code before using this! -- zinmirai 4-17-2008
(Some notes about the changes: We removed the query from the loop and used a SQL JOIN across the MobData and MobSummonData table, resulting in only 1 query needed to be performed, rather than (1+[Number of Mobs]). This should result in a huge performance boost, and will be applied to the rest of the database objects soon.
It should be as follows:
Initializing.cpp, Line ~92
Make sure you comment out, or remove your old one or you will have a previously defined function error.
Also add:
#include "MySQLM.h"
#include <sstream>
to the directives in Initializing.cpp. (the top of the script, where you see the other includes)
Next, you have to expose the MYSQL maple_db; variable as public (this should probably be done with a public function, but this will work for now), so in
MySQLM.h, Line 9, Change:
To
Now, you have to rearrange the initialization functions so that you are connected to the database when your mob script attempts to query MySQL... To do that:
MapleStoryServer.cpp
CHANGE:
TO:
Once you replace that function and rearrange the initialization in MapleStoryServer.cpp, you have completed the conversion from the use of XML files to the use of your MySQL database, which will be many times faster than parsing XML files.
Closing
Of course, this is only a proof of concept, and partial implementation, but if it were finished, I feel that it would open up many possibilities (enabling things like faster server loading times, and the ability to edit these settings via web interface). I hope you enjoyed this tutorial, and plan on it being a complete release when I'm done converting the rest of the objects.
Thanks for reading!
Credit goes to me, my boredom, doyos, tsj5j, and anyone I forgot ^^ (I'll complete this list when the project is 100% complete).
The source code in this article is released under the BSD License.
------------------
Here is the ENTIRE SQL Dump of the XML files, as of 0.0.7b
Here is the PHP Source for mobinsert.php
Here is the new InitializeMobs function
The project will be updated soon.
The purpose of this release is to start moving towards storing all of the XML data files in the MySQL database, for the sake of performance, centralized storage, and availability. One of the things that I noticed when I initially set up TitanMS, was the amount of time that it took to 'initialize' the data files, which basically reads and parses them, every time you start the server. Of course, you wouldn't normally stop and start a server so often as you do while debugging it, but it is disadvantageous to be storing the data in this manner.
Parsing strings is very expensive, especially when they are not indexed in any way, and are in a bulky markup such as XML. Also there is a very large overhead from storing 9,969 xml files that are only several kilobytes a piece (this comes from the chunking of the filesystem to the high seek times to read from the MFT after reading a few kilobytes, and then back again.)
Goal:
To utilize the database to eliminate overhead, provide data accessibility, and increase server performance, all of which are lost when storing the game data into XML files.
Implementation:
Here is what I've implemented so far. It utilizes the preexisting MySQL Database, and requires very little server code changes. Unfortunately, I've only implemented Mobs Initialization in this manner, which is one of the least intensive XML loading procedures. I am currently working on implementing Maps Initialization, which is by far one of the longest running parsing functions. (I've counted it's execution time to be upwards of 40-50 seconds, on a AMD Athlon 64bit 3200+, with 2.25GB of ram)
My first step to doing this was designing the database tables in a normalized, yet efficient, fashion, as to promote easy data manipulation and fast access. For Mobs, this required breaking the information into two tables, dataMobs, and dataMobSummons. (the prefix data is what I'm using to differentiate the Data tables from the others)
In your database manager (Navicat, phpmyadmin), enter SQL query mode and execute these two statments.
Please note that this database table has been modified for better performance (and naming conventions), and has not been updated here. Please wait until we post the updated design before using this! -- zinmirai 4-17-2008
dataMobs
Code:
CREATE TABLE `datamob` (
`ID` bigint(20) NOT NULL auto_increment COMMENT 'Unique Row ID for a specific Mob',
`MobNumber` bigint(20) default NULL COMMENT 'Number of the mob, used externally',
`HP` bigint(20) default NULL COMMENT 'HP of the Mob',
`MP` bigint(20) default NULL COMMENT 'MP of the mob',
`Exp` bigint(20) default NULL COMMENT 'Experience Points of the Mob',
PRIMARY KEY (`ID`),
KEY `idxMobNumber` (`MobNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;dataMobSummons
Code:
CREATE TABLE `datamobsummon` (
`ID` bigint(20) NOT NULL auto_increment COMMENT 'Unique row identifier for MobSummons',
`MobNumber` bigint(20) NOT NULL COMMENT 'ID of the Mob this summon belongs to',
`SummonMobNumber` bigint(20) NOT NULL COMMENT 'ID of the mob that is summoned by the mob identified in MobID',
PRIMARY KEY (`ID`),
KEY `idxMobID` (`MobNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;The next step is to import the data from the pre-existing XML files. I wrote a php script for this that you can run using CLI (php -f), which allowed me to quickly import all of the Mob XML files into the database. (unfortunately this is also where I'm having a little bit of trouble.. The XML parsing for Maps gets a little complex, due to it requiring 5 different tables, and involving nesting with many duplicate element names)
The script is as follows:
Just edit the configuration to match yours. Note that this script will delete all of the information in those two tables! (dataMob, dataMobSummon)
mobinsert.php
Code:
<?php
/*********************
** Configuration
*********************/
$path = "C:\\MapleStory\\PrivateServer\\src\\MapleStoryServer\Mobs\\";
$mysql['host'] = 'localhost';
$mysql['user'] = 'root';
$mysql['pass'] = '';
$mysql['db'] = 'maplestory';
/*********************
** Don't edit below here if you don't
** know what you're doing! I won't fix
** your errors!
*********************/
// First we delete the entire table data.
// Yeah, yeah, I know.. But duplicates would suck
// don't you think? :P
$conn = DatabaseConnector();
mysql_query('TRUNCATE TABLE dataMob', $conn);
mysql_query('TRUNCATE TABLE dataMobSummon',$conn);
// The following was for the mysql connection testing, you can probably delete this.
//$rs = mysql_query('SELECT * FROM users') or die(mysql_error());
//print(mysql_num_rows($rs));
$dir = opendir($path);
while (false !== ($file = readdir($dir))) {
if($file != "." && $file != ".." && stripos($file,'.xml'))
{
$CurrentMob = new Mob();
$CurrentTag = '';
//This was used for testing Summons, can probably delete.
//$file = '5220001.xml';
$CurrentMob->MOBNUMBER = substr($file,0,strlen($file)-4);
$xmlparser = xml_parser_create();
xml_set_element_handler($xmlparser, "start_tag", "end_tag");
xml_set_character_data_handler($xmlparser, "tag_contents");
if (!($fp = fopen($path.$file, "r")))
{
print("WARNING: Cannot open ".$path.$file."\n");
}
else
{
print ('Opened ' . $path.$file . "...\n");
while ($data = fread($fp, 4096))
{
// Strip whitespace
$data=eregi_replace(">"."[[:space:]]+"."<","><",$data);
if (!xml_parse($xmlparser, $data, feof($fp))) {
$reason = xml_error_string(xml_get_error_code($xmlparser));
$reason .= xml_get_current_line_number($xmlparser);
print("XML ERROR :: " . $reason);
}
}
}
xml_parser_free($xmlparser);
$CurrentMob->Insert();
unset($CurrentMob);
}
}
// $parser = handle to our parser
// $name = name of the current tag
// $attrib = an array containing any attributes of the current tag
function start_tag($parser, $name, $attribs) {
global $CurrentTag;
// echo "Current tag : ".$name."\n";
$CurrentTag = $name;
if (is_array($attribs)) {
// echo "Attributes : \n";
while(list($key,$val) = each($attribs))
{
// echo "Attribute ".$key." has value ".$val."\n";
}
}
}
// $parser = handle to our parser
// $name = name of the current tag
function end_tag($parser, $name) {
// echo "Reached ending tag ".$name."\n\n";
}
function tag_contents($parser, $data)
{
global $CurrentMob,$CurrentTag;
// echo "Contents : ".$data."\n";
// We have to do special handling for SUMMON
if($CurrentTag != 'ID')
{
$CurrentMob->$CurrentTag = $data;
}
else
{
array_push($CurrentMob->SUMMON, $data);
}
}
class Mob
{
// Don't ask why these are in all caps... the xml element names
// were, so, I made them to match (for easier loading) and then
// it looked odd having only 2 that weren't in all caps..
public $FILE;
public $MOBNUMBER;
public $HP;
public $MP;
public $EXP;
public $SUMMON = array();
public function __constructor()
{
$FILE = '';
$MOBNUMBER = '';
$HP = 0;
$MP = 0;
$EXP = 0;
$SUMMON = array();
}
public function Insert()
{
$conn = DatabaseConnector();
$sql = 'INSERT INTO dataMob
(MobNumber, HP, MP, EXP) VALUES
("'.$this->MOBNUMBER.'",
"'.$this->HP.'",
"'.$this->MP.'",
"'.$this->EXP.'")';
mysql_query($sql,$conn);
print(mysql_error($conn));
$this->InsertSummon();
}
private function InsertSummon()
{
$conn = DatabaseConnector();
foreach($this->SUMMON As $SummonMobNumber)
{
$sql = 'INSERT INTO dataMobSummon
(MobNumber, SummonMobNumber) VALUES
("'.$this->MOBNUMBER.'",
"'.$SummonMobNumber.'")';
mysql_query($sql,$conn);
print(mysql_error($conn));
}
}
}
// Returns MySQL Connection Resource ID
function DatabaseConnector()
{
global $mysql;
$conn = mysql_connect($mysql['host'],$mysql['user'],$mysql['pass']) or die(mysql_error());
mysql_select_db($mysql['db'],$conn);
return $conn;
}
?>If it fails, it will most likely throw an error.. But it really shouldn't, it's very straight forward. (just possible connection problems, etc)
Note that you will need PHP to execute this script, which can be downloaded for free at
To view the content, you need to sign in or register
After you have successfully imported the data, you just have to make the necessary code changes.
For Mobs, we are going to replace the function void Initializing::initializeMobs()
Please note that this source has been modified for better performance (and the updated table naming conventions), and has not been updated here. Please wait until we post the updated code before using this! -- zinmirai 4-17-2008
(Some notes about the changes: We removed the query from the loop and used a SQL JOIN across the MobData and MobSummonData table, resulting in only 1 query needed to be performed, rather than (1+[Number of Mobs]). This should result in a huge performance boost, and will be applied to the rest of the database objects soon.
It should be as follows:
Initializing.cpp, Line ~92
Code:
void Initializing::initializeMobs(){
MYSQL_RES *mres;
MYSQL_ROW mrow;
char query[255];
sprintf_s(query,255,"SELECT * FROM dataMob");
mysql_real_query(&MySQL::maple_db, query, strlen(query));
mres = mysql_store_result(&MySQL::maple_db);
mrow = mysql_fetch_row(mres);
int ret = 0;
while ((mrow = mysql_fetch_row(mres)))
{
MobInfo mob = MobInfo();
MYSQL_RES *mres2;
MYSQL_ROW mrow2;
// Col0 : RowID
// 1 : MobNumber
// 2 : HP
// 3 : MP
// 4 : Exp
int mobnumber = atoi(mrow[1]); // This is the Mob Number, as stored in the database (Not to be confused with the Row ID!!!)
mob.hp = atoi(mrow[2]);
mob.mp = atoi(mrow[3]);
mob.exp = atoi(mrow[4]);
sprintf_s(query,255,"SELECT * FROM dataMobSummon WHERE MobNumber = '%i'", mobnumber);
mysql_real_query(&MySQL::maple_db, query, strlen(query));
mres2 = mysql_store_result(&MySQL::maple_db);
while((mrow2 = mysql_fetch_row(mres2)))
{
mob.summon.push_back(atoi(mrow2[2]));
}
// We do special handling for the summons, as it's stored in another table
// to preserve normalization.
Mobs::addMob(mobnumber,mob);
}
}Also add:
#include "MySQLM.h"
#include <sstream>
to the directives in Initializing.cpp. (the top of the script, where you see the other includes)
Next, you have to expose the MYSQL maple_db; variable as public (this should probably be done with a public function, but this will work for now), so in
MySQLM.h, Line 9, Change:
Code:
private:
static MYSQL maple_db;
public:
static int connectToMySQL();
Code:
// private:
// static MYSQL maple_db;
public:
static MYSQL maple_db;Now, you have to rearrange the initialization functions so that you are connected to the database when your mob script attempts to query MySQL... To do that:
MapleStoryServer.cpp
CHANGE:
Code:
Initializing::initializing();
printf("Initializing Timers... ");
Timer::timer = new Timer();
Skills::startTimer();
Maps::startTimer();
printf("DONE\n");
printf("Initializing MySQL... ");
if(MySQL::connectToMySQL())
printf("DONE\n");
else{
printf("FAILED\n");
exit(1);
}
Code:
printf("Initializing MySQL... ");
if(MySQL::connectToMySQL())
printf("DONE\n");
else{
printf("FAILED\n");
exit(1);
}
Initializing::initializing();
printf("Initializing Timers... ");
Timer::timer = new Timer();
Skills::startTimer();
Maps::startTimer();
printf("DONE\n");Once you replace that function and rearrange the initialization in MapleStoryServer.cpp, you have completed the conversion from the use of XML files to the use of your MySQL database, which will be many times faster than parsing XML files.
Closing
Of course, this is only a proof of concept, and partial implementation, but if it were finished, I feel that it would open up many possibilities (enabling things like faster server loading times, and the ability to edit these settings via web interface). I hope you enjoyed this tutorial, and plan on it being a complete release when I'm done converting the rest of the objects.
Thanks for reading!
Credit goes to me, my boredom, doyos, tsj5j, and anyone I forgot ^^ (I'll complete this list when the project is 100% complete).
The source code in this article is released under the BSD License.
------------------
Here is the ENTIRE SQL Dump of the XML files, as of 0.0.7b
To view the content, you need to sign in or register
Here is the PHP Source for mobinsert.php
To view the content, you need to sign in or register
Here is the new InitializeMobs function
To view the content, you need to sign in or register
(you will need to add the #include "MySQLM.h" #include <sstream> directives)[/QUOTE]The project will be updated soon.
