Better storage engine

[kmekc]

The write/load of a save to a sql database is not very efficient.

The actual serialize/deserialize methods are much faster than a mysql server. Also much more reliable, cause if you first save to file and later to database you will have the two points of failure instead of one when only saving to file. And it is pointless to say you will not corrupt the whole save with sql because you should never trust a corrupted save. Just think about referential integrity.

Another point is that if storing whole item information into one data row. You will violate first normal form in database normalization. So you will have to ask yourself why to use a database if you violate some of the basic rules.

To store the world in an database only makes sense if you design the whole object model to it. Look at the commercial mmorpgs. They have server lines and big database clusters. And everything is designed to use the databases cause without them, they could not handle the server lines and transfer data from one shard to another.

But RunUO is designed to handle one server and not a cluster. So if you want another storage engine, you have to take another approach in design.

[Acronis]

The main point of this is mostly to make it so there are no world saves and for better redundancy (can use SQL replication). The actual process does take a bit longer especially if the SQL server is remote, but it is not seen by the users.

Also I can take a database snapshot at any given time to do real time backups. If I want I can pretty much back up hourly, or even every half hour. Later on I can seperate data from processing. So SQL would be it's own server and RunUO would be another server.

Now you're right that it's best to design FOR that, but it would be easier to make an emulator from scratch at that point (thought of it, but I have no clue how to do the gzip packets) So I just kept the serialization method. There is still an advantage to that though and it's that each object is an individual entity. if something does corrupt for whatever reason, it won't involve the entire DB, just a small part of it.

Also the file I mentioned is only if the server goes down. In normal circumstances it will never write to file.

[arul]

Originally Posted by Acronis

The main point of this is mostly to make it so there are no world saves and for better redundancy (can use SQL replication). The actual process does take a bit longer especially if the SQL server is remote, but it is not seen by the users.

Also I can take a database snapshot at any given time to do real time backups. If I want I can pretty much back up hourly, or even every half hour. Later on I can seperate data from processing. So SQL would be it's own server and RunUO would be another server.

Now you're right that it's best to design FOR that, but it would be easier to make an emulator from scratch at that point (thought of it, but I have no clue how to do the gzip packets) So I just kept the serialization method. There is still an advantage to that though and it's that each object is an individual entity. if something does corrupt for whatever reason, it won't involve the entire DB, just a small part of it.

Also the file I mentioned is only if the server goes down. In normal circumstances it will never write to file.

This is a nonsense. If you save something that shouldn't have been saved, or don't save something that should have been saved for that matter, the problem is in your code and should be fixed therein, not just 'dropping a row and all is cool' ...

The implied incosistency of your system introduces a lot of security holes, with a disasterous consequence.

[Acronis]

Originally Posted by arul

This is a nonsense. If you save something that shouldn't have been saved, or don't save something that should have been saved for that matter, the problem is in your code and should be fixed therein, not just 'dropping a row and all is cool' ...

The implied incosistency of your system introduces a lot of security holes, with a disasterous consequence.

And this is why there needs to be TONS of testing on this to ensure 100% data integrity. But look at the serialize system, it only takes one badly written deserialize method to cause the server to fail to load. now THAT is bad. In fact, to make matters worse, if a backup job occures durring a save it can actually corrupt the ENTIRE database AND crash the shard rendering it down until an administrator manually restores the latest backup. This will not be an issue with SQL.

If something DOES go wrong, it won't affect the entire shard.

There is still tons of testing left to do before this is considered production. Every possibility for stuff like SQL injection needs to be considered, and the proper order of queries is a must.

There is also an issue where duplicate rows try to be entered and I think this is a result of a serial number being "freed" before the item using up that serial is removed from the DB, so I will need to fix that as well as it could cause issues. There's still stuff like this to fix, but once it'is 100% this system will be very solid and easier to manage compared to the old system. Players will also enjoy being able to play knowing their progress is saved in real time, and never have to wait after another save again. Save times are linear to the number of objects, so big shards get really big saves. Being in the middle of a dual and having to wait 10 seconds for a save is not cool.

[arul]

Originally Posted by Acronis

And this is why there needs to be TONS of testing on this to ensure 100% data integrity. But look at the serialize system, it only takes one badly written deserialize method to cause the server to fail to load. now THAT is bad. In fact, to make matters worse, if a backup job occures durring a save it can actually corrupt the ENTIRE database AND crash the shard rendering it down until an administrator manually restores the latest backup. This will not be an issue with SQL.

