Using MySQL for a 2D MMORPG?

[Ethanbot]

Hello everyone,

I'm writing a very simple 2D MMORPG game. Right now it consists of a single map, a single character sprite, and only the ability to walk around. I'm doing this to demonstrate how much easier having a ready and able database to use for the server-side end it is to get a 2D MMORPG up and running. I do, however, have one issue.

Though it seems to work pretty well as of now, at times it lags a little bit. I've only tested with two players, so I have absolutely no idea how it'll react with, say, twenty players on a single map, all buying things, fighting, chatting, etc.

I'm running the MySQL server off of this computer, my own personal one, and I'm still continuing my normal endeavors such as surfing the web, downloading things, etc. I do understand this will slow me down. My question, in the long run, is this: How can I beef up the speed of the MySQL server, and in turn, speed up the game? Can I somehow use the UDP protocol, wouldn't that help?

Any help from anyone would be greatly appreciated.

[Ed Mack]

As much as you can try to optimise bits and pieces, I think having a beefy server capable of dealing with the load (or some sort of server cluster if things get that big) is pretty much the answer.

However, just don't worry till later :P Then you can beg faithful users ect..

[Ethanbot]

As much as you can try to optimise bits and pieces, I think having a beefy server capable of dealing with the load (or some sort of server cluster if things get that big) is pretty much the answer.

However, just don't worry till later :P Then you can beg faithful users ect..

;) Mooching.. yes... good idea..

But seriously, if anyone has some optimization ideas, let me know. I'm very new to running my own server, but not much MySQL itself. Anyway, I heard UDP is better for games because the packets aren't verified like in TCP/IP. Is there a way to make MySQL use UDP?

[geon]

Eeeeh... You shouldn't let the clients connect to the database server (MySQL). The database should be used for data storage only, while your own server software communicates with MySQL. After processing the data can be sent to your clients via UDP.

The main reasons for this is:
* Security
* Modularity
* Simplicity

I also believe using MySQL for a project like this is a bit like shooting mosquitos with a minigun. I think a library like SQLite would perform better in such a scenario, since it is lightweight and you would skip the overhead of sockets.

[Ethanbot]

Eeeeh... You shouldn't let the clients connect to the database server (MySQL). The database should be used for data storage only, while your own server software communicates with MySQL. After processing the data can be sent to your clients via UDP.

The main reasons for this is:
* Security
* Modularity
* Simplicity

I also believe using MySQL for a project like this is a bit like shooting mosquitos with a minigun. I think a library like SQLite would perform better in such a scenario, since it is lightweight and you would skip the overhead of sockets.

Thank you for your input.

At one point I had my own server programmed, and the game lagged much worse. It's actually faster than it was. I'm afraid writing a server to deal with everything may make it lag even worse than before. I'd also like to mention I'm using DXGame (Visual Basic 6.0), which I'm aware is not ideal for games, but knowing this may help everyone give me better feedback.

Is SQLite a database like MySQL, or something to access MySQL? As long as I can use it with VB6, I'll learn it. I do need to make this faster though, and I know it's possible, because I've seen people do it. I'm just doing something wrong.

Thanks again everyone for your help. Hopefully I can get some more feedback on this.

NOTE: The old server simply relayed data to the other clients, there was no storage, nothing. It was sent as a normal text string, such as ("MOVECHAR!CharNum,X,Y,Dir,AnimFrame"). I will not be using this system even if I implement a new server, I will still be using some type of database (preferably accessible using PHP as well). So instead of simply relaying, I presume the client would send something along the lines of my MOVECHAR command to the server, and the server would then query the database. This is obviously going to take more time, and in turn, cause more lag. My only option is to speed up my database and server, so once again, any ideas or tips you have will be greatly appreciated.

[geon]

It was sent as a normal text string, such as ("MOVECHAR!CharNum,X,Y,Dir,AnimFrame")

This would be the first place I would look for improvements. Switch to a binary protocol, instead of the text based. Normally, game protocols like these are heavily optimized to fit in as small space as possibly.

Count your commands. If they are less than 256, you can use a single byte to store it. Depending on the command, the following data would have a different meaning and different length. Doing it binary will also let you get away without a single string compare, wich could help.

SQLite is a database library, instead of a stand alone server. It means the SQL-engine will be directly integrated into the main application. To use it for networking, you need a server that can handle the connections to the clients.

