Better storage engine

[Adaptatron5000]

This has been mentioned a lot but I say it again since I truely believe it would be good. I have plans in the future to do this for my shard.

1: Make everything load and save to a mysql database. The trick here is you only save what changes, or "update" what changes. There would not be actual world saves. Instead, every time an item changes, it is sent to a queue of some sort that is process in another low priority thread and commits to the database. So either an update query, or an add, or a delete, depending on situation. This is done real time, but if a ton of stuff happens at once, like tiling 200000 dragons, it would maybe take a while for it to be written, as you'd want the queue to have a delay so its not bogging the server (like a save, which currently needs to freeze the world).

2: Some stuff should be in Item, not in child class. ex: material ID and AoS props. The way stuff like materials and AoS properties are set now is horribly ugly. If armor, weapon, clothing, talisman, ring etc all have materials and AoS properties, and are all wearable items, then why have seperate clases for them? The amount of typecasting throughout all of RunUO is brutal when it comes to wearable items. Put them in Item. What if you want a lantern to have some properties? Can't. But if you put all this stuff in Item the possibilities are endless.

1 and 2 kind of go together in a way, since way I would see it is one mysql table would be for Items. So all the variables in Item would have their own myself field, starting with serial. Proper indexing would be needed too. Now for the extra stuff that special items have (ex: spawners have mobile lists and spawner properties) then either a seperate table could be used, or simply serialize it in a binary field in the same table, like a "other" field. I'm not sure how good mysql is for binary data though, so maybe theres a better way.

In the future I plan to do these changes and many other major ones to my shard, so I can always post back how it works out. The trick to such huge change though is to make sure the data itself is not lost in transition.

[Garret]

I realy like posts like that.
Was thinking about Item's class re-design for a while.
Best way i think will be Item's sub-classes
Wearable class - for items that may be wearable, will contain all posible properties.
OR use interfaces for it, so if item should be wearable, attach wearable intarface that caontain all needed, maybe wearable interface coulbe better to parse for sub-systems, like resists, weapon\armor attributes, special functions (like replensh charges on SE flutes).

About SQL as db for saving, as idea it's great, updating only changed things in place of saving all things together all the time. Btw it will be posible to make back-up function to undo made changes based on date\time of chanege.
p.s. One man already made runuo on SQL as db for saving, couldn't tell his name until he'll agree.

[Jeff]

1. This is not following good programming practice. If not ALL drived classes of Item should have those properties, item should not have those properties.

2. This has been discussed but was turned down for a couple reasons, ease of use, and sheer volume of sql data. Most ppl dont know sql and dont have access to a server that could handle the amount of load this would have on a sql box.

[arul]

Originally Posted by Jeff

1. This is not following good programming practice. If not ALL drived classes of Item should have those properties, item should not have those properties.

Not all, but what about the majority? I did a little item dump on my shard (1.1M items) and nearly 90% of the items had AOSProps and MaterialID. The rest were just Static decorations and spawners.

So, I'll elaborate on the original suggestion a bit:

1/ Make the AOS properties, materialID and probably a few more props part of the Item class.
2/ Make it so Static doesn't derive from Item and is a lightweight, sealed and immutable struct/class.
3/ Consider the above for the spawners as well.

Those changes should make an AOS+ server most of the time faster, and never slower. Plus the memory consumption should also be lower.

But who knows if it's worth the effort ...

[Garret]

Jeff, reread my post.
We have base Item class as is, it won't be changed at all, if we need to create item with aos properties, we crate it based on Item class with attached interface, we could attach only needed parts.
need resistances? attach Resistance intarface
need weapon attributes? attach weaponattributes interface
need armor attributes? just attach needed.
If we need to create a decoration item, we will create class named "Static" base on Item class, and nothing more.
For containers all will be same as it's now.
Need to create a sword? make class based on Item and attach weaponattributes interface.

Only thing is that will be better to review some current classes, weaponattributes, armorattrobutes, and make something like otherattributes to store things that may be used in both classes.
That's about items...

[Jeff]

Originally Posted by Garret

Jeff, reread my post.
We have base Item class as is, it won't be changed at all, if we need to create item with aos properties, we crate it based on Item class with attached interface, we could attach only needed parts.
need resistances? attach Resistance intarface
need weapon attributes? attach weaponattributes interface
need armor attributes? just attach needed.
If we need to create a decoration item, we will create class named "Static" base on Item class, and nothing more.
For containers all will be same as it's now.
Need to create a sword? make class based on Item and attach weaponattributes interface.

