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_at_dauphine.fr Witold.litwin_at_dauphine
  .fr
• CERIA Laboratory

2
Overview

• Objective
• SDDS DBS Principles
• The Gross Architecture of SD-SQL Server
• Server Side
• Client Side
• Experimental Performance Analysis
• Conclusion Future Work

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
4
Objective

• Use the SDDS theory.
• Use the Distributed Partitioned Views of DBSs.
  

• We call the result SD-DBS Architecture
• Our prototype is SD-SQL Server.

5
SDDS Principles

• Scalable partitioning using splits of overloaded
  servers.
• Clients have  private images  of data
  partitioning.

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.

7
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
8
Gross Architecture
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.

10
SDDS Layer
Segments of a Scalable Table
DB3
DB2
DB1

Segment
Segment
Segment
Client Side
Scalable Distributed View
Scalable Distributed View

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.

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.

13
Server Side Split Mechanism
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
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.

16
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
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
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 using the
  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 nlt n then Adjust the view.
• 4.Execute Q.

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

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.

21
Experim Perf Analysis Server Side
22
Experim Perf Analysis Client Side

• Example
• Lets 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)

23
Experim Perf Analysis Client Side
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.

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
0x00002000gt0) and (status 0x0010 gt0))
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.

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.

28
END
THANKS FOR YOUR ATTENTION
Soror.sahri_at_dauphine.fr Witold.litwin_at_dauphine.
fr