Undergarun

There is something strange with new version, with 1280 pl online CPU is under heavy load (94%-99%) but with 1012 pl online and visibility and reloc branch 2 CPU load is about 58-64% so less performance? I have to wait until tomorrow in order to make pics with 1000 pl online and to be able to compare with visibility and reloc branch 2 tests. Stay tuned!

Shauren is sharing his own idea trying to help Cyberium and co. in Dungeon Encounters development for MaNGOS! Please, be more polite with people who tries to help you ;-)

$this->ExecuteQuery("TRUNCATE TABLE pet_aura"); I truncate pet_aura table but should work with: $this->ExecuteQuery("UPDATE pet_aura SET guid = ".$itr." WHERE guid = ".$data['id'].""); And as i know pet talents are stored in abdata field in character_pet.

I made a php script for id reorder: <?php ##################################################################################### # # MaNGOS PET DEBUGGER WRITTEN BY UNDERGARUN # ##################################################################################### $petdebuggerhandler = new MaNGOS_PET_DEBUGGER('host', 'root', 'pass', 'characters', '/var/log/petdebug.log'); ######### PLEASE, DO NOT MODIFIED ANYTHING BEYOND THIS POINT ######################### $petdebuggerhandler->rebuildPetGeneralUniqueIdentificators(); class MaNGOS_PET_DEBUGGER { var $connection; var $db; var $logname; var $petlist; function __construct($host, $user, $pass, $db, $logpath) { set_time_limit(0); // Evitar timeout de ejecución del script. error_reporting(E_ALL); ini_set('display_errors', '1'); $this->connection = mysql_connect($host.':3306', $user, $pass) or $this->errorLogMessage('Imposible establecer conexión con mySQL. Info provide => Host: '.$host.' User: '.$user.' Pass: '.$pass.' Mysql err: '.mysql_error($this->connection)); $this->db = mysql_select_db($db, $this->connection) or $this->errorLogMessage('Imposible establecer conexión con la base de datos: '.$db.' MySQL err: '.mysql_error($this->connection)); $this->logname = $logpath; } function __destruct() { mysql_close($this->connection); } /** * * Logger functions * **/ private function debugLogMessage($msg) { $logPetition = fopen($this->logname, 'a'); fwrite($logPetition, '['.date('d/m/Y').']['.date('H:i:s').'][DEBUG]: '.$msg); fwrite($logPetition, "\\r\\n"); fclose($logPetition); } private function errorLogMessage($msg) { $logPetition = fopen($this->logname, 'a'); fwrite($logPetition, '['.date('d/m/Y').']['.date('H:i:s').'][ERROR]: '.$msg); fwrite($logPetition, "\\r\\n"); fclose($logPetition); } /** * * MySQL functions * **/ private function BeginTransaction() { mysql_query('BEGIN', $this->connection); } private function CommitTransaction() { mysql_query('COMMIT', $this->connection); } private function RollbackTransaction() { mysql_query('ROLLBACK', $this->connection); $this->errorLogMessage('There was an error. MySQL errno: '.mysql_errno($this->connection).' MySQL error: '.mysql_error($this->connection).' Rollbacking everything!'); die('An error happens, please check '.$this->logname); exit(); } private function ExecuteQuery($query) { $executedquery = mysql_query($query, $this->connection) or $this->RollbackTransaction(); return $executedquery; } private function FetchArray($resultSet) { return mysql_fetch_array($resultSet); } /** * * Reorder method * **/ public function rebuildPetGeneralUniqueIdentificators() { $this->debugLogMessage('Starting process...'); $this->BeginTransaction(); $this->petlist = $this->ExecuteQuery('SELECT id FROM character_pet ORDER BY id'); for($itr = 0; $data = $this->FetchArray($this->petlist); $itr++) { $this->ExecuteQuery("UPDATE character_pet SET id = ".$itr." WHERE id = ".$data['id'].""); $this->ExecuteQuery("UPDATE pet_spell SET guid = ".$itr." WHERE guid = ".$data['id'].""); $this->ExecuteQuery("UPDATE pet_spell_cooldown SET guid = ".$itr." WHERE guid = ".$data['id'].""); $this->debugLogMessage('PET (Guid: '.$data['id'].') relocated to Guid: '.$itr.' successfully!'); } $this->ExecuteQuery("TRUNCATE TABLE pet_aura"); $this->CommitTransaction(); $this->debugLogMessage('Process Finished!'); echo ('Operation completed successfully! You can check the process in '.$this->logname.'.'); } } ?>