[TheNut]

It would help to know more about how you setup the DB and communicate with it. Typically you can use any database you want for any purpose. Just make sure you can create an infinite number of records (last time I checked MySQL had an upper limit) and stored procedures (supported in the latest MySQL?). Stored procedures make a big difference in performance when you need to access content frequently.

Secondly, you should minimize the amount of information you check from your database during runtime. You should copy meaningful content from the database over into server memory and access content via a fast algorithm like a hash/session (every player logged in should have a session ID). You should notice a significant performance improvement with this setup. You should also separate your DB server from your game server if possible.

As geon suggested, you should use a binary protocol over ASCII. In my engine I use XML, which I dispatch to a parser that converts the contents over to a binary network message with a header for the server to process (best of both worlds). It works very well with clients and with no noticeable performance penalty. The code is clean and easy to debug. For the server side, if performance is not an issue I follow the same format; otherwise I create an object for each message and dump the binary data directly into them.

[Ethanbot]

You should copy meaningful content from the database over into server memory and access content via a fast algorithm like a hash/session (every player logged in should have a session ID).

Are there any documents explaining some fast algorithms for this purpose? I'm still pretty new to anything networking. This is my first real multiplayer game. Any websites or tips from yourself or others would be greatly appreciated, as I'm still not too sure as to what you're saying.

Thanks for your reply.

[SmokingRope]

Are there any documents explaining some fast algorithms for this purpose?

He's referring to a "Hashtable" which can generally be used to access data using an index of an arbitrary type. If each of your clients is referred to with a unique id you could then store data in your "Hashtable" for each client regardless of the value used as the id. When a client connects to your server you should then access your database and put whatever data is relevant to the client into said hashtable. This way you can 'quickly' access the data which is normally stored in the database by using the hashtable and the 'unique id'.

VB.Net has a built-in 'Hashtable' which you could probably put to use right away. Once you start using the hashtable you'll need to remember to save whatever data you have put into it back into the database when each client quits.

[Ethanbot]

He's referring to a "Hashtable" which can generally be used to access data using an index of an arbitrary type. If each of your clients is referred to with a unique id you could then store data in your "Hashtable" for each client regardless of the value used as the id. When a client connects to your server you should then access your database and put whatever data is relevant to the client into said hashtable. This way you can 'quickly' access the data which is normally stored in the database by using the hashtable and the 'unique id'.

VB.Net has a built-in 'Hashtable' which you could probably put to use right away. Once you start using the hashtable you'll need to remember to save whatever data you have put into it back into the database when each client quits.

Two questions.

Can you give me a very simple 2D MMORPG example of using a hashtable (what info am I retrieving, etc)?

How can I utilize hashtables in VB6?

Thank you for your reply and hopefully your answer to my last questions. =)

[SmokingRope]

First to clarify, at least in VB.Net it's "Hashtable" or


dim myHash as new Hashtable() ' Create a hashtable

myHash.add(playerId, new Player()) ' Add a player into the hash
somefunc( myHash.Item(playerid) ) ' Call a function passing the player as a parameter


Anyway, a simple example of some data you might put into your hashtable would be your players coordinates or maybe their hitpoints.


Public Class Player
Private hitpoints as integer
Private maxhitpoints as integer
' Place Other Variables Here
End Class


You would then load a copy of this class into the hashtable for every player when they log in. Each time you make a copy of the class you would load the information from the database.

The real goal is to avoid repeated queries into your database when you're trying to achieve high performance. Each query into the database requires numerous instructions as the data is pulled from the filesystem, passed through the databases query mechanism and eventually cast into the proper data type in your program.

Using the hashtable and the class above you can achieve a huge speed increase in exchange for additional memory being consumed by your program. It's the classic speed/size trade off that plagues the world of program writing.

If you concern yourself with where you place your database queries and try to minimize the number of queries you make you can significantly speed up your program!

[Ethanbot]

First to clarify, at least in VB.Net it's "Hashtable" or


dim myHash as new Hashtable() ' Create a hashtable

myHash.add(playerId, new Player()) ' Add a player into the hash
somefunc( myHash.Item(playerid) ) ' Call a function passing the player as a parameter


Anyway, a simple example of some data you might put into your hashtable would be your players coordinates or maybe their hitpoints.


