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

Database Cracking CIDR 2007 Cracking Example Each query is treated as an advice on how data should be stored Physically reorganize based on the selection predicate Column A Cracker column of A Cracker column of A 13 4 4 Q1: 16 9 2 select * 4 2 1 Piece 1: A <= 7 Piece 1: from R 9 7 3 A <= 10 where R.A > 10 2 1 6 and R.A < 14 12 3 7 Q1 Q2 7 8 9 Piece 2: 7 < A <= 10 1 6 Result tuples 8 (copy) (in−place) Q2: 19 13 13 Piece 2: select * 3 12 12 10 < A < 14 Piece 3: 10 < A < 14 from R 14 11 11 where R.A > 7 11 16 14 Piece 3: Piece 4: 14 <= A <= 16 and R.A <= 16 8 19 16 14 <= A 6 14 19 Piece 5: 16 < A

Database Cracking CIDR 2007 Cracking Example Each query is treated as an advice on how data should be stored Physically reorganize based on the selection predicate Column A Cracker column of A Cracker column of A 13 4 4 Gain knowledge Q1: 16 9 2 on how data is select * 4 2 1 organized Piece 1: A <= 7 Piece 1: from R 9 7 3 A <= 10 where R.A > 10 2 1 6 and R.A < 14 12 3 7 Q1 Q2 7 8 9 Piece 2: 7 < A <= 10 1 6 Result tuples 8 (copy) (in−place) Q2: 19 13 13 Piece 2: select * 3 12 12 10 < A < 14 Piece 3: 10 < A < 14 from R 14 11 11 where R.A > 7 11 16 14 Piece 3: Piece 4: 14 <= A <= 16 and R.A <= 16 8 19 16 14 <= A 6 14 19 Piece 5: 16 < A

