Actually, when you start weighing database pros and cons, without ever mentioning the overhead for development, in a response to someone who has started a thread outlining what he has implemented it's a pretty clear indication that you're naysaying the necessity. And that's exactly what you did. You claimed the functionality databases offer for query is solved easier using bash and grep. That, sir, is using a tiny, tiny hammer for your screw.

You may say that NEW projects should use a database. Well, I say that people should use one on EXISTING projects. The right tool for the job didn't change just because you had the wrong tool handy. If you're going to be hacking at it for years, then spend the time and do things right. The entry level for putting stuff into a database isn't 400 hours. You could get everything moved over with no complex relationships in hours, not hundreds of hours. The OP obviously did a meticulous setup, which I applaud. But estimating 400 hours necessarily entry level for moving data into a database is about as absurd as 1 million billion hour. And I don't think he ever did that. The milage is going to vary based on A) your experience with the codebase in question B) your experience with the database technology and C) a lot of other things.

It's not just power outages, any hardware failure, failure to shutdown the system or the server daemon properly.
Seriously, if one insists on MySQL, I do recommend using InnoDB as the backend.
Because speed isn't important as data integrity, IMHO.
And there is no advantage in MySQL for multiple process access when MyISAM does not allow row-level locking anyways.
Again InnoDB is a better backend for that because it do.
Oracle E and DB2 PE probably satisfy similar vague requirements.
And if one still insists on MyISAM, I'd recommend backing up everyday, just like any smart admin would do with their Diku flatfiles.

Yeah that is the key point…IF….


Actually yes it did..but you seem to fail at understanding a basic ROI rule.

InnoDB produces a noticeable hiccup when players enter and exit the game (item saving / loading). I've tried many different settings but there's no getting rid of it (the tables involved are very large). I can't have the game hiccup everytime someone exits/enters. I've got daily online backups for the last 7 days and offline backups to a RAID-5 for the last 30 days so that part's taken care of. Some of the smaller tables containing important player info seem to show no performance issues when under InnoDB so I'll use it there. It's nice to have such flexibility.

One thing I wonder about is if it's easy to restore a player file from backup using a database.

Yep. I keep the last seven days in a separate BACKUP schema, in tables named after days of the week. If anyone needs a rollback, the same function that normally loads player items is used to restore them to a given day:

if (!day || !strcmp(day, "today")) {
		sprintf(buf, "SQL: Reading object save for %s (#%ld)", ch->player.name, pid);
		log(buf);
		sprintf(buf, "SELECT * FROM PlayerStoredObjects WHERE PID=%ld", pid);
	}
	else {
		sprintf(buf, "SQL: Reading backup object save for %s (#%ld), %s", ch->player.name, pid, day);
		log(buf);
		sprintf(buf, "SELECT * FROM BackupPlayerStoredObjects%s WHERE PID=%ld", day, pid);
	}

When they get their eq restored in-game, it gets saved to the live player object tables.

I've only had to do a full rollback once. It involved swapping the player object tables with those from a daily backup. I haven't needed to go further back than 7 days yet but that would involve swapping the actual table file because those backups are offline (copies of the mysql directory).

What about the rest ? skills learned stats levels pets etc ?

You could also use a binary approach, storing 1, 2, 4, 8, 16, 32, 64, 128 days back.

Do you have mechanisms for detecting object duplications, and is there the possibility for object duplications during crashes? Issues I believe Tyche raised, but I'd like to highlight these specific cases as players are likely to abuse it.

To properly handle that, you'd want to wrap a transaction around the delete/add events. For example, when looting a sword from a corpse, the operation might be to insert a new row in the player_inventory table, copied from a template table of standard items.

begin transaction;
OLD_ID = select max(instance_id) from inventory;
insert into inventory select * from items where obj_id IN (select obj_id from corpse where corpse_id = CORPSE_BEING_LOOTED);
insert into players_inventories (player_id, instance_id) select (PLAYER_ID, instance_id) from inventory where instance_id > OLD_ID);
end transaction;

So, nothing gets replicated if a crash happens during the transaction.

In the case of an object directly moving from one player to another along with gold, you could just do:

begin transaction;
update players_inventories set player_id = NEW_PLAYER_ID where player_id = OLD_PLAYER_ID and obj_id = OBJECT_BEING_TRADED;
update players set gold = gold - GOLD_TRADED where player_id = NEW_PLAYER_ID;
update players set gold = gold + GOLD_TRADED where player_id = OLD_PLAYER_ID;
end transaction;

So again, none of the changes happen until they've ALL successfully happened.
This is pseudo-code, as the stuff in all caps would be parameters passed in from code, and you'd keep OLD_ID in code as well.

As for InnoDB being too slow (or other non-MySQL databases), do what most database people do. Cache your results and do the actual queries only when you need to. IE: use dirty bits to determine if something has changed since the last time it was read. Many good database systems will cache tables or row sets that are commonly used for you. Some even let you emphatically "pin" them in memory.

