NetGore

You could serialise all the data into the database.

For items, 2 ways i think. Serialise or have another table and foreign key the items to the player.

I don't think that the server would want to be connected to the db for 600 players. That would be a schoolboy error and result in a noticeable perf decrease especially with around 600 players (if i've misinterpreted my bad). I'd say it was bandwidth and or amount of storage on their Server. if you've got information of 600 players to hold and you're running a server in your basement you're probably not going to have enough memory or performance. Flat files are good for maps. You could do most other things in the database. Take a look at the NetGore database, it might give you some ideas, especially with foriegn keys.

They have a premium server and unlimited bandwith on a very high speed connection. So that means it was as simple as I thought, how strange they haven't fixed it! I know I won't be going over there to give them that piece of information though, as when I posted help for their other issues they were a lot less than pleasant, I'll be sure to give them a link to my website when my system is up and running properly . There server messes up like nobodies business with the active user count reaching above 500.

Would it be reasonable to presume that if I purchase a 50mbit home broadband connection that I will be able to at least facilitate around 300-400 users from a spare computer? I'm trying to minimise data transfer as much as possible, and with turn based combat a lot of stuff is stationary. The latest maps are included with the client, and are only downloaded to the client if the client tries to access an updated map or a map it doesn't have yet, so as to not keep loading the map from the server (funnily enough, like the game im talking about does, they make a lot of mistakes imo). I mean the only thing sent around between clients is movement which is only the new X and Y coord, one byte each, and speach. Battling will not be a bandwidth issue, nor will trading and NPCs for the moment are stationary, used for information and quests and their positions are sent on the map load event.

Wouldn't using a database table for items totally ruin performance though? I mean I have no idea whether the project will be a success and I'm low enough on funding as it is, so the server will be run from home a long while on not the greatest of machines. I want to minimise the server load while making sure it doesn't lose any functionality it should have.

Thanks for the replies, I was thinking I was crazy for thinking they were being so stupid and not seeing it, theres definitely a large smile across my face now.

Yeah MySQL doesn't allow for a lot of concurrency, MyISAM does table level locking and I think InnoDB wouldn't be much better, the good thing though is that it is much easier to use. You should see how much extra crap is involved when programming Oracle when its optimised for max concurrency. I am surprised they even reached 600.

Well anyway that's why I use smaller versions of Blobs, basically one for each account. Then its very similar to having flat files, there is usually never a time you need to access an individual item in the inventory from the database, instead it would be much faster to load everything first and retrieve from memory (all 16GBs of it ). At least that's what I think, I've never looked into the performance difference of loading blobs to loading every item via a query.

If I was just caching members on login and loading everything about each user then, and only saving on log out, from what I can visualize in my head, wouldn't this mean that the amount of concurrent users would be extremely low? The server model is actually single threaded at the second too so i dont think the code could even execute more than a few connections at once could it?

I don't think loading everything to memory would be the best solution in my case, I am creating my framework under a well established theme for testing purposes - basically because I know it will draw in huge amount of players once the ball gets rolling, I want to be able to sustain as many active users as possible, and re-writing all data to the database every 5 minutes instead of say the 500 players online would save a huge amount of work on the servers behalf. I think I most likely will have a few web related systems as well for things like high scores so there will be even more strain on the database. Actually on that point - would it be better if I created a second database for this that updated itself at set intervals from the main database to reduce strain on the first?

Quote from this source :http://wow.stratics.com/content/features/editorials/mech/

obviously not only the database and file accessing is going to be a performance drain but perhaps a way in which the clients are sent packets.

I recommend reading this article http://www.csie.isu.edu.tw/homepage/ccchen/poster.pdf It makes you think about client/server in a different way. I might even start looking into an implementation of this for NetGore for my own projects.

