MySQL InnoDB and BDB tables support ?

[coughcool]

Is this a possiblity? I think this could improve preformance on the DB servers if we could use transactions.

[DeletedUser]

How so? We are doing 1 insert/update command at a time... there is no reason to use transactions for this imho... if we were updating or inserting multi lines at once, I could see a reason too...

[coughcool]

Honestly I don't know MySQL or Databases very well. I also don't know how the zone servers talk to DB. It certenly would be nice to know if you can spread any light on this.

I found myself reading over some info on Transactions in MySQL.

http://hotwired.lycos.com/webmonkey/backend/databases/tutorials/tutorial2.html

Specificly

http://hotwired.lycos.com/webmonkey/02/11/index3a_page5.html?tw=backend

quote "MyISAM supports only table-level locks of two types: read locks and write locks. "

and

http://hotwired.lycos.com/webmonkey/02/11/index4a.html

quote
"When a client places a shared lock on data, other clients cannot alter that data with UPDATE or DELETE statements, but they can read the locked data via SELECTs. When a client gains an exclusive lock, other clients can neither alter the locked data via UPDATEs and DELETEs, nor can they read the data with SELECTs."


My original thought was on locking. I don't know how many times the eqemu servers update or change the DB if at all. But it seems to me that it would alow multiple people to use the same data at the same time without the deley of the defaul MySQL table type.

Of course this assumes that there is a chance of multiple people accessing the same data.

[theCoder]

I don't think the EMU needs (right now) anything as complex as transactions, since, as Hogie stated, all the operations are single read or writes. Locking could be a concern for scalability, but I think other things such as processor power, memory usage, and bandwidth are more limiting that a process having to wait a couple ms. for a lock to be realesed. But I could be wrong... has anyone experienced any problems with MySQL locking tables or have any numbers showing it's slowing things down?

[Trumpcard]

I havent had any problems so far.. I mega optimize compile my stuff for performance, but I doubt I'm squeezing that much more performance out of it than a precompiled rpm version. At this point, transactions wouldnt really gain us anything.. Later on though, stored procedures will be the way to go though as the query strings and operations start to get bigger and more complex (if they do, might not ever go that way). This is optimal when you have an offbox database, alot less traffic to call the procedure than to pass all the sql.

Im interested in seeing if its directly portable to the 4.X seris of mysql, there are alot of nice performance boosts in the new line.

[DeletedUser]

It should be compatiable with mysql 4.x... You might need to compile with 4.x libraries, but that would be it.

As for how much the data changes.... There are really only 3 tables that change, and one of those is only when you have an option turned on (which is turned off by default).

They are: character_, account, and zone_dump.

The other data is static and only read by the servers. The only time you would update them is when you update your server and your server software should be OFF at that point.

As for character_, it is 1 row per character, and you should not have 2 people logged into the same character at the same time, bad things happen, if that happens, they can be blamed for corrupting their data.