14 Jul, 2011, plamzi wrote in the 1st comment:
Votes: 0
About a week ago, I completed a long project to hook a customized 3.0 CircleMUD codebase into a MySQL server for all its file storing needs. I wanted to share some benefits I have found along the way in case others are contemplating the same and wondering if it's worth the high level of effort to revamp an existing codebase.

Note that word goes here of a standalone relational database server able to be queried and updated independently, not a flat-file or in other ways 'lite' database which is in many cases closed to ouside access.

In short, it's worth it.

What follows is the long story for those who may be interested.

Investment: c. 400 hours. Payoff:

* We can keep track of and modify all items, including items in player save 'files'.

* We can balance mobiles and objects in the entire world with a few simple queries. If we don't want to auto-balance certain areas, we can check if any items/mobs are too under/over-powered.

* We can import hundreds of areas from different Diku derivatives, including customized Merc, remapping item and mobile properties easily to fit our own customizations.

* We can offer web-based enhancements such as a real-time point-of-view map, a dynamically updated leaderboard, a quest generation form. With the exception of the real-time map, those features don't place any strain on the game server itself. Even the real-time map places manageable strain because of a push/pull mechanism made easy by the external database.

* We can design new player, mobile, object properties easily and without disrupting the production server.

* We can clone off tables to a developer server schema, work on them, then merge them back into production. I can grant the QM access to the quest tables, and he can do his thing independently, without code access.

* We can edit help files easily in a web browser and the changes go live instantly for our web-based, mobile, and telnet players alike.

* We can implement and balance quickly features like 'individual character relationships' which would be extremely taxing to do in any other way (it involves storing and updating a record for each link of each char to each other char they meet).

* We can change offline and then publish many (eventually it will be all) data tables without requiring a server reboot. These include: the quest system, the help file system, the character relationship tables.

* We can delegate many maintenance tasks (e. g. backups, character expiration) and also automate many tasks (e. g. eq restoration) via MySQL stored procedures.

* For various ad-hoc needs, there is often no need to create special in-game admin commands because direct querying of the database does the job.

* As a small side benefit, the game is now 1/3 the original size, and highly portable.

I'm sure many more good things are to follow–these are just the ones we've already experienced.
14 Jul, 2011, Rarva.Riendf wrote in the 2nd comment:
Votes: 0
Quote
As a small side benefit, the game is now 1/3 the original size, and highly portable.

Memory wise ? I will nto count the DB anyway, since you probably has a forum that use one so it is installed/launched anyway.
14 Jul, 2011, Kline wrote in the 3rd comment:
Votes: 0
plamzi said:
* We can edit help files easily in a web browser and the changes go live instantly for our web-based, mobile, and telnet players alike.


I've done similar for helps and socials by just moving them to separate files and reading from disk as needed. I implemented a caching mechanism on top of it so if players are hammering 'help fireball' every minute then it won't have any additional disk read past the first one. Files are updated real-time if they are not in the cache, or in short order if they are. It was a fairly simple change for both the helps and socials that has drastically cut down on memory usage.

Good to see your run with MySQL is going so well. I'm always looking for different ways of doing things on Dikurivitives, but attempting to not tie myself down to any external systems such as MySQL in the process.
14 Jul, 2011, plamzi wrote in the 4th comment:
Votes: 0
Rarva.Riendf said:
Quote
As a small side benefit, the game is now 1/3 the original size, and highly portable.

Memory wise ? I will nto count the DB anyway, since you probably has a forum that use one so it is installed/launched anyway.


I meant hard disk footprint. The memory footprint would probably be unchanged for the game regardless of where it reads its stuff from. And of course the MySQL db can take up as much RAM as you've got, so a good implementation pretty much requires you to have a VM or a third-party provider. I was actually surprised at the sheer amount of stuff a MUD needs to run. Compared to the db demands of an average dynamic website, it's humongous.

Kline said:
plamzi said:
* We can edit help files easily in a web browser and the changes go live instantly for our web-based, mobile, and telnet players alike.


I've done similar for helps and socials by just moving them to separate files and reading from disk as needed. I implemented a caching mechanism on top of it so if players are hammering 'help fireball' every minute then it won't have any additional disk read past the first one. Files are updated real-time if they are not in the cache, or in short order if they are. It was a fairly simple change for both the helps and socials that has drastically cut down on memory usage.

Good to see your run with MySQL is going so well. I'm always looking for different ways of doing things on Dikurivitives, but attempting to not tie myself down to any external systems such as MySQL in the process.


