Ruminations on Multi-Tenant Databases

This is a position paper on multi-tenant databases. As motivation, it first describes the emerging marketplace of hosted enterprise services and the importance of using multi-tenancy to handle high traffic volumes at low cost. It then outlines the main requirements on multi-tenant databases: scale up by consolidating multiple tenants onto the same server and scale out by providing an administrative framework that manages a farm of such servers. Finally it describes three approaches to implementing multi-tenant databases and compares them based on some simple experiments. The main conclusion is that existing database vendors need to enhance their products to better support multi-tenancy. 1 Hosted Services and Multi-Tenancy In the hosted service model [GM02a, GM02b, Wa03], a service provider develops an application and operates the system that hosts it. Customers access the application over the Internet using industry-standard web browsers or Web Services clients. As the Internet has matured, hosted services have appeared for an increasingly wide variety of enterprise applications, including ones that manage sales, marketing, support, human resources, planning, manufacturing, inventory, financials, purchasing, and compliance [Th06]. While hosted services are attractive to all segments of the market, they are particularly appealing to smallto medium-sized businesses, which often lack the resources to maintain a complex data center. Hosted services are exploiting such “greenfield” opportunities to expand the overall size of the market. In comparison to traditional on-premises solutions, hosted services can reduce the total cost of ownership of an application by aggregating customers together and leveraging economy of scale. This principle applies to both capital expenditures, e.g., for hardware and software, and operational expenditures, e.g., for bandwidth and personnel. Because a hosted service is focused on one application, the infrastructure and the procedures for managing it can be highly optimized: well-known examples here include the Google File System [GGL03] and Hotmail [Sm05]. Such optimizations are essential to support large numbers of smallto medium-sized businesses, which would otherwise be prohibitively expensive. Multi-tenancy is an optimization for hosted services in which multiple customers are consolidated onto the same operational system, a technique pioneered by salesforce.com [Sa06, Co06]. Multi-tenancy allows pooling of resources, which improves utilization by eliminating the need to provision each customer for their maximum load. Multi-tenancy can also improve management efficiencies by providing a uniform framework for administering the system. A multi-tenant system should support both scale up and scale out [De99]: scale up by consolidating multiple customers onto the same server and scale out by having the administrative framework span a farm of such servers. Scale out is required because it is not cost effective to scale up a single server indefinitely. The administrative framework of a multi-tenant system should support the ability to migrate a customer from one server to another within the farm. For example, a customer might start out on a server that manages trial accounts, be moved to a server that manages small production accounts, and grow until it is moved to a dedicated server that manages only one account. Migration should also be possible between farms, for example, to allow customers to be moved from one data center to another. The administrative framework should also support rolling upgrade, where the servers in a farm are upgraded to a new version of the application one at a time. Since it is difficult to generate realistic Internet-scale workloads for testing, rolling upgrades are needed to gain experience with new versions before they are put widely into production. 2 Requirements on Multi-Tenant Databases Multi-tenancy can be applied at the database layer of a hosted service, where it can be very effective due to the high cost of provisioning and operating databases. This section argues that, in addition to managing customers’ private data, a multi-tenant database should manage customer metadata and shared public data. The administrative framework of a multi-tenant database should maintain metadata about customers, such as their contact information, their location in the farm, and the features they are allowed to access. Certain administrative operations will need to access this metadata alongside of customer data. For example, a service provider might want to scan the metadata to find all customers in a given region and then determine which of those customers have more than a certain amount of data. To support such operations, the administrative framework should offer a unified query language that obviates the need for a general-purpose programming language with embedded queries. In addition to being easier to use, this approach makes it simpler to execute administrative operations in bulk on individual databases in the farm. The target application for a hosted service will generally have a base schema that specifies all of its standard data. A multi-tenant database should maintain an instance of this base schema for each customer. The unified query language should ensure that DDL statements for modifying the base schema and DML statements for transforming existing data within it are applied to all customers in the farm, within the context of a rolling upgrade. The ability to perform such operations in bulk on the individual databases is essential to minimize downtime during an upgrade. Many business applications, such as CRM or ERP, allow customers to extend the base schema, e.g., by adding new columns to existing tables and adding new tables. The administrative framework should maintain the specification of each customer’s schema extensions as part of their metadata. A service provider may want to maintain public information, such as area code data, census data, streaming stock prices, or industry best practices, that is accessible to all customers. Such shared data will generally be read-only, although there are cases where space-saving technologies like copy-on-write [PB03] should be used to allow customers to privately update the data. A multi-tenant database should allow customers to seamlessly access shared data alongside of their private data. This will entail either replicating the shared data on each server in the farm or providing a mechanism to access it remotely. 3 Implementing Multi-Tenant Databases This section describes and compares three approaches to implementing multi-tenant databases: shared machine, shared process, and shared table. These approaches are increasingly better at pooling resources and executing administrative operations in bulk. However they increasingly break down the isolation between customers, weakening security and increasing contention for resources. The discussion presents the results of some preliminary experiments on the memory and disk usage of five databases: PostgreSQL [Po06], MaxDB [Ma06], and three commercial databases cleverly code-named Commercial1, Commercial2, and Commercial3. Unless otherwise indicated, no specialized tuning was performed on these databases. The experiments used a simplified version of the CRM schema offered by salesforce.com containing eleven tables, each with about twelve fields. The experiments were focused on the shared process approach: they created different numbers of schema instances, each with eleven empty tables, and loaded them into memory in the same database process. The experiments are preliminary in that the tables were not populated and no queries were executed against them. The results are shown in Table 1. In all cases, the storage requirements were close to being linear in the number of schema instances, hence only the end points at 1 and 10,000 instances are presented. PostgreSQL and Commercial1 produced various errors that prevented the experiments from completing and the final numbers are extrapolated. These errors were generally fixed limits on internal structures, such as the number of objects in a table space. All numbers are in megabytes.