slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Soror SAHRI Soror.Sahri@dauphine.fr ceria.dauphine.fr/soror/soror.html PowerPoint Presentation
Download Presentation
Soror SAHRI Soror.Sahri@dauphine.fr ceria.dauphine.fr/soror/soror.html

Loading in 2 Seconds...

  share
play fullscreen
1 / 46
Download Presentation

Soror SAHRI Soror.Sahri@dauphine.fr ceria.dauphine.fr/soror/soror.html - PowerPoint PPT Presentation

remy
108 Views
Download Presentation

Soror SAHRI Soror.Sahri@dauphine.fr ceria.dauphine.fr/soror/soror.html

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Thesis Presentation – CERIA Laboratory Design and Implementation of a Scalable Distributed Database System: SD-SQL Server Soror SAHRI Soror.Sahri@dauphine.fr http://ceria.dauphine.fr/soror/soror.html

  2. 1. Introduction 2. State of The Art P L A N 3. SD-SQL Server Architecture 4. SD-SQL Server Application Interface 5. Implementation of SD-SQL Server 6. Performance Measurements 7. Conclusion & Future Work

  3. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Facts Objectve Facts • Most of DBSs have distributed/parallel versions • SQL Server, Oracle, DB2… • DBSs do not provide dynamically scalable tables • All require manual repartitioning when tables scale-up

  4. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Facts Objective Objective • Scalable Distributed Partitioning ofRelational Tables Scalable Distributed Database SystemSD-SQL Server

  5. 1. Introduction 2. State of the Art P L A N 3. SD-SQL Server Architecture 4. SD-SQL Server Application Interface 5. Implementation of SD-SQL Server 6. Performance Measurements 7. Conclusion & Future Work

  6. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Parallel DBMSs SDDSs Parallel DBMSs • Oracle 10g [LB05] [LB05]K, Loney & B, Bryla. Oracle Database 10g, DBA Handbook

  7. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Parallel DBMS SDDSs SDDSs • An SDDS is a new class of data structures • Specific for multicomputers, P2P, Grids… • Why ? • SDDSs provide many scalable distributed partitioning schemes • LH*, RP*, k-RP*, LH*RS… • These schemes can serve as the basis for an SD-DBS architecture

  8. 1. Introduction 2. State of the Art P L A N 3. SD-SQL Server Architecture 4. SD-SQL Server Application Interface 5. Implementation of SD-SQL Server 6. Performance Measurements 7. Conclusion & Future Work

  9. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Overview Nodes, NDBs & SDBs Scalable Tables Images SD-SQL Server? • SD-SQL Server is a Scalable Distributed Database System (SD-DBS) • SD-SQL Server uses the reference architecture • Proposed by Pr. Litwin, Pr. Schwartz & Pr. Risch • 2nd Intl. Workshop on Cooperative Internet Computing, 2002 • SD-SQL Server is based on the RP* SDDS principles • SD-SQL Server runs on Microsoft SQL Server 2000

  10. User/Application User/Application sd_insert sd_alter_table SD-SQL ServerManagers SD-SQLserver SD-SQLpeer SD-SQLclient SD-SQLserver LinkedSQLServers Split NDBs P C S S N2 Ni Ni+1 N1 Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Overview Nodes, NDBs & SDBs Scalable Tables Images Gross Architecture [Litwin & Sahri. WDAS 2004] • The SD-SQL Server originality ? • The automatic extension of the scalable tables • of their NDBs • of their SD-SQL Server nodes

  11. …… Node1 Node2 Node3 Nodei DB1NDB DB2NDB DB1NDB DB2NDB DB1NDB DB2 SDB DB1 SDB MDB Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Overview Nodes, NDBs & SDBs Scalable Tables Images The Nodes, NDBs & SDBs

  12. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Overview Nodes, NDBs & SDBs Scalable Tables Images Scalable Tables • A scalable (distributed) table is a collectionof segments • Segments are SQL tables • A scalable table has, initially, only one primary segment • At some server or peer NDB • All the segments of a scalable table have the same scheme

  13. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Overview Nodes, NDBs & SDBs Scalable Tables Images Scalable Tables: Meta-data • Each scalable table has meta-data: • The segment capacity • The actual partitioning of the scalable table • The check constraint of each segment A check constraint defines the Min and Max for each segment • These meta-data are stored in the meta-tables • excluding the check constraints

  14. N1.DB1 N2.DB1 N3.DB1 Ni.DB1 Primary N1.DB1 N1.DB1 RP N2.DB1 N3.DB1 1000 Size Ni.DB1 Nodes Meta-Tables Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Overview Nodes, NDBs & SDBs Scalable Tables Images Scalable Tables: Meta-data DB1 SDB ……. T Scalable Table

  15. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Overview Nodes, NDBs & SDBs Scalable Tables Images Scalable Tables: Splitting • The number of segments in a scalable table is variable • A segment that overflows splits • A split occurs when an insert overflows the segmentcapacity • Every split produces one or more new segments for a scalable table

  16. p=INT(b/2) C( S)= { c: c < h = c (b+1-p)} C( S1)={c: c > = c (b+1-p)} b+1-p p S S1 S Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Overview Nodes, NDBs & SDBs Scalable Tables Images Scalable Tables: Splitting Check Constraint? b+1 b SELECT TOP P * INTO Ni.Si FROM S ORDER BY C ASC SELECT TOP P * WITH TIES INTO Ni.S1 FROM S ORDER BY C ASC

  17. N1 N2 N3 N3 Ni sd_create_node_database N4 NDBDB1 NDBDB1 NDBDB1 NDBDB1 NDBDB1 NDBDB1 DB1 SDB DB1 SDB DB1SDB sd_insert sd_insert sd_insert T ScalableTable Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Overview Nodes, NDBs & SDBs Scalable Tables Images Scalable Tables: Splitting sd_create_node sd_create_node_database …….

  18. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Overview Nodes, NDBs & SDBs Scalable Tables Images Images • AnImagehides the scalable table partitioning • An image is an SQL Server distributed updateable partitioned view of the table • An SQL Server Union-all view with check constraints • An image resides on client or peer NDBs

  19. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Overview Nodes, NDBs & SDBs Scalable Tables Images Image Types • Primary image • Resides at the creation node • Has the name of the scalable table • Secondary images • Reside at other client or peer NDBs of the SDB • Have a specific name, other than that of the table • To avoid name conflict

  20. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Overview Nodes, NDBs & SDBs Scalable Tables Images Image Adjustment • An image presents the actual partitioning of its scalable table • Defines the partitioning as known to the client • It do not address any new segments resulted from a split • Are dynamically adjustable by the client • When a query to the image comes in • Image checking • Image adjustment if necessary

  21. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Overview Nodes, NDBs & SDBs Scalable Tables Images Image Adjustment • Get the number of segments presented in the image, N1 • Get the number of segments of the scalable table, N2 • Compare N1 and N2: • If N1<N2 then Image Adjustment • Alter the partitioned view definition

  22. N1.DB1 N2.DB1 N3.DB1 N4.DB1 PrimaryImage Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Overview Nodes, NDBs & SDBs Scalable Tables Images Images: Example DB1 SDB T Image T Scalable Table CREATE VIEW TAS SELECT * FROM N2.DB1.SD._N1_T CREATE VIEW TAS SELECT * FROM N2.DB1.SD._N1_TUNION ALL SELECT * FROM N3.DB1.SD._N1_T UNION ALL SELECT * FROM N4.DB1.SD._N1_T

  23. 1. Introduction 2. State of the Art P L A N 3. SD-SQL Server Architecture 4. SD-SQL Server Application Interface 5. Implementation of SD-SQL Server 6. Performance Measurements 7. Conclusion & Future Work

  24. Principles Nodes Management SDBs & NDBs Management Scalable Tables & Images Management Scalable Queries Management INSERTsd_insert • CREATE TABLEsd_create_table Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Principles [Litwin, Schwartz & Sahri. IASTED-DBA 2006] • The application interface manipulates scalable tables through SD-SQL Server commands • The SD-SQL Server commandsstart with ‘sd_’ to distinguish from SQL Server commands for static tables

  25. Principles Nodes Management SDBs & NDBs Management Scalable Tables & Images Management Scalable Queries Management Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Nodes Management • Node Creation • sd_create_node‘Dell1’/* Serverby default */ • sd_create_node‘Ceria’, ‘client’ • Node Alteration • sd_alter_node‘Ceria’, ‘ADD server’/* Becomes peer*/ • Node Removal • sd_drop_node‘Ceria’

  26. Principles Nodes Management SDBs & NDBs Management Scalable Tables & Images Management Scalable Queries Management Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work SDB & NDB Management • SDB Creation • sd_create_scalable_database ‘SkyServer’, ‘Dell1’, ‘Server’,2 /* Creates the primary SkyServer NDB as well at Dell1*/ • SDB Alteration • sd_create_node_database‘SkyServer’, ‘Ceria’, ‘Client’ • SDB Removal • sd_drop_scalable_database‘SkyServer’

  27. Principles Nodes Management SDBs & NDBs Management Scalable Tables & Images Management Scalable Queries Management Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Scalable Tables Management • Scalable Table Creation • sd_create_table‘PhotoObj (Objid BIGINT PRIMARY KEY..)’, 10000 • Scalable Table Alteration • sd_alter_table ‘PhotoObj ADD t INT’, 1000 • sd_create_index‘run_index ON Photoobj (run)’ • sd_drop_index‘PhotoObj.run_index’ • Scalable Table Removal • sd_drop_table‘PhotoObj’

  28. Principles Nodes Management SDBs & NDBs Management Scalable Tables & Images Management Scalable Queries Management Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Images Management • Secondary Image Creation • sd_create_image‘Ceria’, ‘PhotoObj’ • Secondary Image Removal • sd_drop_image'PhotoObj’

  29. Principles Nodes Management SDBs & NDBs Management Scalable Tables & Images Management Scalable Queries Management Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Scalable Queries Management USE SkyServer /* SQL Server command */ • Scalable Update Queries • sd_insert‘INTO PhotoObj SELECT * FROM Ceria.Skyserver-S.dbo.PhotoObj’ • Scalable Search Queries • sd_select‘* FROM PhotoObj’ • sd_select‘TOP 5000 * INTO PhotoObj1 FROM PhotoObj’, 500

  30. 1. Introduction 2. State of the Art P L A N 3. SD-SQL Server Architecture 4. SD-SQL Server Application Interface 5. Implementation of SD-SQL Server 6. Performance Measurements 7. Conclusion & Future Work

  31. Image Binding Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Internal Processing Concurrency Experiments Command Processing [Litwin, Schwartz & Sahri. WDAS 2006] • Let Q a scalable query using the PhotoObj image: • sd_select‘COUNT (*) FROM PhotoObj’ Find Images in Q PhotoObj Image Adjustment Execution of Q

  32. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Internal Processing Concurrency Experiments Concurrency • SD-SQL Server processes every command as SQL distributed transaction at Repeatable Read isolation level • Much less blocking than at Serializable Level • SD-SQL Server performs the split asynchronously with the insert that triggered it • It launches the actual splitting as an asynchronous job called splitter

  33. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Internal Processing Concurrency Experiments Concurrency • Splits use exclusive locks on segments and on tuples in RP meta-table. • Shared locks on other meta-tables: Primary, NDB meta-tables • Scalable queries use basically shared locks on meta-tables and any other table involved

  34. Splitter sd_alter_table Dell3 RP Dell2 Dell1 PhotoObj Dell1.SkyServer Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Internal Processing Concurrency Experiments Concurrency: example X Exclusive Lock Waiting Shared Lock X Exclusive Lock Exclusive Lock

  35. 1. Introduction 2. State of the Art P L A N 3. SD-SQL Server Architecture 4. SD-SQL Server Application Interface 5. Implementation of SD-SQL Server 6. Performance Measurements 7. Conclusion & Future Work

  36. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Internal Processing Concurrency Experiments Experimental Environment • 6 Machines Pentium IV 1.7 GHz • RAM: 780 Mb & 1 Gb • Operating System: Windows 2K Server • Ethernet Network: max bandwidth of 1 Gb/s • Use of SQL Analyzer for editing queries • Use of SQL Profiler to take measurements

  37. Principles Nodes Management SDBs & NDBs Management Scalable Tables & Images Management Scalable Queries Management Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work The SkyServer Benchmark • We use SkyServer database as benchmark • Provided and installed at Ceria by Dr. Gray • SkyServer brings the entire database of the Sloan Digital Sky Survey, SDSS • We use of thePhotoObj table as an example scalable table • In our experiments, PhotoObjhas 158,426 tuples (about 260 MB) • Originally, it has 14 M tuples

  38. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Internal Processing Concurrency Experiments Splitting Measurements [Litwin, Sahri & Schwartz. WDAS 2004] Splitting of PhotoObj scalable table into 2, 3, 4 and 5 segments according to different capacities

  39. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Internal Processing Concurrency Experiments Image Adjustment (Q1):sd_select‘TOP 10 objid FROM PhotoObj WHERE objid not in (SELECT objid FROM PhotoObj WHERE objid <= @objidMax’ Query (Q1) execution time

  40. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Internal Processing Concurrency Experiments Comparison between SD-SQL Server and SQL Server [Litwin, Sahri & Schwartz. BNCOD 2006] (Q2):sd_select‘COUNT (*) FROM PhotoObj’ Execution time of (Q2) on SQL Server and SD-SQL Server

  41. 1. Introduction 2. State of the Art P L A N 3. SD-SQL Server Architecture 4. SD-SQL Server Application Interface 5. Implementation of SD-SQL Server 6. Performance Measurements 7. Conclusion & Future Work

  42. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Conclusion Future Work Conclusion • Scalable distributed databases with scalable tables are now a reality with SD-SQL Server • No more manual repartitioning • Unlike in any other DBS we know about • The performance analysis proves • Efficiency of our design • Immediate utility of SD-SQL Server

  43. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Conclusion Future Work Future Work • More performance measurements • With the SDSS queries • With the SkyServer benchmark of 80 Gb size • Error processing • Management of fault tolerance • Use of the high availability methods

  44. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Conclusion Future Work Future Work • Application on other DBMSs • Oracle, DB2, etc. • Use of the SD-SQL Server principles on P2P systems or Grid Computing • Use of SD-SQL Server as core component of a virtual repository of eGov documents

  45. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Conclusion Future Work Thanking • Work partly supported by • CEE Project eGov • MS Research • CEE Project ICONS

  46. Introduction State of the Art SD-SQL Server Architecture SD-SQL Server Application Interface Implementation of SD-SQL Server Conclusion & Future Work Conclusion Future Work Thank you for your Attention Soror SAHRI Soror.Sahri@dauphine.fr http://ceria.dauphine.fr/soror/soror.html