Also, make sure you analyze your queries and add indexes where it will actually make a difference, but don't add a zillion indexes so writing takes forever. That's where a solid schema matters, rather than just a handful of fat tables.

I've never had to restore the Player Skills table or other saved char stats, but it can be done analogously by calling the load char stats function with an optional "day of the week" parameter. All player info tables are backed up with a daily stored procedure. I've just never had to restore anything except eq (my game requires corpse recovery and sometimes the game crashes while people are making their way back to their corpse).



I should probably use the binary backup approach you're suggesting since some players may not catch a problem with their char right away.

During the transition from flat player saves to SQL, I tried a more economical approach to rewriting player saves and I had a bug which was causing occasional duplication. But that was the bug's fault and not the database's. Once that was fixed, I've never experienced duplication problems.



MyISAM doesn't support transactions but its speed is unsurpassed and I need it. I have not been able to get InnoDB to perform well enough for player item saves in particular. Indexes and query economy are great general advice that I have already followed, caching is enabled and generous. But for various practical reasons, loading a player's items proves to be a challenge for InnoDB. For instance, my player saves carry info about the item's position in the inventory so it can be restored exactly, which means that in most cases, as items shift places, the result of a query for the same player's objects will be different.

Because I need to keep using MyISAM, right now I'm using a "good enough" approach to duplication prevention. I carried over the easiest safeguard from the flat file era: always begin by wiping the char's eq save. That way, if a crash happens right then and there, the char ends up with nothing and you restore them from backup, rather than risk spawning multiples they may or may not report. The player saves have been in MySQL since March and I haven't had issues that would prompt me to make saves any safer.

Why not make a narrow table for inventory position then? If the items themselves haven't changed, there's no need to rewrite the entire set of rows just to update a single column. That would also probably prevent a bunch of key constraint checks from needing to happen, since it would just be something like player_id, item_id, inventory_slot.

I've used PostgreSQL in large production environments with many thousands of write operations a minute, and thus find it hard to believe it's the database at fault, unless MySQL does practically ZERO optimization. Not having transactions would be a deal-breaker for me.

Typically, with transactions, your write operations go into the transaction log very quickly, thus freeing your process to go back to its own work. The database itself will move those rows into the proper spots in the table as it gets time and flushes them from in-memory cache. In some cases, that means it's actually faster than not using transactions, especially if you're writing to many different tables in quick succession.

YMMV obviously, and if you're happy with the results, that's what matters. :)

Something sounds very wrong with this. Not that MyISAM might be faster, but that players would even notice a difference regardless of the database used.

Agreed. How long would you guess this hiccup is? Are we talking seconds or only slightly noticeable?

You might see exactly how many MS the database query/update is taking. Most of them give you that information in my experience, although I don't know anything about InnoDB.

Could be due to any number of things… a suboptimal index representation on disk, the wrong columns being indexed, bad caching, some kind of funky locking, etc.
Agreed that there shouldn't be a noticeable delay unless you're doing some very big processing.

Generally it's trivial to restore an entire table, rather than a particular snapshot of a record and all its relations.
However, there are patterns for defining and implementing transactional logging either in the application itself or in triggers in the database.
I've always preferred using triggers for this. It simplifies the application, but might tie it to a particular database (i.e. Oracle PL/SQL).

I spent some more time tweaking InnoDB-specific settings and the hiccup is gone. I suspect it was trying to cache too much data into the RAM and ended up paging (the player object table is the largest one). Looks like I can set InnoDB as default before I share any code here. Thanks for the inspiration.

Just one table? Why would you put everything into just one table? It's better to do multiple, than squeezing everything into one table that would cause accessing/querying that table a pain.

I have multiple tables for player files. Core data, inventory, quests and etc; all separate tables linking to one player. Restore of a player file can be a pain, but it's worth it for efficiency and system balance.

Communication impedance mismatch.

:D

In my experience, having certain things (those which will always be queried together and seldom joined in other queries) in one table makes querying easier, not harder. It also prevents certain properties from falling out of whack (at least with MyISAM it did), which is why I had item inventory position in the same table. Since I do a lot of manual querying, I've found that keeping the number of tables down makes it more humanly manageable. So whenever there's no performance penalty, I have wider tables.

Not sure why you can't just automate a procedure for updating all the tables you have. In the case of player, I too have multiple tables (more than a dozen), and I've made procs to update all that's needed. The point is restoring a player file from multiple tables can be just as easy as restoring from one table when automated. Even if your db doesn't support procs, you can do a series of queries with temp tables and get anything automated via the client.

And, to add to the list of benefits of a standalone db, I can now do:

call worldReplace('massice', 'massive');

Planning to be typo-free by tomorrow! ;)