Your extremely simple way is not the best way because players can teleport into the instance using WoWEm.... (Cheating tools). My effective way is delete the instance_template record and move all players in it to another map because if someone tries to login when is in a map without instance_template MaNGOS will crash.

I will take look at it. Both branches(old_way and simplified) have this problem? Yes, problem happens with both branches

As i wrote on GitHub y prefer the second one UPDATE creature_template SET unit_flags = 1 WHERE entry = 1; better than UPDATE creature_template SET unit_flags=1 WHERE entry=1; And the same for remaining cases. Best regards and kindly yours

Tests for visibility and reloc branch 2 (https://github.com/SilverIce/mangos/commits/visibility_op2_simplified) Visibility.RelocationLoverLimit = 10 Visibility.AIRelocationNotifyDelay = 1000 Visibility.RelocationLoverLimit = 10 Visibility.AIRelocationNotifyDelay = 100 Tests for visibility and reloc branch 1 (https://github.com/SilverIce/mangos/commits/relocation_old_ver2) Visibility.NotifyPeriod = 1000 Visibility.NotifyPeriod = 200 PD1: Test with 2000+ pl are not possible without a mtmaps enviroment. PD2: After changing Visibility.AIRelocationNotifyDelay or Visibility.NotifyPeriod from lower value to higher... and tip .reload config CPU don't decrease from 100% of CPU use until next server reboot / crash.

The pastebin.com patch is wrong but we are testing the right one on our realms ;-)

Every week appears one of the "happy asserts" but i don't know the reason of them. Can anybody explain the reason o where is the problem in order to get ride this asserts? I'm not using a clean source # Assert 1: mangos-worldd: ../../../src/game/../shared/ByteBuffer.h:361: void ByteBuffer::append(const uint8*, size_t): Assertion `"size() < 10000000" && 0' failed. # Assert 2: Object::GetUInt32Value (this=0x0, index=67) at ../../../src/game/Object.h:178 MANGOS_ASSERT( index < m_valuesCount || PrintIndexError( index , false) ); # Assert 3: mangos-worldd: ../../../src/game/SpellAuras.cpp:8515: SpellAuraHolder::SpellAuraHolder(const SpellEntry*, Unit*, WorldObject*, Item*): Assertion `"caster->GetObjectGuid().IsUnit()" && 0' failed. (This one appears only in one realm and always the same and keeping in mind that i use the same mangos rev and ytdb rev in all my realms... i don't know why is happening.) Thank you.

From what Ambal said, i continued with this report because it does not work correctly for me with Default mysql confs, and requires reconfigure of non-mangos configurations Of course, go ahead with the tests and feedback ;-)

We have transaction-isolation = READ-COMMITTED in our my.cnf!!!

+1 But i am researching the problem carefully.

Thank you for help cyberium developing the dungeon finder for MaNGOS. ¡¡Gracias ;-) !!

The Max Queries with current layers is 7421 but is not accurate information for a comparison cuz i decrease PlayerSave.Interval from 600000 to 450000 and i have more players when the realm is not under a possible data failure and some others cataclysmic warnings. Keep in mind that with current db layer the characters db connection is not full only under a heavy load (80-90% CPU). So from now on with the patch the load is splitted into different threads. prepared statements are gonna improve performance for sure!! And Bandwidth decrease is usefull for a lot of people without a unlimited 10 gbps connection between mangos and mySQL. ;-) Keep up the good work!!! We love u "Ambal, the Mr. Performance"! ;-)

Thank you so much ;-)

Test results: 9 hours uptime (2452pl online): 1 crash related to camera, no issues reported or data inconsistency. MapUpdate.Threads = 2 LoginDatabaseConnections = 1 WorldDatabaseConnections = 1 CharacterDatabaseConnections = 2 Max Queries executed by MySQL at the same time: 8371 Queries. Bandwith avg: 29.2 MB per second.

Auction House Bot is not a World of Warcraft Server feature, is more or less like a modification for low-populated servers.

Testing in progress but you have to fix compile: //round-robin connection selection - SqlConnection * Database::getQueryConnection(); + SqlConnection * getQueryConnection(); Thanks kero99 for compile fix Edit: What about mysql autoreconnect feature for mangos connections?

Yes, and current way in clean source is totally right, but for some unknown reason, we saw lots of "Duplicate entry" errors in logs, so we decided to avoid it with this wrong way

