Redoing the save/load system to use a real database.

Keninishna

Active Member
Sep 12, 2015
37
11
Hi all, I'm learning c# and decided this would be a good project to do it. Right now analyzing the code, ServUO uses a very complex serialization system to save and load the world states.

At first I was thinking of using a object oriented database like db4o:
https://en.wikipedia.org/wiki/Db4o#One-line-of-code_database

Only problem is its no longer maintained but I might try to implement it anyways if its faster. What I'll do instead is write the code to store everything in a SQL database so people can manage their own dbs plus its probably more newb friendly (plus I'm already a sql dba).

I was thinking about something like cassandra because its more scale-able, although I doubt any one shard is going to be that huge. Another thing I want to achieve is no waiting for the world to save so its just continually persistent.

I've written some simple classes to save the world to a database which doesn't seem too hard its just world.items world.mobiles etc... although I haven't compiled it and tested it out yet because I'm noticing everything has a serialize and deserialize class to it. Like every monster, npc, its coded in everywhere and I'm not sure if I can direct the flow of code to avoid it maybe by changing the base serialize class but at that point I might as well rewrite like a large chunk of ServUO. I'm watching Alan's (aka itwouldbewise) videos on youtube and saw him some on twitch and its really helpful but maybe some other devs around here know some things and could give me an idea on how much work would be needed to just wrap in a sql driver and get it saving to that. I figure without the server having to serialize everything it should save a lot faster and if I just want to eventually get a persistent world state I could use the delta ticks and maybe some compression and just load the delta into a memory queue and send it off to the db (it looks like the code already does some of this.) If I ever get this done I'll upload it to git and submit a pull request or fork it or code it to convert the serialized dbs to the sql db. Thanks for any feedback.
 

tass23

Moderator
Aug 28, 2013
1,022
144
42
St. Petersburg, FL
www.uoexpanse.com
Shard Name
The Expanse
Donate
Donate money to this user
Hi all, I'm learning c# and decided this would be a good project to do it. Right now analyzing the code, ServUO uses a very complex serialization system to save and load the world states.

At first I was thinking of using a object oriented database like db4o:
https://en.wikipedia.org/wiki/Db4o#One-line-of-code_database

Only problem is its no longer maintained but I might try to implement it anyways if its faster. What I'll do instead is write the code to store everything in a SQL database so people can manage their own dbs plus its probably more newb friendly (plus I'm already a sql dba).

I was thinking about something like cassandra because its more scale-able, although I doubt any one shard is going to be that huge. Another thing I want to achieve is no waiting for the world to save so its just continually persistent.