Caching of requests is something MySQL handles natively for all queries that read info. So I got that for free without having to do any work, and it's going to apply to anything being read from the db going forward, not just help files. You'd have to implement a host of other things, too, to match the speed of a MySQL database. Not saying that any MUD needs one, but if you're serving game information to a website, e. g., then you have concurrency and you're entering the realm where a database can and will shine.

I'd be curious to know what you perceive you gain from "not tying yourself down to any external systems." Is your project a codebase?
14 Jul, 2011, Runter wrote in the 5th comment:
Votes: 0
re memory usage

A simple optimization that's easy with a database is lazy loading non-essential fields from the database. For example, loading descriptions only the first time they're accessed. A vast majority of descriptions (especially item and npc) would never be loaded at all. And for some muds, a lot of room descriptions. There's other fields that benefit from this as well. And it's not really slow.

Not that I think the disk or memory usage is really an issue either way.
14 Jul, 2011, Rarva.Riendf wrote in the 6th comment:
Votes: 0
Runter said:
re memory usage
Not that I think the disk or memory usage is really an issue either way.

As a matter of fact if disk usage is really not an issue for muds, memory footprint still is as if you want to keep the cost very low, and still have a webpage and a forum running as well you can have as low as 256meg available total
My mud is around 30meg of ram, have a build port and that is already 60, if you ever think of a code port (I dont see any reason to have one nowadays, you can code on your own computer with better IDE) that is 90. Add to that a forum (basic phbb) and you then dont have that much left.
14 Jul, 2011, Kline wrote in the 7th comment:
Votes: 0
plamzi said:
Caching of requests is something MySQL handles natively for all queries that read info. So I got that for free without having to do any work, and it's going to apply to anything being read from the db going forward, not just help files. You'd have to implement a host of other things, too, to match the speed of a MySQL database. Not saying that any MUD needs one, but if you're serving game information to a website, e. g., then you have concurrency and you're entering the realm where a database can and will shine.

I'd be curious to know what you perceive you gain from "not tying yourself down to any external systems." Is your project a codebase?


Simplicity. Untar, make, play. It is a codebase, yes, see my sig. I'd like it to be as newbie (never touched *nix before) friendly as I can, so I've kept away from things I know are very nice to have like Boost and MySQL.

I do use MySQL for some of the storage on one of my older personal games. It started as a learning exercise and I haven't quite ported 100% of the game into it yet, but I do enjoy the benefits I get out of it so far :).
14 Jul, 2011, Runter wrote in the 8th comment:
Votes: 0
15 Jul, 2011, Scandum wrote in the 9th comment:
Votes: 0
Hammering that hard drive all day long is gonna wear it down sooner rather than later. I guess an in-memory database could be used.

Regarding the benefits, it's not that hard to write a player directory scanner that loads and quits all players, updating all character data while at it.
15 Jul, 2011, Runter wrote in the 10th comment:
Votes: 0
Scandum said:
Hammering that hard drive all day long is gonna wear it down sooner rather than later. I guess an in-memory database could be used.

Regarding the benefits, it's not that hard to write a player directory scanner that loads and quits all players, updating all character data while at it.


If this is in response to using a database, most databases don't do disk reads for every query. Most are pretty reasonable in their caching, and I would not worry about wearing down your hard drive.
15 Jul, 2011, kiasyn wrote in the 11th comment:
Votes: 0
Scandum said:
Hammering that hard drive all day long is gonna wear it down sooner rather than later. I guess an in-memory database could be used.

Regarding the benefits, it's not that hard to write a player directory scanner that loads and quits all players, updating all character data while at it.


These are terrible suggestions. In-memory databases? What if the game/db crashes leaving it in an inconsistent state. Directory Scanner? Far more overhead than running a query.

If you don't want to go full-hog database, use something like SQLite, just. not. that.
15 Jul, 2011, Scandum wrote in the 12th comment:
Votes: 0
kiasyn said:
These are terrible suggestions. In-memory databases? What if the game/db crashes leaving it in an inconsistent state. Directory Scanner? Far more overhead than running a query.

As the game crashes you create a backup of the last save, save the databases, let the crash finish, and boot back up. From experience I can assure you corruption is theoretically possible but extremely rare in practice.

You'd run a directory scanner infrequently (maybe twice a year) whenever you need a global player file update, it's not much different from a copyover.

SQLite is a good suggestion.
15 Jul, 2011, David Haley wrote in the 13th comment:
Votes: 0
Quote
As the game crashes you create a backup of the last save, save the databases, let the crash finish, and boot back up. From experience I can assure you corruption is theoretically possible but extremely rare in practice.

