Most efficient use of mySQL database tables?

#1 Incase anyone else is doing an overly complex inventory system I've had a sit down with a couple professional developers and here is the consensus..

From our discussions, the most efficient way to handle high volume parsing for a system like this is a table that contains the entire inventory group for all players. Key colums would be the playerID and packID. It is going to be a large table..

So given the fact that it will be a large table: Reducing the LAN stress for high volume querries we would use a local temp database (Perhaps SQLite) to handle the high volume of transport for server requests. Then the SQlite DB exports inventory updates to the mySQL server on a timed basis.

Overview: mySQL server has an inventory table for ALL playerID packslots. When a player logs in to a mission server the mission server requests the players inventory from the mySQL DB and loads it into the SQLite local DB. All inventory transactions from the server are handled by the localized SQLite DB which sends updates of all the players inventory on that mission server to the mySQL db on a timed basis.

This system is certainly not perfect I am sure but it seems liek a very effective way to handle a high volume mission critical DB table.

Mark

PS: As always comments and suggestions are more than welcome.

#2 I'm currently tryin to do something similar, using mysql to hold inventory data, and lots of other stuff. I've used databases with websites before and loved how limitless the possibilities were. All game content could be pulled from a database as its needed, contextually. Mine wont be complex yet, but I do want to support simple containers(easy to do). At the moment I'm still tryin to get the info from the db to my local inv system, maybe I do need the SQLlite db like you suggest.

#3 Huge database files of any type are scary. It seems like a waste to use one huge inventory file for ALL inventory in the game, especially when it is on a massive playscale. This would include all players who are offline, which is completely unnecessary.

When you are thinking about databases in the terms of a massive multiplayer game, you want to keep the filesizes down and keep them out of memory as much as possible, or you are effectively assuming your server has unlimited memory.

Also consider the fact that if you allow players to have multiple character per account, players tend to mule quite a bit when they have limited inventory/bank/etc space to store things, which effectively multiplies your inventory file size by the amount of characters each account is allowed.

The only inventory that really matters is that which is actively in the game, or that which may be perceived by online players such as in the case of visible items stored in a players house.

You might consider having a seperate server that only deals with character info and inventory, keeping each in a seperate file, only opening the files when needed (when something is changed).

#4 SQLite needs 256 bytes of RAM for every 1MB of database.

Do the math and memory usage adds up really quick when you leave large database files in memory.

#5 Thanks for the info on SQLite Entropy I wasnt aware on the RAM reqs.

I think I perhaps wasnt clear enough on my second post re the table configuration of mySQL. I didn't mean to suggest for a moment that a person should attempt to load the entire database of all inventory items in the game into a table but more specifically a table that contains rows for the inventory of all created characters in teh game and what they are currently carrying.

I spent a few hours last night thinking things through on this and created a table for character equiment (which includes 6 inventory slots much like the original EQ and any currently equiped items) and another table for "active" containers.

The container table has 2 key columns, playerID and packID. So you can parse the container table by player ID to return the 6 active container packIDs for that player.

Works very well. I'm sure it's not perfect but it works very effectively.

As to SQLite, the design was for it to hold only the container and equipment tables for players currently logged into that server. I am targetting a potential 100 concurrent players for my mission server design which could mean a whole lot of data in SQLite. I'll have to do the math tonight and see if it's worth it. If it's not I can write a fairly efficient flatfile DB for the localized temp table storage.

Thanks again for the SQLite info and for getting me to clarify the breakdwon of the inventory equipment and containers.

Mark

#6 Yeah 100 people shouldnt be too bad. SQLite will handle that with ease.