I'm building an online multi platform game, something like Ludo game but it takes 10 players to play about 30 to 60 minutes. Lets assume every 10 players in my Database are a row at Lobby table.
So when I have 10 rows at lobby table, it means 100 users are online.
the Back-end server is a Node.js that uses web socket protocol.
for now I'm about to release the game, But I'm trying to see some scaling of long term growing number of users.
In node.js server I have implemented an Interval every 1 second to read and change data on database.
if I use Innodb table Engine things are good until I have about 500 rows, after that the 1 second interval executes longer... and it really uses so much of my pc NVME ssd (more than 70%) and very little usage of memory or CPU.
if I use Issam table Engine things are good about 3000 rows and then it executes longer... however I have no idea in real situation with real users if it locks or not, and it also is not using so much of resources and no ssd usage at all which is odd comparing to innodb.
Now I have this questions:
1- Is there any memory or CPU limitations on Node.js that I can Increase to perform better?
2-Is there any limitations on MySQL or table engines that I can Increase to perform better?
3- I'm Using typeorm so I may use other databases other than MySQL like MariaDB or PostgreSQL ,... do you recommend them to perform better?
4- Right now I'm using only one table called lobby that has more than 30 columns and contains 6000 rows (60,000 online users) which is not performing good (More than 1 second executes), What can I do to make it better ? and it's size is only 1.5 Mb.
5- And the most important question, How do google or Instagram and big companies handle their big multi read write databases?? what am I missing? Is there some magical codes ? What I can not understand is that even my PC Cpu and memory do not engaged that much, Just like 10 to 20% of CPU and about 1Gb of ram used by the application and my whole 6000 rows is just 1.5Mb .
So far I tried increasing innodb buffer size, merging some quires together and ...