Database cracking Stratos Idreos, Martin Kersten and Stefan Manegold - - PowerPoint PPT Presentation

▶
database cracking
SMART_READER_LITE
LIVE PREVIEW

Database cracking Stratos Idreos, Martin Kersten and Stefan Manegold - - PowerPoint PPT Presentation

May 23, 2023 318 likes •629 views

Database cracking Stratos Idreos, Martin Kersten and Stefan Manegold CWI Amsterdam, The Netherlands The open problem - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Dynamic

slide-1
SLIDE 1

Database cracking

Stratos Idreos, Martin Kersten and Stefan Manegold CWI Amsterdam, The Netherlands

slide-2
SLIDE 2
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

The open problem

Dynamic environments What kind of indices should be used, when and on which data? Database experts or special tools monitor the system More difficult in databases with huge datasets

slide-3
SLIDE 3
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Database cracking

We explore self-organization Each query triggers physical re-organization of the database We designed and implemented a DBMS using database cracking We work on top of MonetDB, a column oriented database system

slide-4
SLIDE 4
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking a database

3 8 6 2 12 13 4 17 15

select A>5 and A<10

slide-5
SLIDE 5
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking a database

3 8 6 2 12 13 4

select A>5 and A<10

17 15 3 4 2

slide-6
SLIDE 6
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking a database

3 8 6 2 12 13 4

select A>5 and A<10

17 15 3 4 2 8 6

slide-7
SLIDE 7
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking a database

<= 5 >= 10

3 8 6 2 12 13 4

select A>5 and A<10

17 15 3 4 2 8 6 12 15 17 13

> 5

slide-8
SLIDE 8
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking a database

<= 5 >= 10

3 8 6 2 12 13 4

select A>5 and A<10

17 15 3 4 2 8 6 12 15 17 13

> 5

slide-9
SLIDE 9
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking a database

<= 5 >= 10

3 8 6 2 12 13 4

select A>5 and A<10

17 15 3 4 2 8 6 12 15 17 13

> 5

Improve data access for future queries

slide-10
SLIDE 10
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking a database

<= 5 >= 10

3 8 6 2 12 13 4

select A>5 and A<10

17 15 3 4 2 8 6 12 15 17 13

> 5 select A>2 and A<15

Improve data access for future queries

slide-11
SLIDE 11
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking a database

<= 5 >= 10

3 8 6 2 12 13 4

select A>5 and A<10

17 15 3 4 2 8 6 12 15 17 13

> 5 select A>2 and A<15

2 Improve data access for future queries

slide-12
SLIDE 12
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking a database

<= 5 >= 10

3 8 6 2 12 13 4

select A>5 and A<10

17 15 3 4 2 8 6 12 15 17 13

> 5 select A>2 and A<15

2 4 3 Improve data access for future queries

slide-13
SLIDE 13
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking a database

<= 5 >= 10

3 8 6 2 12 13 4

select A>5 and A<10

17 15 3 4 2 8 6 12 15 17 13

> 5 select A>2 and A<15

2 4 3 8 6 Improve data access for future queries

slide-14
SLIDE 14
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking a database

<= 5 >= 10

3 8 6 2 12 13 4

select A>5 and A<10

17 15 3 4 2 8 6 12 15 17 13

> 5 select A>2 and A<15

2 4 3 8 6 12 13 Improve data access for future queries

slide-15
SLIDE 15
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking a database

<= 2 >= 10

3 8 6 2 12 13 4

select A>5 and A<10

17 15 3 4 2 8 6 12 15 17 13

> 5 select A>2 and A<15

2 4 3 8 6 12 13 17 15

> 2 > 5 >= 10 >= 15

Improve data access for future queries

slide-16
SLIDE 16
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking a database

<= 2 >= 10

3 8 6 2 12 13 4

select A>5 and A<10

17 15 3 4 2 8 6 12 15 17 13

> 5 select A>2 and A<15

