comparison of different implementations of multi-tenant ... · bachelor thesis presentation...

Technische Universität MünchenDepartment of Computer Science

Comparison of Different Implementations of

Multi-Tenant Databases

Bachelor Thesis Presentation

Vladislav Lazarovlazarov@in.tum.de

Technische Universität MünchenDepartment of Computer Science

Basics

17.07.07 Vladislav.Lazarov@in.tum.de 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 Vladislav.Lazarov@in.tum.de 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

Vladislav.Lazarov@in.tum.de

Technische Universität MünchenDepartment of Computer Science

Multi-Tenant Databases

17.07.07 Vladislav.Lazarov@in.tum.de 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 Vladislav.Lazarov@in.tum.de 7

Technische Universität MünchenDepartment of Computer Science

MTD Implementation Approaches

Shared Machine Shared Process Shared Table

Vladislav.Lazarov@in.tum.de

Technische Universität MünchenDepartment of Computer Science

MTD Benchmark

17.07.07 Vladislav.Lazarov@in.tum.de 9

Technische Universität MünchenDepartment of Computer Science

MTD Benchmark Workload Generator

17.07.07 Vladislav.Lazarov@in.tum.de 10

Technische Universität MünchenDepartment of Computer Science

MTD Benchmark CRM DB Schema

17.07.07 Vladislav.Lazarov@in.tum.de 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 Vladislav.Lazarov@in.tum.de 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 Vladislav.Lazarov@in.tum.de 13

Technische Universität MünchenDepartment of Computer Science

Creating Tables

17.07.07 Vladislav.Lazarov@in.tum.de 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 Vladislav.Lazarov@in.tum.de 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 Vladislav.Lazarov@in.tum.de 16

Technische Universität MünchenDepartment of Computer Science

Implementation of the Benchmark

• Shared Table DB2

– Add tenant_id

– Remove setSchema()

17.07.07 Vladislav.Lazarov@in.tum.de 17

Technische Universität MünchenDepartment of Computer Science

Conclusions&

Future Work

17.07.07 Vladislav.Lazarov@in.tum.de 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 Vladislav.Lazarov@in.tum.de 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 Vladislav.Lazarov@in.tum.de 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