Merging Whats Cracked, Cracking Whats Merged Adaptive Indexing in - - PowerPoint PPT Presentation

Merging What’s Cracked, Cracking What’s Merged

Adaptive Indexing in Main-Memory Column-Stores

Stratos Idreos, Stefan Manegold, (CWI) Harumi Kuno, Goetz Graefe (HP Labs) PVLDB 2011, 4(9)

Physical design problem

which indexes to build?

• n which data parts?

and when to build them? Database systems perform efficiently

• nly after proper tuning...

DBA without cracking

Physical Design

Sample Workload

Timeline

Physical Design

Sample Workload Analyze Performance

Timeline

Physical Design

Sample Workload Analyze Performance Prepare Estimated physical design

Timeline

Physical Design

Sample Workload Analyze Performance Prepare Estimated physical design

Timeline

Queries

Physical Design

Sample Workload Analyze Performance Prepare Estimated physical design

Timeline

Queries

Complex and time consuming process

Physical Design

Sample Workload Analyze Performance Prepare Estimated physical design

Timeline

Queries

Complex and time consuming process

?

Dynamic Workloads Very Large Databases?

Dynamic environments

idle time workload knowledge

Dynamic environments

idle time workload knowledge

some problem cases

Dynamic environments

idle time workload knowledge

• Not enough idle time to finish proper tuning

some problem cases

Dynamic environments

• By the time we finish tuning, the workload changes

idle time workload knowledge

• Not enough idle time to finish proper tuning

some problem cases

Dynamic environments

• By the time we finish tuning, the workload changes
• No index support during tuning

idle time workload knowledge

• Not enough idle time to finish proper tuning

some problem cases

Dynamic environments

• By the time we finish tuning, the workload changes
• No index support during tuning
• Not all data parts are equally useful

idle time workload knowledge

• Not enough idle time to finish proper tuning

some problem cases

Database Cracking

Remove all tuning, physical design steps but still get similar performance as a fully tuned system

How? Design new auto-tuning kernels

(operators, plans, structures, etc.) For dynamic environments:

DBA with cracking

Database Cracking

no monitoring no preparation no human involvement no external tools no full indexes

Database Cracking

no monitoring no preparation no human involvement no external tools

Continuous on-the-fly physical reorganization

no full indexes

Database Cracking

no monitoring no preparation no human involvement no external tools

Continuous on-the-fly physical reorganization

no full indexes

partial, incremental, adaptive indexing

Database Cracking

no monitoring no preparation no human involvement no external tools

Continuous on-the-fly physical reorganization designed for modern column-stores

no full indexes

partial, incremental, adaptive indexing

Database Cracking

Each query is treated as an advice

• n how data should be stored

Cracking Example

Database Cracking CIDR 2007

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Cracking Example

Database Cracking CIDR 2007

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate Result tuples

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Gain knowledge

• n how data is
• rganized

Physically reorganize based on the selection predicate Result tuples

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Gain knowledge

• n how data is
• rganized

Dynamically/on-the-fly within the select-operator

Physically reorganize based on the selection predicate Result tuples

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Dynamically/on-the-fly within the select-operator

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Dynamically/on-the-fly within the select-operator

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Dynamically/on-the-fly within the select-operator

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Dynamically/on-the-fly within the select-operator

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Dynamically/on-the-fly within the select-operator

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Dynamically/on-the-fly within the select-operator

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Dynamically/on-the-fly within the select-operator

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Dynamically/on-the-fly within the select-operator

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate

Dynamically/on-the-fly within the select-operator

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

Physically reorganize based on the selection predicate Result tuples

Dynamically/on-the-fly within the select-operator

Database Cracking CIDR 2007

Cracking Example

Each query is treated as an advice on how data should be stored

from R where R.A > 10 and R.A < 14 select * Q2: select * from R where R.A > 7 and R.A <= 16 Q1: 1 3 6 7 9 8 13 12 11 14 16 19 Piece 5: 16 < A Piece 3: 10 < A < 14 Piece 1: A <= 7 Piece 2: 7 < A <= 10 Piece 4: 14 <= A <= 16 Cracker column of A Cracker column of A 10 < A < 14 14 <= A A <= 10 Piece 1: Piece 3: Piece 2: (in−place) (copy) Q1 Q2 Column A 13 16 4 9 2 12 7 1 19 3 14 11 8 6 4 9 2 7 1 3 8 6 13 12 11 16 19 14 4 2

