mysql database application

MySQL Sharding Models for SaaS Applications
December 14, 2021 – 05:14 am
Requirements For Building Java GUI To Access MySQL Database - Java

MySQL Sharding ModelsIn this blog post, I’ll discuss MySQL sharding models, and how they apply to SaaS application environments.

MySQL is one of the most popular database technologies used to build many modern SaaS applications, ranging from simple productivity tools to business-critical applications for the financial and healthcare industries.

Pretty much any large scale SaaS application powered by MySQL uses sharding to scale. In this blog post, we will discuss sharding choices as they apply to these kinds of applications.

In MySQL, unlike in some more modern technologies such as MongoDB, there is no standard sharding implementation that the vast majority of applications use. In fact, if anything “no standard” is the standard. The common practice is to roll your own sharding framework, as famous MySQL deployments such as Facebook and Twitter have done. MySQL Cluster – the MySQL software that has built-in Automatic Sharding functionality – is rarely deployed (for a variety of reasons). MySQL Fabric, which has been the official sharding framework, has no traction either.

When sharding today, you have a choice of rolling your own system from scratch, using comprehensive sharding platform such as Vitess or using a proxy solution to assist you with sharding. For proxy solutions, MySQL Router is the official solution. But in reality, third party solutions such as open source ProxySQL, commercial ScaleArc and semi-commercial (BSL) MariaDB MaxScale are widely used. Keep in mind, however, that traffic routing is only one of the problems that exist in large scale sharding implementations.

Beneath all these “front end” choices for sharding on the application database connection framework or database proxy, there are some lower level decisions that you’ve got to make. Namely, around how your data is going to be led out and organized on the MySQL nodes.

When it comes to SaaS applications, at least one answer is simple. It typically makes sense to shard your data by “customer” or “organization” using some sort of mapping tables. In the vast majority of cases, single node (or replicated cluster) should be powerful enough to handle all the data and load coming from each customer.

What Should I Ask Myself Now?

The next set questions you should ask yourself are around your SaaS applications:

  • How much revenue per customer are you generating?
  • Do your customers (or regulations) require data segregation?
  • Are all the customers about the same, or are there outliers?
  • Are all your customers running the same database schema?

I address the answers in the sections below.

How Much Revenue?

How much revenue per customer you’re generating is an important number. It defines how much infrastructure costs per customer you can afford. In the case of “freemium” models, and customers generating less than $1 a month an average, you might need to ensure low overhead per customer (even if you have to compromise on customer isolation).

Typically with low revenue customers, you have to co-locate the data inside the same MySQL instance (potentially even same tables). In the case of high revenue customers, isolation in separate MySQL instances (or even containers or virtualized OS instances) might be possible.

Data Segregation?

Isolation is another important area of consideration. Some enterprise customers might require that their data is physically separate from others. There could also be government regulations in play that require customer data to be stored in a specific physical location. If this is the case, you’re looking at completely dedicated customer environments. Or at the very least, separate database instances (which come with additional costs).

Customer Types?

Customer size and requirements are also important. A system designed to handle all customers of approximately the same scale (for example, personal accounting) is going to be different than if you are in the business of blog hosting. Some blogs might be 10, 000 times more popular than the average.

Same Database Schema?

Finally, there is a there is the big question of whether all your customers are running the same database schema and same software version. If you want to support different software versions (if your customers require a negotiated maintenance window for software upgrades, for example) or different database schemas (if the schema is dependent on the custom functionality and modules customers might use, for example), keeping such customers in different MySQL schemas make sense.

Sharding Models

This gets us to the following sharding isolation models, ranging from lowest to highest:

  • Customers Share Schemas. This is the best choice when you have very large numbers of low-revenue customers. In this case, you would map multiple customers to the same set of tables, and include something like a customer_id field in them to filter customer data. This approach minimizes customer overhead and reduces customer isolation. It’s harder to backup/restore data for individual customers, and it is easier to introduce coding mistakes that can access other customers data. This method does not mean there is only one schema, but that there is a one-to-many relationship between schemas and customers. For example, you might have 100 schema’s per MySQL instance, each handling 1000 to 10000 customers (depending on the application). Note that with a well-designed sharding implementation, you should be able to map customers individually to schemas. This allows you to have key customer data stored in dedicated schemas, or even on dedicated nodes.
day of good deeds angelina jolie good deeds good deeds full movie 2012 good deeds mp4 movie download madea good deeds kim kardashian good deeds good deeds quotes images good deeds bracelets synonym for good deeds do good deeds think good thoughts good deeds never die perry tyler good deeds madeas good deeds good deeds movie showtimes good deeds full movies free online movie good deeds 100 good deeds for eddie mcdowd episode 1 daddy good deeds watch online five good deeds bible verse about showing off good deeds spur one another on in love and good deeds good deeds trailer subtitulado free good deeds good deeds in school good deeds kids can do when does the movie good deeds come out good deeds atlanta idris elba no good deeds good deeds movie music bible verses about rewards for good deeds good deeds day 2020 good deeds do not exist good deeds online subtitulada define good deeds abigail mac does good deeds! good deeds without recognition oprah winfrey good deeds tyler perry good deeds full movie free online good deeds release date good deeds movie release date good deeds movie 2014 don't do your good deeds publicly watch tyler perry good deeds movie online free good deeds film location michelle obama good deeds watch good deeds online megavideo good deeds full movie megashare what displays animals, displays the good deeds of a family, and represents the ancestors? good deeds insurance good deeds society mn good deeds that people do don't do good deeds to be seen good deeds dvd redbox good deeds dvd release good deeds by police officers doing good deeds to others tyler perry good deeds movie download watch free good deeds good deeds thesaurus good deeds movie synopsis muslim good deeds ways to do good deeds people doing good deeds caught on camera list of good deeds for kids good deeds caught on camera good deeds 2016 doing good deeds in secret ten good deeds good deeds project when is good deeds coming to dvd stimulate one another to love and good deeds full movie good deeds examples of good deeds in school tyler perry full movie good deeds good deeds soundtrack tupac good thoughts good words good deeds the conductor zubin mehta tyler perry good deeds on dvd good deeds manger craft doing good deeds in secret islam no good deeds movie theater short stories on good deeds good deeds dvd amazon
Related Posts