InnoDB or MyISAM The Best Performance

[davide874]

Hi everyone,

i just figured out how poor is l2j databse implementation due to the lack of foreign keys.
there are no checks on the consistancy between tables (i.e i can create 2 npc with same id in custom_npc and npc tables)

I'd like to contribute to the l2jdp project by completely reworking the database and chaning the engine from MyIsam to InnoDb (because MyIsam doesn't allow foreign key constraints)

I'd like a reply from someone of the L2J team to tell me if my idea is worth the effort.

Best regards!

[MELERIX]

(i.e i can create 2 npc with same id in custom_npc and npc tables)

you can't because "id" is PRIMARY KEY

only "idTemplate" can be used by more NPCs (for obvious reasons) because is used for model design

or you mean about duplicated "id" in both tables at same time ?

and btw tables are designed to use the defaul engine from your MySQL version, because some people still use old versions of MySQL using MyISAM by default.

thats why we don't have "forced" things like... ENGINE=InnoDB inside tables.

[davide874]

mmm i see (btw i was referring to have 2 same ids in different tables)

I don't know what's the policy inside l2j team... but let me suggest you (MELERIX, cause you're the only one who ansered me ) to suggest other l2jdevs to switch into InnoDb engine. Adding foreign keys with cascade or restrict policy would make the db more consitent avoiding duplicated data or un-needed one.

I also documented myself a bit on how performances impact the 2 engines and it seems that InnoDB acts really fast expecially in SELECT queries (the most frequent in l2jserver once it's fully started)

Hope to see some more replies on this just to know other people opinion

Cya!

[Probe]

Static table that are only loaded once on server startup should stay with myisam, but dynamic tables (character, clan, items etc) should be InnoDB.
and yes foreign keys would be a big improvement but l2j's idfactory does the clean up itself on server startup

[davide874]

Static table that are only loaded once on server startup should stay with myisam, but dynamic tables (character, clan, items etc) should be InnoDB.
and yes foreign keys would be a big improvement but l2j's idfactory does the clean up itself on server startup

oh...that's something i don't know about... can you tell me more about the idfactory thing?

i also read on some blogs that mixing different table engines inside the same DB is not a good solution in term of stability and performance. I'm not an expert in the field so it's just what i read :/

[Probe]

As a substitute for foreign keys, l2j has these queries:

http://trac.l2jserver.com/browser/trunk ... .java#L199

what I did was add foreign keys to db where possible, and I kept only the clean up queries that can't be done via foreign keys (for eg, onboard CB tables, territory wars)

as for the engine thing, dunno works great for me and many others

[shoko]

InnoDB or MyISAM The Best
I has open server has longtime ago from hellbound(l2jfree) and today Hi5(l2jserver) without lost data database table items 1.2G if I Use MyISAM Structure
and I has ALTER TABLE items; to InnoDB Structure it file has change to Big size 3.2G
I use same computer spec Corei7 Ram8G SSD 128G Windows 2003 server 64bit Mysql 5.5
can be Discussion about this InnoDB or MyISAM The Best Performance for use I will goto upgrade that
sorry my english is so bad

[MELERIX]

performance in both engines will be similar (if you have a good server), but InnoDB could take an advantage with MySQL 5.5 and above, due the use of multi-thread CPU.

now about security, I personally prefer InnoDB, because is safe, instead of MyISAM that if system crashed or electric energy goes down, most of the tables could result damaged.

here is some more info: http://www.oracle.com/partners/en/knowl ... 522945.pdf