Implementing SD-SQL Server: a Scalable Distributed Database System

1. Implementing SD-SQL Server: a Scalable Distributed Database System Soror SAHRI Witold LITWIN Soror.sahri@dauphine.frWitold.litwin@dauphine.fr CERIA Laboratory WDAS Workshop, Lausanne, Jul. 9th

2. Overview • Objective • SDDS & DBS Principles • The Gross Architecture of SD-SQL Server • Server Side • Client Side • Experimental Performance Analysis • Conclusion & Future Work WDAS Workshop, Lausanne, Jul. 9th

3. Objective • Most of DBSs have parallel versions • SQL Server, Oracle, DB2 • DBSs do not provide dynamically scalable tables. • Manual reorganizing if a table scale-up. Add a layer on DBSs’ Architecture WDAS Workshop, Lausanne, Jul. 9th

4. Result Objective • Use the SDDS theory. • Use the Distributed Partitioned Views of DBSs. • We call the result SD-DBSArchitecture • Our prototype is SD-SQL Server. WDAS Workshop, Lausanne, Jul. 9th

5. SDDS Principles • Scalable partitioning using splits of overloaded servers. • Clients have private images of data partitioning. WDAS Workshop, Lausanne, Jul. 9th

6. Distributed & Partitioned Views • Distributed & Partitioned Views allow the data in a large table to be split into smaller member tables in distributed servers. • The data is partitioned between the member tables based on ranges of data values. • Horizontal partitioning. • The data ranges for each member table are defined in a CHECK constraint specified on the partitioning key. • A view that uses UNION ALL to combine selects of all the member tables into a single result set is then defined. WDAS Workshop, Lausanne, Jul. 9th

7. Scalable data Tables Partitioned & distributed views Images SD-SQL Server • Marriage of SDDS & DBS principles. • Application to SQL Server 2000 DBS. SDDS SQL Server 2000 SD-SQL Server Scalable tables Scalable Distributed Views WDAS Workshop, Lausanne, Jul. 9th

8. Application Application Application SD-DBS SDDSlayer SD-DBS SD-DBS Di D1 D2 SQLServerlayer …... SQL Server SQL Server SQL Server Gross Architecture WDAS Workshop, Lausanne, Jul. 9th

9. SDDS Layer The SDDS Layer is composed of: • Server Side: • manages the scalable tables, • and the split mechanism. • Client Side: • manages the scalable distributed views. WDAS Workshop, Lausanne, Jul. 9th

10. Server Side SDDS Layer Segments of a Scalable Table DB1 DB2 DB3 …… Segment Segment Segment Client Side Scalable Distributed View Scalable Distributed View …… WDAS Workshop, Lausanne, Jul. 9th

11. Server Side: Scalable Tables • The creation of a scalable table is similar to that of any table. • With the usual CREATE TABLE statement. • Each scalable table has a CHECK CONSTRAINT. • Use of additional clauses related to scalable tables and store them in meta-tables: • The limit size is stored in the SD-SIZE meta-table. • The available servers are stored in the SD-SITE meta-table. • The actual partitioning is stored in SD-C and SD-RP meta-tables. WDAS Workshop, Lausanne, Jul. 9th

12. Server Side: Scalable Tables Example: The creation of the scalable table Customer calls the stored procedure CREATE_SCALABLE_TABLE: EXEC CREATE_SCALABLE_TABLE ‘CREATE TABLE Customer (Customerid numeric PRIMARY KEY)’, 100 • A trigger will be created on the Customer table. It launches the split when Customer exceeds its maximal size. WDAS Workshop, Lausanne, Jul. 9th

13. Server1.DB_1 Server2.DB_1 Customer Customer ……… Split Split SD_SITE SD_SIZE SD_SITE SD_SIZE SD_C SD_RP SD_C SD_RP Meta-tables Meta-tables Server Side: Split Mechanism WDAS Workshop, Lausanne, Jul. 9th

14. Server Side: Split Mechanism Server1.DB_1 Server2.DB_1 Customer Customer Split SD_C SD_RP SD_C SD_RP Server1.DB_1 Server2.DB_1 Server1.DB_1 Server1.DB_1 Meta-tables Meta-tables WDAS Workshop, Lausanne, Jul. 9th