Database Cracking CIDR 2007 Cracking Example Each query is treated as an advice on how data should be stored Physically reorganize based on the selection predicate Column A Cracker column of A Cracker column of A 13 4 4 Gain knowledge Q1: 16 9 2 on how data is select * 4 2 1 organized Piece 1: A <= 7 Piece 1: from R 9 7 3 A <= 10 where R.A > 10 2 1 6 and R.A < 14 12 3 7 Q1 Q2 7 8 9 Piece 2: 7 < A <= 10 1 6 Result tuples 8 (copy) (in−place) Q2: 19 13 13 Piece 2: select * 3 12 12 10 < A < 14 Piece 3: 10 < A < 14 from R 14 11 11 where R.A > 7 11 16 14 Piece 3: Piece 4: 14 <= A <= 16 and R.A <= 16 8 19 16 14 <= A 6 14 19 Piece 5: 16 < A 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 Physically reorganize based on the selection predicate Column A Cracker column of A Cracker column of A 13 4 4 Q1: 16 9 2 select * 4 2 1 Piece 1: A <= 7 Piece 1: from R 9 7 3 A <= 10 where R.A > 10 2 1 6 and R.A < 14 12 3 7 Q1 Q2 7 8 9 Piece 2: 7 < A <= 10 1 6 8 (copy) (in−place) Q2: 19 13 13 Piece 2: select * 3 12 12 10 < A < 14 Piece 3: 10 < A < 14 from R 14 11 11 where R.A > 7 11 16 14 Piece 3: Piece 4: 14 <= A <= 16 and R.A <= 16 8 19 16 14 <= A 6 14 19 Piece 5: 16 < A 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 Physically reorganize based on the selection predicate Column A Cracker column of A Cracker column of A 13 4 4 Q1: 16 9 2 select * 4 2 1 Piece 1: A <= 7 Piece 1: from R 9 7 3 A <= 10 where R.A > 10 2 1 6 and R.A < 14 12 3 7 Q1 Q2 7 8 9 Piece 2: 7 < A <= 10 1 6 8 (copy) (in−place) Q2: 19 13 13 Piece 2: select * 3 12 12 10 < A < 14 Piece 3: 10 < A < 14 from R 14 11 11 where R.A > 7 11 16 14 Piece 3: Piece 4: 14 <= A <= 16 and R.A <= 16 8 19 16 14 <= A 6 14 19 Piece 5: 16 < A 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 Physically reorganize based on the selection predicate Column A Cracker column of A Cracker column of A 13 4 4 Q1: 16 9 2 select * 4 2 1 Piece 1: A <= 7 Piece 1: from R 9 7 3 A <= 10 where R.A > 10 2 1 6 and R.A < 14 12 3 7 Q1 Q2 7 8 9 Piece 2: 7 < A <= 10 1 6 8 (copy) (in−place) Q2: 19 13 13 Piece 2: select * 3 12 12 10 < A < 14 Piece 3: 10 < A < 14 from R 14 11 11 where R.A > 7 11 16 14 Piece 3: Piece 4: 14 <= A <= 16 and R.A <= 16 8 19 16 14 <= A 6 14 19 Piece 5: 16 < A 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 Physically reorganize based on the selection predicate Column A Cracker column of A Cracker column of A 13 4 4 Q1: 16 9 2 select * 4 2 1 Piece 1: A <= 7 Piece 1: from R 9 7 3 A <= 10 where R.A > 10 2 1 6 and R.A < 14 12 3 7 Q1 Q2 7 8 9 Piece 2: 7 < A <= 10 1 6 8 (copy) (in−place) Q2: 19 13 13 Piece 2: select * 3 12 12 10 < A < 14 Piece 3: 10 < A < 14 from R 14 11 11 where R.A > 7 11 16 14 Piece 3: Piece 4: 14 <= A <= 16 and R.A <= 16 8 19 16 14 <= A 6 14 19 Piece 5: 16 < A 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 Physically reorganize based on the selection predicate Column A Cracker column of A Cracker column of A 13 4 4 Q1: 16 9 2 select * 4 2 1 Piece 1: A <= 7 Piece 1: from R 9 7 3 A <= 10 where R.A > 10 2 1 6 and R.A < 14 12 3 7 Q1 Q2 7 8 9 Piece 2: 7 < A <= 10 1 6 8 (copy) (in−place) Q2: 19 13 13 Piece 2: select * 3 12 12 10 < A < 14 Piece 3: 10 < A < 14 from R 14 11 11 where R.A > 7 11 16 14 Piece 3: Piece 4: 14 <= A <= 16 and R.A <= 16 8 19 16 14 <= A 6 14 19 Piece 5: 16 < A 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 Physically reorganize based on the selection predicate Column A Cracker column of A Cracker column of A 13 4 4 Q1: 16 9 2 select * 4 2 1 Piece 1: A <= 7 Piece 1: from R 9 7 3 A <= 10 where R.A > 10 2 1 6 and R.A < 14 12 3 7 Q1 Q2 7 8 9 Piece 2: 7 < A <= 10 1 6 8 (copy) (in−place) Q2: 19 13 13 Piece 2: select * 3 12 12 10 < A < 14 Piece 3: 10 < A < 14 from R 14 11 11 where R.A > 7 11 16 14 Piece 3: Piece 4: 14 <= A <= 16 and R.A <= 16 8 19 16 14 <= A 6 14 19 Piece 5: 16 < A 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 Physically reorganize based on the selection predicate Column A Cracker column of A Cracker column of A 13 4 4 Q1: 16 9 2 select * 4 2 1 Piece 1: A <= 7 Piece 1: from R 9 7 3 A <= 10 where R.A > 10 2 1 6 and R.A < 14 12 3 7 Q1 Q2 7 8 9 Piece 2: 7 < A <= 10 1 6 8 (copy) (in−place) Q2: 19 13 13 Piece 2: select * 3 12 12 10 < A < 14 Piece 3: 10 < A < 14 from R 14 11 11 where R.A > 7 11 16 14 Piece 3: Piece 4: 14 <= A <= 16 and R.A <= 16 8 19 16 14 <= A 6 14 19 Piece 5: 16 < A 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 Physically reorganize based on the selection predicate Column A Cracker column of A Cracker column of A 13 4 4 Q1: 16 9 2 select * 4 2 1 Piece 1: A <= 7 Piece 1: from R 9 7 3 A <= 10 where R.A > 10 2 1 6 and R.A < 14 12 3 7 Q1 Q2 7 8 9 Piece 2: 7 < A <= 10 1 6 8 (copy) (in−place) Q2: 19 13 13 Piece 2: select * 3 12 12 10 < A < 14 Piece 3: 10 < A < 14 from R 14 11 11 where R.A > 7 11 16 14 Piece 3: Piece 4: 14 <= A <= 16 and R.A <= 16 8 19 16 14 <= A 6 14 19 Piece 5: 16 < A 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 Physically reorganize based on the selection predicate Column A Cracker column of A Cracker column of A 13 4 4 Q1: 16 9 2 select * 4 2 1 Piece 1: A <= 7 Piece 1: from R 9 7 3 A <= 10 where R.A > 10 2 1 6 and R.A < 14 12 3 7 Q1 Q2 7 8 9 Piece 2: 7 < A <= 10 1 6 8 (copy) (in−place) Q2: 19 13 13 Piece 2: select * 3 12 12 10 < A < 14 Piece 3: 10 < A < 14 from R 14 11 11 where R.A > 7 11 16 14 Piece 3: Piece 4: 14 <= A <= 16 and R.A <= 16 8 19 16 14 <= A 6 14 19 Piece 5: 16 < A 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 Physically reorganize based on the selection predicate Column A Cracker column of A Cracker column of A 13 4 4 Q1: 16 9 2 select * 4 2 1 Piece 1: A <= 7 Piece 1: from R 9 7 3 A <= 10 where R.A > 10 2 1 6 and R.A < 14 12 3 7 Q1 Q2 7 8 9 Piece 2: 7 < A <= 10 1 6 8 (copy) (in−place) Result tuples Q2: 19 13 13 Piece 2: select * 3 12 12 10 < A < 14 Piece 3: 10 < A < 14 from R 14 11 11 where R.A > 7 11 16 14 Piece 3: Piece 4: 14 <= A <= 16 and R.A <= 16 8 19 16 14 <= A 6 14 19 Piece 5: 16 < A Dynamically/on-the-fly within the select-operator

