Better storage engine

[Adaptatron5000]

Better storage engine

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]

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]

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]

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.

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]

comment on cpu power

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]

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]

cpu

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.