15. Client Side • Scalable & distributed view definition is located at each SD-SQL Server client using the scalable table. • Clients can have different SD views of the same scalable table. • Not include all the existing segments. • A client meta-table C-Image contains the number of segments of each SD view on each client. WDAS Workshop, Lausanne, Jul. 9th

16. 2 1 Customer Customer Client Side Server1.DB_1 Server2.DB_1 C-Image C-Image Customer Customer SELECT * FROM Server1.DB_1.dbo.Customer UNION ALL SELECT * FROM Server2.DB_1.dbo.Customer SELECT * FROM Server1.DB_1.dbo.Customer Customer_View Customer_View WDAS Workshop, Lausanne, Jul. 9th

17. Client Side: View Adjustment Find all the scalable tables in Q Check each scalable view Adjust the outdate ones Pass Q to DBS for usual execution WDAS Workshop, Lausanne, Jul. 9th

18. Client Side: View Adjustment Example: Q: SELECT * FROM Server2.DB_1.dbo.Customer_view, T 1.Find the scalable tables in Q: Customer_view 2.Check the correctness of the view usingthe SD-RP and C-Image meta-tables. • Count Customer segments in SD-RP as n. • Check n and n’ the number in C-Image meta-table. 3. If n’< n then Adjust the view. 4.Execute Q. WDAS Workshop, Lausanne, Jul. 9th

19. Client Side: Query Processing The SQL commands used for the application are: • SELECT, INSERT, UPDATE , DELETE. • Each command is executed by a stored procedure. • Example: EXEC SELECT_VIEW_SITE ‘select * from Customer_view’ • CREATE_SCALABLE_TABLE • ALTER_SCALABLE_TABLE • DROP_SCALABLE_TABLE • CREATE_SCALABLE_VIEW • DROP_SCALABLE_VIEW WDAS Workshop, Lausanne, Jul. 9th

20. Experimental Performance Analysis Test environment: • The hardware consisted of 1.8 GHz P4 PCs, connected through 1Gbps Ethernet. • The experiments are used with the Customer table. • The timing of the operations are measured using the SQL Profiler. WDAS Workshop, Lausanne, Jul. 9th

21. centralized split distributed split 3000 2000 Split time (ms) 1000 0 100 1000 10000 Segment Capacity Experim Perf Analysis: Server Side WDAS Workshop, Lausanne, Jul. 9th

22. Experim Perf Analysis: Client Side Example: • Let’s the Customer_view definition: CREATE VIEW Customer_view AS SELECT * FROM Server1.DB_1.dbo.Customer. • On Server2.DB_1, we execute Q: INSERT INTO Customer_view VALUES (25) WDAS Workshop, Lausanne, Jul. 9th

23. Query execution + View Query execution View update 150 100 Exécution Time (ms) 50 0 100 1000 10000 Segment Capacity Experim Perf Analysis: Client Side WDAS Workshop, Lausanne, Jul. 9th

24. Experim Perf Analysis:SkyServer Database • Use of the SkyServer Database from the website: http://research.microsoft.com/~gray/SDSS • Creation of PhotoObj table as a scalable table. • Photoobj has 158 426 rows and 400 columns. Its size is 506 MB. • Generation of two segments with the PhotoObj’ split. WDAS Workshop, Lausanne, Jul. 9th

25. Experim Perf Analysis: SkyServer Database • The split time of The PhotoObj table is about 1mn. • The execution time of the query 44 sec without the view update and 45 sec with the view update. select * from PhotoObj_view WHERE (status & 0x00001000 = 0) and NOT ( (status & 0x00002000>0) and (status & 0x0010 >0)) WDAS Workshop, Lausanne, Jul. 9th

26. Conclusion • Scalable Tables are now Reality • For SD-SQL Server at least at present. • Dynamic data partitioning. • Splitting time is practical • Small for small segments, larger for larger segments…. • Sub-linear with the segment size. • Time to test a scalable view or to adjust it is negligible. • The prototype is available in CERIA Lab. WDAS Workshop, Lausanne, Jul. 9th

27. Future work • More complex SQL queries: • With subqueries, views, aliases… • Complete th SQL Libarary. • Use of indexes to optimize the execution time of queries. • More performance analysis with the SkyServer database. WDAS Workshop, Lausanne, Jul. 9th

28. END THANKS FOR YOUR ATTENTION Soror.sahri@dauphine.frWitold.litwin@dauphine.fr WDAS Workshop, Lausanne, Jul. 9th