Saving data from a program that is in the middle of crashing is usually not terribly reliable – if you're in the middle of crashing, chances are that your data is corrupt in the first place!
15 Jul, 2011, Rarva.Riendf wrote in the 14th comment:
Votes: 0
David Haley said:
Quote
As the game crashes you create a backup of the last save, save the databases, let the crash finish, and boot back up. From experience I can assure you corruption is theoretically possible but extremely rare in practice.

Saving data from a program that is in the middle of crashing is usually not terribly reliable – if you're in the middle of crashing, chances are that your data is corrupt in the first place!

Well you could corrupt your database well before crashing because your program has a flaw as well.
And it is easier to check differences in data in flat files of different version than in a db (I mean it for most muds, because data is still humanly manageable).

Moving to a DB has some advantages and I thought of it but :
-it is a huge work. (though with a snippet that would replace OLC by DB call use it would be really fast)
-for most points you can also 'live without it' as an update/copyover/reboot is like less than 10 seconds. And I think it is bearable for a mud as you wont do it every day as well. (again for a 'bigger game' it is another story)



Quote
* We can keep track of and modify all items, including items in player save 'files'.

I have a tool that regulary check for all items even if players if not logged, because if an item has been looted (pk) and the item is then not used enough I will 'steal' it as I don't like asshole that just loot people to piss them off and then try to make the item unrecoverable.

Quote
* We can balance mobiles and objects in the entire world with a few simple queries. If we don't want to auto-balance certain areas, we can check if any items/mobs are too under/over-powered.
* We can import hundreds of areas from different Diku derivatives, including customized Merc, remapping item and mobile properties easily to fit our own customizations.

That is actually the main interest of a DB use, especially when importing areas. I also have tools that can do it through OLC though. But a DB is the best here


Quote
* We can offer web-based enhancements such as a real-time point-of-view map, a dynamically updated leaderboard, a quest generation form. With the exception of the real-time map, those features don't place any strain on the game server itself. Even the real-time map places manageable strain because of a push/pull mechanism made easy by the external database.

Well I am more mixed about that, because unless you separate the db server from the game server, cpu time still have to be used somewhere. And nothing prevents you to make the same with code running the updating anyway, reading the files from another server.

Quote
* We can design new player, mobile, object properties easily and without disrupting the production server.

Yep easier to do it this way that to replicate olc commands from build port to game port.

Quote
* We can clone off tables to a developer server schema, work on them, then merge them back into production. I can grant the QM access to the quest tables, and he can do his thing independently, without code access.

Why would he need code access anyway ?

Quote
* We can edit help files easily in a web browser and the changes go live instantly for our web-based, mobile, and telnet players alike.

Helps are text files you can edit easily wherever they are ?

Quote
* We can implement and balance quickly features like 'individual character relationships' which would be extremely taxing to do in any other way (it involves storing and updating a record for each link of each char to each other char they meet).

Nod again mass data updating is the point of DB

Quote
* We can change offline and then publish many (eventually it will be all) data tables without requiring a server reboot. These include: the quest system, the help file system, the character relationship tables.

You still have to freeze your char to avoid concurrency while doing it, that would be the copyover most muds use. No need for a 'reboot' as well.

Quote
* We can delegate many maintenance tasks (e. g. backups, character expiration) and also automate many tasks (e. g. eq restoration) via MySQL stored procedures.

Well you can also do that doing Unix scripts. It is bash knowledge vs db knowledge.

Quote
* For various ad-hoc needs, there is often no need to create special in-game admin commands because direct querying of the database does the job.

But then you have to get a tool to access database and know your way in SQL. I can do it, most competent coder knows enough SQL , but that means you push out many builders that dont have any coding skills. So you need to create the commands for them in the first place anyway imho.

That is my pov, I dont say it is not a great tool, it is, but only if you have it from the beginning. Retrofitting a DB in a diku derivative is a great job though, if you share it I will probably look into it :p
But doing it in the first place was not really worth it for me, seems you were crazy enough to do it :p
15 Jul, 2011, Runter wrote in the 15th comment:
Votes: 0
tl;dr

The point of a database is the query. Not saving this or that, or speed. Yes, it brings a lot of other nice things to the table, but the problem they solve is how to efficiently query your store. Diku based muds have an amazingly difficult time doing even basic query. For example, "count how many of item vnum 101 is stored in pfiles". That would be gross. Or how about something as simple as checking the IP of someone logged in vs what you have on record for all pfiles? I can sit here all night thinking of querys that are useful that would be amazingly annoying to try to implement properly in a non DB solution. With a proper database these things are at your finger tips usually in a single line of code, and very efficiently. Anyone arguing against a proper database here is really arguing against a database anywhere, because this is the *perfect place* for one. You really aren't going to find a better sweet spot.
15 Jul, 2011, Rarva.Riendf wrote in the 16th comment:
Votes: 0
Runter said:
tl;dr

