Periodic querying

I have a few design questions that I've been thinking about for a while now. With the code base I've been programming, everything is completely query driven and it is working out well. I have set it up so some things that are frequently referenced but don't change (like a character's name) are stored in string-to-string map for the character (like a cache). An idea I've been playing with is having the character know what new data it needs and gather that data periodically and stick it in this cache instead of executing queries immediately and often. Of course this incurs the classic problems of maintaining data in two locations, so I'm not entirely sure if I should be using this cache for anything other then relatively static data that won't change much (and when it is changed, its updated in both the cache and database).

The major thing that is bothering me is that all SQL queries are executed immediately. If there were significant load on the server it seems like it would be easy to red line the database. Now, I haven't crunched the numbers to figure out how much it would take to really give PostgreSQL or MySQL a workout, but I was wondering if it might be more prudent of me to make a query scheduling system that puts the queries into a queue to be executed. There would be some significant changes to the way commands are executed as a result, but thats not a big deal at this point. The idea would be to limit the number of queries in a given cycle if there were a large amount of queries, thus alleviating some stress on the database management system.

So what do you think? Should I be concerned with the server immediately executing queries? Or are the databases advanced enough that it probably won't matter unless I have an ungodly amount of activity on the server?

I really do not want to have to re-engineer anything relating to the database and how the interface operates in the later stages, so right now the query scheduler seems like a good idea.

I should note that I'm working my best to make sure NPCs and other things don't need to query the system too much, so hopefully when few players are around the database activity will be fairly low (or completely non-existent).

I've not played much with this kind of work, but given the nature of the problem and the many variables involved I would just benchmark what you have now and see if you need to be worried. It might turn out that your current implementation is just fine. If it's not, then you can worry about how to improve it, by identifying bottlenecks in the interaction with the database.

If the database server is on the same PC as the game server, it will probably be quite fast. Otherwise, network latency might get you eventually.

It isn't that dissimilar a problem to standard SMAUG, where the player's data is stored in player files, which are periodically saved, but cached in memory for normal gameplay.

I don't know how easy it is to change, but one approach would be to "write through" but cache reads.

So unless you have complex queries it could go like this:

• I need the player's max mana - check cache
  
• If not in cache, get from SQL read (and put in cache)
  
• From then on, it is in cache
  
• If I need to change the max mana, update the cache and also do a SQL update to update the database as well.

That way, the database is always up-to-date. Of course, this assumes you have a single server process, if you have multiple ones all looking at the database this won't work.

That sounds like a very workable solution. Looking at the codebase now I'm not entirely sure it would be too difficult to add a query scheduling system if it was needed because of the way the interface is setup.

So, for now, I guess I will leave it be and monitor how active it is. The database is hosted on the same system so network latency shouldn't be an issue.

AddictMUD was based on CircleMUD about 14 years ago, but in that time I've moved so many things to MySQL (very little is stored in a file anymore).

The biggest thing I worried about was the game lag by putting too much in the database, but it turned out to be much faster than I anticipated. I don't really cache anything, and do a lot of "on the fly" reading of things from the database like skill knowledge, and clan association and rank.

The potential for 100's of queries, with some updates, done per second, is huge. Never once has there been game lag as a result. But I've also been very careful to test all queries, make sure they return small data sets (fewer rows the better), and I always keep indexes in mind.

The moral being, the database can handle a LOT more than you think, as long as you keep your queries optimized and your tables properly indexed.

Dan.

I'm aware they can handle a lot, I've worked with more complex systems. I'm just worried about optimal efficiency, though as it was pointed out I don't really need to be. I too have been testing my queries to return the smallest most exact amount of data possible (less coding that way :P), and correctly setting up tables and indexes certainly goes a long way.

Thank you for your input on the matter! :)