asynchronous view maintenance for vlsd databases under the guidance of prof s sudarshan l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Asynchronous View Maintenance for VLSD Databases Under the guidance of : Prof. S. Sudarshan PowerPoint Presentation
Download Presentation
Asynchronous View Maintenance for VLSD Databases Under the guidance of : Prof. S. Sudarshan

Loading in 2 Seconds...

play fullscreen
1 / 41

Asynchronous View Maintenance for VLSD Databases Under the guidance of : Prof. S. Sudarshan - PowerPoint PPT Presentation


  • 214 Views
  • Uploaded on

Asynchronous View Maintenance for VLSD Databases Under the guidance of : Prof. S. Sudarshan. Purva P. Joshi 08305907. Introduction. Large scale shared nothing databases (PNUTS, BigTable, Dynamo, Cassandra) trade query expressiveness for scalability and performance.

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 'Asynchronous View Maintenance for VLSD Databases Under the guidance of : Prof. S. Sudarshan' - maxim


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
asynchronous view maintenance for vlsd databases under the guidance of prof s sudarshan

Asynchronous View Maintenance for VLSD DatabasesUnder the guidance of : Prof. S. Sudarshan

Purva P. Joshi

08305907

introduction
Introduction
  • Large scale shared nothing databases (PNUTS, BigTable, Dynamo, Cassandra) trade query expressiveness for scalability and performance.
  • Support primary key operations like:
    • Range scans
    • Single record look-ups
  • Indexes and Materialised Views are the only feasible way for such D/b s to handle :
    • Equijoins
    • Aggregate queries
    • Look-ups on secondary attributes
motivation for asynchronous updates
Motivation for Asynchronous Updates

Asynchronous updates: Updates to the view are made “after” updates to the base table.

Synchronous updates: Updates to the view are made together with updates to the base table.

The data is highly distributed across different geographical areas and hence synchronous updates are extremely expensive and lead to higher query response times.

asynchronous v s synchronous view maintenance
Asynchronous v/s Synchronous View Maintenance
  • Advantages of Asynchronous View Maintenance :
    • Decrease in latency of writing to the database.
    • Less cross-server communication.
  • Approach followed : Deferred View Maintenance
  • Challenges:
    • Scalability
    • View must be updated even in presence of failures (aborting an update is not possible)
    • Efficient view replication across distant data centres
pnuts architectural overview
PNUTS : Architectural Overview

Query

Processor

Update

Processor

LVT

Maintainer

RVT

Maintainer

Storage

Clients

API

Read Query Request

Storage server

Query

Processor

Query Routers

Log Manager

Request forwarded to server

Partition

Controller

Storage servers

pnuts architectural overview6
PNUTS : Architectural Overview

Query

Processor

Update

Processor

LVT

Maintainer

RVT

Maintainer

Storage

Clients

To

remote

Data centers

asynchronous-

-ly

API

Write Query Request

Storage server

Query

Processor

Query Routers

Log Manager

Request forwarded to server

Write-ahead log

Partition

Controller

Storage servers

publish subscribe model and distribution
Publish-Subscribe Model and Distribution

Updates are committed when they are published to the YMB

YMB propagates the update to different regions and applies to the replicas

Steps to ensure no loss occurs before updates are applied to database :

Logging to multiple disks on different servers

Wide area replication : messages are relayed across YMB clusters in separate geographical areas for delivery to local subscribers.

data and query model
Data and Query Model
  • Tables :
    • Items (ItemId, Name, Category, Description, Price)
    • Reviews (ReviewId, ItemId, Rating, Text, ReviewerId)
  • Queries not supported
    • Join and Group-by aggregate queries against base tables
  • Consistency
    • Per-record consistency
    • No ACID-style guarantee
    • Range and table scans do not guarantee a consistent snapshot of the data
record mastery and record timelines
Record mastery and Record timelines
  • Record mastery
    • Per-record master-ship
    • All updates first applied to master and then propagated to replicas
    • Master record holds locks and prevents conflicting writes
    • Log manager delivers updates made to a record to all replicas in appropriate order w.r.t the master record
  • Record timeline
    • Each point on timeline identified by a record version
    • Matser record : latest version
mechanisms for view maintenance rvts
Mechanisms for View Maintenance : RVTs
  • Remote View Tables (RVTs)
    • Each view is stored in an independent PNUTS table – separate from the base table (the table on which the view is defined)
    • Partitioned based on its own key, which is different from the base table key .
    • So, view records are likely to be stored on different servers than the corresponding base table records.
    • Maintained “asynchronously” : can be stale
  • E.g. : CREATE VIEW ByPrice SELECT Price, ItemId, Name, Category FROM Items;
    • Partitioned on View key : (Price, ItemId)
