1 / 35

Realizing a Multi-Tenant SaaS Platform based on Universal Table Schema Layout

Realizing a Multi-Tenant SaaS Platform based on Universal Table Schema Layout. Author: Deik Hoong Tan Professor : Chun Feng Liao. Outline. Introduction Related Work System Design System Implementation Experiment Conclusion. Introduction. Cloud computing

hagen
Download Presentation

Realizing a Multi-Tenant SaaS Platform based on Universal Table Schema Layout

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Realizing a Multi-Tenant SaaS Platform based on Universal Table Schema Layout Author: DeikHoong Tan Professor : Chun Feng Liao

  2. Outline • Introduction • Related Work • System Design • System Implementation • Experiment • Conclusion

  3. Introduction • Cloud computing • Enabling ubiquitous, convenient, on-demand network access to a shared pool of configurable computing resources (NIST) • End users access cloud-based applications through a web browser • Cloud service model • Infrastructure as a Service (IaaS) • Platform as a Service (PaaS) • Software as a Service (SaaS)

  4. Introduction-Software as a Service • Delivers applications/services over the web • No upfront investment in servers or software licensing for users • “pay-as-you-go” model • Software application accessed without additional hardware or software • Examples: • Google Docs • Dropbox • Salesforce.com  • Hotmail or Gmail • Microsoft OneDrive, etc

  5. Introduction-Multi-Tenancy • Tenant = A person/organization • Multi-tenancy • Single instance of software serving multiple tenants • Ensures one tenant operates in isolation from all others

  6. Introduction-Multi-Tenancy • Challenges • How to select and realizing appropriate schema layout • Scalability • High performance • Tenant-specific customization • Goal • Todevelop a platform for converting SQL statements to fulfills Universal Table Layout • To customize columns through the platform

  7. Related Work • Multi-Tenant Database Architecture • Separate Database • Share Database, Separate Schema • Share Database, Share Schema

  8. Related Work-Separate Database • Separate Database • Hardware resources share by different tenants • Create specific database to serve each tenant • Pros • Good data isolation • High security • Cons • Maintenance cost expensive • Scalability is very poor

  9. Related Work-Share Database,Separate Schema • Share Database,Separate Schema • Share one database instance • One database instance store all tables from different tenant. • Pros • Reduce maintenance cost compared with separate database • Provides better scalability than separate database • Cons • Performance begins degrade when the number of tables is increase • Scalability still the limitation

  10. Related Work-Share Database,Share Schema • Share Database,Share Schema • Share one database instance and schema • Uses one table manage multi-tenant data • Pros • Low cost compared last solution • Highly scalability • Cons • Difficult design and implement • Performance obviously degraded when database tables become too sparse

  11. Related Work • Schema Mapping Technique (Aulbach et al) • Private Table Layout • Extension Table Layout • Universal Table Layout • Pivot Table Layout • Chunk Table Layout • Chunk Folding Layout • Universal Table is the most flexible schema and if carefully design can achieve reasonable performance (Li et al)

  12. Related Work-Universal Table Layout • Generic structure with VARCHAR value columns • n-th column of a logical table is mapped to ColN in an universal table • Extensibility: # of columns may expand as needed • Disadvantages • Very wide rows  Many NULL values • No index support (note column index not very meaningful) logical table (after renaming)

  13. Related Work- SalesForce.com Schema Layout

  14. System Design • Considerations • Provide ability of tenant-specific customization • Enhance the ability of software developers to manage the information of tenants.

  15. System Architecture • Components of system architecture • Wrapper • Web Application • SQL Transform Layer • Database

  16. System Flow Tenant10 : Select * from product Product Tenant10 : Select * from data where TenantId=10 and Name=“Product” Data

  17. Multi-Tenant Adapter Design • MySQLiis a relational database driver used in PHP interact with MySQL database • DBI is inherit with MySQL • Construtor • TenantId • Password • Database • SetTenantId • Prepare • Execute • Close

  18. Universal Table Layout Design

  19. SQL Rewriting Design • Transform various SQL statement to fulfill Universal Table Layout • SQL statement rewrite rules • Select (Liao et al) • Create Table • Alter

  20. Create Table Transformation • Transform CREATE TABLE statement • Parse CREATE TABLE statement • Store the table name of logical table and tenantId to Objects table • Store columns name, number of columns, data type and latest objectIdto Field table

  21. Select Transformation • Transform SELECT statement • Parse SELECT statement • Obtain table name through FROM element and found objectId in the Objects table • Determine SELECT element is "*" or specific columns and search columns names and columns position in Field table • Transform SELECT statement by tenantId, objectId, fieldName, fieldNum

  22. ALTER Transformation (For Tenant) • Types of ALTER statements • Add a column • Delete a column • Rename table name • Modify columns name and data type • Transform ALTER statement • Determine which type ALTER statement is. • According type of ALTER statement to make changes on Object and Fields table.

  23. System Implementation • Scenario • A tenant need to build a e-commerce web site • Require three tables • Product • Order • OrderLineitem

  24. Create Table

  25. CreateTable

  26. Create Table

  27. Customize Schema

  28. Select Data

  29. Experiment environment • Client (Laptop) • Apache JMeter • Server (Desktop PC) • Ubuntu 12.04 • MySQL 5.5.34

  30. Experiment Design • Assume three tables Order Product OrderLineitem

  31. Experiment Design • Data quantity of each table • Product Table 10000 rows • Order Table 10000 rows • OrderLineitem 10000 rows • Physical database • M10, M20, M30, M40, M50 total five databases where stored 10 to 50 tenants information

  32. Experiment Design • SQL statement • Select , Project , Join , Index(primary key) • Experiment 1 #(Database)={M10,M20,M30,M40,M50} • Execute each SQL statement at difference tenant database • Experiment 2 #(Thread)={20,40,60,80…200} • Database fixed at M10,M30,M50

  33. Preliminary Experiment 1 Tenant Database

  34. Preliminary Experiment 2 Thread Thread Thread Thread

  35. Conclusion • Design a SaaS platform based on Universal Table Schema Layout • Transform SQL statement to fulfill Universal Table Schema Layout • Design a multi-tenant adapter let tenants more easier use the platform • SQL rewriting of insert, update, delete statements

More Related