Architecture and Interface of Scalable Distributed Database System SD-SQL Server

1. Architecture and Interface of Scalable Distributed Database System SD-SQL Server Soror SAHRI Witold LITWIN Thomas SCHWARTZ Soror.sahri@dauphine.frWitold.litwin@dauphine.frtjschwarz@scu.edu Ceria Laboratory Comp. Eng. Dep. Paris-Dauphine University Santa Clara U. The IASTED International Conference onDBA 2006

2. Outline • Introduction • Overall Architecture • Application Interface • Implementation • Conclusion

3. 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. Solution? Introduction A Scalable Distributed Database System: SD-DBS

4. Why SDDSs? Introduction AppliesSDDS technology to DBSs • Provide many scalable distributed partitioning schemes. • LH*, RP*, k-RP*, LH*RS… • These schemes can serve as the basis for SD-DBS architecture

5. SD-SQL Server Architecture User/Application User/Application sd_insert sd_select SD-SQL ServerManagers SD-SQLserver SD-SQLpeer SD-SQLclient SD-SQLserver LinkedSQLServers D1_T T _D1_T _D1_T _D1_T Split NDBs D2 Di Di+1 D1

6. …… Node1 Node2 Node3 Node i DB1 DB2 DB1 DB2 DB1 DB2SDB DB1SDB MDB SD-SQL Server ArchitectureNodes, SDBs, NDBs

7. SD-SQL Server ArchitectureScalable Tables: Segments • 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 • The number of segments in a scalable table is variable. • If a segment overflows, its split is triggered

8. SD-SQL Server ArchitectureScalable Tables: Split • A split occurs when an insert overflows the segmentcapacity • Splits produce other segments for a scalable table. • Each is located at a different NDB • Within the SDB • If there is not enough NDBs, splits dynamically append new ones

9. SD-SQL Server ArchitectureImages • Images hide the scalable table segments • An image is a distributed updateable partitionedviewof a scalable table • Union-all view with check constraints • An image presents the scalable table partitioning • It do not address any new segments resulted from a split • Each scalable table has only oneprimary image and one or several secondary images

10. DB1SDB N1.DB1 N2.DB1 Ni.DB1 T Scalable Table Primary Image SD-SQL Server ArchitectureImages … CREATE VIEW T AS SELECT * FROM N1.DB1._N3_T UNION ALL SELECT * FROM N2.DB1._N3_T UNION ALL SELECT * FROM Ni.DB1._N3_T

11. SD-SQL Server Application Interface • 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. INSERTsd_insert • CREATE TABLEsd_create_table

12. SD-SQL Server Application Interface • Use of the SkyServer DB as benchmark http://research.microsoft.com/~gray/SDSS • PhotoObj table as a scalable table. • PhotoObjhas 158,426 tuples (about 260 MB) • Use of the laboratory machines • Ceria, Dell1, Dell2….

13. SD-SQL Server Application InterfaceNodes 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’

14. SD-SQL Server Application InterfaceSDB/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’

15. SD-SQL Server Application InterfaceScalable Tables • 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’

16. SD-SQL Server Application InterfaceImages • Secondary Image Creation • sd_create_image‘Ceria’, ‘PhotoObj’ • sd_create_image‘Ceria2’, ‘PhotoObj’ • Secondary Image Removal • sd_drop_image'PhotoObj’

17. SD-SQL Server Application InterfaceScalable Queries USE Skyserver /* SQL Server command */ • Scalable Update Queries • sd_insert‘INTO PhotoObj SELECT * FROM Ceria5.Skyserver-S.PhotoObj’ • Scalable Search Queries • sd_select‘* FROM PhotoObj’ • sd_select ‘TOP 5000 * INTO PhotoObj1 FROM PhotoObj’, 500

18. SD-SQL Application Interface

19. SD-SQL Application InterfaceCommand Processing • SD-SQL Server uses distributed stored procedures with dynamic SQL… • SD-SQL Server processes every command as a distributed transaction at Repeatable Read isolation level • See details in the papers • “SD-SQL Server: a Scalable Distributed Database System”, CERIA Research Report 2005-12-13, December 2005  • “Overview of Scalable Distributed Database System SD-SQL Server”, Intl. Workshop on Distributed Data and Structures, WDAS 2006, Santa Clara, CA, Carleton Scientific.

20. 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 • See the “Related Work” in the paper • The performance analysis proves • Efficiency of our design • Immediate utility of SD-SQL Server

21. For more details: http://ceria.dauphine.fr Thank you.