Browse Prior Art Database

A method to avoid sparsely filled tables in databases to save space supporting multi-tenant cloud computing environments

IP.com Disclosure Number: IPCOM000201997D
Publication Date: 2010-Nov-30
Document File: 4 page(s) / 104K

Publishing Venue

The IP.com Prior Art Database

Abstract

In cloud environments with lots of tenants subscribing the same application a lot of sparsely filled tables are created as part of the creation of the application tables per tenant in namespaces given by database schemas. Due to the implementation of current relational database systems there is a lot of space wasted with this approach, since the overhead per table in the "table header" multiples by number of tables times number of tenants and can easily grow into terabyte range. The method described is significantly reducing the amount of wasted space by providing an application-transparent approach compressing multiple sparsely filled tables from various tenants into a single, larger table.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 47% of the total text.

Page 01 of 4

A method to avoid sparsely filled tables in databases to save space supporting multi-tenant cloud computing environments

Cloud computing has two distinct roles:

Cloud Service Provider (be it a private = in house or a public = external cloud service)

Cloud Service Consumer - the tenant subscribing to a cloud service based on a service level agreement (SLA)

For the Cloud Service provider, the business case depends on efficient use of resources to provide economics of scale to the Cloud Service Consumer (a.k.a. a tenant) which means the infrastructure must be shareable and resources must be used efficiently and wherever possible without redundancy. For a database server used for an application shared by multiple tenants, we have the following situation:

For each tenant subscribing to the cloud service within the database server all tables for the application are created in a tenant-specific schema (a schema is a concept to logically group database objects).

For a business object, there are often a 50+ to several hundred lookup tables (country codes, marital status, titles, color codes, units of measure, package sizes, ...). A characteristic of lookup tables is that they are typically very, very small - thus the overhead per table wastes a lot of space.

There are in normalized data models typically significantly more lookup tables then data tables - thus creation/deletion/change of lookup tables causes the majority of the load on the database catalog
An application such as CRM typically has 40-80 major business objects.

For a cloud service provider, a cloud service becomes interesting to offer if several thousand to several ten of thousands tenants subscribe. This yields easily to hundreds of thousands small - or even empty - tables in the database system.

In such an environment, the following issues exists:

Tens of thousands of tables which creates a database catalog which is very hard to maintain for a DBA
Tremendous amount of wasted of space since each table has an base overhead because major standard application packages have thousands of tables where the wasted space can be in the larger then 20 GB per tenant. If there are lots of tenants, this becomes very rapidly a problem not only on the storage level but also from main memory consumption since tables require table control blocks in memory. The wasted space is not only on the production system, but also in backups.

The key ideas solving these pain points are:

Algorithm to identify tables with a compatible schema


Merge the small tables with a compatible schema into a single large table to avoid waste of space
Add an additional column to the large table identifying the tenant schema Create appropriate new indexes for uniqueness using this new column as first column
Create a view per tenant to restrict access to application specific data in original table format --- this step achieves application transparency

1


Page 02 of 4

Enable modification of the views with INSTEAD OF-triggers

B...