BLOB vs flat file tables

Newbie & Debugging Questions
#1

In my MMORPG, I recently noticed a problem with inventory items.

At the moment, I store inventory items like this: (very slimmed down version:)

Table: inventoryitems
Primary Key: Integer
ItemID: Integer
CharacterID: Integer
… etc

Then to load an inventory I’ll do a SELECT … FROM inventoryitems WHERE CharacterID=?

Then to save an inventory, I’ll first delete their existing items with DELETE FROM inventoryitems WHERE CharacterID=? and then reinsert their inventory as the server sees it.

The problem with this approach (that I just recently discovered) is that after roughly 4.2 billion item updates (which is easy to hit in an MMO), the numbers overflow and the server starts overwriting old items.

I’ve been thinking about ways to circumvent this problem, and I’ve come up with the following solution:

  1. Deleted the table inventoryitems
  2. Added a BLOB column to the Characters table called Inventory, made Inventory’s binary-serializable and I’ll just load and save inventory data into this column for each character.

Now, I get some advantages doing this method:

  • I never run out of primary keys.
  • I don’t need to do a delete.

I’ve asked a few other people how they would fix this problem, and they said they’d use a 64 bit integer as primary key instead. Now, I don’t believe this is solving the problem, but instead pushing it up another 2^32 levels (roughly 900 trillion).

The question I wanted to ask is, and I’ve done quite a bit of research on this matter but I wanted to get some input from other people, is how do BLOBs perform when compared to multi-row tables like the inventoryitems table?

I’d assume they’d be faster (given my binary-serialization algorithm packs well), but I just wanted to see what other people say.

Thoughts?

#2

This is premature optimization up the ass, but hey what the hell :smiley:

[quote]I’ve asked a few other people how they would fix this problem, and they said they’d use a 64 bit integer as primary key instead.
[/quote]
I would give the same answer.

I don’t understand the premise. You say that after x item updates you will run out of primary keys. An update is an operation, the change of an entry. An operation doesn’t get a primary key as you dont save operations - unless for history / rollback purposes I dunno.
I guess explain a bit more clearly why these operations get saved themselves.

#3

I would give the same answer.

I don’t understand the premise. You say that after x item updates you will run out of primary keys. An update is an operation, the change of an entry. An operation doesn’t get a primary key as you dont save operations - unless for history / rollback purposes I dunno.
I guess explain a bit more clearly which these operations get saved themselves.
[/quote]
I used update loosely. An item update is not an SQL update.

An item update is actually an erasure of all the players items from the database and a reinsert of all items. Which means that for 100 items, I log in, log out, all these items would have their pkeys increased by 100 (assuming nobody else loggedin/loggedout).

It’s way too difficult to track all updates to items, for example if the stats on the item changed, if the item got moved somewhere, etc. There’s way too many cases to consider so an SQL_UPDATE is out of the question.

#4

In-game, when would this happen exactly in your model here ?

#5

Upon log off or world switch.

Items are stored in server memory until the client is terminated, in which case it will update all the players data in the database.

#6

If you need to save item stats, have a separate table for that, and a second dedicated relation table. For the latter, you could even have a composited primary key and would not need to give IDs at all.
Treat the cause, not the symptom.
If more than 2^64 entries are needed for tracking inventory, there is something fundamentally going on wrong.

#7

That is exactly how I feel here.
But… I don’t feel qualified to recommend anything here because this is something to get right. So I would personally really look at how big mmo’s actually do this.

#8

Your assigning new primary keys for an update operation. This is not how it should be done, an update should just update, not delete and insert. Also that is a lot of updates and 2**64 stupid many.

Say you have 10000 players and you update the data once every second (hint, that is too often). It will take 119 hours to use all the keys. If you just update you only use 10000 keys. If you have 2**64 keys it will take 58 million years to use all the keys at a rate of 10000 per second.

#9

The problem here is you’d need to track where each and every one of the items is, not to mention map them with their unique key in server memory.

In a fast changing environment like a MMORPG this quickly becomes wasteful, hard to track and bug prone.

#10

thats why there ought to be a professional paper or something on this topic

I mean we are really dealing here with big data, networking/parallelism/threading, low latency / performance requirements, security aspects and failsafes so that no data is lost. that is one tough piece of software and should be created by looking at successful designs.

#11

What are you talking about? updating something is just as easy and in fact easier than deleting it and re-adding it and assigning new keys. Not to mention less likely to result in lost data when it crashes.

All MMO’s have to keep track of everything no matter how they are implemented.

#12

Alright, so say I dropped the item. It disappeared and no longer exists. How do I delete it with an UPDATE?

Say I traded the item to another player. The other player now has my item. How do I update ownership with an UPDATE?

Say I lost the item to a quest. The server has now erased my item from my inventory. How do I update the table so my item is now gone?

The problem is there’s way too many cases to consider, and therefore it is a lot simpler to just delete everything and do a re-insert, unless you want to do a DB query every time somebody’s inventory is changed (which I would not do, since it’s a lot quicker to batch the SQL query at the end of it all upon logout or world switch), not to mention you’d also need to keep track of the item’s Primary Key in order to update it. (4 bytes/item wasted for no good reason).

Anyway, my main question is, are BLOBs the best way to approach this or is there a better way? I’ve just heard BLOBs need to be converted to ASCII before the DB will handle it (which doubles the bandwidth required) but i’m not sure if this assumption is correct.

#13

Changing ownership is 1 DELETE and 1 INSERT, no need to query the database and with a compound primary key no need to get new primary key values.
Memory is really no issue at all.
All this is normal in any high performance business application.