It seems you’ve had quite a lot of experience with server technologies. The dual-quad core AMD Opteron CPUs were very popular server processors back in the day. I’d imagine many online games from that era, such as FFXI, WoW, EVE, and many others, likely had servers running on those CPUs. However, as you mentioned, technology has advanced significantly since then, although I find myself pretty nostalgic for those days personally.
You’re spot on about the limitations of SQLite3 and its impact on this game’s performance. SQLite3 is notorious for its single-threaded nature and is not well-suited for a game of this complexity. It struggles to handle high levels of concurrency and can become a bottleneck when processing a large number of queries simultaneously.
In hindsight, using a more robust database management system like MySQL could have potentially benefited this game in several ways. Multi threading mainly, but also, MySQL offers features like ACID compliance, transaction support, and crash recovery, for data integrity and reliability.
But of course, this all would have come with higher resource requirements, a much more difficult development curve, and licensing fees. I’m not a professional database manager or SQL expert, so it would be difficult for me to judge their decision too harshly, but I can say for certain after several months of hosting this server that it’s challenging to keep the database operational on a high population server, and the community is anything but forthcoming with information since they see us all being in competition with one another over player numbers. It’s unfortunate. We did have to wipe the database on one of our servers once back in December, but I’ve learned a few things since then, and I’m hoping to avoid having to do it in the future. Fingers crossed. Of course, the benefit of having two game servers with the server transfer mod is that people can easily protect their progress even in the event of a wipe, so it wasn’t too bad.
In reality, I probably could have run both servers on just the single machine, but I’ve decided to get two, not only because it performs a little better that way but also for future-proofing if I decide to add some more of the custom maps in the future, which I’ll probably be doing.
