an overview of a scalable distributed database system sd sql server
Download
Skip this Video
Download Presentation
An Overview of a Scalable Distributed Database System: SD-SQL Server

Loading in 2 Seconds...

play fullscreen
1 / 54

An Overview of a Scalable Distributed Database System: SD-SQL Server - PowerPoint PPT Presentation


  • 96 Views
  • Uploaded on

An Overview of a Scalable Distributed Database System: SD-SQL Server. Witold LITWIN, Soror SAHRI & Thomas SCHWARZ [email protected] [email protected] [email protected]

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' An Overview of a Scalable Distributed Database System: SD-SQL Server' - oakes


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
an overview of a scalable distributed database system sd sql server

An Overview of a Scalable Distributed Database System: SD-SQL Server

Witold LITWIN, Soror SAHRI &Thomas SCHWARZ

[email protected]@[email protected]

Ceria Laboratory Comp. Eng. Dep. Paris-Dauphine University Santa Clara U.

BNCOD 2006

slide2

Overview

  • Introduction
  • Architecture
  • Command Interface
  • Processing
  • Performance
  • Conclusion & Future Work
partitioned tables
Partitioned Tables
  • Most DBSs have distributed/parallel versions with partitioned tables
    • SQL Server, Oracle, DB2, MySQL, Postgres…
benefits of partitioning
BENEFITS OF PARTITIONING

BENEFITS OF PARTITIONING

Partitioning can provide tremendous benefits to a wide variety of applications by

improving manageability, performance, and availability. It is not unusual for

partitioning to improve the performance of certain queries or maintenance

operations by an order of magnitude. Moreover, partitioning can greatly simplify

common administration tasks.

Partitioning also enables database designers and administrators to tackle some of

the toughest problems posed by cutting-edge applications. Partitioning is a key

tool for building multi-terabyte systems or systems with extremely high

availability requirements.

Partitioning in

Oracle Database 10g Release 2

An Oracle White Paper

May 2005

hassle of partitioning
Hassle of Partitioning
  • DBSs require manual partitioning
    • And manual repartitioning when tables scale-up
  • DBSs do not provide dynamically scalable tables
slide6

Facts

  • http://ceria.dauphine.fr/CERIA-publications.html
    • Research Report, December 2005
      • [Oracle Database 10g]
slide7

SD-SQL Server Goal

ScalableDistributed Partitioning of Relational Tables

Scalable Distributed Database System

SD-DBS

slide8

Role Model: an SDDS

  • A scalable distributed data structure
  • Specifically designed for possibly very large data on multi-computers or networks of WSs
    • P2P & Grids in modern vocabulary
  • Why SDDS Role Model?
  • Several SDDS schemes are well-known by now:
    • LH*, RP*, k-RP*, LH*RS…Chord, VBI & most of P2P schemes
  • The domain has over 20.000 references on Google
  • An SD-DBS reuses SDDS design principles
    • With DB management specificity
slide9

SD-SQL Server

  • The first and yet the only SD-DBS
  • Implements the SD-DBS architecture
      • Litwin, Schwartz & Risch (2002)
  • Runs on Microsoft SQL Server 2000
    • Shared Nothing Architecture
    • Up to 250 nodes at present
slide10

User/Application

User/Application

sd_insert

sd_create_table

SD-SQL ServerManagers

SD-SQLserver

SD-SQLpeer

SD-SQLclient

SD-SQLserver

LinkedSQLServers

T

Split

_D1_T

_D1_T

_D1_T

NDBs

C

P

S

S

D2

Di

Di+1

D1

Gross Architecture

D1_T

slide11

……

Node1

Node2

Node3

Nodei

DB1

DB2

DB1

DB2

DB1

DB2 SDB

DB1 SDB

MDB

Nodes, SDBs & NDBs

slide12

NDB Types

  • Client NDB
    • Interfaces applications & users
    • Carries only images
      • No actual tables with application data
  • Server NDB
    • Carries actual tables
      • segments
  • Peer NDB
    • Both functions
  • Primary NDB
    • First for an SDB
slide13

Scalable (Distributed) Table

  • For the application: a table of an SDB
  • Internally: a collectionof segments behind client images
  • A segment is an SQL table
      • One per NDB of the SDB
      • Sharing the scalable table scheme
        • Except its check constraint
          • Min and Max value of the partition key
      • With size limit
      • Splitting when overflows occur
  • The check constraintspartition the partitionkey space