If something DOES go wrong, it won't affect the entire shard.

There is still tons of testing left to do before this is considered production. Every possibility for stuff like SQL injection needs to be considered, and the proper order of queries is a must.

There is also an issue where duplicate rows try to be entered and I think this is a result of a serial number being "freed" before the item using up that serial is removed from the DB, so I will need to fix that as well as it could cause issues. There's still stuff like this to fix, but once it'is 100% this system will be very solid and easier to manage compared to the old system. Players will also enjoy being able to play knowing their progress is saved in real time, and never have to wait after another save again. Save times are linear to the number of objects, so big shards get really big saves. Being in the middle of a dual and having to wait 10 seconds for a save is not cool.

Makes me wonder how many brain cells it takes to realize that if you break only single part of the chain, that the lion that was shackled to it will kill you. Worse yet, you're breaking encapsulation here, the persistance layer shouldn't be aware of the implementation details of the overlying layer.

You really don't need TONS of testing here, you write a byte - you read a byte, simple as that. Only people who are not aware of as to how the serialization system is working make mistakes here. With SQL it would be even worse:

1) SQL injection vulnerability
2) Reference incosistency
3) Overall throughput

To use your words: If something DOES go wrong, then it IS YOUR FAULT (or whoever's who wrote the code), and the commons sense suggests that you SHOULD CRASH, time and again until the problem is fixed. It's like swallowing all exceptions and ignoring them, they have been thrown for a reason.

Because of lack of a context, you can't have a global fault handler to save your ass, you'd need a fault handler for each type being saved/load, which is a lot of additional overhead.

[Acronis]

Originally Posted by arul

Makes me wonder how many brain cells it takes to realize that if you break only single part of the chain, that the lion that was shackled to it will kill you. Worse yet, you're breaking encapsulation here, the persistance layer shouldn't be aware of the implementation details of the overlying layer.

You really don't need TONS of testing here, you write a byte - you read a byte, simple as that. Only people who are not aware of as to how the serialization system is working make mistakes here. With SQL it would be even worse:

1) SQL injection vulnerability
2) Reference incosistency
3) Overall throughput

