[11284]Prepared statements for MySQL [part 1]

[Auntie Mangos]

Ok, now thanks to Undergarun and kero99, I have some nice stats to share with you

This is the MySQL Server statistics for current mangos server, which is not using prepared statements. Taken from http://getmangos.eu/community/topic/15925/11045patch-multiple-mysql-connections-to-database/:

And here are the stats from MySQL for mangos with prepared statements:

42% of DB requests are using prepared statements now (look at the 'stmt execute' graph!) And this is achieved by only 90 requests out of total 900 implemented in mangos core.

Another interesting point is that SELECTs dramatically dropped to only 7% of total requests in comparison to up to 28.3% on regular mangos server. This might be due to lowered "Player.SaveInterval" value in config, I'll confirm that with tester.

Anyway, fingers crossed on upcoming small patch from fgenesis which will be able to get profiling data on per-DB-request basis. And in turn we will be able to move more queries which are critical to server performance on using prepared statements.

P.S. Correct me in case I did wrong interpretation of 'stmt execute' graph. In case of prepared statements, aren't the 'select/delete/insert/update' graphs updated too? Anyone?

Cheers to everyone

[Ambal]

Hi, everyone

Here is the first part of prepared statement work I'm doing for the Core. If you are not familiar with prepared statements, here are the benefits they give:

1) more speed - your DBMS is not spending time on reparsing your SQL requests all the time, it just does it once;

2) less bandwidth with DBMS - sending data in binary format is very space efficient comparing with plain string requets;

3) more security - no SQL injection is possible.

This part of a patch is intended to implement prepared statements support for INSERT+DELETE+UPDATE queries - such requests cover ~70% of all DBMS tasks. The SELECT statement support will be the next part of this work.

Currently I've rewritten all SQL requests which are invoked from Player::SaveToDB() function. Also, if someone has any profiling data about what SQL requests are used most of the time - I'll greatly appreciate it

NOTE: For PostgreSQL users there will be a compatibility fix, which will use current plain SQL requests - I don't have time to implement native support for it, sry =/

WARNING: BACKUP YOUR DATABASES!!!! And I would greatly appreciate if you can test this patch with mtmaps installed

repo: https://github.com/Ambal/mangos/tree/prepared_statements

update #1: PostgreSQL support is added. Major code refactoring.

update #2: ODBC-style API for parameter binding. Fix reported compilation issue. Support rev [11235].

update #3: More item queries are using prepared statements. Compile errors on *nix are resolved with kero99 help.

update #4: Fix broken SQL syntax for some prepared statements. Thanks to Undergarun for reporting.

update #5: CRITICAL - rewrite some code due to serious issues with function static variable initialization in multithreaded environment. Thanks to Vinolentus. Expect slight increase in CPU usage.

update #6: resolve issues caused by improper usage of static local variables. Should restore CPU usage to normal.

[Thyros]

hi ambal, your patches are very nice, i like that what you do.

[kero99]

Hi Ambal, thank you soo much for this implementation .

Compile error in linux, i post it in that commit:

https://github.com/Ambal/mangos/commit/fac5959508427baa2da69093220c7a177e546633

Best regards

[Unkle Nuke]

You just keep impressing everyone with yet another brilliant improvement to server performance, Ambal. You're one of the shining stars here and I hope you will always prosper in return for everything you have given to this community.

[lecails]

Awesome improvement.

[Ambal]

Hi, everyone.

kero99, thank you for pointing to compilation issue - I'll fix it definitely today.

P.S. I think I'll change API a bit also to make it more ODBC-style compatible aka:

SqlStatement stmt = db.CreateStatement();
stmt.addInt();
stmt.addBool();
...
stmt.Execute();

Exposing function parameters to client as it is done in current code is not very nice idea, IMO...

Cheers

[Ambal]

I've fixed compilation issues and updated my patch to the latest [11235] rev. Also more ODBC-compatible API is introduced for statement parameter binding. Feel free to try

[kero99]

Hi again Ambal, compile in linux still fail sorry =(, i post it in that commit:

https://github.com/Ambal/mangos/commit/f2d3783421c8cf12d1eb2a4640428949f61c3b97

Best regards

[Ambal]

compile in linux still fail

it is resolved, kero99. Try again

[kero99]

Hi again, code need some changes to compile in linux, i wrote a little fix, tell me if correct:

http://pastebin.com/f1vi7zJ5

Regards

[Ambal]

Your changes are fine, kero99. I've pushed them into my repo. Thank you very much

[Ambal]

Fixed broken SQL syntax for some prepared statements. Thanks to Undergarun for reporting.

[tyrael]

does this patch work like an anticheat?

I mean, players won't be able to speed hack or fly or climb? or dupe items?

[ankso]

does this patch work like an anticheat?

I mean, players won't be able to speed hack or fly or climb? or dupe items?

If you think so, you should may be learn a little bit more about MaNGOS/C++

...

[Ambal]

does this patch work like an anticheat?

No,it is not an anticheat - this patch is intended to speed up DB interaction. Current SQL requests in mangos are already protected from SQL injection attacks, prepared statements simply do not require any additional code to do this.

[Ambal]

CRITICAL: please, update to the latest patch version due to found critical issues with function static variable initialization in multi-threaded environment! And report by how much your server CPU usage has increased (shouldn't be more than 5-6%).

[Ambal]

New version of the patch which will properly use static local variables to hold prepared statement related data. Support for rev [11265].

[Ambal]

I suggest everyone interested to start testing the latest version of the patch since currently no problems were found on 1k+ realms. In case no issues will be reported I'll commit this patch in nearest week.

Cheers

[Undergarun]

Some information about prepared statements tests:

PlayerSave.Interval = 300000 (Save characters every 5 minutes)

PlayerSave.Stats.MinLevel = 0 (No stats storage)

Query Cache Hit rate = 0 (MySQL Query Cache disabled, Tweaking query_cache_size and query_cache_limit in your my.cnf should improve Query Cache Hit rate)

World of Warcraft Armory by Shadez (Increase MySQL workload seriously and the armory_characters_stats is wasting resources so it should be improved in other way because the blob is really heavy and bad way.)

MySQL SERVER Htop

3k+ ppl MySQL dedicated server workload

Best Regards to everyone and special thanks to Ambal for its georgeous work.

[Ambal]

In [11284]. Next stop is support for SELECT queries Thanks to everyone involved!