Better storage engine

[shrundowr]

Dont kill a great idea, because the way it is done.. is not very good.

New inventions always cost more money, than they may be worth. But thats not the way new things get made.

(Yes, i am new to this project)
But can you give me a pointer to were i can find this proof, why this MySQL idea wont work?

No one said it wouldn't work, they (RunUO Developers) simply said it they tried it before and it was actually less efficient than flat file. CTRL+F for "Ryan" on this page

 

[Belni]

Ofcourse its less efficient than a flat file.

RunUO was writen in C#, not in ASM. For every choice you win some, you loose some.
Game wise, its smart to use flat files, as data storage. But if you want to monitor you economy or see where all your mobiles went, etc. A SQL database is a much better choice.

Ever wondered how many female players, were wearing a hat? No small task to get that out of a flat file based system... On a DBMS system almost anyone can write such a query.

 

[Jeff]

Ofcourse its less efficient than a flat file.

RunUO was writen in C#, not in ASM. For every choice you win some, you loose some.
Game wise, its smart to use flat files, as data storage. But if you want to monitor you economy or see where all your mobiles went, etc. A SQL database is a much better choice.

Ever wondered how many female players, were wearing a hat? No small task to get that out of a flat file based system... On a DBMS system almost anyone can write such a query.

Not where it is done like Acronis did, he stored everything in 1 column, all properties concatenated. To be honest, this project could have been cool if he did it right, but he got lazy.

P.S. it has nothing to do with C#. Using C++ to SQL would not have made much of a difference, if any.

 

[Belni]

*grins*

Atleast we agree, that it would be cool... If done right.

 

[Kamron]

Many people have brought up the point to me that commercial MMORPGs like WoW and Everquest use databases and ask why it wouldn't work for UO.

A couple things come to mind. First, the hardware used is incredible compared to 99% of RunUO servers. For databases (any type) to be functional, the server must have multiple processors with multiple cores each and a shitton of memory. Then dedicate a processor to a subshard of the gameserver, and another processor with a separate hard drive and ram set (blade servers can do this), to do database work.

In addition, using blade servers with custom operating systems and cluster software (which was introduced with .NET Framework 4.0... so they say), allows each subshard of the game server to simultaneously back up objects to a local backup server (on the same rack). The local backup server is highly redundant, and then shares universal information with the main account servers.

Keep in mind that for all of this to be necessary (and practical), a server would need THOUSANDS (2500 for WoW) of players online at a time ALL the time. Also WoW doesn't have world saves, because there are no player owned objects (like houses or boats or other clutter on the ground) that are left in the world when the person logs out. When you log out, your character is PULLED from the world COMPLETELY. Its taken out of memory and saved back into the database using a queue system. This is the reason why it takes time to mail objects from one player to another within WoW.

None of this is necessary for Ultima Online, and the portability that WoW uses cannot be replicated because there are objects such as houses, boats, guildstones, etc.

