An Overview of a Scalable Distributed Database System
An Overview of a Scalable Distributed Database System: SD-SQL Server Witold LITWIN, Soror SAHRI & Thomas SCHWARZ Witold. litwin@dauphine. fr Soror. sahri@dauphine. fr Ceria Laboratory Paris-Dauphine University BNCOD 2006 tjschwarz@scu. edu Comp. Eng. Dep. Santa Clara U.
Overview 1. Introduction 2. Architecture 3. Command Interface 4. Processing 5. Performance 6. Conclusion & Future Work BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Partitioned Tables ¢ Most DBSs have distributed/parallel versions with partitioned tables l SQL Server, Oracle, DB 2, My. SQL, Postgres… BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
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 10 g Release 2 An Oracle White Paper May 2005 BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Hassle of Partitioning ¢ DBSs require manual partitioning l ¢ And manual repartitioning when tables scale-up DBSs do not provide dynamically scalable tables BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Facts § http: //ceria. dauphine. fr/CERIA-publications. html § Research Report, December 2005 § [Oracle Database 10 g] BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
SD-SQL Server Goal Scalable Distributed Partitioning of Relational Tables Scalable Distributed Database System SD-DBS BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Role Model: an SDDS ¢ ¢ A scalable distributed data structure Specifically designed for possibly very large data on multi-computers or networks of WSs l P 2 P & Grids in modern vocabulary à Why SDDS Role Model? ¢ ¢ ¢ Several SDDS schemes are well-known by now: l LH*, RP*, k-RP*, LH*RS…Chord, VBI & most of P 2 P schemes The domain has over 20. 000 references on Google An SD-DBS reuses SDDS design principles l With DB management specificity BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
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 l Up to 250 nodes at present l BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Gross Architecture User/Application sd_insert sd_create_table SD-SQL Server Managers Linked SQL Servers SD-SQL client SD-SQL server S S C D 1_T P T _D 1_T NDBs SD-SQL peer D 1 BNCOD 2006 – July 18 th, 2006 D 2 _D 1_T Di Split _D 1_T Di+1 An Overview of a Scalable Distributed Database System: SD-SQL Server
Nodes, SDBs & NDBs MDB Node 1 Node 2 DB 1 DB 2 DB 1 SDB DB 2 SDB BNCOD 2006 – July 18 th, 2006 Node 3 DB 1 DB 2 …… Nodei DB 1 An Overview of a Scalable Distributed Database System: SD-SQL Server
NDB Types l Client NDB • Interfaces applications & users • Carries only images • No actual tables with application data § Server NDB • Carries actual tables • segments l Peer NDB Primary NDB § First for an SDB • Both functions BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Scalable (Distributed) Table ¢ ¢ ¢ For the application: a table of an SDB Internally: a collection of 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 constraints partition the partition key space BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Scalable (Distributed) Table ¢ The primary segment First allocated for a new table l At some server or peer NDB of SDB l • The peer creating the table • The primary server of the client creating the table BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Gross Architecture User/Application sd_insert sd_create_table SD-SQL Server Managers Linked SQL Servers SD-SQL client SD-SQL server S S C D 1_T P T _D 1_T NDBs SD-SQL peer D 1 BNCOD 2006 – July 18 th, 2006 D 2 _D 1_T Di Split _D 1_T Di+1 An Overview of a Scalable Distributed Database System: SD-SQL Server
Scalable (Distributed) Table ¢ For the application, it the client image is the table l ¢ The image name is the table name Primary image l Created during the scalable table creation • at the client or peer NDB creating the table ¢ Secondary images Created later on l On other NDBs of the SDB l For local applications l By a dedicated command l • sd_Create Image…. BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Scalable (Distributed) Table ¢ Internally, every image is a specific SQL Server view of the segments: l Distributed partitioned union view CREATE VIEW T AS SELECT * FROM N 2. DB 1. SD. _N 1_T UNION ALL SELECT * FROM N 3. DB 1. SD. _N 1_T UNION ALL SELECT * FROM N 4. DB 1. SD. _N 1_T l Updatable • Through the check constraints l With or without Lazy Schema Validation BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Gross Architecture User/Application sd_insert sd_create_table SD-SQL Server Managers Linked SQL Servers SD-SQL client SD-SQL server S S C D 1_T P T _D 1_T NDBs SD-SQL peer D 1 BNCOD 2006 – July 18 th, 2006 D 2 _D 1_T Di Split _D 1_T Di+1 An Overview of a Scalable Distributed Database System: SD-SQL Server
SD SQL Server Meta-Tables ¢ ¢ Store various SD-SQL Server meta-data In particular about each scalable table l 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. RP meta-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 BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
SD SQL Server Meta-Tables ¢ At every client or peer NDB l 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 BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
SD SQL Server Meta-Tables ¢ At every NDB l l ¢ ¢ SD. SDBNode points towards the primary NDB SD. MDBNode points towards the MDB At MDB l SD. Nodes indicates all the available SD-SQL Server nodes • Over linked SQL Server nodes l SD. SDB describes all the SDBs At every primary NDB l SD. NDB points to every NDB of the SDB BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Scalable Tables: Meta-data DB 1 SDB N 1. DB 1 N 2. DB 1 N 3. DB 1 ……. Ni. DB 1 Primary T Scalable Table Size SDBNodes N 1. DB 1 1000 Ni. DB 1 N 1. DB 1 RP N 2. DB 1 N 3. DB 1 Meta-Tables BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Scalable Table Expansion ¢ The number of segments in a scalable table may grow l An overflowing segment splits • Creating one or more new segments A split occurs when an insert overflows the segment capacity ¢ The trigger launches the split as an asynchronous job called splitter ¢ l To avoid the application level timeout BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Gross Architecture User/Application sd_insert sd_create_table SD-SQL Server Managers Linked SQL Servers SD-SQL client SD-SQL server S S C D 1_T P T _D 1_T NDBs SD-SQL peer D 1 BNCOD 2006 – July 18 th, 2006 D 2 _D 1_T Di Split _D 1_T Di+1 An Overview of a Scalable Distributed Database System: SD-SQL Server
Scalable Table Expansion ¢ Every new segment l Is basically created at an existing NDB that does not yet have any segments of the expanding table • provided there is any l Otherwise a new NDB is first appended to SDB • Provided there is an available SD SQL Server node Inherits the “father”’s schema l Gets its new check constraint l Gets indexes as defined at the “father” l BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Single Segment Split Single Tuple Insert Check Constraint? p=INT(b/2) C( S)= { c: c < h = c (b+1 -p)} C( S 1)={c: c > = c (b+1 -p)} b+1 b b+1 -p S S 1 SELECT TOP Pi * INTO FROM S ORDER BYBY CC ASC SELECT TOP Pi * WITH TIES INTONi. Si Ni. S 1 FROM S ORDER ASC BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Single Segment Split Bulk Insert Single segment split BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Multi-Segment Split Bulk Insert Multi-segment split BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Split with SDB Expansion sd_create_node_database sd_create_node N 1 N 2 N 3 N 4 Ni NDB NDB DB 1 NDB DB 1 SDB DB 1 sd_insert ……. Scalable T BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
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 l Image adjustment if necessary l BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Image Adjustment ¢ ¢ Get the number of segments presented in the image, N 1 Get the number of segments of the scalable table, N 2 Compare N 1 and N 2: If N 1<N 2 then Image Adjustment l Alter the partitioned view definition BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Image: Example DB 1 SDB N 1. DB 1 Primary Image N 2. DB 1 N 3. DB 1 N 4. DB 1 T Image T Scalable Table CREATE VIEW T AS SELECT * FROM N 2. DB 1. SD. _N 1_T UNION ALL SELECT * FROM N 3. DB 1. SD. _N 1_T UNION ALL SELECT * FROM N 4. DB 1. SD. _N 1_T BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Application Interface § The application interface manipulates scalable tables through SD-SQL Server commands § The SD-SQL Server commands start with ‘sd_’ to distinguish from SQL Server commands for static tables INSERT sd_insert CREATE TABLE BNCOD 2006 – July 18 th, 2006 sd_create_table An Overview of a Scalable Distributed Database System: SD-SQL Server
Nodes Management ¢ Node Creation sd_create_node ‘Dell 1’ /* Server by default */ l sd_create_node ‘Ceria’, ‘client’ l ¢ Node Alteration l sd_alter_node ‘Ceria’, ‘ADD server’ /* Becomes peer*/ ¢ Node Removal l sd_drop_node ‘Ceria’ BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
SDB & NDB Management ¢ SDB Creation l sd_create_scalable_database ‘Sky. Server’, ‘Dell 1’, ‘Server’, 2 /* Creates the primary Sky. Server NDB as well at Dell 1*/ ¢ SDB Alteration l ¢ sd_create_node_database ‘Sky. Server’, ‘Ceria’, ‘Client’ SDB Removal l sd_drop_scalable_database ‘Sky. Server’ BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Scalable Tables Management ¢ Scalable Table Creation l sd_create_table ‘Photo. Obj (objid BIGINT PRIMARY KEY…)’, 10000 • No foreign keys yet ¢ Scalable Table Alteration sd_alter_table ‘Photo. Obj ADD t INT’, 1000 l sd_create_index ‘run_index ON Photoobj (run)’ l sd_drop_index ‘Photo. Obj. run_index’ l ¢ Scalable Table Removal l sd_drop_table ‘Photo. Obj’ BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Image Adjustment ¢ Secondary Image Creation sd_create_image ‘Ceria’, ‘Photo. Obj’ l sd_create_image ‘Dell 2’, ‘Photo. Obj’ l ¢ Secondary Image Removal l sd_drop_image 'Photo. Obj’ BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Scalable View ¢ A view of an image Involving perhaps static tables l And perhaps static views l… l ¢ Declared under SD-SQL Server by the SQL Server CREATE VIEW command BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Scalable Queries Management USE Sky. Server ¢ Scalable Update Queries l ¢ /* SQL Server command */ sd_insert ‘INTO Photo. Obj SELECT * FROM Ceria 5. Skyserver-S. Photo. Obj’ Scalable Search Queries sd_select ‘* FROM Photo. Obj’ l sd_select ‘TOP 5000 * INTO Photo. Obj 1 FROM Photo. Obj’, 500 l BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Command Processing ¢ Let Q a scalable query using the Photo. Obj image: l sd_select ‘COUNT (*) FROM Photo. Obj’ Find Images in Q Image Binding Check Photo. Obj Image for Correctness Adjust Photo. Obj Image if needed Send Q’ to SQL Server for Execution BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Concurrency § SD-SQL Server processes every command as SQL distributed transaction at Repeatable Read isolation level § Tuple level locks § Shared locks § Exclusive 2 PL locks § Much less blocking than the Serializable Level BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
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 BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Concurrency: Example Splitter X Exclusive Lock Dell 1 RP sd_alter_table Dell 2 Waiting Shared Lock Dell 3 X Exclusive Lock Photo. Obj Dell 1. Sky. Server BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Experimental Environment § 6 Machines Pentium IV 1. 7 GHz RAM: 780 Mb & 1 Gb § Operating System: Windows 2 K Server § Ethernet Network: max bandwidth of 1 Gb/s § § Use of SQL Analyzer for editing queries § Use of SQL Profiler to take measurements BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
The Sky. Server Benchmark ¢ We use Sky. Server database as benchmark l ¢ Provided and installed at Ceria by Dr. Gray Sky. Server brings the entire database of the Sloan Digital Sky Survey, SDSS We use of the Photo. Obj table as an example scalable table l In our experiments, Photo. Obj has almost 159 K tuples (about 260 MB) l • Originally, it has 14 M tuples BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Split Time Splitting Photo. Obj with 160 k tuples into 2… 5 segments, according to segment capacity BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Split Time Splitting Photo. Obj with 160 k tuples and indexes into 2… 5 segments according to segment capacity BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Split Time Analysis ¢ Longer split time may timeout a query put on wait ¢ Future solution: Incremental Splitting l The splitter moves tuples by an increment at a time • Let us say 1000 tuples l Then ends up by calling upon itself • The query may proceed as the splitter releases the exclusive lock on the RP tuple l The process continues for next increment etc as long as there are tuples to move BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Image Adjustment (Q) sd_select ‘COUNT (*) FROM Photo. Obj’ Query (Q 1) execution time BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Scalable View Processing (Q) sd_select ‘COUNT (*) FROM Ti’ BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
SD-SQL Server / SQL Server § (Q): sd_select ‘COUNT (*) FROM Photo. Obj’ Execution time of (Q) on SQL Server and SD-SQL Server BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Conclusion ¢ Scalable tables are now a reality with SD-SQL Server l No more manual repartitioning l • Unlike in any other DBS we know about ¢ Performance analysis proves Efficiency of our design l Immediate utility of SD-SQL Server l BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Future Works ¢ ¢ ¢ SQL Server 2005 portage Incremental splits Virtual repository of e. Gov documents l ¢ ¢ Foreign keys for scalable tables More performance measurements l Skyserver & other benchmarks Error processing High availability l ¢ SQL Server XML View Parity segments Application to other DBMSs l Oracle, DB 2, etc. BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
Thank you for your attention Work performed between 2003 -2006 Partly founded by Ms. Research EEC Icons Project EEC E-Gov Project BNCOD 2006 – July 18 th, 2006 An Overview of a Scalable Distributed Database System: SD-SQL Server
- Slides: 54