[patch][6924] faster guild-loading

[balrok]

who has written the patch?

balrok

what is the aim of the patch:

speedup guildloading at server-start

is there a bugreport?

no

Cause the last days i had a real big characters-database with very much testdata, i noticed, that on server-startup there are many 4 queries for every guildmember to get it's data..

example:

  SQL: SELECT name FROM characters WHERE guid = '1086483'
  SQL: SELECT data FROM characters WHERE guid='1086483'
  SQL: SELECT zone FROM characters WHERE guid='1086483'
  SQL: SELECT class FROM characters WHERE guid='1086483'

cause it always accesses characters-table i thought about merging those queries together..

a viewable patch can be found here: http://mangos-files.eu/patches/?file=faster_guildloading.patch

and a downloadable one here: http://mangos-files.eu/patches/faster_guildloading.patch

i haven't tested,if this realy will improve the speed, also the code looks not so nice as before, so you have to decide what will be better

[bsidebg]

Man... you are great

[Alex]

Don't really think it'll help much if MySQL server is connected directly (i.e. via Unix socket). MySQL row cache does its job well. If MySQL server is connected via network though, it's another story.

[leak]

Indeed, already had network cards that required special kernel flags to be able to deal with the mass of tiny queries that mangos is doing on startup.

Gonna test this one asap.

[leak]

Alright down from ~56sec to ~49sec guild loading time having mysql on an extra server with a dediacated connection.

I think this is definately a step towards the right direction.

Guild loading still consumes about 50% of the mangos bootup time, i hope there is some room for further improvement.

[freghar]

Alright down from ~56sec to ~49sec guild loading time having mysql on an extra server

Guild loading still consumes about 50% of the mangos bootup time, i hope there is some room for further improvement.

Well since I moved my old DB to InnoDB engine, the guild loading got about 5 times faster ...

[freghar]

Don't really think it'll help much if MySQL server is connected directly (i.e. via Unix socket). MySQL row cache does its job well. If MySQL server is connected via network though, it's another story.

IIRC MaNGOS uses only TCP connection to the database server(s). Last year's benchmarks proved they have almost the same response and are faster than local unix socket connections.

Ofc you can use several layers of caches, including memcached ...

[balrok]

if this doesn't affect the speed so much, i think this can be rejected..

cause the coding-style doesn't look as good as before..

i made the last improvement, which i could find about this, and merged this new query to the guildloading query above..

in my github repo under the branch "guildloading" this can be found..

but i don't think i look further for this, cause i just did a delete from guild, and now my problems are solved^^

maybe mangos shouldn't load all guilds at startup.. only that one, which are needed in the last 2-7 days.. and if guild-information about not loaded guilds is needed, then it trys to load it after this.. - but i can't implement something like this^^ - it's just an idea

[VladimirMangos]

Patch in [6924]. Thank you!