Technische Universität MünchenDepartment of Computer Science
Comparison of Different Implementations of
Multi-Tenant Databases
Bachelor Thesis Presentation
Vladislav [email protected]
Technische Universität MünchenDepartment of Computer Science
Basics
17.07.07 [email protected] 3
Technische Universität MünchenDepartment of Computer Science
Software as a Service
• ever-growing Internet population
• software models need to change
• on premisses applications no longer adequate
• providing applications in hosted environment
• new business models (pay-per-use, subscribe)
• reducing costs
17.07.07 [email protected] 4
Technische Universität MünchenDepartment of Computer Science
Multi-Tenancy
• consolidation of multiple customers onto the same infrastructure
• scalability, cost efficiency, enhancements of business processes
• scale-up & scale-out, growth of customers
• usability, reliability, performance
• main challenge: behave as if the system is fully dedicated to a single customer
Technische Universität MünchenDepartment of Computer Science
Multi-Tenant Databases
17.07.07 [email protected] 6
Technische Universität MünchenDepartment of Computer Science
Multi-Tenant Databases (MTD)
• license costs for commercial DBMS high
• hosted application devoted to one service
• base schema for all, extension possibilities
• pooling of database resources
• administative framework serving more customers
• management of sensitive data (CRM, ERP)
17.07.07 [email protected] 7
Technische Universität MünchenDepartment of Computer Science
MTD Implementation Approaches
Shared Machine Shared Process Shared Table
17.07.07 [email protected] 9
Technische Universität MünchenDepartment of Computer Science
MTD Benchmark Workload Generator
17.07.07 [email protected] 10
Technische Universität MünchenDepartment of Computer Science
MTD Benchmark CRM DB Schema
17.07.07 [email protected] 11
Technische Universität MünchenDepartment of Computer Science
Population of the Data
• preparing CSV files to be loaded into the database
• making use of algorithm for generation of dense unique random numbers (Wisconsin benchmark)
17.07.07 [email protected] 12
Technische Universität MünchenDepartment of Computer Science
The Algorithm of Englert and Gray
• Generation of a sequence of numbers which are:
– dense: all integers [0..n] appear in sequence;
– unique: each integer appears exactly once;
– random: the sequence appears to be random
Pick a prime p larger than n and a generator g for themultiplicative group modulo p. Then the series is:
gi mod p | i = 1, ..., p ∩ (gi mod p) ≤ n
17.07.07 [email protected] 13
Technische Universität MünchenDepartment of Computer Science
Creating Tables
17.07.07 [email protected] 14
Technische Universität MünchenDepartment of Computer Science
Loading Data in the DBMS
• DB2's Load Utility
• Derby's Bulk Import
• PostgreSQL's COPY
• Oracle's SQL*Loader
17.07.07 [email protected] 15
Technische Universität MünchenDepartment of Computer Science
Implementation of the Benchmark
• Shared Process PostgreSQL
– FETCH FIRST n ROWS ONLY => LIMIT n
– SET SCHEMA => SET SEARCH_PATH=
– Isolation Levels. PostgreSQL supports only
• Read Committed
• Serializable
17.07.07 [email protected] 16
Technische Universität MünchenDepartment of Computer Science
Implementation of the Benchmark
• Shared Table DB2
– Add tenant_id
– Remove setSchema()
17.07.07 [email protected] 17
Technische Universität MünchenDepartment of Computer Science
Conclusions&
Future Work
17.07.07 [email protected] 18
Technische Universität MünchenDepartment of Computer Science
Conclusions
• most promising approach: Shared Process
• different SQL dialects
• different data load tools
• first results: no high variance in response times
• SaaS promising for SMBs
17.07.07 [email protected] 19
Technische Universität MünchenDepartment of Computer Science
Future Work
• blur the line between data and metadata
– share a base schema in shared process
– give the tenant identifier in shared table a role
similar to a table identifier
• find best balance between isolation/security, resource pooling and scalability
• define a feasible multi-tenant environment forhosted services
17.07.07 [email protected] 20
Technische Universität MünchenDepartment of Computer Science
Bibliography
• Stefan Aulbach, Dean Jacobs, and Alfons Kemper. A
Benchmark for Multi-Tenant Databases. Unpublished
Manuscript, 2007.
• Dean Jacobs and Stefan Aulbach. Ruminations on Multi-
Tenant Databases. In BTW Proceedings 2007.
• Jim Gray, Prakash Sundaresan, Susanne Englert, Kenneth
Baclawski, and Peter J. Weinberger. Quickly Generating Billion-
Record Synthetic Databases. In Proceedings of the 1994 ACM
SIGMOD International Conference on Management of Data,
Minneapolis