The point of a database is the query. Not saving this or that, or speed. Yes, it brings a lot of other nice things to the table, but the problem they solve is how to efficiently query your store. Diku based muds have an amazingly difficult time doing even basic query. For example, "count how many of item vnum 101 is stored in pfiles". That would be gross. Or how about something as simple as checking the IP of someone logged in vs what you have on record for all pfiles? I can sit here all night thinking of querys that are useful that would be amazingly annoying to try to implement properly in a non DB solution. With a proper database these things are at your finger tips usually in a single line of code, and very efficiently. Anyone arguing against a proper database here is really arguing against a database anywhere, because this is the *perfect place* for one. You really aren't going to find a better sweet spot.


Everything you mentionned though is very easy to do in bash with a simple grep. In usually even less character typed than a db query, and without having to know anything else than a directory. A DB has some use when something is big enough or 'generic' enough. And as I said a DB in a mud is really nice if you already has it when you start. Retrofitting one…less so.

I will add a great benefit of having a DB: you can scrap your engine way more easily as well. So if you want to move away from Diku it is way easier.
15 Jul, 2011, kiasyn wrote in the 17th comment:
Votes: 0
Runter said:
tl;dr

The point of a database is the query. Not saving this or that, or speed. Yes, it brings a lot of other nice things to the table, but the problem they solve is how to efficiently query your store. Diku based muds have an amazingly difficult time doing even basic query. For example, "count how many of item vnum 101 is stored in pfiles". That would be gross. Or how about something as simple as checking the IP of someone logged in vs what you have on record for all pfiles? I can sit here all night thinking of querys that are useful that would be amazingly annoying to try to implement properly in a non DB solution. With a proper database these things are at your finger tips usually in a single line of code, and very efficiently. Anyone arguing against a proper database here is really arguing against a database anywhere, because this is the *perfect place* for one. You really aren't going to find a better sweet spot.


I have never wished MB had a 'like' button more than I do now.
15 Jul, 2011, plamzi wrote in the 18th comment:
Votes: 0
Rarva.Riendf said:
Runter said:
tl;dr

The point of a database is the query. . .


Everything you mentionned though is very easy to do in bash with a simple grep. In usually even less character typed than a db query, and without having to know anything else than a directory. A DB has some use when something is big enough or 'generic' enough. And as I said a DB in a mud is really nice if you already has it when you start. Retrofitting one…less so.

I will add a great benefit of having a DB: you can scrap your engine way more easily as well. So if you want to move away from Diku it is way easier.


Grepping is good enough only if the question is "Count/show me the lines where…" and if the answer is all in one text file / directory. Grepping across lines or across files with different formats is a headache. Modifying a file is a pain. Sure, you can use sed or awk for that, but then you're writing advanced scripts for every simple query you'd need. And if the answer lies across a good number of different files, there's a chance you'll never get it.

I would recommend not only retrofitting an old codebase with a full-scale database but also writing a new one that comes with, say, an SQL schema. OK, so the initial deployment will be a bit harder, and it will place some hardware/know-how demands on the dev. But the payoff is tremendous.
15 Jul, 2011, David Haley wrote in the 19th comment:
Votes: 0
Rarva said:
Well you could corrupt your database well before crashing because your program has a flaw as well.

Obviously: if you have a bug, you have a bug!

But if you're in the middle of crashing, the probability of your data being bad is much, much higher than if you are not in the middle of crashing.

kiasyn said:
I have never wished MB had a 'like' button more than I do now.

I think you mean "+1"… :wink:
15 Jul, 2011, Rarva.Riendf wrote in the 20th comment:
Votes: 0
Quote
But if you're in the middle of crashing, the probability of your data being bad is much, much higher than if you are not in the middle of crashing.

If you are in the middle of crashing and you knew it, I think that means you know what kind of exception you have. So if it comes from data or something else. I do not think that have a DB or anything else to save data makes any difference.

Quote
I would recommend not only retrofitting an old codebase with a full-scale database but also writing a new one that comes with, say, an SQL schema. OK, so the initial deployment will be a bit harder, and it will place some hardware/know-how demands on the dev. But the payoff is tremendous.

Don't get me wrong I am all for using one, but I think retrofitting one is a waste of man hour that would be better used in migrating to a modern codebase that use one already. Unless well you share your code so people can use it :p
0.0/81