1) You do not want to use a database server as a game server. Packing all the services on a single machine is okay for development and testing environments, but any proper deployment needs to factor database services into different hosts from game services.

2) You do not want to use a database as your main IPC or state distribution mechanism. It should not be used to communicate player movement updates, or player chat, or anything like that. Databases are used when you absolutely need transactional integrity and durability. What are you doing 3 times a second to 4,000 users that needs durability and transactional integrity?

Well I'm using the database for everything which I need present after restarting the server. That includes player specific data(level, experience, current hp. etc.), item specific data(socket history, durability, etc.) and other similar topics. There're certain items which are used upon using a skill and I update item stacks on database as soon as they're used in-game so that's where the 3 queries per second idea came from. I update things like current exp, hp, position, quickbelt, etc. on certain intervals or when the player disconnects.

As I said I was using Oracle previously but was only using very simple features of it. Was hoping to find a more lightweight solution with similar performance/scalability. Will try the struct approach(could build a separate server for this to solve the #1 issue hplus presented but I'm not sure if it's needed in my case). Thanks for the answers.

So, you can use a database that gives you 10,000 durable writes per second. I would suggest Redis, or perhaps MongoDB (which I'm not at all a fan of) hosted on a server with an SSD RAID and plenty of RAM.
The NoSQL approach to storage may be able to support your high-write-rate use case better than traditional relational databases.
There are also some in-RAM SQL relational databases coming out that you could look into.

It may be possible to achieve those numbers with a single instance of MySQL or Postgres on a single server, but that would be hard and require very highly tuned queries and tables.
(For example Postgres has significant write amplification around updates to rows that have secondary indices.)
You will at some point need to horizontally shard your database across multiple hosts, and that will reduce your ability to do consistent transactions between users. (Commit both user A and user B in the same transaction.)
The main issue is one of cost. Building a game like you suggest will not work, economically, at scale. Unless the game deals with big money -- like real casino games, or whatever.

For games, I would assume that the cost isn't worth it. The simplest trade-off, which most games take, is to keep all game state in RAM in a game/app server, and only commit back to a database every so often (every 10 minutes, or when something really important changes.)
If the server crashes, players' state rolls back to whatever was last checkpointed. This should happen very seldom, and assuming important things affecting the economy, like trade between characters, is committed immediately, you're good.

An improvement on that, if you really want durable last-available-state, is to stream user state to a durable queuing system.
The queuing system could be configured to "last received state" for each topic (where a topic is a player,) and you'd have something that checkpoints the player from the queue into the database every so often.
Because message queues can run in durable mode, and generally have better throughput than relational databases, this might be cheaper to operate.

For games, I would assume that the cost isn't worth it. The simplest trade-off, which most games take, is to keep all game state in RAM in a game/app server, and only commit back to a database every so often (every 10 minutes, or when something really important changes.)
If the server crashes, players' state rolls back to whatever was last checkpointed. This should happen very seldom, and assuming important things affecting the economy, like trade between characters, is committed immediately, you're good.

In this case(where I use simple structs and dump them into a file instead of using an actual database), would I need to query the required information in login server from game server(account credentials, certain parts of player data, etc.) using sockets?

Additionally, if I were to use a database like SQLite instead of structs, how would I know what to commit on certain intervals from game server to database? Do I delete the old data and write a new one with whatever is available on RAM?

1) You do not want to use a database server as a game server. Packing all the services on a single machine is okay for development and testing environments, but any proper deployment needs to factor database services into different hosts from game services.

2) You do not want to use a database as your main IPC or state distribution mechanism. It should not be used to communicate player movement updates, or player chat, or anything like that. Databases are used when you absolutely need transactional integrity and durability. What are you doing 3 times a second to 4,000 users that needs durability and transactional integrity?

Well I'm using the database for everything which I need present after restarting the server. That includes player specific data(level, experience, current hp. etc.), item specific data(socket history, durability, etc.) and other similar topics. There're certain items which are used upon using a skill and I update item stacks on database as soon as they're used in-game so that's where the 3 queries per second idea came from. I update things like current exp, hp, position, quickbelt, etc. on certain intervals or when the player disconnects.