Database Cracking CIDR 2007 Cracking Example The more we crack, the more we learn Each query is treated as an advice on how data should be stored Physically reorganize based on the selection predicate Column A Cracker column of A Cracker column of A 13 4 4 Q1: 16 9 2 select * 4 2 1 Piece 1: A <= 7 Piece 1: from R 9 7 3 A <= 10 where R.A > 10 2 1 6 and R.A < 14 12 3 7 Q1 Q2 7 8 9 Piece 2: 7 < A <= 10 1 6 8 (copy) (in−place) Result tuples Q2: 19 13 13 Piece 2: select * 3 12 12 10 < A < 14 Piece 3: 10 < A < 14 from R 14 11 11 where R.A > 7 11 16 14 Piece 3: Piece 4: 14 <= A <= 16 and R.A <= 16 8 19 16 14 <= A 6 14 19 Piece 5: 16 < A Dynamically/on-the-fly within the select-operator

Database Cracking, SIGMOD 09 Self-organizing behavior (TPC-H) MonetDB Sel. Crack MySQL Presorted Sid. Crack Presorted 10000 Response time (milli secs) 1000 764 TPC-H Query 15 330 420 300 250 200 150 100 70 0 5 10 15 20 25 30 Query sequence

Database Cracking, SIGMOD 09 Self-organizing behavior (TPC-H) MonetDB Sel. Crack MySQL Presorted Sid. Crack Presorted 10000 Response time (milli secs) 1000 764 TPC-H Query 15 330 420 300 Normal MonetDB 250 200 selection cracking 150 100 70 0 5 10 15 20 25 30 Query sequence