maintaining rvts
Maintaining RVTs

Query

Processor

Update

Processor

LVT

Maintainer

RVT

Maintainer

Storage

Clients

To

remote

Data centers

asynchronous-

-ly

API

Update base table Request

Storage server S1

Query

Processor

Query Routers

Log Manager

Request forwarded to server

Write-ahead log plus “Information necessary to update views (old field values)”

RVT

Maintainer

Partition

Controller

Storage servers

RVT Mainainer of S1 subscribes to the log manager

to get updates for records in S1

mechanisms for view maintenance lvts
Mechanisms for View Maintenance : LVTs
  • Local View Tables (LVTs)
    • Construct a view over each base table partition
    • View records are on same partition as corresponding base records.
    • Maintained “synchronously” : High query cost
  • E.g. : CREATE VIEW ByPrice AS SELECT Price, count(*) FROM Items GROUP BY Price;
  • Items of a price range (say, “7000”) will be scattered across partitions.
  • Need to retrieve LVT records from all such partitions and add them to get the total sum.
combining rvts and lvts
Combining RVTs and LVTs
  • Main application of LVTs : materialize aggregates over RVTs.
  • E.g. :

LVT on RVT

RVT on Base table

Base Table

view types
View Types
  • Indexes
  • Equi-joins
  • Selections
  • Group-by-aggregates
view types indexes
View Types : Indexes
  • Index : Projection and re-ordering of a base table
  • E.g. RVT : ByPrice(Price, ItemId, Name, Category)
    • Name, Category : extra attributes
  • Addition of new record to base table:
view types indexes16
View Types : Indexes
  • Updation of View key attribute:
  • Updation of non view-key attribute:
  • Index as LVT : search expensive
view types equi joins
View Types : Equi-joins
  • E.g. : Joining Items and Reviews tables
  • RVT on two ItemId indexes (one each for tables Items and Reviews), sorted on ItemId
  • Actual join performed at query time
view types equi joins18
View Types : Equi-joins
  • Co-locating records to be joined in the same partitions but deferring the actual join until query time.
  • Outer-join
  • Can join three or more tables on the same attribute via view
  • LVTs are not appropriate : different base table partitions
view types selections
View Types : Selections

Subset of base table records

E.g. : CREATE VIEW ELECTRONICITEMS

SELECT * FROM Items WHERE Category=‘Electronics’ ;

Implemented as RVTs

LVTs can be used , but cost is high

Not widely used

view types group by aggregates
View Types : Group-by aggregates

RVTs : replica of a single record; cannot be used

LVTs are useful : synchronously updated

Design issue:

LVTs on base table

LVTs on RVT of base table

Maintaining aggregates : synchronous update when base record changes

For min (max) queries, scan on partition after update to get new min (max)

aggregate lvt and query example
Aggregate LVT and Query example

RVT on Base table

LVT on RVT

LVT on Base

Base Table

unsupported view definitions
Unsupported View Definitions
  • Joins of three or more tables on different attributes
    • Non-co-located records
  • Joins other than equi-joins
    • Expensive
  • Full SQL-99 aggregate functions
    • Only SQL-92 aggregates : count, min, max, sum, avg
    • No support for percentile, standard deviation
design issues
Design Issues
  • View Maintenance : by Client or by System
    • Maintenance by clients possible
    • Hiding logs : maintenance by system preferred
  • Updates’ frequency
    • Synchronous
      • View update as part of base table transaction - approach followed by LVTs
    • Lazy
      • approach followed by RVTs
    • Batched lazy
      • Group commit
    • Periodic view refresh :
      • high throughput
      • high staleness
      • wasted effort
consistency model
Consistency Model

v. 2

v. 5

v. 1

v. 3

v. 4

v. 6

v. 7

v. 8

Time

Generation 1

  • Review of record-level consistency model
  • Figure modifed from the original at “PNUTS: Yahoo!’s Hosted Data Serving Platform” slides by : Brian F. Cooper, Raghu Ramakrishnan, Utkarsh Srivastava, Adam Silberstein, Philip Bohannon,et al

Update($20)

Update($15)

Insert (“toaster”,$10)

ReadAny=$10

ReadLatest=$15

ReadCritical(5)=$20

maintaining view consistency
Maintaining View Consistency
  • Base-consistency model : timelines of all records are independent
  • Views : Multiple records are connected to base records
  • A view record “vr” is depedent on base record “r” on which it is defined, while the base record “r” is incident on “vr”
  • Indexes, selections, equi-joins : one-to-one
  • Group-by-aggregates : many-to-one