slide14

Scalable (Distributed) Table

  • The primary segment
    • First allocated for a new table
    • At some server or peer NDB of SDB
      • The peer creating the table
      • The primary server of the client creating the table
slide15

User/Application

User/Application

sd_insert

sd_create_table

SD-SQL ServerManagers

SD-SQLserver

SD-SQLpeer

SD-SQLclient

SD-SQLserver

LinkedSQLServers

T

Split

_D1_T

_D1_T

_D1_T

NDBs

C

P

S

S

D2

Di

Di+1

D1

Gross Architecture

D1_T

slide16

Scalable (Distributed) Table

  • For the application, it the client image is the table
    • The image name is the table name
  • Primary image
    • Created during the scalable table creation
      • at the client or peer NDB creating the table
  • Secondary images
    • Created later on
    • On other NDBs of the SDB
    • For local applications
    • By a dedicated command
      • sd_Create Image….
slide17

Scalable (Distributed) Table

  • Internally, every image is a specific SQL Server view of the segments:
    • Distributed partitioned union view

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

    • Updatable
      • Through the check constraints
    • With or without Lazy Schema Validation
slide18

User/Application

User/Application

sd_insert

sd_create_table

SD-SQL ServerManagers

SD-SQLserver

SD-SQLpeer

SD-SQLclient

SD-SQLserver

LinkedSQLServers

T

Split

_D1_T

_D1_T

_D1_T

NDBs

C

P

S

S

D2

Di

Di+1

D1

Gross Architecture

D1_T

slide19

SD SQL Server Meta-Tables

  • Store various SD-SQL Server meta-data
  • In particular about each scalable table
    • At each server or peer NDB
      • SD.Size meta-table
        • Segment capacity
          • The number of stored tuples triggering a split
          • Same for every segment at present
      • SD.RPmeta-table
        • the actual partitioning of the scalable table
          • The location of each segment
      • SD.Primary table
        • The location of the SD.RP table for each segment in the NDB
slide20

SD SQL Server Meta-Tables

  • At every client or peer NDB
    • In SD.Image table
      • All the local images
      • The name of the image
      • The type
        • Primary or secondary
      • The number of segments
        • As seen by an image
        • Not necessarily the actual one
slide21

SD SQL Server Meta-Tables

  • At every NDB
    • SD.SDBNode points towards the primary NDB
    • SD.MDBNode points towards the MDB
  • At MDB
    • SD.Nodesindicates all the available SD-SQL Server nodes
      • Over linked SQL Server nodes
    • SD.SDB describes all the SDBs
  • At every primary NDB
    • SD.NDBpoints to every NDB of the SDB
slide22

DB1 SDB

…….

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

Scalable Tables: Meta-data

T Scalable Table

SDBNode

Meta-Tables

slide23

Scalable Table Expansion

  • The number of segments in a scalable table may grow
    • An overflowing segment splits
      • Creating one or more new segments
  • A split occurs when an insert overflows the segmentcapacity
  • The trigger launches the split as an asynchronous job called splitter
    • To avoid the application level timeout
slide24

User/Application

User/Application

sd_insert

sd_create_table

SD-SQL ServerManagers

SD-SQLserver

SD-SQLpeer

SD-SQLclient

SD-SQLserver

LinkedSQLServers

T

Split

_D1_T

_D1_T

_D1_T

NDBs

C

P

S

S

D2

Di

Di+1

D1

Gross Architecture

D1_T

slide25

Scalable Table Expansion

  • Every new segment
    • Is basically created at an existing NDB that does not yet have any segments of the expanding table
      • provided there is any
    • Otherwise a new NDB is first appended to SDB
      • Provided there is an available SD SQL Server node
    • Inherits the “father”’s schema
    • Gets its new check constraint
    • Gets indexes as defined at the “father”
slide26

Single Segment SplitSingle Tuple Insert

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

S

S1

Check Constraint?

b+1

b

SELECT TOP Pi * INTO Ni.Si FROM S ORDER BY C ASC

SELECT TOP Pi * WITH TIES INTO Ni.S1 FROM S ORDER BY C ASC

split with sdb expansion

N1

N2

N3

Ni

N3

sd_create_node_database

N4

NDBDB1