To use your words: If something DOES go wrong, then it IS YOUR FAULT (or whoever's who wrote the code), and the commons sense suggests that you SHOULD CRASH, time and again until the problem is fixed. It's like swallowing all exceptions and ignoring them, they have been thrown for a reason.

Because of lack of a context, you can't have a global fault handler to save your ass, you'd need a fault handler for each type being saved/load, which is a lot of additional overhead.

Obviously if there is an error it will be my fault as I am the only dev, but it's also up to me to fix it. This goes with ANY programming. There will always be a degree of human error somewhere. This is why even big programs like Windows crash. This is also why there is tons of testing to ensure that data integrity remains under any situation.

Though having a whole system crash over an error that may not be fatal is not really a good approach. Why produce unneeded downtime? You don't see a race car driver quickly stop the car and quit if a tire is in dire need of change. He'll try to make it to the pit stop to have it fixed and continue the race.

[Lord_Greywolf]

also from what i am reading here, i can see where this will bog down the more and more players, critters, etc that are on

each time something takes a step, it has to be saved, because of new location
so a champ spawn with 200 critters spawned will be constantly updating those 200 critters to the database
even if it is just x & y and maybe z -also direction too

and that is just 1 champ spawn
have 10 of those going
plus the people at them
so each hp change for all critters and players there, plus dura on equipment
spells, timers, etc also all changing

then you got all fo the other mobs walking around the world, vendors, etc
each and every change of direction, step, etc all has to be saved on the fly

it all sounds great, but i can see where you will be bogging yourself down constantly with all these things being saved continiously

[Acronis]

Originally Posted by Lord_Greywolf

also from what i am reading here, i can see where this will bog down the more and more players, critters, etc that are on

each time something takes a step, it has to be saved, because of new location
so a champ spawn with 200 critters spawned will be constantly updating those 200 critters to the database
even if it is just x & y and maybe z -also direction too

and that is just 1 champ spawn
have 10 of those going
plus the people at them
so each hp change for all critters and players there, plus dura on equipment
spells, timers, etc also all changing

then you got all fo the other mobs walking around the world, vendors, etc
each and every change of direction, step, etc all has to be saved on the fly

it all sounds great, but i can see where you will be bogging yourself down constantly with all these things being saved continiously

That's actually part of the optimizations being done. When an item changes a flag is set on the object, then when the save loop gets to that item, it will produce the query, cache it for now, and flip the flag back, then another thread will actually submit the query and take care of the work.

I've been playing with different intervals to find out what is best. Right now only saves done within 5 seconds are resaved. The system can take care of about 9000 item changes/deletes/adds per second so far. Still need to optimize it a lot though.

[arul]

Originally Posted by Acronis

Obviously if there is an error it will be my fault as I am the only dev, but it's also up to me to fix it. This goes with ANY programming. There will always be a degree of human error somewhere. This is why even big programs like Windows crash. This is also why there is tons of testing to ensure that data integrity remains under any situation.

Though having a whole system crash over an error that may not be fatal is not really a good approach. Why produce unneeded downtime? You don't see a race car driver quickly stop the car and quit if a tire is in dire need of change. He'll try to make it to the pit stop to have it fixed and continue the race.

Don't get me wrong, I'm totally pro an alternative storage engine for RunUO, you just want to use it for a completely wrong reason.

Your system tries to take care of the effect, completely ignoring the cuase.

Your comparison to Windows is another nonsense, unlike your thesis, the crucial part of windows - the kernel, works deterministically at all situations. If you somehow magically happened to delete a half of the windows registry the system wouldn't try to "accomodate", it'd simply crash.

EDIT: Your comparison to a car driver is also wrong, the driver has no clue about the state of the tire, because the car already "fixed" it and only turned on the "Something bad happened" flash.
If the error weren't fatal, then why it would have had crashed in the first place?

The system entered an incosistent state, that which cannot be resolved without a human assistance, crashing is adequate.

[Lichtblitz]

Originally Posted by Acronis

I've been playing with different intervals to find out what is best. Right now only saves done within 5 seconds are resaved. The system can take care of about 9000 item changes/deletes/adds per second so far. Still need to optimize it a lot though.

If I get it right - you're saving all differences within a certain time period, right? In this case all differences that have been made within 5 seconds, correct? How do you take care of atomic transactions?

For example:

• Your scanning thread starts at serial 0x1.
• Your scanning thread reaches serial 0x100.
• Ingame a mobile is spawned which is sitting on a horse. The horse's serial will be assigned to 0x50 and the rider will be 0x200.
• The scanning thread reaches 0x200, saving the rider.
• The server crashes. The rider is saved but not the mount. The save has been corrupted.

Things like this has always been a major issue for background saves ever since Sphere server because the server application has never been designed for background saves. How do you circumvent that problem?

[arul]

The key thing is that the shard is running, isn't it?! </sarcasm>

[Lichtblitz]

Originally Posted by arul

The key thing is that the shard is running, isn't it?! </sarcasm>

I guess we got your point and the point of all the others who criticized this way of storing. I too think that this is not a very practical approach but all the arguments have been presented and it should not be the purpose of this forum to evangelize people to one's own belief. I'm trying to learn something here that could be used for partial saves with the current save system. For example partial background saves every few minutes and a full foreground save every day or so to minimize a possible data loss and world save times. The problem is that I haven't figured out a clever way to avoid world save corruption when the world save takes place in the middle of an atomic transaction. The best solution would be to implement real atomic transactions but that would mean to completely rewrite large portions of the core.

[Acronis]

Originally Posted by Lichtblitz

If I get it right - you're saving all differences within a certain time period, right? In this case all differences that have been made within 5 seconds, correct? How do you take care of atomic transactions?

For example:

• Your scanning thread starts at serial 0x1.
• Your scanning thread reaches serial 0x100.
• Ingame a mobile is spawned which is sitting on a horse. The horse's serial will be assigned to 0x50 and the rider will be 0x200.
• The scanning thread reaches 0x200, saving the rider.
• The server crashes. The rider is saved but not the mount. The save has been corrupted.

Things like this has always been a major issue for background saves ever since Sphere server because the server application has never been designed for background saves. How do you circumvent that problem?

This is something that can become an issue in the event of a crash or Xing out of runuo if the queue grows too large, but so far I have not seen it happen. Though a normal reboot ensures that everything is saved before rebooting and that the buffer writer is caught up with the world ch anges. The reboot save is the only one that actually "freezes" the world for about 1 second as it ensures the queue is flushed and that no new objects are added to it. Really if I wanted to I could still have world saves but just make them save what changes, that's another option. This way the saves would only be 1-2 seconds, regardless of the items in the world, so it would still be better then the file db.

[Lord_Greywolf]

how many items/mobiles are you doing your testing with?

because on a nearly empty shard - only a few 1000 things/critters, i have saves of .2 to .3 seconds

so if your buffer is taking 1 seconds for a final flush, which is not even a complete hard save, it is either slower or ytou have to be having 10's of 1000' of thing in there

so what i am saying, how many 100's of thousands of items/critters do you have in there?
because if not that big of an amount to be testing with, then your saving times are a little big

[Acronis]

The testing is being done with about 900k objects. To do a full save with the regular system it takes about 10 seconds of interruptd game play time. The SQL system never interrupts game play as it's done in another thread. Only the shard shut down takes about 1 sec as it stops all writes/activity and waits for buffer to catch up + waits for it to be written to DB (which normally happens in background).

I want to test with like 10 million objects later on. The speed is highly determined by the SQL server itself though. If I put on like raid 10 it would be super fast.

[Jeff]

Originally Posted by Acronis

The testing is being done with about 900k objects. To do a full save with the regular system it takes about 10 seconds of interruptd game play time. The SQL system never interrupts game play as it's done in another thread. Only the shard shut down takes about 1 sec as it stops all writes/activity and waits for buffer to catch up + waits for it to be written to DB (which normally happens in background).

I want to test with like 10 million objects later on. The speed is highly determined by the SQL server itself though. If I put on like raid 10 it would be super fast.

RAID has nothing to do with the SQL server speed when it is loaded into memory. Unless you have a dumb setup where the amount of memory is lower than the size of the database itself... which is pointless.

[Acronis]

Originally Posted by Jeff

RAID has nothing to do with the SQL server speed when it is loaded into memory. Unless you have a dumb setup where the amount of memory is lower than the size of the database itself... which is pointless.

MySQL uses some level of caching but I doubt it will cache the ENTIRE db into memory. A DB can grow to be many TB big (not a RunUO DB but maybe other applications). You're not going to have TB's of ram on a server. RAID 10 is usually the recommended configuration for DB servers. Raid 0 would be even faster but if a drive fails you're screwed. This is why it's unrealistic to depend on the DB being stored in ram. It gets written to disk often otherwise you'd end up with corrupted DBs when a server is shut down badly. (ex: pull the plug or power outage and no UPS). So you still need a very fast NVRAM for a SQL server.

If I had the money I'd go with 15krpm SAS drives, but I will stick with sata.

[Jeff]

Originally Posted by Acronis

MySQL uses some level of caching but I doubt it will cache the ENTIRE db into memory. A DB can grow to be many TB big (not a RunUO DB but maybe other applications). You're not going to have TB's of ram on a server. RAID 10 is usually the recommended configuration for DB servers. Raid 0 would be even faster but if a drive fails you're screwed. This is why it's unrealistic to depend on the DB being stored in ram. It gets written to disk often otherwise you'd end up with corrupted DBs when a server is shut down badly. (ex: pull the plug or power outage and no UPS). So you still need a very fast NVRAM for a SQL server.

If I had the money I'd go with 15krpm SAS drives, but I will stick with sata.

However, we were talking about RunUO, so your point is invalid...

[Vorspire]

Lighten up Jeff, his information is more useful than your off-topic reply

[Acronis]

Been progressing well. Some bugs here and there to fix, but otherwise working out well. There's a video here demonstrating the system in action.

The tricky part now is making sure that every item gets triggered to save when it's changed. In most cases I can call up the trigger in the get/set function (the set portion).


For those who decide to try this, let me tell you, it's NOT easy! I fell totally behind with ML doing this but it will be worth it in the end. Then I have to code SA...lol

[osd_daedalus]

Originally Posted by Acronis

For those who decide to try this, let me tell you, it's NOT easy! I fell totally behind with ML doing this but it will be worth it in the end. Then I have to code SA...lol

you post, we test

[Ryan]

Originally Posted by Acronis

Been progressing well. Some bugs here and there to fix, but otherwise working out well. There's a video here demonstrating the system in action.

The tricky part now is making sure that every item gets triggered to save when it's changed. In most cases I can call up the trigger in the get/set function (the set portion).


For those who decide to try this, let me tell you, it's NOT easy! I fell totally behind with ML doing this but it will be worth it in the end. Then I have to code SA...lol

WOW - I was curious to look at this because we actually tinkered with this for a while to see how feasible it was to store the entire world into a database - there were so many advantages we thought of but in the end there just wasn't enough reasons to do it...

If you figure the amount of time you've spent putting this into MySQL and equate that to money you could buy some seriously good hardware and use that instead..

Another thing - Your information on the video:

Part of the revamp is to convert the obsolete, unscalable file storage system to mysql storage system. This has been a large and complex task due to the nature of how the data is stored and the structure not being designed for a DBMS. Despite the challenge, it has been achieved, and it is nearly complete! This video is a quick demonstration of the save process.

...is just a bunch of grandstanding bullshit.

When you're done with this - please by all means release it so that I can take a world like UOGamers Hybrid and show you just how much this would get crushed by 1,000+ players online, dynamic event systems, almost 18 million items, almost 1 million mobiles and 250,000 accounts with anywhere from 20 - 30% of them being active daily.

[Acronis]

Originally Posted by Ryan

WOW - I was curious to look at this because we actually tinkered with this for a while to see how feasible it was to store the entire world into a database - there were so many advantages we thought of but in the end there just wasn't enough reasons to do it...

If you figure the amount of time you've spent putting this into MySQL and equate that to money you could buy some seriously good hardware and use that instead..

Another thing - Your information on the video:



...is just a bunch of grandstanding bullshit.

When you're done with this - please by all means release it so that I can take a world like UOGamers Hybrid and show you just how much this would get crushed by 1,000+ players online, dynamic event systems, almost 18 million items, almost 1 million mobiles and 250,000 accounts with anywhere from 20 - 30% of them being active daily.

Remember, it only saves what changes, so it does not matter how many items or mobiles there are in total. What may slow down my system is something like "[global set hue 1" then every single item/mobile is flagged for change so it will have a backlog but it will eventually catch up. When I load off a file DB it takes about 10 minutes for the buffer to catch up, but meanwhile the world is running fine for the players. there's just a risk of data loss if the shard is to be badly shut down or something. While SQL has a bit more overhead it has the advantage of only saving what changes, not everything. The goal is not to have zero revert in the case of a crash, but to have very minimal revert. I've actually been thinking of setting save intervals to something like every 30 seconds. It's currently at every 5 seconds. They would speed up dynamically if there's a backlog.

As my system uses more ram to buffer the SQL statements, my biggest challenge is overcoming the .NET memory limitation. A .NET app will crash with OutOfMemoryExeption when it starts to use too much ram regardless of what is left on the system. Normally anything past 2GB and it crashes but it's hit and miss. I need to test this on a 64-bit platform as I'm hoping this limitation does not exist there.

[Vorspire]

public override void InvalidateProperties()
{
    _SaveRequired = true;

    base.InvalidateProperties();
}

Storing data in MySQL for local purposes is a bit silly.
It would be much faster to create your own storage system, or use the existing system with more care :/

Do you plan on using multi-threading with this system?

[Acronis]

Originally Posted by Vorspire

public override void InvalidateProperties()
{
    _SaveRequired = true;

    base.InvalidateProperties();
}

Storing data in MySQL for local purposes is a bit silly.
It would be much faster to create your own storage system, or use the existing system with more care :/

Do you plan on using multi-threading with this system?

Actually I had originally thought of using invalidateproperties to trigger a save, but it is undesirable in some situations, so ended up making a Modify() function.

And yeah it is multithreaded. The way it works is every 10 secs or so all flagged items are "saved" in a memory buffer. this is where the SQL statements are made and put in a string. The items are then unflagged. This happens in a fraction of a second as iterating through a dictionary containing only changed items and creating the strings is rather fast. This then tells the SQL thread that it can now proceed to save to SQL. At this point the world continues to function and the SQL save thread now reads through the buffer, and writes to the DB. During this time no buffer save can happen so modified items just accumulate as they change. When an item is flagged it's also put into a separate dictionary.

If I wanted to I could have a separate thread for Item, Mobile, Guild and Account but I chose not to as I don't think it's needed.

Still some tweaking to be done though. If I tile 500 DFs then tile another 500 DFs with a different team, they all fight each other and it really lags up. I need to find a code profiler for C# that's free so I can find out what is the biggest slow down in my system.