Only thing is that will be better to review some current classes, weaponattributes, armorattrobutes, and make something like otherattributes to store things that may be used in both classes.
That's about items...

You can do this outside of the distro, create it, and for every item just inherite from it.

[Apache787]

Great Idea, but like Jeff said, most people would not have a computer that would have the required processing power and hard drive speeds to accomplish the sql stack in a timely manner.

My theory is that freezes happen because 1)The server needs to collect everything that needs to be added to a list at a single point in time and 2)The server dumps the data onto the hard drive(which to decrease the write time, takes priority over any reads)

For #1, the time it takes depends on the Processor and the Ram speed. The average joe normally has a Pentium 4 or equivalent if they have not bought a new computer in the past few years. And for ram, they probably 800 fsb dimms.

For #2, the time depends on the hard drive, SSD drives being the fastest and most expensive, where the average joe has a 7200 rpm drive, and the average clueless joe having a 5400 rpm drive.

[b0b01]

No theory involved, he isn't asking why there are frrezes. The game server just halts untill all data is saved so that the data being saved is not altered during the save. databases aren't logical because it would be too heavy on the servers.

And yes you can speed up saves by better equipment, but that wasn't the point of the threadstarter. Anyway did anyone notice he was banned?

[Jeff]

Originally Posted by Apache787

For #1, the time it takes depends on the Processor and the Ram speed. The average joe normally has a Pentium 4 or equivalent if they have not bought a new computer in the past few years. And for ram, they probably 800 fsb dimms.

this is instantatious since its all stored in memory, so you are wrong.

Originally Posted by Apache787

For #2, the time depends on the hard drive, SSD drives being the fastest and most expensive, where the average joe has a 7200 rpm drive, and the average clueless joe having a 5400 rpm drive.

SSD drives are not the fastest, they infact cannot compare to some nice 15k rpm drives, and those costs less per gb than SSDs. If you were to use SSD drives you are an idiot, waste of money, and seektime is super slow.

[Garret]

Second part of main idea is to recompose current items serealisable values so server could faster save\load it, and to make each item\mobile take less storage.
First part is make saves into SQL database, it's good idea, but need to be written correctly so each save will update fields in current saves instead of writing new save that replacing old one.

[Garret]

The over side of SQL saves.
It need to be tested, we just don't know will it be more productive on medium-huge servers.

Just got other idea that may live, may not =) //to be tested\discussed
only on sql.
When server started, make it load only items and mobiles that are in "visible world",
not load items and mobiles that are in internal map, load them only when they will be requested. that may make server eat less memory, but may take more cpu when player log in. Dunno myself =)

[b0b01]

Well that is obviously the way that databases work, you write and receive data from it when you need it. You should however ask yourself if that is efficient. I mean you will get a lot of drive activity and a lot of cpu power will be required. And currently it works fine with the ram, and ram is cheap.

But feel free to test it

[Garret]

Save system should be stay same as it's now, i mean it will be saving only in selected period of time, so drive activity should stay same, but ram, it will requirem less ram, and less cpu in that case.

[Jeff]

i cant see how justifying a 5-10 seconds save every hour for HOURS upon HOURS of work to make it go away....

[Garret]

Some people coding for own fun =)

[Lord Mashadow]

Then try it and let us know which way is sensible. I think runuo team members could have done it if it was proven to be more efficient than the binary serialization. Which is an also reason for most of us to be present here now. Many of us wouldn't be here today if runuo was too difficult to setup and play.

[baldmenace]

its not so much the cpu that matters as much as memory since most of the indexes are loaded in to memory.
what makes it tough is trying to run both runou and a database server at the same time, since the db requires a fair amount of memory per connection, it adds up quickly. There are a few techniques to alleviate this, one way is to use a connectionless, methode but a little more programming is required to do it, also some things such as oh stored procedures will help with like spawns and such
but it would take a lot of work to make it half and half, but the result could be that world saves would be seemless, and not hault the operation of the game.
The best practice is one machine to run uo server and another that runs the database both machines wouldnt have to be very fast, but the database server would have to have a decent amount of memory. at least a gig minimum.