The second link didn't seem to match what I was going for, with it being abount multiple servers running a single game world, I'm pretty certain that a pretty basic system will be able to run my server application. The first was interesting though, but I actually wrote all of this stuff in to the code a little while back. When anything is needed to be sent to a player the server adds it to the users send buffer, and at the end of the loop if that connection isn't already sending data then the new data starts being sent. The only exception to this is the server allowing a player to move to a certain tile, which is sent back as soon as it's processed because otherwise you appear to lag.

I'm not using the very best packet formation at the moment, but as I'm at such an early point in developement I've decided to leave them be for now as they are extremely easily read by me when monitoring input and output and won't be testing it with the outside public for at least 3 months I expect.

Are there any other ways not seen in this topic, that a database can hold up your system? I want to be as fully prepared with ideas as possible, before I start to write the code.

There only needs to be 1 database and 1 server to connect to it.

When a player logs in, load the player's data into server memory from the database.

When a player does anything, save the data to server memory.

When a player disconnects, save the data from server memory to the database.

It's that simple. Besides connection speed/bandwidth, hardware scaling will be the least of your worries, don't over optimise and make a mess of your code, stick with the perfect technology assumption until BETA.

The second database im refering to, would only be used for things like player high scores. If the website ends up becoming very popular - which is always the dream right - wouldnt a whole lot of extra queries hitting the main database slow down the server loop itself? if a lot of players where logging in and out at the same time too obviously.

Also, from a lot of the source code and articles I have been reading, people seem to open and close the connection to the database when they retrieve or save something. If the database is on the same system as the server application - is it reasonable to keep the connection alive the whole time the server loop is running? It seems logical to do it like this so there is no holdup, but none of what I've read does this.

You would have a game-server and web-server connect to a single database.

The game-server maintains a single connection to the database for the lifetime of the server. The webhost however will open and close connections for each connection instance (each user that connects to the website basically), this is unless you are running a single instance enterprise application e.g, using Enterprise Java (not recommended).

It won't slow down the "server loop" if you are doing stuff asynchronously.

Again don't pre-optimise, you can scale your hardware or create a distributed database/server system later on IF it gets popular, and I'm talking tens of thousands of connections to the website at once. Otherwise just upgrade your webhost. If you are going to have lots of connections to view highscores and whatnot, then just run the database on the webhost and have your game server connect to it. Most people are used to waiting a couple of seconds for login so it should be fine.

Thanks a lot for the help guys, this was very much the sanity check I needed, and the little bits of extra information will help too too.

I can't exactly remember the conclusion I came up with for NetGore, even though I did quite a lot of research on this topic. If I remember correctly, a good driver will pool the connections, so re-establishing the connection (namely locally, remotely will always be a bit more expensive) will be quite cheap when compared to the cost of the query itself. But, if you are just using the database locally and from that one connection, I can't see much harm in leaving the connection open.

My advice would be to design it so that, if the connection is open, it remains open. If it is closed, then it is opened. This way, all it would take is to remove the line to close the connection to leave it open. Makes adding a more complex system later much easier.

Just to confirm this, I made a little test:

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
 
namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            var sb = new MySqlConnectionStringBuilder();
            sb.UserID = "root";
            sb.Password = "";
            sb.Server = "localhost";
            sb.Database = "demogame";
 
            Stopwatch w = new Stopwatch();
 
            using (var conn = new MySqlConnection(sb.ToString()))
            {
                w.Start();
 
                for (int i = 0; i < 1000; i++)
                {
                    conn.Open();
 
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "SELECT 1+" + i;
                        using (var r = cmd.ExecuteReader())
                        {
                            r.Read();
                            var v = r.GetInt32(0);
                            if (v != (i + 1))
                                throw new Exception();
                        }
                    }
 
                    conn.Close();
                }
 
                w.Stop();
            }
 
            Console.WriteLine(w.ElapsedMilliseconds);
            Console.Read();
        }
    }
}

Having the Open/Close() happen every iteration was about 40% slower. Not really much to worry over, seeing as we're still talking about 0.2ms tacked onto each query.

