DOL on Linux with Mono3

[Leodagan]

Yeah with a table using almost 3GB I understand memory can be more useful than for my inventory table of 110 MB

Did you try any performance optimization on Tables definition ? Like using numeric ID's, changing varchar to char (MySQL love fixed size type) reducing itemtemplate and uniqueitemtemplate ID to about 50 / 100 chars (MySQL can guess it using ANALYSE() ), setting indexes or unique constraints ? Reducing Numeric field size ? (like slotPosition that could be a 16bits unsigned.)

But DOL don't like fixed CHAR on PrimaryKey so you have to use a VARCHAR, VARCHAR forces MySQL to do a full index scan for searching a key.

I was wondering if DOL supports Database "Enum" types These are pretty optimized too (size does not vary between records, only when updating table schema !)

[Tolakram]

I modified the DB code a while back to use numeric primary keys (auto-increment) if desired for new tables. Unfortunately this is only a small part of the problem. The latest core solved a good bit of the Inventory table issue when Graveen normalized Inventory to move uniques out and then only have inventory track character specific numbers.

[Leodagan]

Yes I saw there was a great support for Auto_inc Primary Key, it needs to change some part of the table code to take advantage of this and break compatibility with Trunk Database, but it's great to be able to rely on it if there are performance issues

Having foreign key for item template and unique template is great too, but this table is clearly one of the most heavy one and I understand that can become a big performance issue with no specific attention to the database itself (DBA optimizations, Profiling, Etc...)

I really think that hardware capacity must be taken into account to handle a table that goes above 500 MB, a default config MySQL won't handle a huge shard inventory table... For now I just resized my CHAR field to the max itemtemplate length + UUID length, but further modification will need a data migration

[Blue]

We already use integer primary keys on all tables. But this was still not enough. Giving the database enough memory helped incredible with the query times.

[Leodagan]

Well I keep that in mind, I think there are some experimentations about splitting big table, I'm not sure how will search algorithm handle that then (I think you'll have to split your table with an ID range, and use it as prefix to query the correct table)

Needing 3GB of RAM for 3GB Table with a player base that isn't online all at the same time looks weird in terms of pure "theoretical" computing... I hear my algorithms teacher yelling O(n) is bad

[Blue]

I used partitioning on inventorytable. Did not help much.

But yes, keep that in mind, lots of memory is of utmost importance for InnoDB. Theres not one table anymore that makes problems for us. And as I said our login procedure took up to 1000ms to query all chars (with visible items) for the account.

[Graveen]

I made some tests 2 years ago with partitionning on inventory table, and the benefits where really ridiculous. I think the requests are not enough targetted to particular fields to enjoy this feature.

Storm use MyISAM instead of InnoDB. The lack of true transactionnal is counterbalanced by regular backups, and the performance gain is noticeable.

[Graveen]

Anyhow, i have no ideas how it is performance related in comparison of InnoDB with tons of RAM, and if adding RAM also benefits MyISAM (or any light engine).

[Dinberg]

Does anyone remember the archiving feature DoL used to have? The idea was that every day database entries of characters not logged in for ~10days would be moved to archived databases that weren't cached. This works not just for Characters, but also inventory item, account, etc.

I can't remember exactly why it was turned off, but probably because compatability was broken. It does seem like a good solution though!

[Blue]

Anyhow, i have no ideas how it is performance related in comparison of InnoDB with tons of RAM, and if adding RAM also benefits MyISAM (or any light engine).

My point is I kept using MyISAM all the years due to performance until I've read that report about InnoDB being slow with too less memory assigned. And they were completely right. InnoDB is now outperforming MyISAM.

[Graveen]

For thoses interested in more than a (true) assertion, here is a recent (2012) and complete bench: http://dimitrik.free.fr/blog/archives/2 ... in-56.html

Effectively, it totally outperforms massively MyISAM in many cases.
Warning anyhow:
- this is heavily related to MySQL 5.6 (although you could expect improvements with previous versions)
- a single core with 1GB ram is not really aimed by this bench

OMW to migrate Storms' to InnoDB !

[Leodagan]

Be careful on these benchs !

They are run on server hardware that isn't detailed, but seeing tests on 32-cores/threads could be a hint that it's running with a lot of memory and great I/O ! (like 10k rpm or 15k rpm SAS drives on a RAID cache board !)

More common hardware will hit other bottlenecks than these dedicated Database Servers !

[Blue]

Something appealing about InnoDB is row level locking instead table locking.

[HunabKu]

I think it's not objective but i fell that's more responsive on DoL with InnoDB.

[Leodagan]

yep Row-level locking can do a great job when updating a big table like inventory

And Inventory are the first source of "lagginess" that player can feel (not seeing the item updated when moving it in inventory is obvious lag...)