[Jeff]

Originally Posted by baldmenace

its not so much the cpu that matters as much as memory since most of the indexes are loaded in to memory.
what makes it tough is trying to run both runou and a database server at the same time, since the db requires a fair amount of memory per connection, it adds up quickly. There are a few techniques to alleviate this, one way is to use a connectionless, methode but a little more programming is required to do it, also some things such as oh stored procedures will help with like spawns and such
but it would take a lot of work to make it half and half, but the result could be that world saves would be seemless, and not hault the operation of the game.
The best practice is one machine to run uo server and another that runs the database both machines wouldnt have to be very fast, but the database server would have to have a decent amount of memory. at least a gig minimum.

a gig? maybe 4gb for a standard server 100 player server, the shear amount of overhead from the server may requre a gig....also, do you even understand the concept of stored procedures? You explination on when to use them doesn't sound like you do....

[baldmenace]

not going to get in to an aurgument over this.
ill just prove that it can be done on the req listed

[Acronis]

Sorry to bump an old thread, but just to say that I am currently working on this.

It proved to be easier and make more sense to stick to serialization due to the nature of how items/data is currently managed, unless I wanted a SQL table for every single item type. So I will have the following tables:

items
mobiles
guilds
chat (this may change, it's for knive's chat)
accounts (and other related tables)

Possibly:
spawner related tables
House related tables


Right now I'm doing strictly items to see how it goes.

I have a row that has this structure:

serial
type
data

So the serial, the type (may not be needed but leaving it for now)
and the data is serialized data. I had the intention of packing data so like, a 32bit int is a 4 byte binary string, and so on, but C# strings do not do binary well, so it will actually be delimited values, so like this:

Write(34);
Write("this is a string");
WriteBool(false);

Will translate to:

34;16;this is a string;0;

Not very efficient mind you, currently to save 700k items takes about 20 minutes. (using a remote SQL server in a VM on a not so powerful machine).

Though this is where it gets interesting. If I do a save right after this, because nothing changed, it takes less then a second. I will optimize this further by having another dictionary that stores items that have been changed, removed, or added. The save sequence wont iterate through all the items in the world, but rather, the items in this dictionary.

Items have a flag to set if it has been added, removed, changed, or unchanged. Unchanged items are ignored in a save, removed items are deleted using the DELETE query, changed items are updated using UPDATE query (reserialized completly) and added items are well, INSERTed.

There is one memory issue with this though, currently with the file stream system serialized data is actually buffered to file as it is being serialized, so you never end up with much data being stuck in memory during the save. This is untrue for SQL as a string needs to be built up in order to submit the query. Houses seem to be the biggest cultpit, and pretty much anything else that has a list of items (ex: walls).

To remedy this I will have to redo how houses and spawners are done. Not sure how yet. At this point, I get random "OutOfMemoryExeptions" when I do a full blown world save. I'm not done doing my optmizing though, looks like there may be other issues.

The biggest issue will be the initial transition from file to SQL. The first save will be like half an hour of downtime.

To add to this, I also want to look at the possibility of doing saves in the background. This will be a big project though as I need to make everything thread safe. I tried to do it within a timer just to see what happens, but it locks (removed the code that pauses the netstate) so there is something weird happening. I have a feeling C# threads are not actually real threads, like C++ pthreads. So this could be a challenge. I have not fully looked at background saves yet though.

This is very rough at this point, and I'm not even sure how well it will work as far as loading the world will go. I have a bad feeling the world load may simply be too long, but we'll see. Thats not a HUGE concern as I can always just not have server wars and try to avoid reboots as much as possible. So if it does in fact take half an hour to load, then so be it, but I'm hoping it wont take that long. SELECT is faster then INSERT so I should be safe.

My main worry at this point is memory. My current process seems to be very ram intensive, while saving.

[Mideon]

Interesting, keep us updated, I'd love to hear what you come up with.

[Acronis]

Just an update.

Writing directly to SQL prooves to be slow due to the fact that it has to do a query for each add/delete/update. I decided to buffer the queries to a file, then have an external program take care of querying the DB in the background.

Basically the idea here is get the data saved ASAP, but worry about putting it in the DB later. The program that will input to DB will be written in C++ and take care of all the sanitation, as C++ is much faster. Now the possible downside of this is if this program takes too much resources it may cause lag but I can always add a sleep after each 100 queries, or something. I will be setting up various file locks to ensure both RunUO and this program do not conflict with each other.