As kero99 said, i restart mysql service in order to make clear stats for the scheduled test... as you can see in the next screenshoot, after 23h update mysql is using about 24 GB of Ram again! Thats because when MaNGOS ask MySQL Server for some information, this get cached to RAM memory speeding up the next time that MaNGOS ask for the same information even if is updated by MaNGOS, but when i stop MySQL service all information stored in RAM memory gets flushed to disks. Anyway, RAM depends on your mysql config and db size, this patch has nothing to do with RAM use on MySQL Server. Believe it or not yes!, because innoDB makes locks per row and not per table so when the async connection is performing transactions, the dedicated connections for selects is able to ask information even if the other connection is performing transactions with the same db table. And of course it allows me to decrease PlayerSave.Interval to 450000 avoiding big rollbacks when MaNGOS crash. Actually with my amount of players i haven't got lag but i can see better performance. Only when MaNGOS crashes with 2000+ pl online and restart... i have some lag during 1-2 minutes because 2000pl tries to login at the same time. Thats what happen: Most of the queries are SELECT!!! That's the reason for ask about a pool of SELECT connections!! So... after 24 hours of test and with 2 crashes not related to this, not reported issues, and no data inconsistency... ¡¡¡CONGRATULATIONS AMBAL FOR A GREAT WORK!!! I'll implement the patch in the other 2 realms! Some things related to Query counting stats: In our repo we change: "PExecute("INSERT INTO group_instance SELECT guid, instance, permanent FROM character_instance WHERE guid = '%u'", player_lowguid);" by "CharacterDatabase.PExecute("REPLACE INTO group_instance SELECT guid, instance, permanent FROM character_instance WHERE guid = '%u'", player_lowguid);" and "CharacterDatabase.PExecute("INSERT INTO pet_spell (guid,spell,active) VALUES ('%u', '%u', '%u')", m_charmInfo->GetPetNumber(), itr->first, itr->second.active);" by "CharacterDatabase.PExecute("REPLACE INTO pet_spell (guid,spell,active) VALUES ('%u', '%u', '%u')", m_charmInfo->GetPetNumber(), itr->first, itr->second.active);" so all REPLACE statements in the Query counting stats are INSERT statements in clear sources. As i know Replace Statements is not supported by PostGree SQL P.D: Keep in mind that kero99's servers is my servers! Its own servers ;-) So your pool of select connection is welcome ;-) Cheers

Asynchronous connection uses SELECTs in transations? Test in progress... ;-) [EDIT] General information for the test: Character DB size -> 15 GB Number of characters -> More than 200.000 entries Number of items in item_instance db -> 10.563.732 items. (3,3 GB of innodb data) Table with highest number of entries -> character_achievement_progress with more than 51.800.000 entries. (3,0 GB of innoDB data). Hardware: 2x 6 cores - 24 threads - 2x intel Xeon X5650 6x 2.66Ghz 48 GB DDR3 ECC Raid Controller SAS LSI (RAID HARD 10) Intel SSD X25-E 32 GB x 8 disk 4 raid hard. MySQL rev: 5.1.52 + Memory malloc for high scalability and performance developed by Google Mtmaps with MapUpdate.Threads = 2 30 minutes (1325 pl online): No issues reported MySQL Workload: MySQL Query Counting: MySQL Traffic & Query Analytics: 1 hour 30 minutes (2048 pl online): No issues reported, no crashes. MySQL Workload: MySQL Query Counting: MySQL Traffic & Query Analytics: 7 hour 30 minutes (2421 pl online): No issues reported, no crashes....

Sorry but query counters are globals to the mySQL Server, but the first stats counter only include characters db, mangosworld db and ScriptDev2 db. Realmd is in another dedicated login server (Realmd + mysql) 48 h stats for realmd dedicated server: This server is a login server for 3 realms, more than 10.000 players login every 24 hours. Some more mySQL server information: (Character Db - Mangosworld Db - ScriptDev2 Db) This afternoon i'm gonna make a test with your patch in the 2000+ pl server. I will reboot mysql server for restart stats and provide the feedback with clean stats.

Between 2400 - 2800 in rush hour. (Sometimes 3000+) I think mySQL cluster is not needed until 5000-6000 pl online (maybe more) My hardware for a dedicated mySQL Server is: 2x 6 cores - 24 threads - 2x intel Xeon X5650 6x 2.66Ghz 48 GB DDR3 ECC Raid Controller SAS LSI (RAID HARD 10) Intel SSD X25-E 32 GB x 8 disk 4 raid hard. Actually my CPU is always with only 1 thread at 85-90% of CPU load (Character database connection). and 23 unused threads So much processor for mySQL and about 20-30 GB RAM used. My problem is fixed with this patch if all works as expected! ;-) ^^ Screenshoot taken with 1261 pl online

Confirmed, i have had similar "freeze" crashdumps. A lot of them!!