2 4 3 8 6 12 13 17 15

> 2 > 5 >= 10 >= 15

Improve data access for future queries

slide-17
SLIDE 17
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking a database

<= 2 >= 10

3 8 6 2 12 13 4

select A>5 and A<10

17 15 3 4 2 8 6 12 15 17 13

> 5 select A>2 and A<15

2 4 3 8 6 12 13 17 15

> 2 > 5 >= 10 >= 15

Improve data access for future queries The more we crack the more we learn

slide-18
SLIDE 18
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking algorithms

There are two types of cracking algorithms Split a piece in two new pieces Split a piece in three new pieces

slide-19
SLIDE 19
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Design

The first time a range query is posed on an attribute A, a cracking DBMS makes a copy of column A, called the cracker column of A A cracker column is continuously physically re-organized based on queries that need to touch attribute such as the result is in a contiguous space For each cracker column, there is a cracker index

slide-20
SLIDE 20
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

The cracker select operator

 The simple select operator:

  • 1. Scans the column
  • 2. Return a new column that contains qualifying values

 The crackers select operator:

  • 1. Searches the cracker index
  • 2. Physically re-organizes pieces found
  • 3. Update the cracker index
  • 4. Return a slice of the cracker column as result

 More steps but faster because we analyze less data

slide-21
SLIDE 21
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Testing the select operator

slide-22
SLIDE 22
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Research and opportunities for cracking

 Optimization  optimal piece size / granularity / index (avl) depth  Exploit cracking for join queries, aggregate queries etc.  Concurrency issues  Cracking histograms  Distributed cracking  A priori cracking  ...

slide-23
SLIDE 23
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Research and opportunities for cracking

 Optimization  optimal piece size / granularity / index (avl) depth  Exploit cracking for join queries, aggregate queries etc.  Concurrency issues  Cracking histograms  Distributed cracking  A priori cracking  ...

THANK YOU!

slide-24
SLIDE 24
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Cracking vs Indices and Sorting

 How cracking compares to a sorting strategy?  Sort data upfront and then use binary search  For a sorting strategy we have to make an investment upfront  Sorting needs prior knowledge of query workload  Similar arguments stand for indices

slide-25
SLIDE 25
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Impact on query plan

select R.c from R where 5 < R.a < 10 and 9 < R.b < 20 algebra.select(Ra, 5, 10) algebra.select(Rb, 9, 20) algebra.OIDintersect(Ra1, Rb1) algebra.fetch(Ra2, Rc)

Ra1 Rb1 Ra2

slide-26
SLIDE 26
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Impact on query plan

select R.c from R where 5 < R.a < 10 and 9 < R.b < 20 algebra.select(Ra, 5, 10) algebra.select(Rb, 9, 20) algebra.OIDintersect(Ra1, Rb1) algebra.fetch(Ra2, Rc) crackers.select(Ra, 5, 10) crackers.select(Rb, 9, 20)

Ra1 Rb1 Ra2

slide-27
SLIDE 27
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Impact on query plan

select R.c from R where 5 < R.a < 10 and 9 < R.b < 20 algebra.select(Ra, 5, 10) algebra.select(Rb, 9, 20) algebra.OIDintersect(Ra1, Rb1) algebra.fetch(Ra2, Rc) crackers.select(Ra, 5, 10) crackers.select(Rb, 9, 20)

Ra1 Rb1 Ra2

Becomes expensive

slide-28
SLIDE 28
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Impact on query plan

select R.c from R where 5 < R.a < 10 and 9 < R.b < 20 algebra.select(Ra, 5, 10) algebra.OIDintersect(Ra1, Rb1) algebra.fetch(Ra2, Rc) crackers.select(Ra, 5, 10)

Ra1 Ra2 algebra.joinselect(Ra1, Rb,9,20)

slide-29
SLIDE 29

Scalability

  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
slide-30
SLIDE 30
  • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

TPC-H query 6