With files the saves are taking a matter of seconds. The beauty is any save after the initial data conversion save is under 1 second even if I tile thousands of objects. To keep things simple think I'll still have world saves, but I'll see if I can sneak them in every minute and see if they cause any real impact. Might be able to make it somewhat seamless regardless of how much total data the shard has, since it only will save what changes.

Now another option here is totally forget SQL, and simply restructure the file saves so that it is possible to save what only changes, but only way I can think of is 1 file per serial, which would probably end up being slow anyway. The beauty with SQL is the ease of backing up, and the possibility of interfacing more with the web to some extent. (the data is still serialized, so it may be a strain on the web server to do all that parsing).

but so far think I'm onto something now.

I do not plan to release this code or support it, however I'll be willing to write a tutorial of some sort on how I went about it and I may release the program that buffers to the db.

[Lord_Greywolf]

two other drawbacks i see to this

1st one is if you get a corrupt save some how
now that item has not changed, so it is not saved over
so a day or 2 later you restart the server
the save is read in, but there is only the one main database to read from, and it is corrupt from that one item
and no other saves to revert back to

and unless some one is able to work with the database files, they are wacked

so need to implament some way of being able to bypass ANY error that comes up them
and also make a way to do new full saves when they want (i.e. maybe a daily one automaticaly they can refer back to then

2nd one is how much storage roome would all of these saves take then?
can you do only the last 30 days worth and then drop oldest one to make room for newest one, etc

but if you can get a better save going, that would be great, just pointing out potintial problems i see from what you have described

[Acronis]

Thats the beauty of sql, can do snapshots, replication for redundancy, and just general mysqldump backups. Also if there's something wrong with an item, only that item will be screwed, not all of them. Ex: say I manually go muck up the DB on a few items, when those go to load rather then throw an error and stop it will maybe log as a warning or something, and just deserialize as default values (probably 0s) so you could end up with a weapon with no mods if that item got corrupted, but right now if an item gets corrupted it pretty much halts everything.

Now as for disk space, it does take a bit more because of how I chose to serialize the data but that's not a huge issue as mysql compresses very well so offsite backups will still be rather small. A few 100 megs for a big shard maybe.

I still have tons of error handling and such to impliment though. I'll have to keep trying to break it and think of ways it can self recover. Also have to ensure that if the SQL server goes down the shard can continue to run and buffer to file.

So yeah lot of stuff to consider still.

[Acronis]

Just a general update on this as lot of progress has been done. World load is fully implemented now. Also fixed lot of bugs related to stuff not saving properly.

I can now load the world, login, do a bunch of changes such as make a house, add items, etc, X out of RunUO (simulating a crash or other problem that would cause the app to go down hard) and restart. When I log back in my changes are there. If I do [restart then it guarantees everything saves as it flushes the queue. Now if TONS of items have been changed/added this could take a while. Ex: [global set hue 1 would probably result in a 1-2 hour save with a million items/mobiles. General game play wont take much time to save completely though.

I've also cut down on the file -> sql replication time and brought it down to about 90 minutes on a lower performance test environment. This conversion happens while the shard is fully live and responsive. Once the conversion is complete the load state is then changed to SQL and any further world loads will be done from SQL and the files are never touched again. The beauty with this is I can update my shard from file to SQL without any downtime other then a restart. I'm a 99.9 nazi, I don't like downtime.

I've managed to properly detect a SQL server connection loss and I'm not working on handling it properly. Basically what will happen is queries will just be saved to a delta file and accumulated to that file. When SQL server goes back up it will then start to process that delta file in the order the queries were written to it, and keep writing to that file until the file has been fully processed. This enables the SQL server to go down for whatever reason, and not cause any issues such as lost items. There is still lot of testing to do with this.

There is however a performance issue on lower end machine. As this process does require more resources, lower end machines will really see a hit.

All in all, this is working out great once the issues are fixed. I may consider releasing it to public but not yet sure about that due to all the code changes required and my core is super customized so to make it work on SVN would be hard. I will however write some kind of tutorial on how I went about doing this, and the steps I took. Perhaps someone can even find a way to make it better.


edit: oh and I usually post more detailed updates here.