Increasing Buffer-Locality for Multiple Index Based Scans through - - PowerPoint PPT Presentation

▶
increasing buffer locality for multiple index based scans
SMART_READER_LITE
LIVE PREVIEW

Increasing Buffer-Locality for Multiple Index Based Scans through - - PowerPoint PPT Presentation

May 01, 2023 213 likes •875 views

IBM Research Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Christian A. Lang Database Research Group Bishwaranjan Bhattacharjee IBM T.J. Watson Research Center Tim

slide-1
SLIDE 1

IBM Research

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control

Christian A. Lang Database Research Group Bishwaranjan Bhattacharjee IBM T.J. Watson Research Center Tim Malkemus Kwai Wong IBM Toronto Lab

slide-2
SLIDE 2

2 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Goal Improve query performance (throughput+latency) for ad-hoc index scan-heavy multi-query workloads (e.g., DSS workloads) with minimal architecture dependency/impact

slide-3
SLIDE 3

3 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Example DSS Queries

select sum(l_extendedprice*l_discount) as revenue from lineitem where l_shipdate >= ’01/01/2006’ and l_shipdate < ’01/01/2006' + interval '1' year and l_quantity > 10; select sum(l_extendedprice*l_discount) as revenue, avg(l_extendedprice*l_discount) as avgSale from lineitem where l_shipdate >= ’10/01/2006’ and l_shipdate < ’10/01/2006’ + interval ‘3' month and l_quantity > 30;

slide-4
SLIDE 4

4 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Challenges in Multi-Query DSS Workloads

DSS workloads include scanning of large amounts of data (e.g., aggregate calculation) Cannot optimize ahead of time (many ad-hoc queries, unknown start times) Trend: even more I/O bound queries (disk seek/access times not keeping up with capacity growth/CPU speed) Sub-optimal cache reuse (current RDBMS treat queries (mostly) in isolation)

slide-5
SLIDE 5

5 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Known Solutions (not to scale)

Impact on existing architecture Can handle drift/ad-hoc queries? No (static queries) Yes Higher Lower QPipe [Harizopoulos05] SISCANs (cache and index independent) LRU-K LRFU ARC Teradata ? SQLServer ? Multi-query

  • ptimization

Cooperative Scans [Zukowski07] Oracle ? TEM [Kotidis01] BP-aware QO [Ramamurthy/DeWitt05] NonStop SQL/MX [Clear99] RT-DBMS

slide-6
SLIDE 6

6 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Outline

Current Index Scan Architecture SISCAN – “Circular” Index Scan

– Placement – Speed Control

Implementation Issues

– Index-independent Relative SISCAN Location – “Bufferpool-independent” SISCAN-aware Caching

Experimental Results Conclusions

slide-7
SLIDE 7

7 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Current Index Scan Architecture

Table pages stored on disk Bufferpool Index structure

slide-8
SLIDE 8

8 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Current Index Scan Architecture

Table pages stored on disk Bufferpool

HJ HJ Tscan ORDERS NLJ NLJ IXSCAN LINEITEM … … IXSCAN CUSTOMER

Query 1 Index structure

slide-9
SLIDE 9

9 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Current Index Scan Architecture

Table pages stored on disk Bufferpool

HJ HJ Tscan ORDERS NLJ NLJ IXSCAN LINEITEM … … IXSCAN CUSTOMER

Query 1 Query Execution A Index scan process Index structure

slide-10
SLIDE 10

10 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Current Index Scan Architecture

Table pages stored on disk Bufferpool A

1 1 1

Index structure

slide-11
SLIDE 11

11 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Current Index Scan Architecture

Table pages stored on disk Bufferpool A

5 6 3 4 6 3 4 5 1 2 3 4 5 6

Index structure

slide-12
SLIDE 12

12 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Current Index Scan Architecture

Table pages stored on disk

1

Bufferpool A

5 6 3 4 6 3 4

HJ HJ Tscan ORDERS NLJ NLJ IXSCAN CUSTOMER … … IXSCAN CUSTOMER

Query 2 Query Execution B

3 4 6 5

Index scan process

1 2 3 4 5 6

Index structure

slide-13
SLIDE 13

