Cassandra or MySQL

Initial Plan

When I started working on this application, I wanted to try a different type of database, NoSQL one in this case. I had never used Apache Cassandra before, and the idea of a highly scalable, distributed system sounded appealing. In the first version of the application, I configured everything to work with Cassandra. It ran great on my local machine, but once I deployed it to my small cloud server, I immediately ran into a serious limitation: RAM.

Cassandra turned out to be much heavier, as it’s designed for clusters and less so for small hobby servers. Even with my minimal setup, I could see that it required more than 4 GB of RAM (screenshot below) and even more to run it comfortably while doing queries and other operations. That was not ideal and forced me to switch to something else.

Switch to MySQL

MySQL turned out to be far lighter and didn’t demand nearly as many resources, needing only about 500 MB of RAM (screenshot below), which is significantly lower than Cassandra. It starts up fast, runs reliably with very little memory, and fits well for my project and for my server setup with just 1 GB of RAM.

At first, though, MySQL felt noticeably slower. My initial approach involved sending multiple queries one after another. This might not have been a problem with just a few queries, but I had around 700 airports for which I wanted to create routes. That amount of data highlighted that this approach was unsustainable. I rewrote my database logic to combine the queries into a single one, which reduced the time needed to save all 246,051 routes from about 14 minutes to under 3 minutes. It turned out that I managed to make it even quicker than standard Cassandra queries. Here are the details:

Conclusion

I managed to try the Cassandra NoSQL database and learned a lot about these types of databases and Cassandra itself. It worked great on my local machine, which was powerful enough to run it. However, the RAM requirements made it impractical for my server setup.

Switching to MySQL was a great move, as it allowed me to finish my application and run it successfully on my server. It was just as easy to run locally as Cassandra but with the added benefit of lower resource requirements.

The initial performance issues disappeared once I optimized my queries and I even saw better performance for the exact workload without needing a distributed system behind it.

In the end, the choice was simple: Cassandra is powerful and it was great to give it a try and test it, but MySQL was the right tool for my project and my server.