The more we crack, the more we learn

Physically reorganize based on the selection predicate Result tuples

Dynamically/on-the-fly within the select-operator

Database Cracking CIDR 2007

Self-organizing behavior (TPC-H)

70 330 100 150 200 250 300 5 10 15 20 25 30 Query sequence TPC-H Query 15 764 420 1000 10000

• Sel. Crack
• Sid. Crack

MonetDB Presorted MySQL Presorted Response time (milli secs)

Database Cracking, SIGMOD 09

Self-organizing behavior (TPC-H)

70 330 100 150 200 250 300 5 10 15 20 25 30 Query sequence TPC-H Query 15 764 420 1000 10000

• Sel. Crack
• Sid. Crack

MonetDB Presorted MySQL Presorted Response time (milli secs)

Normal MonetDB selection cracking

Database Cracking, SIGMOD 09

Self-organizing behavior (TPC-H)

70 330 100 150 200 250 300 5 10 15 20 25 30 Query sequence TPC-H Query 15 764 420 1000 10000

• Sel. Crack
• Sid. Crack

MonetDB Presorted MySQL Presorted

Preparation cost 3-14 minutes

Response time (milli secs)

Presorted MonetDB Normal MonetDB selection cracking

Database Cracking, SIGMOD 09

Self-organizing behavior (TPC-H)

70 330 100 150 200 250 300 5 10 15 20 25 30 Query sequence TPC-H Query 15 764 420 1000 10000

• Sel. Crack
• Sid. Crack

MonetDB Presorted MySQL Presorted

Preparation cost 3-14 minutes

Response time (milli secs)

Presorted MonetDB MonetDB with sideways cracking Normal MonetDB selection cracking

Database Cracking, SIGMOD 09

Self-organizing behavior (TPC-H)

70 330 100 150 200 250 300 5 10 15 20 25 30 Query sequence TPC-H Query 15 764 420 1000 10000

• Sel. Crack
• Sid. Crack

MonetDB Presorted MySQL Presorted

Preparation cost 3-14 minutes

Response time (milli secs)

Presorted MonetDB MonetDB with sideways cracking Normal MonetDB selection cracking

Database Cracking, SIGMOD 09

Self-organizing behavior (TPC-H)

70 330 100 150 200 250 300 5 10 15 20 25 30 Query sequence TPC-H Query 15 764 420 1000 10000

• Sel. Crack
• Sid. Crack

MonetDB Presorted MySQL Presorted

Preparation cost 3-14 minutes

Response time (milli secs)

Presorted MonetDB MonetDB with sideways cracking Normal MonetDB selection cracking

Database Cracking, SIGMOD 09

Self-organizing behavior (TPC-H)

70 330 100 150 200 250 300 5 10 15 20 25 30 Query sequence TPC-H Query 15 764 420 1000 10000

• Sel. Crack
• Sid. Crack

MonetDB Presorted MySQL Presorted

Preparation cost 3-14 minutes

Response time (milli secs)

Presorted MonetDB MonetDB with sideways cracking Normal MonetDB selection cracking

Database Cracking, SIGMOD 09

Indexing Overview

workload analysis index building query processing

• ffline indexing

workload analysis workload analysis

Indexing Overview

workload analysis index building query processing

• ffline indexing
• nline indexing

workload analysis index building query processing workload analysis workload analysis

Indexing Overview

workload analysis index building query processing

• ffline indexing
• nline indexing

adaptive indexing workload analysis index building query processing workload analysis workload analysis adaptive indexing

Indexing Overview

workload analysis index building query processing

• ffline indexing
• nline indexing

adaptive indexing workload analysis index building query processing workload analysis workload analysis adaptive indexing

workload knowledge idle time adaptive

• nline
• ffline

Database Cracking

Each query is treated as an advice on how data should be stored

CIDR’07 Selection cracking SIGMOD’07 Updates SIGMOD’09 Sideways and partial cracking

Can be thought of as an incremental quicksort

Database Cracking

Each query is treated as an advice on how data should be stored