Database Cracking, SIGMOD 09 Self-organizing behavior (TPC-H) MonetDB Sel. Crack MySQL Presorted Sid. Crack Presorted 10000 Response time (milli secs) 1000 764 TPC-H Query 15 330 420 300 Normal MonetDB 250 200 selection cracking 150 Presorted 100 MonetDB 70 0 5 10 15 20 25 30 Preparation cost Query sequence 3-14 minutes

Database Cracking, SIGMOD 09 Self-organizing behavior (TPC-H) MonetDB Sel. Crack MySQL Presorted Sid. Crack Presorted 10000 Response time (milli secs) 1000 764 TPC-H Query 15 330 420 300 Normal MonetDB 250 200 selection cracking 150 MonetDB with Presorted 100 sideways cracking MonetDB 70 0 5 10 15 20 25 30 Preparation cost Query sequence 3-14 minutes

Database Cracking, SIGMOD 09 Self-organizing behavior (TPC-H) MonetDB Sel. Crack MySQL Presorted Sid. Crack Presorted 10000 Response time (milli secs) 1000 764 TPC-H Query 15 330 420 300 Normal MonetDB 250 200 selection cracking 150 MonetDB with Presorted 100 sideways cracking MonetDB 70 0 5 10 15 20 25 30 Preparation cost Query sequence 3-14 minutes

Database Cracking, SIGMOD 09 Self-organizing behavior (TPC-H) MonetDB Sel. Crack MySQL Presorted Sid. Crack Presorted 10000 Response time (milli secs) 1000 764 TPC-H Query 15 330 420 300 Normal MonetDB 250 200 selection cracking 150 MonetDB with Presorted 100 sideways cracking MonetDB 70 0 5 10 15 20 25 30 Preparation cost Query sequence 3-14 minutes

Database Cracking, SIGMOD 09 Self-organizing behavior (TPC-H) MonetDB Sel. Crack MySQL Presorted Sid. Crack Presorted 10000 Response time (milli secs) 1000 764 TPC-H Query 15 330 420 300 Normal MonetDB 250 200 selection cracking 150 MonetDB with Presorted 100 sideways cracking MonetDB 70 0 5 10 15 20 25 30 Preparation cost Query sequence 3-14 minutes

Indexing Overview offline indexing workload analysis index building query processing workload analysis workload analysis

Indexing Overview offline indexing workload analysis index building query processing online indexing workload analysis index building query processing workload analysis workload analysis

Indexing Overview offline indexing workload analysis index building query processing online indexing workload analysis index building query processing adaptive indexing adaptive indexing workload analysis workload analysis

Indexing Overview offline indexing workload analysis index building query processing offline workload knowledge online indexing workload analysis index building online query processing adaptive adaptive indexing idle time adaptive indexing workload analysis workload analysis

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 Can be thought of as an incremental quicksort The core cracking algorithm is extremely lazy

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) sort 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) sort sort 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) sort sort sort 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) sort sort sort sort 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) binary sort search sort sort sort 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) binary sort search binary sort search sort sort 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) binary sort search binary sort search binary sort search sort 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) binary sort search binary sort search binary sort search binary sort search 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) binary sort search binary sort 50 search binary 100 sort search binary sort search 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) binary sort search binary sort 50 search binary 100 sort search binary sort search 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) binary sort search binary sort 50 sorted search binary 100 sort search binary sort search 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) binary sort search binary sort 50 sorted search binary 100 sort search binary sort search Final Initial 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) select(A,55,70) sort sort 50 sorted 100 sort sort Final Initial 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) select(A,55,70) sort sort 50 50 sorted binary search 100 100 sort sort Final Initial 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) select(A,55,70) select(A,150,170) sort sort 50 50 sorted 100 100 sort sort Final Initial 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) select(A,55,70) select(A,150,170) sort 50 sort 50 50 sorted 100 100 100 sort sort Final Initial 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) select(A,55,70) select(A,150,170) binary sort search binary 50 sort 50 50 sorted search 100 100 binary 100 sort search binary sort search Final Initial 100

