First, I would like to clarify, I have been talking about loading the spells into shared memory, not loading info on the fly from the database (that's just resource suicide).
Quote:
Originally Posted by KLS
-You have slightly increased load times for spells.
|
I know PHP isn't the best "language" for speed, but according to preliminary testing, it actually IS faster to calculate the max spell ID & load the spells from the database than the spells_us.txt file WITHOUT caching (
SQL_NO_CACHE):
PHP Code:
<?php
class Database
{
private $connectoid = false; //Used to track this specific database connection so we don't mess with the other connections, also can be used to determine the state of the connection
private static $user = "username"; //Username to access the Database
private static $pass = "password"; //Password to access the Database
private static $host = "localhost"; //IP address or hostname
protected $db = "peq"; //Name of the Database we're accessing, can be changed if needed
protected $benchmarks = Array();
public function __construct() {
if ($this->connectoid = mysql_connect(Database::$host, Database::$user, Database::$pass)) {
mysql_select_db($this->db, $this->connectoid);
}
}
public function __toString() {
return "Database " . ($this->IsConnected() ? "" : "not ") . "connected";
}
public function IsConnected() {
return ($this->connectoid ? true : false);
}
//test function, we don't want to be broadcasting our login info to everyone irl
public function ReturnLoginInfo() {
return "Host: " . Database::$host . "<br>\nUser: " . Database::$user . "<br>\nPass: " . Database::$pass . "<br>\nDB: " . $this->db;
}
public function Query($q) {
$this->benchmarks[Query][result][start] = microtime(true);
$res = mysql_query($q, $this->connectoid); //results
$this->benchmarks[Query][result][end] = microtime(true);
$this->benchmarks[Query][row][start] = microtime(true);
if ($row = mysql_fetch_array($res, MYSQL_ASSOC)) { //single row of data
$this->benchmarks[Query][row][end] = microtime(true);
$i = 0; //iterator
$ret = array();
do {
$ret[++$i] = $row;
} while ($row = mysql_fetch_array($res, MYSQL_ASSOC));
if (is_array($ret)) //Only return data in an array, otherwise it's an error
return $ret;
} else {
return $this->GetErrorNum($this->connectoid);
}
}
public function GetBenchmarks() {
$this->TotalBenchmarks();
return $this->benchmarks;
}
private function TotalBenchmarks() {
$this->benchmarks[Query][result][total] = $this->benchmarks[Query][result][end] - $this->benchmarks[Query][result][start];
$this->benchmarks[Query][row][total] = $this->benchmarks[Query][row][end] - $this->benchmarks[Query][row][start];
}
protected function GetErrorNum($c) {
return mysql_errno($c);
}
public function __destruct() {
if ($this->IsConnected())
mysql_close($this->connectoid);
}
}
?>
PHP Code:
<html>
<?php
ini_set("memory_limit","256M");
ini_set("max_execution_time","600");
//Automagically load Classes where needed from ../includes/ClassName.class.php
function __autoload($class_name) {
require_once '../includes/' . $class_name . '.class.php';
}
function DatabaseGetMax() {
$db_time_start = microtime(true);
$db = new Database;
$db_time_query = microtime(true);
$db_spells = $db->Query("SELECT SQL_NO_CACHE MAX(id) FROM spells_new");
$db_time_end = microtime(true);
$db_time_total_overhead = $db_time_query - $db_time_start;
$db_time_total_query = $db_time_end - $db_time_query;
$db_time_total_mysql = $db->GetBenchmarks();
$db_time_total = $db_time_end - $db_time_start;
echo "Result: " . $db_spells[1]["MAX(id)"] . "<br>\n";
echo "Class Time: " . $db_time_total_overhead . " seconds<br>\n";
echo "Query Time: " . $db_time_total_query . " seconds (MySQL Time: " . $db_time_total_mysql[Query][result][total] . " seconds)<br>\n";
echo "Total Time: " . $db_time_total . " seconds<br>\n";
}
function DatabaseLoad() {
$db_time_start = microtime(true);
$db = new Database;
$db_time_query = microtime(true);
$db_spells = $db->Query("SELECT SQL_NO_CACHE * FROM spells_new");
$db_time_end = microtime(true);
$db_time_total_overhead = $db_time_query - $db_time_start;
$db_time_total_query = $db_time_end - $db_time_query;
$db_time_total_mysql = $db->GetBenchmarks();
$db_time_total = $db_time_end - $db_time_start;
echo "Spells Loaded: " . count($db_spells) . "<br>\n";
echo "Class Time: " . $db_time_total_overhead . " seconds<br>\n";
echo "Query Time: " . $db_time_total_query . " seconds (MySQL Time: " . $db_time_total_mysql[Query][result][total] . " seconds)<br>\n";
echo "Total Time: " . $db_time_total . " seconds<br>\n";
}
function FileGetMax() {
$file_dir = ""; // needs / at the end if anything other than the current directory
$file_name = "spells_us.txt";
$file_time_start = microtime(true);
if (file_exists($file_dir . $file_name) && is_readable($file_dir . $file_name)) {
$file_time_load = microtime(true);
$Lines = file($file_dir . $file_name);
$file_time_parse = microtime(true);
foreach($Lines as $key => $value) {
$Data[$key] = explode("^", $value);
}
$file_time_end = microtime(true);
} else {
echo "Unable to read: " . $file_dir . $file_name . "<br>\n";
}
if (!$file_time_end)
$file_time_end = microtime(true);
$file_time_total_open = $file_time_load - $file_time_start;
$file_time_total_load = $file_time_parse - $file_time_load;
$file_time_total_parse = $file_time_end - $file_time_parse;
$file_time_total = $file_time_end - $file_time_start;
echo "Result: " . $Data[count($Data)-1][0] . "<br>\n";
echo "Open Time: " . $file_time_total_open . " seconds<br>\n";
echo "Load Time: " . $file_time_total_load . " seconds<br>\n";
echo "Parse Time: " . $file_time_total_parse . " seconds<br>\n";
echo "Total Time: " . $file_time_total . " seconds<br>\n";
}
function FileLoad() {
$file_dir = ""; // needs / at the end if anything other than the current directory
$file_name = "spells_us.txt";
$file_time_start = microtime(true);
if (file_exists($file_dir . $file_name) && is_readable($file_dir . $file_name)) {
$file_time_load = microtime(true);
$Lines = file($file_dir . $file_name);
$file_time_parse = microtime(true);
foreach($Lines as $key => $value) {
$Data[$key] = explode("^", $value);
}
$file_time_end = microtime(true);
} else {
echo "Unable to read: " . $file_dir . $file_name . "<br>\n";
}
if (!$file_time_end)
$file_time_end = microtime(true);
$file_time_total_open = $file_time_load - $file_time_start;
$file_time_total_load = $file_time_parse - $file_time_load;
$file_time_total_parse = $file_time_end - $file_time_parse;
$file_time_total = $file_time_end - $file_time_start;
echo "Spells Loaded: " . count($Lines) . "<br>\n";
echo "Open Time: " . $file_time_total_open . " seconds<br>\n";
echo "Load Time: " . $file_time_total_load . " seconds<br>\n";
echo "Parse Time: " . $file_time_total_parse . " seconds<br>\n";
echo "Total Time: " . $file_time_total . " seconds<br>\n";
}
echo "<b>DatabaseGetMax()</b><br>\n";
DatabaseGetMax();
echo "<br>\n";
echo "<b>DatabaseLoad()</b><br>\n";
DatabaseLoad();
echo "<br>\n";
echo "<b>FileGetMax()</b><br>\n";
FileGetMax();
echo "<br>\n";
echo "<b>FileLoad()</b><br>\n";
FileLoad();
?>
</html>
Code:
DatabaseGetMax()
Result: 11999
Class Time: 0.000842094421387 seconds
Query Time: 0.000253915786743 seconds (MySQL Time: 0.000201940536499 seconds)
Total Time: 0.00109601020813 seconds
DatabaseLoad()
Spells Loaded: 11540
Class Time: 0.000181913375854 seconds
Query Time: 1.7691822052 seconds (MySQL Time: 0.547831058502 seconds)
Total Time: 1.76936411858 seconds
FileGetMax()
Result: 11999
Open Time: 3.50475311279E-05 seconds
Load Time: 0.0381419658661 seconds
Parse Time: 0.978446960449 seconds
Total Time: 1.01662397385 seconds
FileLoad()
Spells Loaded: 11540
Open Time: 3.38554382324E-05 seconds
Load Time: 0.0379700660706 seconds
Parse Time: 0.978180885315 seconds
Total Time: 1.01618480682 seconds
However, if we are talking about JUST loading the spells (which can't be done, since we need to know what the highest spell ID is before we load them into shared memory), loading from a file is considerably faster.
Quote:
Originally Posted by KLS
-In my opinion you made it harder on new users who now have to try to figure out 'hey there's two systems for spells, wait do I need them both? I can't find the db table I need anywhere help!!'.
|
That's why I'm not sure if it would be better to give the option or to shove it down everyone's throat (I prefer the former). I guess the expectation would be to switch it over to completely DB. Giving the options would just give an "easier" transition in the short term.
Quote:
Originally Posted by MNWatchdog
It wasnt broke, so why are you fixing it?
Youre just adding an additional level of complication IMO.
|
Why have a database if we're not going to take advantage of it? Yes, we can have the spells stored in a file, but we could also have items, spawns, NPC types, merchant lists, spell lists, AAs, zones, zone servers, etc all in files. But that's just crazy. That's why we have a variables system & a rules system instead of config files like with the MaNGOS emulator (for WoW).