Member Discussions

I've been working on a tinker codebase to learn Django (the Python web application framework) better and have been trying to find the right balance between speed and ease of development. Using Django's database/model API, it's just dead simple to manage queries and relations, but the single-threaded nature of my project may suffer from high volume if the playerbase ever got up there, or the server experienced some load on top of a mid-large sized playerbase.

So I have essentially moved all of the simple but frequently accessed things to memory, such as objects/rooms/exits, and kept some of the more complex queries to SQL so that the RDBMs can take on the load. For example, am I right in thinking that querying for a room's name, description, contents, and exits every time a player 'looks' is a bad idea when we can just stuff all of the objects into a data structure and hit off of that directly?

Django transparently supports SQLite, MySQL, and Postgres. Given these three RDBMs, what kind of volume in terms of queries a second (assuming the MUD was the only thing running on the server) would it take before the game starts locking up waiting for queries to return from the SQL servers on middle of the line hardware? Am I doing the right thing by pushing some of the frequently queried to prevent locking? I'd love to rely more heavily on the RDBMs, but I cringe at the thought of script kiddies logging in with bots and trying to lock things up. I realize it's entirely possible to add command limiters and the such, but I'd like to assume worse possible case for the sake of being prepared.

I can't comment on all of your project... however, querying every time something is accessed is unnecessary, and saving everything in memory for too long is too memory-intensive. As a result, my advice would be that you load every structure accessed into memory with some kind of a soft reference (or a similar implementation). In other words, every time something is queried, load it into memory temporarily. After a certain amount of time, then remove the reference and clean up. When a request for the object is made, check if it exists in memory, and if not load it into memory from the DB.

As for the threading and load handling, if at all possible I would suggest you create an implementation that supports multithreading (Is it that Django doesn't support multithreading? If so, then just ignore that). In any case though, limiting commands from the user would be a reasonable way to solve the problem, and would be necessary if the amount of resources you have is limited (regardless of what DB you use).

Best of Luck,
-Jess

For example, am I right in thinking that querying...
every time a player 'looks' is a bad idea when we can just stuff all of the objects into a data structure and hit off of that directly?

Depends on how the database caches the query.

what kind of volume in terms of queries a second ...would it take before the game starts locking up waiting for queries to return from the SQL servers on middle of the line hardware?

That's something nobody can tell you as it's dependent on the database software, the Dwango middleware, the structure of your tables, the construction of your queries, and the hardware. It's something you'll have to measure for yourself.

Am I doing the right thing by pushing some of the frequently queried to prevent locking?

Probably not. Read up on how and when databases do locking.

In response to the multi-threading question, I'd be hesitant to complicate this rather simple experiment with Python threading. I'd like to eventually learn enough to use them effectively, but that would represent more time than I want to invest while learning the language initially.

Django does do some simple query caching that is somewhat configurable, but not for reasonably complex queries. I know MySQL and Postgres have some as well, but am not sure about SQLite.

Tyche, I don't understand your last comment. It's obviously unnecessary to query for every single little thing, but it's also ineffective to load some of the frequently accessed things into memory? For a single-threaded application, the locking would be caused by waiting for a query to return in order to continue program logic. Hitting from memory would probably hang a lot less than betting on a quick response from your SQL server. Unless I don't understand it right.

I've got somewhat of a soft loading system setup for my objects, but I'm definitely open to ideas. I should probably take a peek at how some other codebases are doing this. It seems like most of the Python ones that are in runnable shape either go to one extreme or the other (entirely SQL or entirely flatfile/memory based).

That's called blocking. You aren't going block on I/O if the RDMS has the data cached. If the RDBMS caches it then it's in memory and there's no I/O blocking. Relational databases will lock either at the record, page, table, or database level depending on the database architecture and the type of query. The types of queries I got from your post would be read with no intent to update, thus there would be no locking. OTOH I suspect you will be updating objects that move around. Multithreading those only complicates the task with very little gain. Do a search either here or on the web on "Dragon's Dinner".

All three of the RDBMS you mention have user configurable cacheing. Most RDBMS's cache algoritms are designed to cache both sequential access and single-item indexed lookup patterns.

I wouldn't spend time pre-optimizing or second guessing performance by developing your own cacheing or multithreading before even contructing, testing and measuring your own application first. Far too many have gotten clever and ignore the thousands of hours of optimization already done by rdbms's and have ended up unoptimizing themselves.

There are a few tips though.
1) If you can prepare your queries, do it only once, and execute them multiple times. That is, avoid dynamic SQL. Use bound SQL if that feature is available.
2) Run explain on your queries to check that they take advantage of indexes. Conversely don't create unnecesary indexes...if none of your queries use an index then it's a waste.
3) Identify small lookup tables and tell your rdbms to cache the entire table in memory.

Ok, that's all pretty reassuring to hear. I wanted to keep this as simple as possible, didn't really want to get into threading for something that's such a tinker project.

But for the frequently queried stuff (like when players hit 'look') and I need room descriptions, names, contents, etc., should I query directly from the RDBMS and hope the cache will carry a lot of the reasonably static data (The room names and descriptions should be more or less constant, but the contents won't), or should I load the room descriptions, names, and object locations into memory and hit it there?

I imagine it doesn't make a huge difference for something that'll never see huge audiences, but I'd like the ability to handle at least a few hundred concurrent players theoretically. I see that there are varying degrees to which you can have your SQL server cache queries, and each kind seems to offer something unique.

It seems like querying for room descriptions/names/exits directly from SQL would make the code simpler, but I'm still not entirely sure as to any potential performance consequences. For the room name/desc, that's one query, the contents would be some kind of join, which leaves us with approximately two queries per 'look', which I am thinking is probably the most commonly spammed command on most games.

Perhaps a good tradeoff would be to set the table type to something memory based as you mentioned and not load it into memory from within the game itself?