MySQL Remote DBA Consultant helped the client to scale up

Creating a SaaS application with mySQL database:

We got an urgent call from a new client that has many clients of its own. They do have a SaaS application with more than 1000 clients. Our consulting firm provides MySQL Remote DBA and MS SQL Consulting services. They needed help with MySQL database right away. We started to engage with them the same day they called us.

Imagine the client has SaaS app with more than 1000 clients of his own. For every client there is one dedicated database. Obviously, the client created a nightmare maintenance for himself. Just to spice up the situation even further, every database seems different then the other one. Indexes are different and some columns in certain tables are different as well. A real mess!    

Here is some additional info of that system:

You create this neat web application and decide to try selling it as a SaaS application on the web. It’s a fairly complex system, roughly 250 tables, and will grow as needed. You decide on MySQL as your database. Since you now want it to be a SaaS application, but when you originally designed it that was not the plan. So, you decide it is easiest to just clone the database for each new client.

Add a bit of front end code so when the user logs in, you determine what company it’s for and switch to the proper database. Piece of cake, and in the real world it works well. You know that every time you add an index, or a new table, or even a new column to the database for a user or your application, that you will have to add it to all the databases because you do want to keep them in sync. That is pretty easy to do, just create a script and run it on all the customer databases. Piece of cake for sure.

When the SaaS application grows and getting out of control:

Well, the web application works well, and customers are liking it, and after a while you have more and more clients. Because of enhancements the application now has 450 tables, of which many are temporary tables, but that is how it was designed. Your salespeople have been busy, you’re now up to 1,100+ clients.

But the system is sure running slow. How could that be? You have got a pretty powerful server. You have added disk space to allow for the added customers, you’re running on Amazon RDS servers.

When you beefed up the memory in the server - and still it does not help:

You’ve beefed up the memory in the server, because with the number of tables and clients, you now have 500,000+ tables in the system. They’re innodb tables, and you’ve allocated 256GB of RAM on the server to the InnoDB buffers. The system still is not fast enough. You bring in a MySQL consultant from our firm and he looks at all the databases, and sees that there aren’t any two that are the same.

Either the indexes are different, or in some cases some columns in certain tables are different, maybe even a different data length or data type. This is a giant mess, but it was going to be a piece of cake to keep things in sync, at least that is what you thought.

Getting MySql Remote DBA Consultant:

Ultimately the mySQL consultant as remote DBA suggests switching to AWS Aurora as Aurora for MySQL is significantly faster than regular MySQL on RDS.

There also appears to be a lot of reporting, and right now it is on the same production server that’s being updated daily by users. Ultimately a read only replica is added to move the reports off to that.

In addition, there are many indexes that aren’t being used, and some that need to be created to help with performance. Even a few duplicate indexes, but of course, this work has to be done on 1,100+ databases. Not a small job to accomplish. On Aurora the application is now running somewhat better.

But really the main issue was not thinking thru how the application could scale. A multi-tenant arrangement with many clients per database would have made things much easier.

I’m sure someone had told someone that multiple databases are much easier to scale, as you can move a database to another server for performance, but that would just mean you’d have two or more servers, with huge InnoDB buffer overhead.

Conclusions: mySQL database should have been designed in advance for best performance:

A properly designed database could still scale. Clients could be moved to other servers, without having 500,000+ tables in the server. One thing to get out of this, is to design your system as you think as it should be designed. If things change, take the time to make the needed changes to design. We would recommend using a data modelling tool for the design. This way you get a global view on the entire system.

This way it is efficient, and the performance will be much greater, and the system can properly scale up.

This way you are more successful than you originally thought you would be, the database will grow with the system. Don’t lose sight of the fact that the application needs to perform properly, and be as efficient as it can reasonably be.

Check our SQL Consulting services:

For more info about our MySQL and MS SQL consulting services we provide please call us: 732-536-4765 or send us an email via our Contact Us page.