Also I would propose that a database is not necessary to make world saves faster. Someone could code a system that does incremental backups, where everything that is flagged for change is recorded to a new file, and every 24 hours, a complete backup is done (merge all incremental using a separate core, so you don't freeze the world). This would reduce the worldsave by the number of changed objects, and provide a way to go through changes as needed, without the overhead of a database. I have done testing with a setup like this using a 1GB worldfile, and it reduced the save time from 25 seconds (3.0ghz quad core extreme using .NET Framework 4.0) to about 4 seconds. Of course the delay is based on activity, the more active the shard, the longer the delay will be.

EDIT: I forgot to mention that there were other changes which led to the drastic decrease in world save time. Serializing accounts in binary instead of XML, not serializing default names for mobiles, not serializing hp, stam, or mana for mobs, not serializing spawners (they are serialized separately to keep the shard portable), not serializing static items (these are also serialized separately for portability), and not serializing inactive spawned mobiles or spawned items (everything is dynamically respawned as needed when the shard boots up). In addition, none of my dynamic systems (active but unused champion spawns, duel pits, or tournaments) serialize. If the shard loads up during one of these events, everything is automatically reset.

 

[Acronis]

I have not touched this in a while as I was working on another project but saw my mail so thought I'd reply with an update.

Yes I will agree I did not use the best structure. What I am doing here is like trying to make a car fly. It was never designed to do so, so sometimes you have to compromise.

Each item type has a different number of "fields" and there would be 1000's of tables if I actually structured it properly, and it would just become nuts to deal with. Though, one way this could be done is with a base class that handles all of this, so each type would have an instance of this class, and define all the fields and such. Maybe in the future, this could be changed. It would also make flagging for change much easier, as it would all be handled in one spot.

Also this is a 1 way thing, data is being written to SQL, not read (other then at startup). I've thought of real time read/write but think that would have too much overhead.

This is still a very highly experimental project. I've ran into so many roadblocks due to how certain things are coded in RunUO that I have considered several times to code an emulator from scratch with a better back end. I also want to be able to dump windows, without trying to use emulation such as mono/wine. So at this point, things could change. I am not yet sure if I want to release this or not, but if I do I will most likely have to release my entire distro as there has been 100's of changes to make this work. Impossible to track at this point.

 

[Kamron]

I know how you feel, I am at that point as well. To get the optimizations I made in my proof of concept, I heavily modified over 90% of the files in RunUO. In addition, before I release my incremental backup system, I would need to rewrite it to make it easier to implement. I ended up modifying the serialization of EVERY object. If someone is not up to the boring and tedious task of modifying all of that, then its definitely not worth it.

I have also been thinking about the best way to flag objects for modification. If you use a wrapper for each object, you could track it on a per property level, and only serialize the modified parts instead of (my current lazy way) of serializing the whole file.

The sad fact is, 99% of shards don't need any of this, and never will.

 

[Jeff]

The really sad part is that I've said this from the very beginning. The hardware required and time dedicated to maintain this would be more then any shard administrator currently has.

Lets take WoW as an extremely overblown example. They have to have roughly 5 professional DBA's (I'm guessing sure, but honestly, I'm probably under-exaggerating) on staff 24/7 per server, constantly monitoring server load and balancing across probably some insanely huge clustered sql array. DBA's that probably get paid more money per hour then I make in a day. On top of that, they use Linux and Oracle, Oracle being one of the best Database Engines (and also the hardest to learn to DBA for) out right now.

Now, I know WoW is an extreme case, with thousands of constant users, but still, even for 100+ users, the hardware required for something like this would be at least well above what anyone can afford. Not to mention the time for the constant maintenance and sql script writing/optimization that would have to go along with it. This entire project started, and was told it wasn't feasible from the beginning, and most likely just to try to prove us wrong Acronis tried it anyway and I commend him for the effort. But, as I've told 20+ people before him, it just isn't a reality for to do something like this for RunUO.

 

[Kamron]

I agree Jeff. It would be one thing if UO was a game where you had 5000+ people online a time, and you could charge money (or afford it with donations)... but its not. I definitely wouldn't spend more time than the proof of concept I did, because I know its a waste as well.

 

[Tartaros]

The memory limit isnt with .net, its a process memory limit for x86 in general. Thus just switching to x64 will fix this... It has absolutely nothing to do with .net

actually, it does. The CLR itself must be compiled in 64bits. And since it does a lot of memory magic, I'd be very surprised if it didn't need any internal modification too.

 

[Tartaros]

I know how you feel, I am at that point as well. To get the optimizations I made in my proof of concept, I heavily modified over 90% of the files in RunUO. In addition, before I release my incremental backup system, I would need to rewrite it to make it easier to implement. I ended up modifying the serialization of EVERY object. If someone is not up to the boring and tedious task of modifying all of that, then its definitely not worth it.

I have also been thinking about the best way to flag objects for modification. If you use a wrapper for each object, you could track it on a per property level, and only serialize the modified parts instead of (my current lazy way) of serializing the whole file.

The sad fact is, 99% of shards don't need any of this, and never will.

could you elaborate about the principle of your system?


In any case it's hard to imagine that the result would be in any way compatible with the official release, since persistence code is part of everything, as you said you needed to edit every file

 

[Jeff]

actually, it does. The CLR itself must be compiled in 64bits. And since it does a lot of memory magic, I'd be very surprised if it didn't need any internal modification too.

Unfortunately you are wrong. The maximum size of any CLR object is 2GB, regardless of x86 or x64 bit builds. The limit itself is per object so yes you CAN exceed 2GB total in x64 but if 1 object hits 2GB in size (and this includes arrays) your assembly will die.

 

[Tartaros]

Unfortunately you are wrong. The maximum size of any CLR object is 2GB, regardless of x86 or x64 bit builds. The limit itself is per object so yes you CAN exceed 2GB total in x64 but if 1 object hits 2GB in size (and this includes arrays) your assembly will die.

How can I be wrong when I never talked about single objects?

I was just refering to your "correcting" of Acronises "I'm hoping .NET has a higher memory limit in 64-bit", with you saying it has nothing to do with .NET - it has, and the concern is justified. If there wasn't a x64 build of .NET, it would have no advantage on a x64 system.

 

[Jeff]

Problem is, data is generally stored in Arrays, which would still have a 2GB limit.. thus making x64 useless to some point. I'm not saying x64 is completely useless, I'm stating that it doesn't really help due to this object limit. Anyway, to be honest, this discussion is semi pointless, Acronis will most likely never release code, and the system has been proven to be useless due to the hardware demand and needed time to maintain the database.

 

[Tartaros]

Data is generally stored in objects... and yes arrays can bo some of the bigger ones but seriously - when was the last time you needed an array of more than than 268,435,456 (268 millions) objects?


The system may be unfeasible for the general RunUO release, because it makes the whole thing far more difficult to set up, but some enthusiasts could still make use of it

 

[Jeff]

Not really sure where you get your numbers from, but the max size of an array is not in the millions, its 2,147,483,647 (2 billion)...

Anyway, a point is a point. I think we can both agree a system like this is pointless, even for the most active servers (UOGamers, etc). The amount of dedicated time you would have to spend just maintaining it, is quite annoying.

We can agree to disagree, or whatever other feelings you have, but in the end, a system like this will only prove to be nothing more then a waste of time. What I don't understand is what do you gain with a system like this? You spend 1000+ hours developing it, making it flawless and useable, only to gain 5-10 seconds back every hour? Come on now... with all "hobbiest" attitudes aside, what is the real appeal in creating a system that no one will be able to use aside from yourself (and even then, chances are you dont have the time to maintain it). Seems to me, that someone is looking for a "Look what I did" rather then a "Look what I contributed"... Oh well, it takes all kinds i suppose.

 

[Tartaros]

Not really sure where you get your numbers from, but the max size of an array is not in the millions, its 2,147,483,647 (2 billion)...

divide that by 8 bytes, which is the theoretical size of a 64bit pointer, and voila...
size counter could be another limitation of the array size, but that's just an implementation detail


Anyway, still waiting for Kamron's answer, his solution could make more sense.

As for "why spend xyz hours to do abc", the answer is - why not? Why do people spend time to create UO shards / emulators / whatever... will be probably more or less the same motivation you have

 

[ntony]

Not really sure where you get your numbers from, but the max size of an array is not in the millions, its 2,147,483,647 (2 billion)...

Anyway, a point is a point. I think we can both agree a system like this is pointless, even for the most active servers (UOGamers, etc). The amount of dedicated time you would have to spend just maintaining it, is quite annoying.

We can agree to disagree, or whatever other feelings you have, but in the end, a system like this will only prove to be nothing more then a waste of time. What I don't understand is what do you gain with a system like this? You spend 1000+ hours developing it, making it flawless and useable, only to gain 5-10 seconds back every hour? Come on now... with all "hobbiest" attitudes aside, what is the real appeal in creating a system that no one will be able to use aside from yourself (and even then, chances are you dont have the time to maintain it). Seems to me, that someone is looking for a "Look what I did" rather then a "Look what I contributed"... Oh well, it takes all kinds i suppose.

It may be true that the save time would not be shorten too much. But there can have no save time at all.

My approach maybe stupid. But maybe there are some wise buddies can make them better.

1. Make update mask for all class attributes. This maybe a terrible huge task. Maybe implement an interface to handle all attributes in an abstract method?

2. Log and queue all object updates into file. Updates can be stored into a queue class on memory, and then log to file.

3. Use a thread to process the queue and save the updates from logs in queue into database. After update succeed, the log in file should be removed.


There isn't a "save time" at all. I think online game engines are using similar or better approach like this.
Of course, this is a huge mission. Worth or not? The people who say "yes" should do it.

 

[Jeff]

It may be true that the save time would not be shorten too much. But there can have no save time at all.

My approach maybe stupid. But maybe there are some wise buddies can make them better.

1. Make update mask for all class attributes. This maybe a terrible huge task. Maybe implement an interface to handle all attributes in an abstract method?

2. Log and queue all object updates into file. Updates can be stored into a queue class on memory, and then log to file.

3. Use a thread to process the queue and save the updates from logs in queue into database. After update succeed, the log in file should be removed.


There isn't a "save time" at all. I think online game engines are using similar or better approach like this.
Of course, this is a huge mission. Worth or not? The people who say "yes" should do it.

You would double your memory usage by doing this.

 

[ntony]

You would double your memory usage by doing this.

There maybe a slightly increase on the memory usage. But why double? How is the memory spent on? Maybe my bad English didn't explain it well.