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.

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"?

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.

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.