Adaptive Merging EDBT’10 , SMDB’10 , Goetz Graefe and Harumi Kuno Incremental sort via external merge sort steps select(A,50,100) select(A,55,70) select(A,150,170) binary sort search binary 50 sort 50 50 sorted search 100 100 binary 100 sort search binary 150 sort search 170 Final Initial 100

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

Performance Analysis c) All queries 200 set-up 100 10K random selections selectivity 10% random value ranges 10 in a 30 million integer column Scan Cumulative Average (secs) 1 (cf., Fig. 10b) 0.1 Sort AM 0.01 Crack 0.004 0.001 1 10 100 1000 10000 Query sequence

Performance Analysis c) All queries 200 set-up 100 10K random selections selectivity 10% random value ranges 10 in a 30 million integer column Scan Cumulative Average (secs) 1 (cf., Fig. 10b) 0.1 Sort AM 0.01 Crack 0.004 0.001 1 10 100 1000 10000 Query sequence

Performance Analysis c) All queries 200 set-up 100 10K random selections selectivity 10% random value ranges 10 in a 30 million integer column Scan Cumulative Average (secs) 1 (cf., Fig. 10b) 0.1 Sort AM 0.01 Crack 0.004 0.001 1 10 100 1000 10000 Query sequence

Performance Analysis c) All queries 200 set-up 100 10K random selections selectivity 10% random value ranges 10 in a 30 million integer column Scan Cumulative Average (secs) 1 AM: high init overhead (cf., Fig. 10b) but fast convergence 0.1 Sort AM 0.01 Crack 0.004 0.001 1 10 100 1000 10000 Query sequence

Performance Analysis c) All queries 200 set-up 100 10K random selections selectivity 10% random value ranges 10 in a 30 million integer column Scan Cumulative Average (secs) 1 AM: high init overhead (cf., Fig. 10b) but fast convergence 0.1 Sort Crack: low init overhead AM 0.01 Crack but slow convergence 0.004 0.001 1 10 100 1000 10000 Query sequence

Questions Adaptive merging and Cracking are extremes

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

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 select(A,50,100) crack 100

Crack-Crack vary initialization and incremental steps taken select(A,50,100) crack crack 100

Crack-Crack vary initialization and incremental steps taken select(A,50,100) crack crack crack 100

Crack-Crack vary initialization and incremental steps taken select(A,50,100) crack crack crack crack 100

Crack-Crack vary initialization and incremental steps taken select(A,50,100) crack crack 50 crack 100 crack 100

Crack-Crack vary initialization and incremental steps taken select(A,50,100) crack crack 50 crack 100 crack 100

Crack-Crack vary initialization and incremental steps taken select(A,50,100) crack crack not sorted 50 crack 100 crack 100

Crack-Crack vary initialization and incremental steps taken select(A,50,100) select(A,55,70) crack crack not sorted 50 crack 100 crack 100

Crack-Crack vary initialization and incremental steps taken select(A,50,100) select(A,55,70) crack crack not sorted 50 50 crack 100 crack 100 crack 100

Crack-Crack vary initialization and incremental steps taken select(A,50,100) select(A,55,70) select(A,150,170) crack crack not sorted 50 50 100 crack 100 crack 100

Crack-Crack vary initialization and incremental steps taken select(A,50,100) select(A,55,70) select(A,150,170) crack crack not sorted 50 50 50 100 100 crack 100 crack 100

Crack-Crack vary initialization and incremental steps taken select(A,50,100) select(A,55,70) select(A,150,170) crack crack crack not sorted 50 50 50 crack 100 100 crack 100 crack crack 150 crack 170 100