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!

we are not trinitycore. remeber

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 ;-)

petnumbers used for pet auras, pet talents and etc

$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.'.');
   }
}

?>

another extremely simple way:

UPDATE areatrigger_teleport SET required_level=81WHERE target_map = 601; -- Azjol-nerub -- was 67

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.

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.

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.

Guys, you should test this branch: https://github.com/SilverIce/mangos/tree/relocation_old_ver2 instead of branch_1 Do not mess patches - this is very important since they differ greatly in terms of performance

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.

correct i added "transaction-isolation = READ-COMMITTED" before making this report and it was working.

however transaction-isolation = READ-COMMITTED is not a default mysql config, so in order to run mangos it requires you to reconfigure mysql in order for it to work

Please, let me know if you'll be able to reproduce this issue again with default settings so I can take a look and help you. We are not interested in the fact that users have to change configs of their MySQL servers in order to keep up with the latest changes in the Core.

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 ;-)

Hmmmm rare, i dont have this issue O_o, mysql config problem?

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

+1 But i am researching the problem carefully.

In fact shouldn't be used IMO. It's based on old tc work. Too much work have been done since that 1st port. New code from cyberium is far better than that. (like actual TC work is )

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.

I never understood than the development wasn't finished, my apologised, but (always a but ) why not implementening some pieces, this developement is ongoing since many years, a lot of people have worked on it, and it is still suprising that it never attains the requirement to be integrated or reviewed, it is quite strange from an outside eye

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?

as i read REPLACE statement exactly same as DELETE INSERT pair by execute time

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

But RAM usage went down from 28GB to 9GB; so not really sure what those images should tell us.

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.

Is your server got any bonuses from dedicated connections for SELECTs? You have so powerful PC to host MySQL server so I guess you will barely see any differences

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....

Undergarun, can you provide us with query counters on per-database basis for characters, mangosd and realmd? It would be nice to see the full picture so we can decide how DB layer can be improved.

As I said, SELECTs are synchronous in 99% of time so having connection pool for them would speed up server. For transactions it is not so simple since they are asynchronous and if they are processed by DBMS at acceptable speed - we don't need to worry about them. If not and SqlDelayThread is overflowed with transactions - this means that this particular area also needs improvement.

Cheers

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.

Great stats Undergarun.

Could you please give us the average amount of players ?

Well, I will perhaps test read/write isolation with a clustered MySQL on low populated server, but I'm still afraid of the database consistency due to the replication. For your information Tungsten cluster does replication very easily, and it includes a connector with SQL read/write splitting. On two VM (single core 1,6GHz with 512Ram) I got some nice perfs (around 700 rw/s). PM me if you need some information on how set it up.

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!!