A few years ago I was working for a University with some financial troubles, so instead of buying a super-expensive Student Information System we decided to build our own.
Using a mix of SugarCRM and Java Servlets we were able to ship a first fully functional version in less than a year. Students, professors, admins and everyone was happy. Especially that it came with self-service allowing students and professors to view and edit grades and absences online.
As the lead developer I became a hero. But not for long. With the first final exams period, the response time of the web app increased significantly. To the point that saving a set of grades for 30 students took about 2 minutes. All eyes were on me again, but this time to blame.
After a quick investigation I discovered that MySQL was guilty, not me, MySQL. The code and the SQL statements were sound and optimized. But MySQL was not able to handle the load. A few Googling sessions later I found myself facing the tough reality: MySQL is not designed to scale up out of the box. Configuring MySQL to scale up is a whole science which I wasn't prepared for.
The slow response time lasted for almost a year, until we got professional help to reinstall the whole server and configure it properly. The new installation improved the response time, but only until the next spike in user requests.
Lesson learned: MySQL is not free. Given the cost of hosting and maintaining a scalable instance, there are far less expensive options out there. For the last 2 years I am full steam on Firestore and didn't regret it. Do you have other, may be better, options in mind? Leave a comment.
Top comments (1)
I manage a project with a MySQL database running on a Google Cloud Platform, curiously on education market too. When we need more "power" we set more cores and RAM on the given cloud server instance and that's all... Working like a charm.
If you need your MySQL to run locally or in a VPS for example you simply can set two servers (or Virtual servers) and give more resources to DB one, i mean, that's not a cloud magic, you can set it up wherever anyways.
Obviously if you use stored procedures and/or you rely on queries to do things you could do with server side language (java in your case) it could be faster for some jobs but you'll need more DB resources -please don't use stored procedures, maintainers will hate you-.
I'm not a DBA or a servers expert, just a senior full stack developer that created loads of projects from scratch and that's my experience. Hope it helps.