The IP.com Prior Art Database
English (United States)
4 pages / 104.3 KB
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
Page 02 of 4
Enable modification of the views with INSTEAD OF-triggers