Implementing SD-SQL Server: a Scalable Distributed Database System - PowerPoint PPT Presentation

About This Presentation
Title:

Implementing SD-SQL Server: a Scalable Distributed Database System

Description:

Title: Query Optimization: From SQL to XQuery Author: Kuznetsov Last modified by: witold Created Date: 8/28/2003 8:11:03 AM Document presentation format – PowerPoint PPT presentation

Number of Views:92
Avg rating:3.0/5.0
Slides: 29
Provided by: Kuz2
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com