13 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Current Index Scan Architecture

Table pages stored on disk

1

Bufferpool A

5 6 6 7

B

1 7 6 5 1 1 2 3 4 5 6 1 7

Index structure

slide-14
SLIDE 14

14 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Current Index Scan Architecture

Table pages stored on disk Bufferpool A

8 7

B

5 4 7 8 4 1 2 3 4 5 6 1 7 8 2 3 5 4 5

Index structure

slide-15
SLIDE 15

15 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Current Index Scan Architecture

Table pages stored on disk Bufferpool A

8 7

B

5 4 7 8 4 1 2 3 4 5 6 1 7 8 2 3 5 4 5

Pages read by A have to be re-read by B extra I/O, slowdown

Index structure

slide-16
SLIDE 16

16 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Outline

Current Index Scan Architecture SISCAN – “Circular” Index Scan

– Placement – Speed Control

Implementation Issues

– Index-independent Relative SISCAN Location – “Bufferpool-independent” SISCAN-aware Caching

Experimental Results Conclusions

slide-17
SLIDE 17

17 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

SISCAN – “Circular” Index Scan

Table pages stored on disk Bufferpool A

5 6 3 4 6 3 4 5 1 2 3 4 5 6

Index structure

slide-18
SLIDE 18

18 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

SISCAN – “Circular” Index Scan

Table pages stored on disk

1

Bufferpool A

5 6 6 7

B

7 6 5 1 1 2 3 4 5 6 1 7

Index structure

2 2 4 4

Start scan B at A’s key position bufferpool pages reused

slide-19
SLIDE 19

19 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

SISCAN – “Circular” Index Scan

Table pages stored on disk Bufferpool A

9 10 7

B

7 1 2 3 4 5 6 1 7

Index structure

2 2 8 8 9 10 8 3 9 4 10 5 3 4 5

When B reaches end key wrap around to start key

slide-20
SLIDE 20

20 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

SISCAN – “Circular” Index Scan

Table pages stored on disk Bufferpool

9 10

B

1 2 3 4 5 6 1 7

Index structure

2 8 9 10 9 4 10 5 3 4 5 6 7 6 7 6 7

After wrapping, B finishes the remaining key range break single IXSCAN into two

slide-21
SLIDE 21

21 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Placement: Where to Start with Multiple Active SISCANs?

Scan C Current location Scan A Current location Scan B Current location Scan C Scan B Scan A Scan ranges

slide-22
SLIDE 22

22 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Placement: Where to Start with Multiple Active SISCANs?

Scan C Current location Scan A Current location Scan B Current location Scan C Scan B Scan A Scan ranges New scan

Where to start new scan? need more information

slide-23
SLIDE 23

23 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Placement: Where to Start with Multiple Active SISCANs?

Scan C Current location Scan A Current location Scan B Current location Scan C Scan B Scan A Scan ranges New scan time Current time location Scan A Scan B Scan C

slide-24
SLIDE 24

24 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Placement: Where to Start with Multiple Active SISCANs?

Scan C Current location Scan A Current location Scan B Current location Scan C Scan B Scan A Scan ranges New scan time Current time location Scan A Scan B Scan C New scan starts with B

slide-25
SLIDE 25

25 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Estimating Sharing Potential

time loc Scan key range for E A B C D

Current time

slide-26
SLIDE 26

26 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Estimating Sharing Potential

time loc Scan key range for E A B C D E

Current time

slide-27
SLIDE 27

27 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Estimating Sharing Potential

time loc Scan key range for E 3 *15 A B C D E

Number of reads: Current time

slide-28
SLIDE 28

28 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Estimating Sharing Potential

time loc Scan key range for E 3 *15 A B C D E 1 *30

Number of reads:

+

Current time

slide-29
SLIDE 29

29 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Estimating Sharing Potential

time loc Scan key range for E 2 *15 A B C D E 3 *15 1 *30

Number of reads:

+ +

Current time

slide-30
SLIDE 30

30 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Estimating Sharing Potential

time loc Scan key range for E A B C D E 3 *20 2 *15 3 *15 1 *30

Number of reads:

+ + +

Current time

slide-31
SLIDE 31

31 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Estimating Sharing Potential

