Pedlar: finish sparx!

I dun wana finish your work! thats yer job!, if i make one, im gonna call it KiasJewishPedlarpwns!

Excuse me?? :thinking:

Pedlar,

Do you load an entire area into memory when the mud boots up, or is it only querying the data into memory as needed when someone walks into the zone? I like the idea of putting area files into the database but I've always wondered what the point would be when you most likely need to load it all into memory anyway.

The only advantage I see is the ability to write a custom PHP/Perl/Whatever web application for OLC. How does changing the data from the PHP app get dealt with in the mud? What if the builder wants a hardcopy of the area file that resulted from all of their work?

Pedlar,
Pehaps you misunderstood my point. I've personally used databases in several of my mud projects. However, that doesn't change the fact that… maintaining a database connection (or worst openning one), generating a query, parsing the query, executing the data, transmitting the data, parsing the data… uses many more resources than a flat file.

In the end, it really depends on what you're doing with the data. I've found that SQL works well for areas and most of the tables. When possible, try to execute queries asynchronously to prevent the mud from waiting for it. It also depends on how fast your game loop was.

In a traditional mud, I didn't run into many problems with latency, but when I was running an event driven mud with a 25-50ms pulse, using SQL dramatically affected overall performance over flat files.

As for crashing w/ flat files… I don't often have that problem anymore, but I always take special care to avoid things I've run into in the past. I've also experimented with XML based area files.

I don't think you could say that database is better or worse than flat files in general. As mentioned it really depends on how you use the stored data. Personally I don't like the homegrown feel of flat text files. I load some data into memory during boot, while others, like helps and certain logs are queried as needed.

SQL makes many things easier, like editing offline characters. I can just send a string like "update players set clan = 'Rural Rangers' where name = 'Joe';" to MySQL/SQLite/etc to change Joe's clan. Updating all the rows in a table at once is equally simple. In addition you can pull all sorts of queries directly instead of coding static, custom functions to handle it.

I'd still like to know how the mud reconciles the database with live memory if the data is loaded at bootup out of the DB. What happens if you have multiple ports accessing the same database and you get people on two different ports saving the same data, but with different stats? Or suppose you take the next natural step and write a PHP app to do web based OLC and now you have 2 mud ports AND the PHP app all wanting to alter the same data?

I think you'd do it the same way flat files work and separate the databases, make one of the ports read-only, or the most recent update would stick and the other would get over written.
Really, you'd have to set up a handler for this. That or just let it go. A problem like this exists in OLC as well. If 20 people are editing the same area, there's nothing to stop the people from over writing prior edits.

In my experience with database I've found them to be fast. Really fast. I don't see any problem with quering a database. Look, a pageload here is about 17 queries and loads in about 100ms, which isn't all that bad, considering it's using an interpreted language to make the query calls. I'm sure with SQL (especially MySQL) you could optimize the hell out of it and probably be safe to drop pulses down to 75ms, maybe even 50. Guess it all really depends on the speed of the PC you're using and if the SQL is local.


Oh! There's so much more! MySQL can dump out stats like crazy! Top 10 list anyone? :P. It doesn't end at OLC; total integration with a website is entirely possible. I've seen MUDs let you actually walk around from the web. You could probably very easily integrate a MUD with QSF.



If the builder wants a hard copy of their area, mysqldump was made for a reason! If they whine about the format, tell them to take a look at a ROM area file and decipher that mess!

So here is my question. What would you suggest? Use a local sql db, or use flat files? :) I'm at the point where I'm writing how the area files are saved/loaded, and I don't want to go forward if there is a better way.

I've been tossing the idea around of using an sql db to handle areas/players/helps/etc, but I haven't gone forward with it for the simple fact that, A.) I know jack about sql right now, and B.) I've always heard that sql was slower.

Cheers!

I can't say definitively whether to use one or the other. I'd strongly recommend learning SQL even if you don't use it in your mud.

In my experience, SQL is superior in most respects except raw performance. I use SQL where possible for a variety of reasons. Keep in mind that my experience is general intranet programming.

SQL can be accessed by most languages and web technologies. It can be used clientside from both Mozilla and IE. It can be used w/ java, .net, C/C++, and python (at least), I've also used it from a variety of shell scripting languages. Making it easy to write tools to do alot of work for you.

SQL can organize and manipulate data for you.

Every SQL engine I've used (except access) has a way to execute queries against the database, this is good for 1 shot queries or testing.

You can easily generate data dumps for backup (or builders) or synchronize data between servers.


The only con I've found to using a SQL database is performance. You'll need to test this personally to see if it's an issue for you. Factors include connection handling (singleton, pooled, or open each time). MUD's tend to be single threaded, so I'd recommend a singleton approach. Since muds have very little data opening/closing the connection is most of the overhead.

If you run into problems w/ the queries, I'm sure people here can help you optimize them. There are simply too many techniques to discuss, and the options vary from database to database.

The structure of the queries is a much larger factor than the quantity of them. I doubt the performance of a web page will have much bearing on the needs of a mud because the amount of data and the structure of the database is different.

I'm going to wager a guess that most of those queries involve less than 5 columns, and less than 30 rows. At most involving 3 tables/joins.

For reference, looking at pfile code for smaug fuss, I estimate:

94 columns for basic player info. (1 row)
2 columns for ignore data (0+ rows)
11 columns for imm data (1 row)
4 columns for skill data (1-150 rows depending)
7 columns for affect data (0-30 rows estimate)
3 columns for killed data (depends on the value of MAX_KILLTRACK and player's level)
3 columns for color data (depends on the value of MAX_COLORS)

That's at least 7 queries, and not counting what each object will require. Also take into account nested objects will need some special logic. Either a recursive function (which needs multiple connections and may exceed the max connections parameter), or an iterative approach (which requires some form of caching), or a data driven approach (which requires the query to organize the data… this would be my solution)

Using your estimate of 100ms for this page, that's easily used half of a normal mud's game cycle for loading a single player.

Now, look at a social, and you're down to a few hundred rows at about 9 columns (RM 4.0 had 590 socials but that's an extreme count)… help files are about 3 columns and a few hundred rows, classes and races are maybe 20-30 rows and a dozen columns. Unlike pfiles, none of these should have many joins.