NDBDB1

NDBDB1

NDBDB1

NDBDB1

NDBDB1

SDB DB1

SDB DB1

SDB DB1

sd_insert

sd_insert

sd_insert

ScalableTable T

Split with SDB Expansion

sd_create_node

sd_create_node_database

…….

slide30

Image Adjustment

  • The splits do not modify synchronously the images
  • Any split makes every image outdated
  • The client or peer verifies every image dynamically when a query to the image comes in
    • Image checking
    • Image adjustment if necessary
slide31

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
slide32

N1.DB1

N2.DB1

N3.DB1

N4.DB1

PrimaryImage

Image: 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

slide33

INSERTsd_insert

  • CREATE TABLEsd_create_table

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
slide34

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’
slide35

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’
slide36

Scalable Tables Management

  • Scalable Table Creation
    • sd_create_table‘PhotoObj (objid BIGINT PRIMARY KEY…)’, 10000
      • No foreign keys yet
  • 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’
slide37

Image Adjustment

  • Secondary Image Creation
    • sd_create_image‘Ceria’, ‘PhotoObj’
    • sd_create_image‘Dell2’, ‘PhotoObj’
  • Secondary Image Removal
    • sd_drop_image\'PhotoObj’
slide38

Scalable View

  • A view of an image
    • Involving perhaps static tables
    • And perhaps static views
  • Declared under SD-SQL Server by the SQL Server CREATE VIEW command
slide39

Scalable Queries Management

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
slide40

Image Binding

Command Processing

  • Let Q a scalable query using the PhotoObj image:
    • sd_select‘COUNT (*) FROM PhotoObj’

Find Images in Q

Check PhotoObj Image for Correctness

Adjust PhotoObj Image if needed

Send Q’to SQL Server for Execution

slide41

Concurrency

  • SD-SQL Server processes every command as SQL distributed transaction at Repeatable Read isolation level
    • Tuple level locks
    • Shared locks
    • Exclusive 2PL locks
    • Much less blocking than the Serializable Level
slide42

Concurrency

  • Splits use exclusive locks on segments and 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
  • All the conccurent executions can be shown serializable
slide43

Splitter

sd_alter_table

Dell1

RP

Dell2

Dell3

PhotoObj

Dell1.SkyServer

Concurrency: Example

X

Exclusive Lock

Waiting

Shared Lock

X

Exclusive Lock

Exclusive Lock

experimental environment
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
the skyserver benchmark
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 almost 159 K tuples (about 260 MB)
      • Originally, it has 14 M tuples
slide46

Split Time

Splitting PhotoObj with 160 k tuplesinto 2…5 segments, according to segment capacity

split time
Split Time

Splitting PhotoObj with 160 k tuples and indexes into 2… 5 segments according to segment capacity

split time analysis
Split Time Analysis
  • Longer split time may timeout a query put on wait
  • Future solution: Incremental Splitting
    • The splitter moves tuples by an increment at a time
      • Let us say 1000 tuples
    • Then ends up by calling upon itself
      • The query may proceed as the splitter releases the exclusive lock on the RP tuple
    • The process continues for next increment etc as long as there are tuples to move
slide49

Image Adjustment

(Q)sd_select‘COUNT (*) FROM PhotoObj’

Query (Q1) execution time

slide50

Scalable View Processing

(Q)sd_select‘COUNT (*) FROM Ti’

slide51

SD-SQL Server / SQL Server

  • (Q):sd_select‘COUNT (*) FROM PhotoObj’

Execution time of (Q) on SQL Server and SD-SQL Server

slide52

Conclusion

  • Scalable tables are now a reality
    • with SD-SQL Server
    • No more manual repartitioning
      • Unlike in any other DBS we know about
  • Performance analysis proves
    • Efficiency of our design
    • Immediate utility of SD-SQL Server
slide53

Future Works

  • SQL Server 2005 portage
  • Incremental splits
  • Virtual repository of eGov documents
    • SQL Server XML View
  • Foreign keys for scalable tables
  • More performance measurements
    • Skyserver & other benchmarks
  • Error processing
  • High availability
    • Parity segments
  • Application to other DBMSs
    • Oracle, DB2, etc.
slide54
Thank you for your attentionWork performed between 2003 -2006 Partly founded byMsResearchEEC Icons ProjectEEC E-Gov Project
ad