[Home] [Downloads] [Search] [Help/forum]

Gammon Software Solutions forum

See www.mushclient.com/spam for dealing with forum spam. Please read the MUSHclient FAQ!

[Folder]  Entire forum
-> [Folder]  MUDs
. -> [Folder]  MUD Design Concepts
. . -> [Subject]  Periodic querying
Home  |  Users  |  Search  |  FAQ
Username:
Register forum user name
Password:
Forgotten password?
Periodic querying

[Reply to this subject]  Reply to this subject   [New subject]  Start a new subject   [Refresh] Refresh page

Posted by Nick Cash   USA  (626 posts)  [Biography] bio
Date Sun 10 Aug 2008 09:24 PM (UTC)  [ quote  ]

Amended on Mon 11 Aug 2008 07:27 PM (UTC) by Nick Cash

Message
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).
~Nick Cash
http://www.nick-cash.com
[Go to top] top
Posted by David Haley   USA  (3,881 posts)  [Biography] bio   Moderator
Date Reply #1 on Sun 10 Aug 2008 09:45 PM (UTC)  [ quote  ]
Message
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.
David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
[Go to top] top
Posted by Nick Gammon   Australia  (18,770 posts)  [Biography] bio   Forum Administrator
Date Reply #2 on Sun 10 Aug 2008 10:05 PM (UTC)  [ quote  ]
Message
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.


- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top
Posted by Nick Cash   USA  (626 posts)  [Biography] bio
Date Reply #3 on Mon 11 Aug 2008 07:33 PM (UTC)  [ quote  ]
Message
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.
~Nick Cash
http://www.nick-cash.com
[Go to top] top
Posted by BrazilNut   (1 post)  [Biography] bio
Date Reply #4 on Tue 07 Apr 2009 03:37 AM (UTC)  [ quote  ]
Message
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.
[Go to top] top
Posted by Nick Cash   USA  (626 posts)  [Biography] bio
Date Reply #5 on Wed 08 Apr 2009 03:58 AM (UTC)  [ quote  ]
Message
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! :)
~Nick Cash
http://www.nick-cash.com
[Go to top] top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.

3,023 views.

[Reply to this subject]  Reply to this subject   [New subject]  Start a new subject   [Refresh] Refresh page

Go to topic:           Search the forum

[Go to top] top

[Home]

Written by Nick Gammon - 5K

Comments to: Gammon Software support
[RH click to get RSS URL] Forum RSS feed ( http://www.gammon.com.au/rss/forum.xml )

[Best viewed with any browser - 2K]    [Internet Contents Rating Association (ICRA) - 2K]    [Web site powered by FutureQuest.Net]