Public Class Player
Private hitpoints as integer
Private maxhitpoints as integer
' Place Other Variables Here
End Class


You would then load a copy of this class into the hashtable for every player when they log in. Each time you make a copy of the class you would load the information from the database.

The real goal is to avoid repeated queries into your database when you're trying to achieve high performance. Each query into the database requires numerous instructions as the data is pulled from the filesystem, passed through the databases query mechanism and eventually cast into the proper data type in your program.

Using the hashtable and the class above you can achieve a huge speed increase in exchange for additional memory being consumed by your program. It's the classic speed/size trade off that plagues the world of program writing.

If you concern yourself with where you place your database queries and try to minimize the number of queries you make you can significantly speed up your program!

Well right now the only place I run queries is in every loop to retrieve the other players' positions. Isn't this the fastest and pretty much only way to do it, or are you saying to retrieve ALL the info and then pull it out locally? Is that truly faster?

Sorry for my ignorance and thank you for your help.

[SmokingRope]

The idea of the database is persistent storage.

I'm not being entirely accurate with this, but the amount of time you spend reading/writing with a database is going to be at least as slow as if you were to load the data from a file on the hard drive. If you're unsure of the speed implications of this i would definitley reccomend trying out writing a some functions to load/save data to file. Now replace your database queries with those functions. You'll quickly realize this isn't very efficient. Just because the query can be accomplished in a few lines of code does not make the database queries fast.

What you should really do in this case is keep all the data in RAM. Accessing data from RAM is much faster than reading from file or from a database.

[Ethanbot]

The idea of the database is persistent storage.

I'm not being entirely accurate with this, but the amount of time you spend reading/writing with a database is going to be at least as slow as if you were to load the data from a file on the hard drive. If you're unsure of the speed implications of this i would definitley reccomend trying out writing a some functions to load/save data to file. Now replace your database queries with those functions. You'll quickly realize this isn't very efficient. Just because the query can be accomplished in a few lines of code does not make the database queries fast.

What you should really do in this case is keep all the data in RAM. Accessing data from RAM is much faster than reading from file or from a database.

Do you know of any sites that can explain storing in RAM using VB?

[SmokingRope]

Here you go... - Clicky (http://justfuckinggoogleit.com/)

[Ethanbot]

Here you go... - Clicky (http://justfuckinggoogleit.com/)

=) Nice try, but I saw the URL before clicking it.

Other than that, I wasn't asking for general information, but personal preferred and personally suggested sites from you, as you seemed knowledgable on the subjects we've been discussing. Apparently I was mistaken. Next time, I'll make sure to check Google before asking someone what they prefer out of respect for that person.

[Reedbeta]

Don't take it personally Ethanbot. Just FYI, what you are asking about is something that's quite basic and it's not likely that there ARE any web pages devoted specifically to this topic. Since it seems that (no offense) you are a relatively inexperienced programmer, you'll need to do some brainwork in order to figure out how to make this happen.

[Ethanbot]

Don't take it personally Ethanbot. Just FYI, what you are asking about is something that's quite basic and it's not likely that there ARE any web pages devoted specifically to this topic. Since it seems that (no offense) you are a relatively inexperienced programmer, you'll need to do some brainwork in order to figure out how to make this happen.

I'm relatively new to net coding, yeah. Other than that, I'm a pretty well versed coder. No offense taken =)

[SmokingRope]

Alright, well after leaving you in suspense ...

Duh Duh Duh ....


Dim x as integer
x = 20;


In the code fragment above, we have created a variable x which is initialized in RAM and contains a value of 20.

[Ethanbot]

Alright, well after leaving you in suspense ...

Duh Duh Duh ....


Dim x as integer
x = 20;


In the code fragment above, we have created a variable x which is initialized in RAM and contains a value of 20.

*slaps forehead*

My mistake, I just misunderstood. That's one of those extremely common things that you just don't really consider.

Other than that, there's no reason to be rude. It was only a misunderstanding.

Also, you don't use semi-colons in VB. Just thought I'd point that out so you don't run into any compilation errors and have people tell you to go fucking google it. =)

[SmokingRope]

LOL ...

No offense was meant, despite the rather vulgar domain name.

I'm primarily a C/C++ developer, forgive my autonomous response to place a ';' at the end of every statement.

[Ethanbot]

LOL ...

No offense was meant, despite the rather vulgar domain name.

