[11045][patch] Multiple MySQL connections to database

[Ambal]

Hi everyone.

Here is a new patch which rewrites and refactors alot of DB code. Several features of this patch:

1) dedicated DB connections for sync (SELECT) and async (INSERT+DELETE+UPDATE) SQL queries. All transactions are executed from async connection so your DB queries will be able to run in parallel. Please, pay attention on server performance.

2) less locks are used in code - better scalability. SqlTransaction class is not using mutexes anymore.

3) allow multiple threads to issue async DB queries with callbacks. Currently only main thread was capable of doing it.

4) simplify code by making refactoring.

Also here is a brief numbers about how many SQL queries of specific type we use in mangos code:

1) SELECT - 398 (191 queries are issued upon server startup: characters - 39; mangosd - 151; realmd - 1)

2) INSERT - 88

3) DELETE - 255

4) UPDATE - 158

So basically SELECTs will use their own connection, and INSERT+DELETE+UPDATE SQL queries will use the other one. This should help you boost your server performance.

WARNING: please, backup your databases before starting tests!!!. In case you will spot any issues with data consistency - we will revert to using single DB connection immediately!!! Please, also test this patch with mtmaps installed.

Updates:

Upd#1: 'Autocommit' mode for MySQL is disabled. For atomic updates it is better to use transactions as more portable solution.

Upd#2: patch is moved on rev [11010].

Upd#3: patch updated to rev [11030]. Configurable connection pool is added Up to 16 connections for SELECTs. Async queries still use dedicated connection for transactions. Also some 'SaveRespawnTime' transactions are combined into single transaction for speed. NOTE: update your mangosd.conf files!

Upd#4: PostgreSQL support is added. Compilation error in prev commit is fixed. Patch is updated to rev [11040].

My repo: https://github.com/Ambal/mangos

Branch: master

Happy testing

[kero99]

OMG!!!!!!!!!!!! Is a wondeful patch!!!!!! Really i need it!!!!! =)

But i have some pain to test it in production environment with 2000+ ppl online... i need to prepare some event in a RPP server

Thanks soo much

[Ambal]

Also, you might try following option in your MySQL config in conjunction with current patch to squeeze more performance from DBMS:

[mysqld]
transaction-isolation = READ-COMMITTED

This will lower transaction isolation level and should speed DB queries a bit more.

[Undergarun]

Ambal has earned the achievement Mr. Performance!

Thank you so much, testing right now!

[selector]

Gods heard my prayer. Thank you!!!

[Undergarun]

3) allow multiple threads to issue async DB queries with callbacks. Currently only main thread was capable of doing it.

How many? One per map.update thread? One thread means one mysql connection?

Thank you 4 all the work.

Did you see https://github.com/TrinityCore/TrinityCore/commit/87218eadcdeac5ba86a035edfd079958405cb24f?

24h of a heavily mySQL Server Workload supporting only a MaNGOS Database with current db layer

[fdb]

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.

[Undergarun]

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

[Ambal]

How many? One per map.update thread? One thread means one mysql connection?

Undergarun, thank you greately for MySQL query counter data - it is more than helpful

Currently each database has 2 (two) connections (you can see it on screenshot provided by Undergarun):

1) for SELECTs, which are executed in sync

2) for transactions, which are executed asynchronously

Speaking of Database::AsyncQuery() function - in current core, unfortunately, you can successfully issue this command only from the main thread. In this patch this will be fixed.

We do not use pool of connections currently since we need profiling data from MySQL to find out what is the bottleneck. Also, current core lacks 'prepared statements' which will improve DB performance even more so brute force connection spawning might not be very useful. Please note that if connection pool is not a problem for making SELECT queries - it is real PITA for making transactions.

If you will see bonus from using this patch and no problems will be spotted - we can develop DB layer even more: add configurable connection pool etc. So continue doing the test and provide us with all information you can and we will use it to make mangos better and faster.

P.S. Guys, currently we have alot of queries in SaveToDB() / RemoveFromDB() which are not wrapped into one single transaction. Take a look into GameObject::DeleteFromDB() - instead of one single transaction we issue up to 5(!) queries! We have more room for optimization, thats for sure.

Cheers

[selector]

offtopic:

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.

Will you marry me and give access to this monster?

[leak]

24h of a heavily mySQL Server Workload supporting only a MaNGOS Database with current db layer

Chart produced by?

[Ambal]

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

[Undergarun]

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.

[Ambal]

Thank you, Undergarun. I'm looking forward to get results of your tests! In case your server will be less laggish with current patch - I'll definitely implement configurable DB connection pool for SELECTs

Cheers

[Undergarun]

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

[zhenya``]

many insert + delete queries in respawn tables.. may be make it MEMORY?

[VladimirMangos]

we have option delay update respawn tables until grid unload.

[Ambal]

Undergarun, thank you for your tests. 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

In current design for async SELECTs we utilize async connection <- this was done for simplicity. Ideally in SqlOperation class objects we need to pass pointer for SqlConnection they will use when executing queries.

Currently I'm at work and cannot download images with profiling data you've posted - will take a look into them this evening. Also, in nearest future I'll definitely update a patch with configurable connection pool for SELECTs.

Stay tuned

[Gotisch]

From cpu usage images with before patch and after looks like cpu usage went up: before it was (avg) 1.24 then it was 1.78 everytime with ~ 1200 people online.

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

[selector]

Tested with 1500ppl. 9hrs uptime and crash(not related to this patch). No issues, no problems. CPU really raised a little, but memory went down from 12G to 8G. After this patch I don't have "mem jumps" with mysql process.

Perfect work Ambal

[Ambal]

Increased CPU utilization by MySQL server can be explained by more queries parsed and executed in parallel.

More queries basically means more SELECTs - with current DB layer where SELECTs occupy only 28.3% of total DBMS requests and can be simply 'starved' by transactions since we have only one single connection protected by mutex. With proposed patch SELECTs are executed in parallel so there is only contention for sync connection. With connection pool this contention can be lowered even more.

Cheers

[kero99]

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

Is only that to test undergarun restart mysql service and innodb flush all cache in memory... in some days RAM go up to 28 again ... innodb cached memory can be set in my.cnf.

Maybe undergarun show us CPU per thread usage with new db layer, in old layer you can see a big usage of one character mysql connections ( CPU1=68% ) and now you can see two of then (CPU1=31%, CPU3=19%)

Best regards

[Undergarun]

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

[VladimirMangos]

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

[Undergarun]

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