maintaining view consistency26
Maintaining View Consistency

v. 2

v. 5

v. 1

v. 3

v. 4

v. 6

v. 7

v. 8

Time

Generation 1

  • E.g. :

Update Review(1,’BAD’,1)

Insert Review(1,’GOOD’,5)

ReadCritical(v.6) = ‘BAD’

BAD

GOOD

5

1

cost of view maintenance
Cost of View Maintenance
  • For an update to a base record “br”
    • Indexes : at most two updates (updates to view key)
    • Equi-joins : similar to indexes
    • Selections : single view update if selection condition is satisfied/not fulfilled by “br”
    • Group-by-aggregates : for sum/count, updation needs only the change in the value of aggregate field of “br” and value off grouped attribute
  • Log record for “br” holds required information
read consistency for views rvts
Read Consistency for Views : RVTs
  • Single-record Reads : one-to-one views
    • consistency guarantees are same as base table views : ReadAny(vr), ReadCritical(vr,v’), ReadLatest(vr)
    • No need for separate version numbers for view records
    • Routing to ensure readlatest returns correct value.
  • Single-record Reads : many-to-one views
    • Multiple base records are incident on a single view record
    • ReadAny : any version of base records
    • ReadCritical : Specific versions of a certain subset of base records (mentioned via a vector) and ReadAny for all other base records
      • Easy since base record versions are available in the RVT/base table on which the aggregate LVT is defined
    • ReadLatest : accessing base table
read consistency for views cost
Read Consistency for Views : Cost
  • ReadAny : may return ver.0 (if record is absent)
  • ReadCritical : in case of stale record, read base table master
  • ReadLatest :
    • High cost for RVTs
    • Must access master base table every time
    • Cheaper than scanning the entire table
  • LVTs are always up-to-date w.r.t local replicas
  • LVT-on-RVT :
    • ReadCritical – cheap
    • ReadLatest - expensive
read consistency for views range scans
Read Consistency for Views : Range Scans
  • Range scanning over views
    • Stale records, Missing records
  • Challenges due to insert and delete during View Maintenance – asynchronous propagation
    • Insert arrives before delete : a record may appear twice
    • Delete appears before insert : missing record
  • Solution
    • Filter out multiple records that correspond to the same base record
    • Retain “tombstones” during deletes
      • Look-up base-table record using key stored in tombstones
      • Needs garbage collection
      • Not yet implemented in PNUTS
evaluation
Evaluation
  • View Maintenance cost measured on
    • Latency : reasonable impact
    • Throughput : decreases
    • Staleness
  • Setup : C++ and Linux/FreeBSD
  • Evaluation of costs
    • 10 GB data on each server
    • MySQL buffer pool – 2GB
    • 90% reads served from cache
    • Thin views (indexed attribute and record primary key)
    • I/O bound
experiment 1 varying view type
Experiment 1: Varying View Type
  • Need to provide enough capacity to accommodate extra view maintenance work
experiment 2 varying read write workload
Experiment 2: Varying Read/Write Workload

Latency increases with increase in write percentage

experiment 3 varying no of views
Experiment 3: Varying no. of views
  • Effect is larger for RVTs than LVTs
query evaluation
Query Evaluation
  • Index plans
    • Look-up on secondary attributes
    • Cost of index scan increases with size of result set
  • Aggregates
    • Count
      • Index scan
      • LVT on base
      • LVT on RVT
query evaluation aggregates
Query Evaluation : Aggregates
  • LVT approaches constant across all group sizes
  • LVT-on-base : most expensive
  • LVT on RVT : cheapest
  • Cost of index scan increases with group size
query evaluation aggregates37
Query Evaluation : Aggregates
  • Fixed group size : 500
  • Index scan and LVT on RVT unaffected by no of partitions
  • For small partitions, LVT on base beats index scan
  • LVT on RVT – best strategy
conclusion
Conclusion

Views are essential to enhance query power in distributed systems.

RVTs : index, equi-join and selection views

LVTs : group-by-aggregate views

Deferred view maintenance : easier since it uses existing PNUTs mechanisms for replication and recovery.

references
References

Asynchronous View maintenance for VLSD databases

Parag Agrawal,Adam S, Brian C, Utkarsh S, Raghu Ramakrishnan, SIGMOD 2009

“PNUTS: Yahoo!’s Hosted Data Serving Platform”

Brian F. Cooper, Raghu Ramakrishnan, Utkarsh Srivastava, Adam Silberstein, Philip Bohannon,et al