Thinking of Scaling MySQL, Say goodbye to stored procedures

Stored Procedure is a unique code in SQL that can be stored for later use. Also, the same code can be used several times. Whenever you need to execute a query, you can call the stored procedure. Moreover, you can pass parameters to a stored procedure to make them act based on the parameter values that are passed.

Challenges with stored procedures

Database servers are the bottleneck for most of the enterprise level applications. With time, data increases and the load on the server increases as well. If no archiving process is in place, the data will keep on growing over years, and the performance of the database will deteriorate. The database server is already doing a lot of work by performing read and write operations. Adding more work in the form of complex business logic written under stored procedures narrows the database server bottleneck.

One of the biggest disadvantages of having your business logic in stored procedures is on the horizontal scalability side. Imagine we have a web application which makes a very complex calculation, and you have the entire logic written in a stored procedure. The calculation involves churning and manipulating the data on the fly and is a CPU and memory intensive task. Further imagine 100 users initiating this calculation at the same time. When running it through a stored procedure, it is very likely to consume lots of CPU and memory. If the resources are not sufficient, you would see a 100% CPU spike and the database would not be in a position to take or serve any more requests.

In this very same example, you will see the database server being the bottleneck and doing almost all the work, and the application servers having advanced features like auto-scaling, just sitting idle and not doing anything.

Often, we increase the capacity of the database server, but this is an expensive solution and not a long term one. Something that is away from horizontal scalability.

Moving complex logic to application layer

A one line solution to the above problem is to move the logic from stored procedures to application layer, and use the database only for simple SELECT, UPDATE and INSERT. Doing so will result in immense benefits.

In this case, where the application server is on auto scaling mode, and the number of requests increase, the application server will have more instances available, and each and every instance will utilize its resources to the fullest. Here, the application server is busy doing complex calculations, the database server is busy supplying the data to the application servers. Everyone’s equally busy. As demand increases, more applications servers will be available to do the job.

This way true horizontal scalability can be achieved.

Batch processing

For batch processing as well, we can leverage the idea of moving away from stored procedures. Considering the batch processing would be a very resource intensive task, moving the logic to the application layer has its benefits.

Other cons of stored procedures

1. Stored procedures are hard to test
2. Source control is extremely difficult
3. Moving to different versions of the database would require through retesting, as some functions become deprecated.
4. For many developers, SQL is barely a programing language
5. Even if we consider it as language, it’s far inferior to the likes of JAVA, .NET to name a few(using ORMs)
6. Passing objects is not possible
7. Versioning is difficult
8. Anyone can modify it on the server
   

Conclusion

Moving the logic away from the stored procedures has a lot more advantages. This also gives us an option to migrate to a different database if required or desired. With stored procedures, you are married to one database technology, as MySQL stored procedures don’t work on MSSQL and so on.

I think writing stored procedures is a good idea when dealing with some ad-hoc requests. Let’s say your client wants to have some report on an ad-hoc basis for some analysis and doesn’t know SQL. In such cases, stored procedures are an easy way to accomplish the task.