How I handled the scalability of the SQL database in Inspector

Valerio Barbera
Graphic with a gear labeled System, Design, Concept. Text over blue background of scalability, horizontal scaling. Representative of Inspector’s SQL database scalability.

Hi, I’m Valerio, software engineer, CTO, and founder at Inspector. In this article, I’ll talk about what I learned by increasing the operational limits of the Inspector SQL database.

Before talking about read-replicas or sharded data, it might be helpful to introduce the problem. And to look at the most common strategies to improve the database’s ability to process an increasing number of queries per second. Thus, granting better growth margins for the application.

Why the database causes so many performance issues?

We often forget that each request or process performed by the application is not “atomic”. When we write a piece of code, we should keep in mind that ten other requests will simultaneously execute that piece of code. If it’s slow, it’s likely to affect other requests running in parallel and at the end of the whole system.

The database is a shared resource used by all processes and servers behind your application. So, be careful with thinking, “it’s okay if this piece of code isn’t optimized“.

Even one poorly designed access to the database can hurt the whole system’s performance.

What problem does scaling the database solve?

Before proceeding, we should clarify a difference between vertical vs horizontal scaling. Both are useful but suited to solve different problems.

Vertical scaling

Illustration of progressively larger servers running left to right. Representative of Inspector’s SQL database scalability.

Do you deal with large datasets, import/export processes, data aggregation algorithms, and similar problems?

If so, you can get more benefits from scaling your database vertically.

Resource-intensive tasks usually don’t have great benefits from multiple instances. When a long-running query executes against an instance, that instance must have enough resources to execute the query with resonable performance. And do so without hurting the performance of other tasks.

For this type of database operation, you should right size the RAM and CPU of the machines to take care of the performance of the SQL tasks you write.

Learn how to tune your SQL queries to run cumbersome queries with optimal performance in this article: How to accelerate application performance with smart SQL queries.

Horizontal scaling

Horizontal scaling aims to solve the opposite problem: allow the database to perform a large number of small queries per second.

It’s a matter of finding a way to run the database on multiple instances. It’s just like a load balancer that distributes the traffic between application servers.

But, when you take a closer look at new database architectures and see what’s working and what’s not, the fundamental problems with relational databases become clearer.

Achieving scalability is a massive challenge for relational databases

Relational databases were first designed to run on a single server in order to maintain the integrity of the table mappings and avoid the typical problems of distributed computing.

Relational database vendors have come out with a whole assortment of improvements to handle these concerns. They use complex architectures like “master-slave” or “sharded-data”.

Below we discuss about “read-replicas” which is the Inspector implementation. It’s also the most common strategy to scale a relational database horizontally.

What is a database read-replica?

A read replica is a copy of the primary instance that reflects changes to the primary in almost real time. You can basically use a read replica to do the following:

  • Offload read requests from the primary instance;
  • Perform a regional migration or fail over to another instance for disaster recovery purposes.

We use the read replica to distribute the load against two instances instead of one.

While it looks cool, this setup still has a weakness. The second instance is readonly.

If we can write on the second instance, it too would have to synchronize the data back to the first one (the master). Still, two-way synchronization is not allowed in relational databases to avoid data corruption.

We can offload only the “read” queries from the primary instance, so it is the right solution if your application is “read” intensive. If your application is write intensive this architecture wouldn’t be enough.

Database read-replica Pros

  1. It is very fast as doesn’t impose any restrictions on read performance.
  2. You can split read and write requests to different servers. For example, all analytics queries can be made on replica nodes.

Database read-replica Cons

  1. It is not very reliable because of asynchronous replication. It means that some committed on master transactions may be not available on slave if the master fails.
  2. Write requests can hardly be scaled. The only option to scale write requests is to increase compute capacity (RAM and CPU) of the Master node.

Master-Master replication can enable two way synchronization, allowing us to write on both instance. But consider that even the largest cloud computing providers doesn’t provide this option in their managed SQL database offerings. It is a tunnel that is best not to enter.

Rely on your cloud provider

We are a small team, that needs to keep our infrastructure management flexible and cost friendly. So we migrated our MySQL instance to AWS RDS. It offers the possibility to create/delete read replicas with a few clicks. As a result we can focus our efforts on application development instead of SQL server manaement.

Inspector is backed by the AWS Startup Program. They allow us to experiment with their cloud platform to find the right solutions for our needs. Regardless, the most known cloud providers offer a managed SQL database with the ability to add read-replicas. For example Amazon RDS or DigitalOcean managed databases.

It’s all about to better understand the architecture and how your application can get more advantages.

Conclusion

One of our code design constraints is to avoid database write operations as much as possible to stay away from the most critical limit to scalability of a SQL database.

I hope this article helped bring new ideas to improve your application.

If you want to learn more about how to scale up your application, join Scalable Applications, the international community of professional developers, to share strategies and experiences building scalable systems.

Application monitoring

If you found this post interesting and want to drastically change your developers’ life for the better, you can give Inspector a try.

Inspector is an easy to use Code Execution Monitoring tool that helps developers to identify bugs and bottlenecks in their application automatically. Before customers do.

screenshot inspector code monitoring timeline

It is completely code-driven. You won’t have to install anything at the server level or make complex configurations in your cloud infrastructure.

It works with a lightweight software library that you can install in your application like any other dependency. Check out the supported technologies in the GitHub organization.

Create an account, or visit the website for more information: https://inspector.dev

Related Posts

How to configure HTTPS in Laravel Homestead

How to enable HTTPS in Laravel Homestead

Hi, I’m Valerio Barbera, software engineer, founder and CTO at Inspector. In this article I’ll show you how to enable HTTPS for your local applications served by Homestead, the official development environment for Laravel developers. I met the need to enable HTTPS because I am working to implement browser notifications for Inspector using Pusher/Beams. But

Laravel cron scheduling and its secrets

Hi, I’m Valerio Barbera, software engineer, founder and CTO at Inspector. Laravel tasks scheduling is one of the most useful features of the framework.The official documentation clearly explains what it is for: In the past, you may have written a cron configuration entry for each task you needed to schedule on your server. However, this

Graphic with Valerio’s avatar a heading reading Laravel validation and custom rules in Inspector with red abstract art on the right

Laravel validation and custom rules in Inspector

Hi, I’m Valerio Barbera, software engineer, founder and CTO at Inspector. Data validation is one of the fundamental features in any application. And it is something developers manipulate almost every day. The value a software provides to users is often a function of the quality of data it collects and provides. Laravel ships with many

How to build scalable applications

Get the e-book about the Inspector scalability journey.