In the last few days we spent a lot of time planning the system: iterating over the database schema, how to implement the APIs, how to implement the client modules, and how all these pieces fit together. This often means that one part will influence the other, until we finally find a setting that fits together and works.
I usually enjoy this part of the project, planning involves a lot of thinking and good strategy - like solving a puzzle, but it can be very stressful sometimes. What I don't like about planning is that it takes time, and during this time, you end up floating in limbo: you can't make concrete plans because you don't know if they will hold up in the long term. The technologies we are considering now for the project are MySQL, AWS Lambda + Gateway, and AWS Elastic Search.
The capabilities of PostgreSQL that I described in the previous post seem to be supported in MySQL 5.7, which makes it a suitable candidate for a database; however, we need to make sure it is capable of enduring the traffic. For the past few days, I've tried n times and failed n-1 times (for now) to create a suitable testing scenario for MySQL. The scenario is simple: set up a VM with MySQL and bombard it with hundreds of millions of rows (with numbers and JSON data) and see what happens - if it behaves as it should, we query it until it breaks. Seems simple, but the universe was trying to thwart my plans (and succeeding) in the past few days:
1st try: The internet was strangely slow that day, when I started the download of the VM. One hour later, it finished: the download was corrupted and I had to start over.
2nd try: VM installed, but the version of the MySQL was wrong and I had to update it - obviously, I broke the installation beyond repair and I just rebuilt a new VM.
3rd try: VM installed and MySQL updated. I also made a little, neat script that inserts batches of 1,000 random registers in the database and let it run for a while. The result: 55,000,000 rows inserted. "Great! Now I can start testing" - I messaged myself mentally. After some informal tests, it was time to go home and I had to insert more registers; we decided to let the script run overnight, but first, "does it stop when we lock the computer?" - we thought, and decided to try. Any sensible person would backup the database before doing this, but 55 million rows really takes a while to download; besides, we are beyond sensible, so we locked it anyway: that's how we corrupted the third VM.
4th try: We quickly set-up the database again (just made a new table) and left the scrip running overnight. During the commute, we were betting the results: I bet 80% on the VM being corrupted, 15% the script broke somehow, 10% someone turned the computer off, and 5% it worked - the fact that it sums up to 110 does not matter, what matters is the general idea. The database was corrupted.
5th try: New VM made, we left the script running for a few hours (reaching around 80 million rows) until the VM completely ran out of space; with a few adjustments, we increased the space and let it run a little more. Tomorrow we will run it again to insert more registers.
So that was the MySQL saga until now. The other limbo that we are floating in is the technology regarding the API: the client suggested that we used AWS Lambda + Gateway, and maybe AWS Elastic Search. These services are really nice (I will probably post about them, if we get to learn more about it), but Lambda + Gateway seem to be a very simplified "framework" for an API - I am afraid that in the future we will have to modify it to be more robust and it will just not work. Although I would like to use them, I fear that the bureaucracy of AWS and its not-intuitiveness will hurt us more than help.