CIDR’07 Selection cracking SIGMOD’07 Updates SIGMOD’09 Sideways and partial cracking The core cracking algorithm is extremely lazy

Can be thought of as an incremental quicksort

Adaptive Merging

Incremental sort via external merge sort steps 100

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno

Adaptive Merging

Incremental sort via external merge sort steps 100

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno

Adaptive Merging

Incremental sort via external merge sort steps

select(A,50,100)

100

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno

Adaptive Merging

Incremental sort via external merge sort steps

sort

select(A,50,100)

100

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno

Adaptive Merging

Incremental sort via external merge sort steps

sort sort

select(A,50,100)

100

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort

select(A,50,100)

100

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort sort

select(A,50,100)

100

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort sort

select(A,50,100)

binary search 100

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort sort

select(A,50,100)

binary search 100 binary search

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort sort

select(A,50,100)

binary search 100 binary search binary search

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort sort

select(A,50,100)

binary search 100 binary search binary search binary search

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort sort

select(A,50,100)

50 100 binary search 100 binary search binary search binary search

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort sort

select(A,50,100)

50 100 binary search 100 binary search binary search binary search

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort sort

select(A,50,100)

50 100 binary search 100 binary search binary search binary search

sorted

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort sort

select(A,50,100)

50 100 binary search 100 binary search binary search binary search

sorted

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno Initial Final

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort sort

select(A,50,100) select(A,55,70)

50 100 100

sorted

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno Initial Final

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort sort

select(A,50,100) select(A,55,70)

50 100 50 100 100

sorted

binary search

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno Initial Final

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort sort

select(A,50,100) select(A,55,70)

50 100 50 100

select(A,150,170)

100

sorted

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno Initial Final

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort sort

select(A,50,100) select(A,55,70)

50 100 50 100

select(A,150,170)

50 100 100

sorted

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno Initial Final

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort sort

select(A,50,100) select(A,55,70)

50 100 50 100

select(A,150,170)

50 100 100

sorted

binary search binary search binary search binary search

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno Initial Final

Adaptive Merging

Incremental sort via external merge sort steps

sort sort sort sort

select(A,50,100) select(A,55,70)

50 100 50 100

select(A,150,170)

50 100 150 170 100

sorted

binary search binary search binary search binary search

EDBT’10, SMDB’10, Goetz Graefe and Harumi Kuno Initial Final

Questions

• Adaptive merging in column-stores?
• Adaptive merging Vs Cracking?
• Can we learn from both AM and Cracking?

Performance Analysis

0.004 200 0.001 0.01 0.1 1 10 100 1 10 100 1000 10000 Query sequence c) All queries (cf., Fig. 10b) Scan Sort AM Crack

Cumulative Average (secs)

10K random selections selectivity 10% random value ranges in a 30 million integer column

set-up

Performance Analysis

0.004 200 0.001 0.01 0.1 1 10 100 1 10 100 1000 10000 Query sequence c) All queries (cf., Fig. 10b) Scan Sort AM Crack

Cumulative Average (secs)

10K random selections selectivity 10% random value ranges in a 30 million integer column

set-up

Performance Analysis

0.004 200 0.001 0.01 0.1 1 10 100 1 10 100 1000 10000 Query sequence c) All queries (cf., Fig. 10b) Scan Sort AM Crack

Cumulative Average (secs)

10K random selections selectivity 10% random value ranges in a 30 million integer column

set-up

Performance Analysis

0.004 200 0.001 0.01 0.1 1 10 100 1 10 100 1000 10000 Query sequence c) All queries (cf., Fig. 10b) Scan Sort AM Crack

Cumulative Average (secs)

10K random selections selectivity 10% random value ranges in a 30 million integer column

set-up AM: high init overhead but fast convergence

Performance Analysis

0.004 200 0.001 0.01 0.1 1 10 100 1 10 100 1000 10000 Query sequence c) All queries (cf., Fig. 10b) Scan Sort AM Crack

Cumulative Average (secs)

10K random selections selectivity 10% random value ranges in a 30 million integer column

set-up AM: high init overhead but fast convergence Crack: low init overhead but slow convergence

Questions

Adaptive merging and Cracking are extremes

Questions

What is there in between? Adaptive merging and Cracking are extremes

Crack-Crack

