[11045][patch] Multiple MySQL connections to database

[Ambal]

Thank you, Undergarun I'll see charts when I'll come back home. Also I have to admit that connection pool itself is not able to save you from 2k ppl trying to login Especially if only one thread is processing async login requests

[Patman128]

Also I have to admit that connection pool itself is not able to save you from 2k ppl trying to login Especially if only one thread is processing async login requests

I was going to say something, but I realized I was wrong.

Maybe realmd should be modified so that it can handle logons with a thread pool, since they should be easily parallelized.

[Ambal]

Configurable connection pool for SELECTs is added to the latest version of this patch, which is updated to rev [11030]. Up to 16 connections per database. Do not spawn more connections than threads you create with mtmaps patch - this is useless. Additional patch details in first post.

Update: now interesting data about number of transaction requests we've created to put data into each database in mangos:

1) characters - 411;

2) mangosd - 51;

3) realmd - 42.

And number of SELECT statements we've created to preload data on server startup:

1) characters - 39;

2) mangosd - 151;

3) realmd - 1.

These numbers are approximate since code search sometimes doesn't provide exact data Yet anyway, with these numbers and profiling data kindly provided by Undergarun we can think of how to tune our DBMS, amount of connections and code

Cheers

[Ambal]

While everyone seems happy with configurable pool of DB connections, a few words about what is the future of DB layer will be.

We should move on using 'prepared statements'. Tonns of queries are executed alot of times, so prepared statement will be very helpful - your MySQL servers will not consume CPU on reparsing the same requests again thus being able to process more requests. Network bandwidth will be reduced since data is transfered in binary format etc.

Extensively increasing amount of connections to DB cannot save us with transactions - it is VERY difficult and error prone to execute them in the safe way using several connections. So this is +1 to move on using prepared statements.

We wanted to use third-party DB library like SOCI http://soci.sourceforge.net/ which supports by using native API DBMSes like MySQL/PostgreSQL/Oracle. But it uses unusual syntax to work with DB so I have doubts about its chances to get into mangos repo.

We can try and revive patch written by Wyk3d long ago since currently I don't see any library which can be easily integrated into mangos.

Cheers

[Ambal]

Ok, you have a few more days to test this patch and submit your comments while I'm preparing a PosgreSQL patch. ...And then we will move on 'prepared statements'.

Cheers

[Undergarun]

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?

[Ambal]

you have to fix compile. What about mysql autoreconnect feature for mangos connections?

Hi, Undergarun. Thank you and kero99 for pointing on compilation issue. Autoreconnect feature is disabled by default in MySQL 5.1. Enabling it is not a problem as well as using a server-side ping() functionality... But if your server looses connectivity to DBMS - it is fatal error (IMO) and simple attempt to reconnect would not help in 99.73% of cases. Also, there are some side effects which will be hard to deal with when we will have prepared statements implemented: http://dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html. So I personally have doubts if we actually need this logic in Core.

Patch is updated:

1) PostgreSQL support is added;

2) compilation error in latest commit is fixed;

3) support for rev [11040].

Cheers

[Undergarun]

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.

[Ambal]

Undergarun, thank you very much Do you have any data about max queries executed on current mangos core so we can make a quick comparison? Maybe you have a new 24h/48h SQL profiling statistics with this patch?

If anything is fine then get ready for this patch to be submitted in nearest 1-2 days. This also does not mean that we will abandon DB layer and will not improve it any further

Cheers

[Undergarun]

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

[Ambal]

In [11045] Big thanks to Undergarun, kero99 and selector. Have fun