I've written some simple classes to save the world to a database which doesn't seem too hard its just world.items world.mobiles etc... although I haven't compiled it and tested it out yet because I'm noticing everything has a serialize and deserialize class to it. Like every monster, npc, its coded in everywhere and I'm not sure if I can direct the flow of code to avoid it maybe by changing the base serialize class but at that point I might as well rewrite like a large chunk of ServUO. I'm watching Alan's (aka itwouldbewise) videos on youtube and saw him some on twitch and its really helpful but maybe some other devs around here know some things and could give me an idea on how much work would be needed to just wrap in a sql driver and get it saving to that. I figure without the server having to serialize everything it should save a lot faster and if I just want to eventually get a persistent world state I could use the delta ticks and maybe some compression and just load the delta into a memory queue and send it off to the db (it looks like the code already does some of this.) If I ever get this done I'll upload it to git and submit a pull request or fork it or code it to convert the serialized dbs to the sql db. Thanks for any feedback.
Couple of things that might help give you some ideas:
Look at MyRunUO folder (if it's still called that in ServUO) in Scripts->Engines. That contains a lot of character export code for publishing various character info to a website. I don't think any documentation still exists (if any ever did) on how to use MyRunUO, but at least the code is there to give you some ideas.

The other thing you should look at is the automated donation store (I'm not sure if it ever got posted here, but this is the RunUO link: http://www.runuo.com/community/threads/runuo-2-0-fully-automated-donation-store-system.98542/).
That package allows for players that donate to receive their items in-game without the need for an Admin to get involved.

Between these two things, you should be well on your way to getting world info saved to a DB. Your first save would likely take quite some time (if you've got an existing playerbase, etc), but after that if the only info that's sent to the DB is what has changed, it wouldn't be too bad.
 
  • Like
Reactions: dmurphy

m309

Admin
ServUO Developer
Mar 3, 2013
1,270
141
41
Great suggestions from @tass23 above. I also seem to remember a partially finished DB saving method being released, I think on RunUO. It may have been part of a full set of server code posted when a shard closed, or maybe as a separate project. In either case, I remember seeing the code at some point if my slowly failing memory still serves me. If anyone can confirm that, or knows where it is, it might be helpful as well.
 

Keninishna

Active Member
Sep 12, 2015
37
11
I found the code in the myrunuo and it looks like its just doing manual queries to a sql db. I believe with LINQ you can model a db where you can just directly store objects in c# which will make it easier than having to type out everything that gets saved into its own sql query. I have a few ideas to implement the system and can probably use the myrunuo code as well. I will have to study LINQ a bit more and see if changes to the db schema can be made easily or if not I will need to implement some workaround. Because later updates of the code that require new save data won't work if not.

I'm thinking worst case scenario just have a column for serialized data and anything extra that people want to add can just throw it into that column and not have to mess with changing the db schema. I know other enterprise applications when they do a major upgrade they just create layers of views in the database that go on top of data from older versions of the app. Right now in servuo the versions are hard coded into case statements when stuff gets deserialized. Anyways if I get something working I'll upload it to git so other people can play around with it.
 

Vorspire

Vita-Nex: Core
Admin
ServUO Developer
Jul 29, 2013
1,136
242
32
United Kingdom
core.vita-nex.com
Shard Name
Ultima Shards: Multiverse
Donate
Donate money to this user
Converting from the flat-file binary system may be as simple as saving things as binary blobs in the database.

Serialization is a fundamental practise of RunUO, not only do the Serialize/Deserialize methods of objects save/load data, they sometimes provide other functionality at run-time like verifying the data.

However you do database saves, you will either have to pass a custom GenericWriter or GenericReader to the Serialize/Deserialize methods; this can work to your advantage.

Instead of passing a FileStream-based BinaryWriter implementation of GenericWriter to Serialize(GenericWriter writer), you may pass a MemoryStream-based BinaryWriter and then use its buffer in a database query.

In theory, you'd only need one table with three columns to store your data, while maintaining compatibility;

'table_entities'
'serial' => INT(11)
'type' => VARCHAR(255)
'data' => BLOB

Where 'serial' is Item.Serial/Mobile.Serial, 'type' is the fully-qualified Type name of the entity and 'data' is the buffer of the custom GenericWriter you pass to Item.Serialize/Mobile.Serialize.

Deserialization would involve reconstructing the 'type' with the 'serial' as a constructor argument, then calling Deserialize with a custom GenericReader that has the 'data' buffered.

Keeping it in sync is the main issue, you won't be able to do the whole 'world saves every N minutes' and have it dump everything all at once, that will destroy your shard with millions of database requests.
This is usually the point at which people give up implementing this feature.

IF I were to write this feature for my own shard, I would leverage the pre-existing Item.DeltaQueue and Mobile.DeltaQueue processing to push updates to the database on a need-only basis. (Entities use their respective DeltaQueues to process critical updates for the object, such as name, hue, map and location changes).

Whenever an entity is queued for a delta update, I would use a MemoryStream-based GenericWriter implementation to call Serialize on the entity at that point, then use the writer's stream as the 'blob' for my database update query.


The reason the server pauses at the time of World Save is because of consistency.
Although the chances are low, it would still be possible for things like item parent tree conflicts.

For example's sake only;

Player puts an item in their pack.
World Save starts.
World Save saves data for the item (saving its' parent as the players' pack)
Player takes item out of their pack and places it on the floor.
World Save saves data for the players' pack.
World Save ends.
Shard reboots.
World Load starts.
World Load puts the item back in the player's pack.
World Load loads data for players' pack, the pack now has two time the weight of the item.


This isn't going to be an easy task, there are a lot of reasons why it hasn't been done mainstream with RunUO;
If I didn't cover them above, completing the feature and finding out yourself surely will :p
The .NET drivers used to communicate with *SQL services are often inefficient.
The ODBC driver for MySQL has some serious issues with performance over time with transactions for example.


Good luck!
 

tass23

Moderator
Aug 28, 2013
1,022
144
42
St. Petersburg, FL
www.uoexpanse.com
Shard Name
The Expanse
Donate
Donate money to this user
OSI does this (saves to a DB). Back in the day though, their saves were only done roughly once a day and that was right before the servers rebooted. There were many times that I lost items because of their once a day saves. I don't know if this has been changed or not, but no matter if you're saving to a DB and/or just doing built-in saves, once a day is probably not good practice when it comes to RunUO/ServUO. I have noticed that the more often you do it causes issues with people trying to play (I think the default was saving every 5 minutes), but the world saves generally were very short (depending on item count, etc). However, if you spaced your saves out (say every 15-30 minutes), the saves might take a tad longer, but offered more time for "unobstructed" play from the world freezing during save time. There are a lot of things that can cut down on save times overall, but the one that most people seem to not like to do is FREEZE their maps when they add custom structures. This alone can really chop your save times dramatically.

I think if someone were to pick this up as a project for release that a DB save would be good, perhaps twice a day (every 12 hours), with world saves every 30-45 minutes, but you wouldn't want a world save and a DB save happening at the same time as that would be a lot of I/Os for most hosted servers (especially if you're using a VPS).
 

Keninishna

Active Member
Sep 12, 2015
37
11
Converting from the flat-file binary system may be as simple as saving things as binary blobs in the database.

Serialization is a fundamental practise of RunUO, not only do the Serialize/Deserialize methods of objects save/load data, they sometimes provide other functionality at run-time like verifying the data.

However you do database saves, you will either have to pass a custom GenericWriter or GenericReader to the Serialize/Deserialize methods; this can work to your advantage.

Instead of passing a FileStream-based BinaryWriter implementation of GenericWriter to Serialize(GenericWriter writer), you may pass a MemoryStream-based BinaryWriter and then use its buffer in a database query.

In theory, you'd only need one table with three columns to store your data, while maintaining compatibility;

'table_entities'
'serial' => INT(11)
'type' => VARCHAR(255)
'data' => BLOB

Where 'serial' is Item.Serial/Mobile.Serial, 'type' is the fully-qualified Type name of the entity and 'data' is the buffer of the custom GenericWriter you pass to Item.Serialize/Mobile.Serialize.

Deserialization would involve reconstructing the 'type' with the 'serial' as a constructor argument, then calling Deserialize with a custom GenericReader that has the 'data' buffered.
Yeah, I was thinking this would be the simplest way of implementing it but I feel like the whole serialization can be bypassed although it might take some restructuring of a lot of code or at least I'll try to get most of it to bypass the serialization and keep part of the data serialized. One thing about having the data in structured tables in sql is because you can optimize it and doing specific queries on the fly would result in greatly reduced ram usage because LINQ will only query items as it runs into it (unless you specify it otherwise) so you could have garbage all over the world but none of it would get loaded into ram until someone ran by it. Also another feature of having the data structured is you can do cool analysis on the world external to the server application. You could have a badass web interface for admins and gms. One part of my vision is to have huge servers with like tons of npcs and such having virtual wars. I think its doable.

Keeping it in sync is the main issue, you won't be able to do the whole 'world saves every N minutes' and have it dump everything all at once, that will destroy your shard with millions of database requests.
This is usually the point at which people give up implementing this feature.

IF I were to write this feature for my own shard, I would leverage the pre-existing Item.DeltaQueue and Mobile.DeltaQueue processing to push updates to the database on a need-only basis. (Entities use their respective DeltaQueues to process critical updates for the object, such as name, hue, map and location changes).

Whenever an entity is queued for a delta update, I would use a MemoryStream-based GenericWriter implementation to call Serialize on the entity at that point, then use the writer's stream as the 'blob' for my database update query.
I agree, saving the whole world every n minutes is just inefficient, and I was thinking about using the delta queue to just stream updates to the database from a memory buffer.

The reason the server pauses at the time of World Save is because of consistency.
Although the chances are low, it would still be possible for things like item parent tree conflicts.

For example's sake only;

Player puts an item in their pack.
World Save starts.
World Save saves data for the item (saving its' parent as the players' pack)
Player takes item out of their pack and places it on the floor.
World Save saves data for the players' pack.
World Save ends.
Shard reboots.
World Load starts.
World Load puts the item back in the player's pack.
World Load loads data for players' pack, the pack now has two time the weight of the item.


This isn't going to be an easy task, there are a lot of reasons why it hasn't been done mainstream with RunUO;
If I didn't cover them above, completing the feature and finding out yourself surely will :p
The .NET drivers used to communicate with *SQL services are often inefficient.
The ODBC driver for MySQL has some serious issues with performance over time with transactions for example.


Good luck!
Yeah, I remember back in the good ole UO days with the server lines and such and I think people used them to dupe items. Consistency and bugs like duping is a huge nono so the world is going to have to have some consistency logic put into it. One thing I was thinking about was if the delta queue buffer stream to the db gets too big just stop buffering until the buffer is flushed and set a checkpoint to send to the db. It would make for a short period of time on the server where nothing is currently being saved so if it crashed while making the checkpoint it would lose some data but with the current save method with the serialization and all its pretty quick or if ram isn't too tight I could just create a separate buffer that waits for the checkpoint to finish. If the server crashes it has a checkpoint to go off of if there is some corruption somewhere since the last checkpoint. I know optimizing this will probably require some painful attention to detail but its the best way to learn.
 
Last edited:

Keninishna

Active Member
Sep 12, 2015
37
11
OSI does this (saves to a DB). Back in the day though, their saves were only done roughly once a day and that was right before the servers rebooted. There were many times that I lost items because of their once a day saves. I don't know if this has been changed or not, but no matter if you're saving to a DB and/or just doing built-in saves, once a day is probably not good practice when it comes to RunUO/ServUO. I have noticed that the more often you do it causes issues with people trying to play (I think the default was saving every 5 minutes), but the world saves generally were very short (depending on item count, etc). However, if you spaced your saves out (say every 15-30 minutes), the saves might take a tad longer, but offered more time for "unobstructed" play from the world freezing during save time. There are a lot of things that can cut down on save times overall, but the one that most people seem to not like to do is FREEZE their maps when they add custom structures. This alone can really chop your save times dramatically.

I think if someone were to pick this up as a project for release that a DB save would be good, perhaps twice a day (every 12 hours), with world saves every 30-45 minutes, but you wouldn't want a world save and a DB save happening at the same time as that would be a lot of I/Os for most hosted servers (especially if you're using a VPS).
Yeah the IOs might be an issue for VPS servers. I would need to have a buffer that keeps track of the deltas and send that over to the db every so often. I'm trying to avoid whole world saves because it seems inefficient to me. But one step at a time.
 

Vorspire

Vita-Nex: Core
Admin
ServUO Developer
Jul 29, 2013
1,136
242
32
United Kingdom
core.vita-nex.com
Shard Name
Ultima Shards: Multiverse
Donate
Donate money to this user
If you wanted to do a web interface using the data, it would be better to serve it as Json from an API in real-time ( http://play.uofreedom.com/shard )

I wouldn't worry about the delta queues being too large for queries;
If you use a range of entries from the queue, you can send batch queries.
I found that with MySQL, you can create a batch of up to 10,000 queries in a single transaction, which will typically complete in under 1 second to a localhost MySQL server.

As for not loading trash in to the world until people run past it - think about the process that would involve;
Whenever a character moves, you'll need to know what to load, so you have to store some data somewhere and check it - if you don't store that data in memory, then you have to query the database. This doesn't sound near as optimal as just having the object sitting in the world doing nothing. The other caveat to lazy-loading objects would be that other systems that rely on those objects would break down.
 

Keninishna

Active Member
Sep 12, 2015
37
11
Yeah, that makes sense. 10k queries or 10k rows? I guess its all relative as to whats being done on the db. As far as the lazy loading goes its built into the underlying LINQ framework. What I would do is make an abstract item class that has some basic information about items such as their location, position, type. That way the world can load the items at start but if something comes up that requires more information like armor stats or something then it dynamically queries the db when the inherited armor class gets executed for a related armor table. There are upsides and downsides to lazy loading of course I'll have to implement it and do some testing but so far I'm still learning the structure of servuo and c#.

I've gone down the rabbit hole and started digging from world.load() and world.save() and I started going through the serialize code and pulling all the variables from that and started designing the db tables. Not sure if clothing should have its own table because it seems identical to armor except for some clothing attribute. Its a lot of work but I'm bored in between jobs right now so it keeps my mind sharp lol.
 

Attachments

  • Like
Reactions: Greed

Vorspire

Vita-Nex: Core
Admin
ServUO Developer
Jul 29, 2013
1,136
242
32
United Kingdom
core.vita-nex.com
Shard Name
Ultima Shards: Multiverse
Donate
Donate money to this user
You could automatically generate your tables by using reflection to take a snap-shot of all the readable/writeable properties and then deferring their database storage type to determine the columns that are needed.

You will likely need a table for each type of mobile and item that exists, objects that inherit BaseClothing will sometimes have additional serialization requirements.

I'm not sure how you will get around things like special code being executed in a Deserialize method;
As I mentioned, the method isn't just used to load the data state, it's use for all sorts of things like starting timers, de-fragmenting lists, etc.

I'm really interested to see what you come up with :D
 

dmurphy

Admin
ServUO Developer
Feb 10, 2014
1,017
233
30
You could automatically generate your tables by using reflection to take a snap-shot of all the readable/writeable properties and then deferring their database storage type to determine the columns that are needed.

You will likely need a table for each type of mobile and item that exists, objects that inherit BaseClothing will sometimes have additional serialization requirements.

I'm not sure how you will get around things like special code being executed in a Deserialize method;
As I mentioned, the method isn't just used to load the data state, it's use for all sorts of things like starting timers, de-fragmenting lists, etc.

I'm really interested to see what you come up with :D
Yep, I use Deserialize to remove errant properties on load for the Humility Hunt for the Humility Virtue, though it could be done elsewhere too.
 

Keninishna

Active Member
Sep 12, 2015
37
11
Well after much debugging and hitting my head and cursing microsoft I managed to get the world to write the mobiles to the db. Well most properties of the mobiles there are some things I need to code in for certain things because it doesn't like integers being null. Its really slow saving though but I can probably do some more optimizing to it and change the sql behind the linq insert and update table code. I basically had to write my own version of the serialize method. I did end up serializing things like item lists. Next step after getting all the mobile data saved is getting items and guilds and custom data to save then finally to loading everything back in lol. One step at a time I guess.
 

Attachments

Norman Lancaster

Well-Known Member
Dec 22, 2015
1,046
104
35
I'm glad you are making progress on this! Have you given any thought to retaining backward-compatibility to be able to load world save data for existing saves? That will be crucial for existing shards.
 

Keninishna

Active Member
Sep 12, 2015
37
11
I'm glad you are making progress on this! Have you given any thought to retaining backward-compatibility to be able to load world save data for existing saves? That will be crucial for existing shards.
Yes, I am leaving all the existing code in for the most part just adding in some if statements to switch to my code if its turned on. I'll be sure to add some command like SaveToSQL while using any save/load method. That would convert the current world to the SQL db then you could load from the SQL db after that.
 

Keninishna

Active Member
Sep 12, 2015
37
11
Just wanted to post a quick update on my progress. It's been slow but I'm slowly learning c# as I go along. So far I've managed to get the mobiles saved in a bulk insert which is much faster than what linq was doing. Used to take 6 seconds to save a few thousand mobiles now it takes 0.4 seconds. Not as fast as I want but that leads me to my next task and that is saving to a buffer in memory first then streaming the data over to the dB. Should lead to very fast world saves. I'm also thinking of never overwriting saves lol that way we can do some detailed stats. If it takes up too much space I can set a job to delete the oldest saves based on a size or time limit.
 

Vorspire

Vita-Nex: Core
Admin
ServUO Developer
Jul 29, 2013
1,136
242
32
United Kingdom
core.vita-nex.com
Shard Name
Ultima Shards: Multiverse
Donate
Donate money to this user
The way saves currently work is by writing all the data to a memory buffer, then dumping it all to a file in one operation.
The bottle-neck comes from the CPU when it comes to world saves, actual serialization of all the items and mobiles takes milliseconds, but dumping that memory to file is the expensive part.
With a database, it's technically no different, the database software/controller still has to take all that data and write it to disc. Database software is, for all intents and purposes, an API that manages a data structure that is stored on disc. With this definition in mind, it's not a stretch to view RunUO as being it's own database software.
The benefit to using an outside database is that you can dump all of your data to the database API and have that API worry about buffering and writing, while your application continues uninterrupted, which I believe is what you're aiming for. I'm not sure if this approach is much different to having world saves run in a background thread, but that's here nor there at this point.

So far, I'm impressed and pretty interested to see how it goes :D
 

Keninishna

Active Member
Sep 12, 2015
37
11
The way saves currently work is by writing all the data to a memory buffer, then dumping it all to a file in one operation.
The bottle-neck comes from the CPU when it comes to world saves, actual serialization of all the items and mobiles takes milliseconds, but dumping that memory to file is the expensive part.
With a database, it's technically no different, the database software/controller still has to take all that data and write it to disc. Database software is, for all intents and purposes, an API that manages a data structure that is stored on disc. With this definition in mind, it's not a stretch to view RunUO as being it's own database software.
The benefit to using an outside database is that you can dump all of your data to the database API and have that API worry about buffering and writing, while your application continues uninterrupted, which I believe is what you're aiming for. I'm not sure if this approach is much different to having world saves run in a background thread, but that's here nor there at this point.

So far, I'm impressed and pretty interested to see how it goes :D

Well let me explain what I mean or am trying to do with the buffer decreasing world save times. From what I understand the reason the world pauses during saves is to prevent inconsistencies with multiple threads and a big ever changing world. So the world pauses and saves and lets everyone go on their marry way. Really though we don't need everyone to wait for the world to flush to disk before resuming. We can pause and have everything copied (which it does anyways now) then resume the world and then deal with the buffered copy as we send it to disk. It should make for pretty instant world saves. Fast enough to not even drop any packets.

Although I have some ideas for other tweaks when sending everything to do the db I can load up 3 threads and connections to the db for mobiles, items and custom data. This should take advantage of multi core systems better and since the data is independent of each other no worries about locks. Yes runuo is a database system in itself, the whole serialization and persistence code is a pretty large chunk of the overall codebase. Moving it over to sql will just make it that much easier for shard owners manage their server and backups etc....
 
  • Like
Reactions: Greed