Sep
19
2017
--

Salesforce Einstein celebrates its first birthday with several new features

 Salesforce launched Einstein, its artificial intelligence platform just one year ago this week. As it celebrates its first birthday, it’s worth taking a look back at the first year and looking at a couple of enhancements they’re adding as a birthday surprise. It’s easy to lose sight of the fact that Einstein isn’t actually a product at all, even though Salesforce markets… Read More

Jul
20
2017
--

Freshdesk owner Freshworks acquires Joe Hukum as it plans a move into chatbots

 After raising $55 million last year to build its business beyond its existing help desk services, today Freshworks (the parent company of Freshdesk) has made an acquisition to help it fill out that strategy. The company has acquired Joe Hukum, a startup out of India that offers a platform for businesses to build their own chatbots. I’ve asked, but the companies are not revealing any terms… Read More

Jun
28
2017
--

As Egnyte turns 10, it moves into data protection

 Egnyte had a big milestone last week. It turned 10 years old, which is a pretty big deal for a startup. The company has survived and even thrived, but as it moves into its second decade, it recognizes that it can’t live on file sync and share alone, and today it announced the general availability of a new product called Egnyte Protect. The product was introduced in Beta last year, but… Read More

Jun
08
2017
--

Algolia raises $53 million for its search engine API

 French startup Algolia just raised a $53 million Series B round led by Accel, a couple of years after raising $18.3 million with… Accel also leading the round. So it looks like it’s a love story between the VC firm and the software-as-a-service startup. Read More

Apr
20
2017
--

Swingvy raises $1.1M to give HR staff in Southeast Asia a break from paperwork

 Human resources paperwork is never fun to take care of—especially if it’s literally on paper. Swingvy co-founder Jin Choeh says that in Southeast Asia, many small businesses are still stuck with physical spreadsheets and piles of forms. Swingvy wants to help them with affordable cloud-based software. The South Korea and Malaysia-based startup just raised $1.1 million in seed funding… Read More

Apr
02
2017
--

New Clerky tools help startups hire and raise funds without running into legal problems

 Clerky may not be a household name like TurboTax today, but the company’s business formation software has been called a “secret weapon” by startup founders in Silicon Valley for years. Many Y Combinator cofounders use it to get their companies started on paper. And now, Clerky is launching two new tools called Hiring and Fundraising to help startups move beyond… Read More

Jan
30
2017
--

MySQL Sharding Models for SaaS Applications

MySQL Sharding Models

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).

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.
  • Schema per Customer. This is probably the most common sharding approach in MySQL powered SaaS applications. Especially ones that have substantial revenue ($10+ per month / per customer). In this model, each customer’s data is stored in its own schema (database). This makes it very easy to backup/restore individual customers. It allows customers to have different schemas (i.e., add custom tables). It also allows them to run different versions of the application if desired. This approach allows the application server to use different MySQL users connecting on behalf of different customers, which adds an extra level of protection from accidental (or intentional) access of data that belongs to different customers. The schema per customer approach also makes it easier to move the shards around, and limits maintenance impact. The downside of this approach is higher overhead. It also results in a large number of tables per instance, and potentially larger numbers of files (which can be hard to manage).
  • Database Instance per Customer. You achieve even better isolation by having a MySQL instance per customer. This approach, however, increases overhead even further. The recent rise of light virtualization technologies and containers has reduced its usage.
  • OS Instance/Container per Customer. This approach allows you to improve isolation even further. It can be used for any customer, but can also be applied to selected customers in a model that uses Schema per Customer model for a majority of them.  Dedicated OS Instance, with improved isolation and better performance SLAs, might be a feature of some premium customer tiers. This method not only allows better isolation, but it also let’s you handle outliers better. You might chose to run a majority of your customers on the hardware (or cloud instance) that has best price/performance numbers, and also place some of the larger customers on the highest performance nodes.
  • Environment per customer. Finally, if you take this all the way you can build completely separate environments for customers. This includes databases, application servers and other required components. This is especially useful if you need to deploy the application close to the customer – which includes the appliance model, or deployment in the customer’s data center or cloud provider. This also allows you to accommodate customers if their data must be stored in a specific location. This is often due to government regulations. It is worth noting that many SaaS applications, even if they do not quite have one environment per customer, have multiple independent environments. These are often hosted in different locations or availability zones. Such setups allow you to reduce the impact of large-scale failures to only a portion of your customers. This avoids overloading your customer service group and allowing the operational organization to focus on repairing smaller environments.

The farther you go down this route – from the shared schema to an environment per customer – the more important is to have a high level of automation. With a shared schema, you often can get by with little automation (and some environments manually set up) and all the schema’s pre-created. If customer sign up requires setting up dedicated database instance or the whole environment, manual implementation doesn’t scale. For this type of setup, you need state-of-the-art automation and orchestration.

Conclusion

I hope this helps you to understand your options for MySQL sharding models. Each of the different sharding models for SaaS applications powered by MySQL have benefits and drawbacks. As you can see, many of these approaches require you to work with a large number of tables in the MySQL – this will be the topic of one of my next posts!

Jan
05
2017
--

After rejecting Google, Namely raises $50M to take on the biggies in HR

2240752018_37976b95df_b One of the big HR startup stories of the last year has been the trials and tribulations of Zenefits, but news coming out today from another company in the space shows that there is still a lot of opportunity in the industry with the right approach. Namely, a New York-based SaaS HR firm that focuses on mid-sized (100 to 1,000 employees) companies offering an all-in-one human resources… Read More

Nov
01
2016
--

Classy raises $30 million to help nonprofits raise donations, make a greater impact

Classy.org headquarters in San Diego. A San Diego startup called Classy has raised $30 million for software-as-a-service that helps nonprofits raise donations online, and keep supporters engaged in a good cause for the long run. Specifically, Classy helps 501c3 or other nonprofit organizations to run crowdfunding, online and mobile campaigns, awareness and fundraising events all under one roof. This makes it possible for nonprofits… Read More

Oct
07
2016
--

Determining the worth of your SaaS company

Photo: Ben McLeod/Getty Images The opaque, confusing and highly volatile practice of valuing a private SaaS business is frustrating for entrepreneurs and investors alike. Furthermore, the lack of transparency adds a tremendous amount of friction to a capital raise or the sale of a company. The reality is, it’s not all that hard to get a quick read on your SaaS company’s valuation. Read More

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com