time loc Scan key range for E 3 *10 A B C D E 3 *20 2 *15 3 *15 1 *30

Number of reads:

+ + + + = 195

Current time

slide-32
SLIDE 32

32 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Estimating Sharing Potential

time loc Scan key range for E A B C D E

Current time

slide-33
SLIDE 33

33 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Estimating Sharing Potential

time loc Scan key range for E A B C D E 2 *15

Number of reads: Current time

slide-34
SLIDE 34

34 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Estimating Sharing Potential

time loc Scan key range for E 2 *15 2 *20 A B C D E

Number of reads:

+

Current time

slide-35
SLIDE 35

35 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Estimating Sharing Potential

time loc Scan key range for E 2 *15 2 *40 2 *20 A B C D E

Number of reads:

+ +

Current time

slide-36
SLIDE 36

36 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Estimating Sharing Potential

time loc Scan key range for E 2 *15 A B C D E

Number of reads:

2 *15 2 *40 2 *20 + + + = 180

Current time

slide-37
SLIDE 37

37 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Estimating Sharing Potential

time loc Scan key range for E 2 *15 A B C D E

Number of reads:

2 *15 2 *40 2 *20 + + + = 180 Starting E near A is the better choice

Current time

slide-38
SLIDE 38

38 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Problem solved? No, scans “drift” apart!

time location

LINEITEM scans during TPC-H

slide-39
SLIDE 39

39 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Problem with Drift

A C D F E B

Key order B B

Initial reader of pages in key

  • rder

E E E F F B

slide-40
SLIDE 40

40 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Problem with Drift

A C D F E B

Key order B B

Initial reader of pages in key

  • rder

E E E F F B

3 disk reads

slide-41
SLIDE 41

41 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Problem with Drift

A C D F E B

Key order B B

Initial reader of pages in key

  • rder

D D F F E E

4 disk reads Drift leads to extra disk reads; How to tolerate some drift without being too rigorous?

slide-42
SLIDE 42

42 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

SISCAN Speed Control

A C D F E B

Key order

Group 1 Group 2 Group 3

Greedy algorithm: group nearby SISCANs until sum of group footprint exceeds bufferpool size

Pages in key

  • rder

Footprint ≤ bufferpool size

slide-43
SLIDE 43

43 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

SISCAN Speed Control

A C D F E B

Key order

Group 1 Group 2 Group 3 Pages in key

  • rder

Leader (wait for A if necessary) Leader (wait for C and D if necessary) Leader (no other group members)

Delay leader until group size ≤ bufferpool size / #groups Upper bound on wait time per SISCAN; Details similar to throttling for table scan sharing [Lang et al., ICDE ‘07]

slide-44
SLIDE 44

44 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Outline

Current Index Scan Architecture SISCAN – “Circular” Index Scan

– Placement – Speed Control

Implementation Issues

– Index-independent Relative SISCAN Location – “Bufferpool-independent” SISCAN-aware Caching

Experimental Results Conclusions

slide-45
SLIDE 45

45 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Index-Independent Relative SISCAN Locations

Problem: hard to determine relative IXSCAN locations (while leaving the index a “black box”) Example (key, page): (‘Alice’, 12), (‘Bob’, 38), (‘Bob’, 91), (‘Carol’, 2) What are the relative locations of these scans? How far apart are they?

slide-46
SLIDE 46

46 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Index-Independent Relative SISCAN Locations

A A’s Anchor

slide-47
SLIDE 47

47 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Index-Independent Relative SISCAN Locations

A A’s offset A’s Anchor

slide-48
SLIDE 48

48 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Index-Independent Relative SISCAN Locations

A A’s offset B B’s Anchor A’s Anchor

slide-49
SLIDE 49

49 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Index-Independent Relative SISCAN Locations

A A’s offset B B’s offset

Distance between A and B unknown

B’s Anchor A’s Anchor

slide-50
SLIDE 50

50 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Index-Independent Relative SISCAN Locations

A A’s offset B B’s offset B’s Anchor A’s Anchor

slide-51
SLIDE 51

51 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Index-Independent Relative SISCAN Locations

A A’s offset B B’s offset