vary initialization and incremental steps taken 100

Crack-Crack

vary initialization and incremental steps taken 100

Crack-Crack

vary initialization and incremental steps taken

select(A,50,100)

100

Crack-Crack

vary initialization and incremental steps taken

crack

select(A,50,100)

100

Crack-Crack

vary initialization and incremental steps taken

crack crack

select(A,50,100)

100

Crack-Crack

vary initialization and incremental steps taken

crack crack crack

select(A,50,100)

100

Crack-Crack

vary initialization and incremental steps taken

crack crack crack crack

select(A,50,100)

100

Crack-Crack

vary initialization and incremental steps taken

crack crack crack crack

select(A,50,100)

50 100 100

Crack-Crack

vary initialization and incremental steps taken

crack crack crack crack

select(A,50,100)

50 100 100

Crack-Crack

vary initialization and incremental steps taken

crack crack crack crack

select(A,50,100)

50 100 100

not sorted

Crack-Crack

vary initialization and incremental steps taken

crack crack crack crack

select(A,50,100) select(A,55,70)

50 100 100

not sorted

Crack-Crack

vary initialization and incremental steps taken

crack crack crack crack

select(A,50,100) select(A,55,70)

50 100 50 100 100

not sorted

crack

Crack-Crack

vary initialization and incremental steps taken

crack crack crack crack

select(A,50,100) select(A,55,70)

50 100 50 100

select(A,150,170)

100

not sorted

Crack-Crack

vary initialization and incremental steps taken

crack crack crack crack

select(A,50,100) select(A,55,70)

50 100 50 100

select(A,150,170)

50 100 100

not sorted

Crack-Crack

vary initialization and incremental steps taken

crack crack crack crack

select(A,50,100) select(A,55,70)

50 100 50 100

select(A,150,170)

50 100 150 170 crack 100 crack crack crack

not sorted

Adaptive Indexing

Sort Radix Crack final partitions fast − convergence − slow initial partitions high − overhead − low HSC HSR HSS HRS HRR HRC HCC HCR HCS Crack Radix Sort slow − convergence − fast low − overhead − high

Adaptive Indexing

Sort Radix Crack final partitions fast − convergence − slow initial partitions high − overhead − low HSC HSR HSS HRS HRR HRC HCC HCR HCS Crack Radix Sort slow − convergence − fast low − overhead − high

Adaptive Indexing

Sort Radix Crack final partitions fast − convergence − slow initial partitions high − overhead − low HSC HSR HSS HRS HRR HRC HCC HCR HCS Crack Radix Sort slow − convergence − fast low − overhead − high

Adaptive Indexing

Sort Radix Crack final partitions fast − convergence − slow initial partitions high − overhead − low HSC HSR HSS HRS HRR HRC HCC HCR HCS Crack Radix Sort slow − convergence − fast low − overhead − high

Adaptive Indexing

Sort Radix Crack final partitions fast − convergence − slow initial partitions high − overhead − low HSC HSR HSS HRS HRR HRC HCC HCR HCS Crack Radix Sort slow − convergence − fast low − overhead − high

Adaptive Indexing

1e-05 0.0001 0.001 0.01 0.1 1 10 100 Response time (secs) b) (scan) Hybrid: Crack Crack Crack Radix Crack Sort 1e-05 0.0001 0.001 0.01 0.1 1 10 100 Response time (secs) a) (scan) Scan Cracking Adaptive Merging Full Index

1 10 100 1000

Queries

Adaptive Indexing

1e-05 0.0001 0.001 0.01 0.1 1 10 100 Response time (secs) b) (scan) Hybrid: Crack Crack Crack Radix Crack Sort 1e-05 0.0001 0.001 0.01 0.1 1 10 100 Response time (secs) a) (scan) Scan Cracking Adaptive Merging Full Index

1 10 100 1000

Queries

Adaptive Indexing

1e-05 0.0001 0.001 0.01 0.1 1 10 100 Response time (secs) b) (scan) Hybrid: Crack Crack Crack Radix Crack Sort 1e-05 0.0001 0.001 0.01 0.1 1 10 100 Response time (secs) a) (scan) Scan Cracking Adaptive Merging Full Index

1 10 100 1000

Queries

Adaptive Indexing

