Mysql Multi-Server from within - Quick Query

#1

Quote:Can someone please follow up this thread with info to why people do not do this

Sure. The answer is that people do do this, but it's usually a custom piece of architecture that is built to fit a specific game's needs. Actually, IBM created a whitepaper on MMO architectures using TGE/TGEA and the HTTPObject as a way for servers to communicate in the background. Also, several developers have created custom load-balancing solutions that go well beyond a MySQL field that says which database to grab a query from.

It's not that two databases has anything to do with the number of queries (I'm running almost 10 schemas now, which will divide up to a few different machines in a perfect world), but DB implementations have everything to do with your game design, and it's a custom thing.

And assuming no one addresses the question is a bit simplistic- it gets addressed all the time, but the information is not posted for various reasons: People feel that they don't want too much of their DB architecture out where it can be analyzed and hacked, they don't feel that it has to do with the game forums, or maybe they're just secretive. But considering that there's a good number of MMOs made with the various versions of Torque engines out, you can rest assured that not only do people know this, but they're implementing advanced methods of DB design.

So the thrust of this thread shouldn't be why no one is doing it, but asking them to post their insight and experience with the methods they've tried.

#2 Personally I think it's a complication that's best avoided, databases can handle many thousands of times the volumes of transactions that an MMO would put on them so unless you grow to the scope of WoW then you're not going to need to worry about it.

And IF you grow to that size chances are you're going to run it on something like an Oracle RAC rather than mySql.

Chances are you're going to end up with data split across databases or requirements to migrate info from one to the other which just causes extra hassle.

Avoid it like the plague - Ted's approach is the right one, keep it all in one database but seperate by logical schemas

#3 Thanks for advice guy, helped me make a crucial dictation to how I was thinking.

#4 Well, with databases, your architecture follows your features. I need a lot of extraneous data for procedural content, conversation system, lists (for procedural content), mission data, and AI, on top of the usual MMO stuff. Dividing it up into separate schemas helps organize it.

Some other things I learned, for everyone's benefit (and there are some DB ninjas lurking here that can add to this list):
- Favor rows over columns: That is, if you have a million and one columns in a table, take a good look at it and see if you really need all those columns there, or if you can make another table.
- Avoid using "SELECT *" unless you really need to grab every column. Otherwise, just do the typing and grab what you need.
- JOINs: This goes to the above. If you need more data returned, you can always do a join instead of returning fifty columns, plus JOINs allow you to combine multiple SQL statements into one, which saves you (and the player) time.
- SaveToString(): This one I learned from Ari from BrokeAss Games in GDC in March (and why I preach about people putting their ideas out there, because if either one of us were secretive about our games, we never would have had the conversation). You can serialize any object by calling SaveToString(), and even use that to dump the object into the database. Very handy, though serializing to a VARCHAR that big in a DB breaks some rules, but still very handy...
- Always put a middle-man between the application and the database. That's just simple network security, right up there with Never trust the client.
- Learn stored procedures. I'm not up to this yet, but I feel like I have time ;)

There's more, and rules which are hard-and-fast for one implementation are not so hard-and-fast for another. My own laptop can handle 2.5 million INSERTs in a 24-hour period, including individual commits. If you cache them and do commits at intervals, which I believe is how many high-transaction networks do it, you can handle even more- and laptops, even cute little custom jobs like mine, ain't as fast as servers.

I'm currently fighting MySQL with data migration from the laptop to the desktop WinXP "server", and I'll probably run the DB metrics on the 750GB RAID 5 (4 drives) that's in it to see what the change is sometime this week ;)

#5 well what about httpobject with php page with a database of mysql hooked up to it.IS THAT A GOOD WAY TO USE SCHEME?

#6 @Robert: I think you're confusing the term "schema" with the word "scheme". They don't mean the same thing. If you're just starting out with MySQL, and I suspect that's the case, then I'd recommend a "21-day book" for it. That's how I learned it initially, and I still run back to it for reference.

In the meantime, a "schema" is basically a grouping of tables. So, I have a "test" schema that contains the NPC and PC tables for stats and such, and then I have a "zone" schema that contains tables for mission data, an "ai" schema that contains tables pertaining to my ai stats, and so on.

It actually does not have to do with where the database server is within a network in relation to other servers or services.

#7 ahh gotcha :)

#8 Good advise as always Ted - must say I like the the term DB ninja (I'm a database guy for my day job and DB Ninja sounds much cooler for a job title than Data Architect).

@Robert - Yes game server to PHP and use the PHP to connect to the databae is a good design which is what Ted meant by:

Quote:Always put a middle-man between the application and the database. That's just simple network security, right up there with Never trust the client.

Obivously remember that anyone can simply run a PHP page from your website though and anything transmitted via HTTP is in clear text so passwords, etc could be stolen that way. As a minimum you should encrypt sensitive info with an MD5 or similar but actually best to use a more detailed encryption process.

To add to Ted's list of database design tips (some of these won't apply to mysql as I'm not up-to-date on it's features)

- Use different accounts to own tables than you connect to the database i.e. tables are store under the user/schema called game_admin and the game connects using the username game_user then only grant the priveleges to game_user that they need to access i.e. if it shouldn't be deleting or updating records to a specific table then don't grant it access. This way if someone does find out your password they are limited to what damage they can do.

- Indexes - these speed up performance for reading data but slow down performance for Inserts, Updates and Deletes so use them wisely, the optimizer in the database will decide whether to use the index or not - generally work to the 10% rule if your query will read less than 10% of the table it would use an index more than that and it's faster to do a full table scan (the value varies depending on the database and what hardware you have but 10% is a good starting point). Basically avoid putting indexes on fields that a simple 'Yes' or 'No' flag (unless you have advanced features i.e. in Oracle bitmap indexes).

- Use Stored Procedures where possible - they're faster (pre-compiled code) and more secure (people can't do anything that the stored procedure doesn't let them), failing that when you have complex queries or joins across multiple large tables then create a View (they're faster as again it's predefined).

- In most databases queries are case sensitive i.e. "Select * from table" is not the same as "SELECT * FROM TABLE", when the database see's a query for the first time it generates a plan for how to execute that query... if it see's a SQL statement that exactly matches one from before it can re-use the same execution plan and is therefore faster. So formulate a standard for capitilizing your queries and stick to it. Also on the same note invesigate using Bind Variables rather than putting them in the query.

- If you have small tables accessed frequently, then investigate Pinning them in memory (called Query Cache in mySql) you'll be a 100 times faster reading them.

Right best get back to some work but there's a few more pointers for you.

#9 About security: An SSL certificate can be had cheap these days, if you have a server which allows it. Some hosts charge more for the installation than the actual cert, and it's not all that much. This should give you enough security for passwords to fly back and forth.

Case and readability: Keeping all DB instructions uppercase isn't only necessary some places, but useful for your brain to filter out the essential parts. If you use PHPMyAdmin for manipulating the DB directly, you get the additional aid of syntax highlighting.

#10 Only problem with using SSL is that you'd have to implement that into the HTTPobject in Torque but sure it's a good idea.

#11 Yeah, the recommendation I've seen for easy SSL support is to use libcurl. I think I saw a resource for exactly that somewhere.

#12 libCurl for T3D is here