I'm primarily a C/C++ developer, forgive my autonomous response to place a ';' at the end of every statement.

I was just kidding as well =)

[eddie]

Kissing and making up over the vulgar domain aside ( ;) ), do you get what they're trying to teach you Ethanbot?

Just curious -- I wasn't sure if it's completely clicked for you yet.

If so, disregard the below. If not:

Performance is (loosely) related to your loops. Specifically, what's going on inside of them. The more stuff you do inside of them, the slower it'll go.

Synchronous IO (shudder) over the internet (double shudder) to a MySQL database, which is probably locking the DB on read (triple shudder), depending on how you're connecting, every position update (wide eyede quadruple shudder) will definitely slow you down.

What some people have suggested here, is to essentially cache the pertinent data in RAM. This will give you the fastest access, and it's a good trick for dealing with stuff at runtime.

Normally you go about it like this:

- Player logs in
- Cache the pertinent information in RAM using variables from the DB
- Player logs off, store to DB

That's the 'ideal' case -- the penalty you pay for using RAM exclusively while the player is logged on, is that you have to check for failure cases (player disconnects randomly? Better save their data. Server unexpectedly quits? Better save their data. etc), and you probably will need to have a worker thread that randomly tells threads to save their data (in case someone's logged on for 36 hours straight when the network crash happens).

Granted, from your design you might want to revisit a few things as well. Security's a large issue if people are connecting directly to MySQL with read/write access, and who knows what ghastly locking will occur if your tables aren't normalized right. Best is to devise your game to be three tier -- client, server, DB backend. It's more architecting, but it'll let you max out your performance and security.

That said -- kudos. You've gotten something running, and that's the most important part. Most people forget this and get overwhelmed in the details and just give up. In my opinion, you're going at it right. Get something working, and then perfect the parts that aren't right.

( Don't ask how far behind I am on my project because of what I just mentioned the paragraph previous. ;) )

[Ethanbot]

Kissing and making up over the vulgar domain aside ( ;) ), do you get what they're trying to teach you Ethanbot?

Just curious -- I wasn't sure if it's completely clicked for you yet.

If so, disregard the below. If not:

Performance is (loosely) related to your loops. Specifically, what's going on inside of them. The more stuff you do inside of them, the slower it'll go.

Synchronous IO (shudder) over the internet (double shudder) to a MySQL database, which is probably locking the DB on read (triple shudder), depending on how you're connecting, every position update (wide eyede quadruple shudder) will definitely slow you down.

What some people have suggested here, is to essentially cache the pertinent data in RAM. This will give you the fastest access, and it's a good trick for dealing with stuff at runtime.

Normally you go about it like this:

- Player logs in
- Cache the pertinent information in RAM using variables from the DB
- Player logs off, store to DB

That's the 'ideal' case -- the penalty you pay for using RAM exclusively while the player is logged on, is that you have to check for failure cases (player disconnects randomly? Better save their data. Server unexpectedly quits? Better save their data. etc), and you probably will need to have a worker thread that randomly tells threads to save their data (in case someone's logged on for 36 hours straight when the network crash happens).

Granted, from your design you might want to revisit a few things as well. Security's a large issue if people are connecting directly to MySQL with read/write access, and who knows what ghastly locking will occur if your tables aren't normalized right. Best is to devise your game to be three tier -- client, server, DB backend. It's more architecting, but it'll let you max out your performance and security.

That said -- kudos. You've gotten something running, and that's the most important part. Most people forget this and get overwhelmed in the details and just give up. In my opinion, you're going at it right. Get something working, and then perfect the parts that aren't right.

( Don't ask how far behind I am on my project because of what I just mentioned the paragraph previous. ;) )


=) Thank you. Though I got the basic idea of what they were saying, you did sum it up very well, and you even added a few pieces of information. Thanks for your compliments as well.

[Prozac]

I wrote a instant messaging program in VB.NET once and I suggest that if you
are handling all the communication yourself rather then with say DirectPlay then
you should kinda "thread pool" by making multiple threads for that handle a certain
number of clients for example if you had 12 clients then you would have 4 threads each handling three unique players.

By doing this you'll prevent your application from lagging or being tied up by
a busy client if done properly.

There might be a better way of doing it but this was my first thought when
designing a large networking system and I wanted to prevent things from getting tied up.