1e-05 0.0001 0.001 0.01 0.1 1 10 100 Response time (secs) b) (scan) Hybrid: Crack Crack Crack Radix Crack Sort 1e-05 0.0001 0.001 0.01 0.1 1 10 100 Response time (secs) a) (scan) Scan Cracking Adaptive Merging Full Index

1 10 100 1000

Queries

Adaptive Indexing

1e-05 0.0001 0.001 0.01 0.1 1 10 100 Response time (secs) b) (scan) Hybrid: Crack Crack Crack Radix Crack Sort 1e-05 0.0001 0.001 0.01 0.1 1 10 100 Response time (secs) a) (scan) Scan Cracking Adaptive Merging Full Index

1 10 100 1000

Queries

Adaptive Indexing

1e-05 0.0001 0.001 0.01 0.1 1 10 100 Response time (secs) b) (scan) Hybrid: Crack Crack Crack Radix Crack Sort 1e-05 0.0001 0.001 0.01 0.1 1 10 100 Response time (secs) a) (scan) Scan Cracking Adaptive Merging Full Index

1 10 100 1000

Queries

Adaptive Indexing

How many queries before the index fully supports a random query? none Cost of first query relative to in- memory scan effort Full Index 1x Adaptive Merging Database Cracking 10 100 1000 2x 5x 10x Ideal Hybrid never Scan Bad Hybrid CC CR CS

Initialization Vs convergence tradeoff

Adaptive Indexing

How many queries before the index fully supports a random query? none Cost of first query relative to in- memory scan effort Full Index 1x Adaptive Merging Database Cracking 10 100 1000 2x 5x 10x Ideal Hybrid never Scan Bad Hybrid CC CR CS

Initialization Vs convergence tradeoff

Adaptive Indexing

How many queries before the index fully supports a random query? none Cost of first query relative to in- memory scan effort Full Index 1x Adaptive Merging Database Cracking 10 100 1000 2x 5x 10x Ideal Hybrid never Scan Bad Hybrid CC CR CS

Initialization Vs convergence tradeoff

Adaptive Indexing

How many queries before the index fully supports a random query? none Cost of first query relative to in- memory scan effort Full Index 1x Adaptive Merging Database Cracking 10 100 1000 2x 5x 10x Ideal Hybrid never Scan Bad Hybrid CC CR CS

Initialization Vs convergence tradeoff

Adaptive Indexing

How many queries before the index fully supports a random query? none Cost of first query relative to in- memory scan effort Full Index 1x Adaptive Merging Database Cracking 10 100 1000 2x 5x 10x Ideal Hybrid never Scan Bad Hybrid CC CR CS

Initialization Vs convergence tradeoff

Disk Concurrency

Adaptive Indexing

How many queries before the index fully supports a random query? none Cost of first query relative to in- memory scan effort Full Index 1x Adaptive Merging Database Cracking 10 100 1000 2x 5x 10x Ideal Hybrid never Scan Bad Hybrid CC CR CS

Initialization Vs convergence tradeoff

Disk Concurrency

More active is best

Adaptive Indexing

How many queries before the index fully supports a random query? none Cost of first query relative to in- memory scan effort Full Index 1x Adaptive Merging Database Cracking 10 100 1000 2x 5x 10x Ideal Hybrid never Scan Bad Hybrid CC CR CS

Initialization Vs convergence tradeoff

Disk Concurrency Updates

More active is best

Adaptive Indexing

How many queries before the index fully supports a random query? none Cost of first query relative to in- memory scan effort Full Index 1x Adaptive Merging Database Cracking 10 100 1000 2x 5x 10x Ideal Hybrid never Scan Bad Hybrid CC CR CS

Initialization Vs convergence tradeoff

Disk Concurrency Updates

More active is best More lazy is best

More in the paper...

Column-store design details and concerns Selectivity effects Concurrency control examples and more...

Ongoing and open topics

Disk based Concurrency control

Multi-cores Compression Workload robustness Aggregations Row-stores Pipelining Optimizer rules

Adaptive Indexing + Auto tuning tools

...and many more...

Ongoing and open topics

Disk based Concurrency control

Multi-cores Compression Workload robustness Aggregations Row-stores Pipelining Optimizer rules

Adaptive Indexing + Auto tuning tools

...and many more...

Thank you!