23 minutes ago, aikaproject said:

the problem is that I think I'm running many queries to the database. ex: 

The problem is that you are not measuring and using empirical evidence to pinpoint the root cause of your situation ;-)

Most MMO architectures separate database operations from game operations because it makes it easier to think of the database as an expensive resource. You "can" block on transactions but you should design your architecture such that your default assumption is that hitting the DB is slow and error-prone. This makes it easier to react appropriately when errors or timeouts occur.

Part of designing a robust system is knowing its failure modes. This means understanding why a lot of DB queries lead to issues in your game, for example. Then you can determine the correct response. Sometimes you need to optimize DB procedures, or add indexes, or build caching on top of the DB layer, or just lower your expectations :-)

Your approach sounds sensible (only load the data that's needed), but I would agree with ApochPiQ, why do you think your database is getting too many queries? Modern RDBMS can handle a LOT of concurrency (especially reads), so I would not assume you are doing too many unless you profile and find DB queries are taking a long time. (You can of course usually run queries on the DB itself to see what the load stats look like).

How many concurrent players do you have?

You could take a look at my latest blog post where I'm trying to describe how I've been separating DB from simulation and networking. I haven't done any work on performance whatsoever - I just write accounts and players to JSON files - and it's already possible to update persist more than 1000 players/second. With a database I'm sure you could do it much faster (at least as long as you don't normalize more than needed).

But my main goal wasn't even performance as such - my main goal was to never let a networking or game simulation thread wait for DB operations. There are many ways to do this - continuation lambdas, state machines, async/await etc.

In particular I recommend reading the 2 articles on ithare.com that I'm linking to. They might be a bit long-winded but really worth a read. Actually I recommend reading a lot of the articles on that site - the author is writing a book series on MMO development.

Databases aren't necessarily faster than file systems. Instead, they provide features file systems don't, like block coalescing for small data items, and multiple indexing, and transparent management of millions of items, and transactional semantics.

Try creating a directory with a million files in it. Most file systems will choke and slow down tremendously.

Also, to actually get transactional update of a file, you need to:

1) write to a temp file

2) call fsync() and fdatasync() and close()

3) call rename() to replace the old file with the temp file

4) call sync()

And sync() is asynchronous, so you don't REALLY know that the rename was actually persistently committed. But at least you'll be in one of two states: old file, or new file, without any half-files.

(This is on UNIX -- Windows has similar system calls available)

True if I work on performance I can probably make things work faster with a DIY database, but with a DB I'm sure I can make things faster than what I have now (160000 individual files in a single folder)