As I said I was using Oracle previously but was only using very simple features of it. Was hoping to find a more lightweight solution with similar performance/scalability. Will try the struct approach(could build a separate server for this to solve the #1 issue hplus presented but I'm not sure if it's needed in my case). Thanks for the answers.

I get that you need to save, load, and backup data, and that you need to quickly access player data while the server is running.
But why do you need a *database* as opposed to just saving, loading, and backing up data in the regular way?

Why can't you just do the plain and simple way:

struct MyData
{
   //--------------------------------------
   //Static data that doesn't change (apart from a developer creating new monsters/items/areas/etc...).
   //--------------------------------------

   std::vector<MonsterTemplates> monsterTemplates;
   std::vector<ItemTemplates> itemTemplates;
   std::vector<DungeonTemplate> dungeonTemplates;

   std::vector<NPC> npcs;

   //--------------------------------------
   //Dynamic data that needs preservation and changes while the server is running.
   //--------------------------------------
   std::vector<PlayerInfo> playerInfo;
   std::vector<PlayerStats> playerStats;
   std::vector<PlayerInventoryData> playerInventories;

   std::vector<ItemInstance> ownedItems;
   
   //--------------------------------------
   //Temporary data that can be discarded when the server shuts down.
   //--------------------------------------

   std::vector<ItemInstance> itemsInWorld;
   std::vector<MonsterInstance> spawnedMonsters;
   std::vector<DungeonInstance> dungeonInstances;
};

As Hodgman pointed out, you're talking about a few MBs of data - i.e. the equivalent of saving and loading a few large images.

For things you want instantly written to file, you can basically push changes (byteOffset, sizeInBytes) to a second thread that can save instantly to (a copy of) the file, or even use some appending scheme to append (byteOffset, sizeInBytes, data) to the end of a separate file, and only rewrite the file as a whole once every minute or so.

Or I guess what I'm asking is, what additional/separate benefits are you wanting to gain from a database that you don't already have when you save and load "the regular way"?

Thare are lots of ways to skin that cat.

If you store each player's data in a file named after the player, then the best way to update is to re-write all the data you have about the player to a temp file, then move the player to a backup file, then move the temp file to the player file.
This is known as a "safe save" and avoids partially-overwritten player files when the server crashes. (flush/sync the file system to commit, though!)

On UNIX, you'd do this:

  char fn_new[100];
  sprintf(fn_new, "%s.new", playername);
  char fn_target[100];
  sprintf(fn_target, "%s.save", playername);
  char fn_old[100];
  sprintf(fn_old, "%s.old", playername);
  int fd = open(fn, O_RDWR | O_CREAT | O_TRUNC, 0644);
  write_data_to(fd);
  fdatasync(fd);
  close(fd);
  link(fn_target, fn_old); // ignore errors, if this is the first save
  rename(fn_new, fn_target); // check errors here
  sync();

Windows is slightly different as it doesn't have the same kind of hard links and doesn't allow rename-with-replace, but same idea.

Depending on how long and how tricky user names you allow, you of course want to quote the username before turning it into a filename :-)
(a username like "../../../../../../../../etc/passwd" would be pretty popular otherwise!)

SQLite is fine for storing local data on a local machine, single-player games, and such. It collapses under large parallel load, and may even corrupt the database on disk when doing so.

Or I guess what I'm asking is, what additional/separate benefits are you wanting to gain from a database that you don't already have when you save and load "the regular way"?

Nothing, really. I didn't know dumping the memory onto a file was a valid option so I had the wrong assumption of needing a database from the start. Also thanks for the example.

Thare are lots of ways to skin that cat.

If you store each player's data in a file named after the player, then the best way to update is to re-write all the data you have about the player to a temp file, then move the player to a backup file, then move the temp file to the player file.
This is known as a "safe save" and avoids partially-overwritten player files when the server crashes. (flush/sync the file system to commit, though!)

On UNIX, you'd do this:

  char fn_new[100];
  sprintf(fn_new, "%s.new", playername);
  char fn_target[100];
  sprintf(fn_target, "%s.save", playername);
  char fn_old[100];
  sprintf(fn_old, "%s.old", playername);
  int fd = open(fn, O_RDWR | O_CREAT | O_TRUNC, 0644);
  write_data_to(fd);
  fdatasync(fd);
  close(fd);
  link(fn_target, fn_old); // ignore errors, if this is the first save
  rename(fn_new, fn_target); // check errors here
  sync();

Windows is slightly different as it doesn't have the same kind of hard links and doesn't allow rename-with-replace, but same idea.

Depending on how long and how tricky user names you allow, you of course want to quote the username before turning it into a filename :-)
(a username like "../../../../../../../../etc/passwd" would be pretty popular otherwise!)

SQLite is fine for storing local data on a local machine, single-player games, and such. It collapses under large parallel load, and may even corrupt the database on disk when doing so.