Distance between A and B = A’s offset – B’s offset

B’s Anchor A’s Anchor

slide-52
SLIDE 52

52 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Index-Independent Relative SISCAN Locations

C B Anchor A

  • ffsets

E Anchor D

  • ffsets

Partial ordering between SISCANs without details of the index structure

slide-53
SLIDE 53

53 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

“Bufferpool-independent” SISCAN-aware Caching

A C D F E B

Key order

Group 1 Group 2 Group 3 Pages in key

  • rder
slide-54
SLIDE 54

54 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

“Bufferpool-independent” SISCAN-aware Caching

A C D F E B

Key order

Group 1 Group 2 Group 3 Pages in key

  • rder

Leader (mark page as HIGH priority) Trailer (mark page as LOW priority) LOW priority HIGH priority LOW priority

Don’t need to change caching algorithm; need only LOW/HIGH priority hints; Details similar to [Lang et al., ICDE ’07]

slide-55
SLIDE 55

55 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Architectural Changes

Index scan process Index scan manager Index “get next page id” “scan index I from startKey to endKey”

slide-56
SLIDE 56

56 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Architectural Changes

Index scan process Index scan manager Index “get next page id” New component Minor modifications Untouched “scan index I from startKey to endKey”

slide-57
SLIDE 57

57 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Outline

Current Index Scan Architecture SISCAN – “Circular” Index Scan

– Placement – Speed Control

Implementation Issues

– Index-independent Relative SISCAN Location – “Bufferpool-independent” SISCAN-aware Caching

Experimental Results Conclusions

slide-58
SLIDE 58

58 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Experimental Results Setup

  • Platforms:
  • 1. HP Integrity rx5670 (4 Itanium2 proc/1GHz, HP-UX, 15GB, FAStT)
  • 2. 8-node p660 cluster (4 PowerPC/600MHz, AIX, 8GB, 16 SSA disks)
  • 100GB TPC-H database
  • Bufferpool size ≈5% of DB size
  • Standard MDC indexes / no hand-tuning
slide-59
SLIDE 59

59 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Staggered Q6 (I/O intensive)

User System Idle Wait 10 20 30 40 50 60 70 % Of Total Time Base SS

CPU Usage Stats For 3 Steams

1st Q6 2nd Q6 3rd Q6 Time Base SS

3 Streams Timings

I/O wait reduced by 50%; More than 50% gain in response time

slide-60
SLIDE 60

60 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Staggered Q1 (CPU intensive)

User System Idle Wait 20 40 60 80 100 120 % Of Total Time Base ScanShare

CPU Usage Stats

1st Q1 2nd Q1 3rd Q1 Timings in Seconds Base Scan Share

Query Timings

Noticeable reduction in response time even for CPU bound queries

slide-61
SLIDE 61

61 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

TPC-H Throughput: Per-stream Gains

1 2 3 4 5 stream time (s) base ss

20% reduction in response time for all streams

slide-62
SLIDE 62

62 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

TPC-H Throughput: Per-Query Gains

Q1 Q10 Q11 Q12 Q13 Q14 Q15a Q16 Q17 Q18 Q19 Q2 Q20 Q21 Q22 Q3 Q4 Q5 Q6 Q7 Q8 Q9

time (s) base ss

No deterioration for any query

slide-63
SLIDE 63

63 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

TPC-H Throughput: Disk Behavior

Time KB Read SS Base

Time Seeks Per Sec SS Base

Page reads and seeks reduced

slide-64
SLIDE 64

64 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Conclusions

Mechanism for better cache reuse and reduced I/O to increase throughput and reduce latency for ad-hoc index scan-heavy multi-query workloads Inter-SISCAN cache locality improved via:

– Starting new SISCANs near similar (speed/key range) running scans – Speed control of SISCANs to reduce drift – SISCAN-based priority hints to bufferpool manager

Fulfills requirements:

– Can handle dynamic “heterogeneous” workloads – Easy integration in architecture

slide-65
SLIDE 65

65 Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Database Research Group / IBM T.J. Watson Research Center

Thank you!

Contact: langc@us.ibm.com Database Research Group IBM T.J. Watson Research Center http://www.research.ibm.com/scalabledb/