What you do NOT want to do is be recreating these objects whenever you can avoid it. Pool whatever you can.

In summary: design your system to support re-opening the connections if it needs to. Initially, just close it whenever the query executes. If it shows as a bottleneck later, change it to never close will only involve removing the single .Close() call.

Just to add to this idea too, as I'm not currently using multi threading I decided to create a seperate piece of software for database access. Still both running on the same machine, but it means any stuff taxing on the database won't slow any in game stuff, the game server just requests player accounts etc from the program rather than the database itself now, and the database program just holds a queue of tasks to be returned. This means regular state backups can still happen pretty frequently while a lot of players are on too.

I would like some comments for this idea, just to know I'm not going about this wrong

but a mySQL database is independant of the game anyway? Generally there won't be a lot of tax on the database anyway. not that would slow down the game anyway.

As the game server is single core and the way it accesses the database means that the program will not continue until the record has been retrieved/saved/whatever. I think that in a system where you start to have a few hundred players playing with many DB accesses per few seconds I think that this could start to hinder performance in a big way. What I mean for the new way of going about things, is simply for the game server to push an item on to a queue on the database handler and then will be passed back when it's ready. means 0 hang possible on game server even with 1000 or 2000 players playing at once, as I think the amount of reads/writes per second would start to add up in terms of taxing gameplay performance. You may say aiming so high is unreasonable, but I would rather push things to the best they can be with what I have at my disposal, I do plan to have a success on my hands

So what your suggesting is a system where the server queues Database requests and only processes them when it has the time?

(kind of like the DoEvents in VB6 where you gave time for the Windows messages to process)

I still don't think you are going to notice a performance increase, because you are still going to need to find the time to process the db requests and you'll need to force the db stuff to process when you REALLY need it?

i may have completely misunderstood you but that's how i see it. I'd recommend using a System.Diagnostics.Stopwatch to time how long it takes you to retrieve say 2000 db requests. I'm not at my usual computer or i'd do it. but i don't think it would be too high. (It does depend on your processor speed) but if MySQL is on a different core then trying to save processor time for the core on the server wouldn't be worth it (obviously).

:S
I'm confused

But the two pieces of software are separate and so have no baring on each other as are in different threads. I'm also using one of the oldest remaining computers on the planet which has a baring on me choosing this method too I think, but will help overall performance I'm sure.

So basically, one programs only function is to hold database requests, process them and if required send the results back to the game server. Keeping the game server application as a high priority process and leaving the database one as normal. This is just a cheap method for making the database access multi-threaded due to the old school coding environment I'm using.

If you really want your database operations to now bog down the server, you should be doing it asynchronously. Send the query and, if it requires reading back values, allow it to interrupt when it is done instead of blocking at the calling method. That way, how long it actually takes doesn't really matter. Of course, design complexity can increase dramatically.

You see the main issue with this, is that I am currently building the prototype client and server in VB6, because I still have a LOT of concepts to teach myself and find my own way with, I find it much more constructive to learn such things in a simple to use language that I'm not spending all my time typing instead of thinking kinda thing? I haven't been able to find a method for doing this within VB6, possibly you know of a way spodi as you are of a higher level in it than I?

I presumed that what I was doing was just a vb type quick fix of the situation, it would be done asynchronously when I bring the engine forward to C#, but I am still a good few months away from this stage and have quite a bit to learn 1st.

Databases (managed) >>>>>>>>>> 999999999999999x flat files

more than that, i came to a conclusion with out really being able to explain it (though didn't really do any research for it) that Microsoft Database management solutions such as MSSQL, even though being much more heavier than MySQL, perform differently on Windows Server editions and especially those editions from 2008 and after. In fact there's no need to explain it.. Microsoft TACTICS!

I don't understand how your post is relevant to the conversation at hand?

The first line yes. But not the rest :/