Thanks for the answer. I'll try to use that method for player data and other similar data which are unlikely to have too many records.

Well I'm using the database for everything which I need present after restarting the server. That includes player specific data(level, experience, current hp. etc.), item specific data(socket history, durability, etc.) and other similar topics. There're certain items which are used upon using a skill and I update item stacks on database as soon as they're used in-game so that's where the 3 queries per second idea came from. I update things like current exp, hp, position, quickbelt, etc. on certain intervals or when the player disconnects.

As mentioned above, traditionally most real-time online games keep their data in-memory and use a relational DB loosely as a write-back cache, saving to the database only when necessary and reading from the database only at startup or when a new player connects. Exceptions often exist for critical transactions (e.g. anything involving money). You might be surprised at how little data is persisted in most online games. The state of NPCs and items are routinely forgotten over server reboots, by design.

I have spoken to people at companies that believed they needed to keep every single thing in the DB, and ended up writing their own database to accommodate the low-latency demands of games, but I don't feel they made a compelling case for why they needed to do this.

Obviously, if you feel that a relational DB is a convenient way for you to load in your game data when the server starts up and for you to be able to edit that data with robust tools, that is a reasonable approach. Personally I prefer other formats for this, and if I do want it in a DB, an object store is usually more convenient. (e.g. MongoDB or Postgresql with a JSONB column).

Postgresql is the best free all-rounder. MySQL is also okay.

Is this funded on concrete experience or even hard evidence? I'm asking because I hear "postgres is better" a lot, but the only real downside in MySQL that I see is that it's not 100% compliant with every little bit of SQL pedantery.

I've used MySQL for decades, and it has never bitten me. Not in an unexpected way anyway... sure, if you use a non-ACID engine, you may lose some... happened maybe 3 or 4 times in my life. But this is something you know in advance. I yet have to see a failure with Inno.

On the other hand, even getting something up and running is (in my opinion) a pain with Postgres, and when I tried, it felt like quite a bit sliggish compared to what I was used to. But maybe that was just because I know nothing of Postgres and did something wrong...

About the actual question, does that huge amount of data have to be saved so often anyway? Items change slots maybe a few dozen times during an entire game session, and they change owner only rarely. Why not write trades to a local journal just to be sure you can recover, and coalesce all item updates once per minute (or less often, even!) to the database.

Similarly, all player stats can easily be held in RAM only, important things logged to a journal just in case, and regularly flushed. If your server crashes so often that this may be an issue, you habe a different problem to look after! And otherwise, if this happens maybe once every other month, no user will be angry if they get restored to full health and full mana upon a crash... as long as they don't lose the Sword of Almighty that they just won a minute earlier (...which the journal prevents from happening to the best of your ability).

I've used Postgres, MySQL, MongoDB, and Sqlite in production. (Had some brief experience with Oracle but that wasn't my code.) Older versions of MySQL had problems which have been resolved in recent years, (including defaulting to the unsafe storage engine if I remember correctly), but Postgres has also caught up when it comes to features (such as finally implementing upsert which MySQL has had for as long as I can remember). There are plenty of comparisons of the 2 DBs and most people who are better informed than me seem to say Postgres is the better choice - the OP can Google for these results if they like. :) Either option will suit their use case, whatever it actually turns out to be.

Also, you should use MySQL with InnoDB. All the other storage engines are less reliable, and MyISAM is down-right dangerous.
(Also, InnoDB is faster than Memory engine :-)

MySQL is super great at online read/write use cases, and as long as you can keep your load to a single machine (and then horizontally shard to scale) picking MySQL (or MariaDB, or PerconaDB, or whatever for you want) is fine.

MySQL is not so good with large, complex queries -- an analytics database might be better as something else.
MySQL is not so good with online schema changes. We have a table of >150 million rows, which we cannot change, because all of the "online change" tools end up either failing, or live-locking and never finishing.
MySQL is not so good with advanced relational algebra -- there's really no such thing as auto-materialized views, advanced triggers with optimization, etc.

PostgreSQL is in many ways the dual of MySQL. It's amazingly strong on almost everything MySQL isn't. However, it is lower performance for heavy online load with simple read/write traffic.

I'd rather look at Microsoft IIS than at Oracle, and I'd rather look at iBM DB/2 than IIS, if I had to look at enterprise SQL databases. But I'd probably rather look at Amazon SimpleDB and Google BigTable before I went that route, anyway -- if you really need scalability, those approaches are proven to scale much better.