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

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

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Goal Improve query performance (throughput+latency) for ad-hoc index scan-heavy multi-query workloads (e.g., DSS workloads) with minimal architecture dependency/impact 2 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control 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; 3 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control 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) 4 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Known Solutions (not to scale) Can handle RT-DBMS drift/ad-hoc BP-aware QO queries? [Ramamurthy/DeWitt05] Cooperative Scans TEM SISCANs [Zukowski07] [Kotidis01] (cache and index Yes QPipe independent) [Harizopoulos05] LRFU Teradata ? SQLServer ? ARC Oracle ? LRU-K No NonStop SQL/MX Multi-query (static [Clear99] optimization queries) Impact on Higher Lower existing architecture 5 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control 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 6 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Current Index Scan Architecture Bufferpool Table pages stored on disk Index structure 7 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Current Index Scan Architecture HJ HJ NLJ … NLJ IXSCAN IXSCAN LINEITEM CUSTOMER … Tscan ORDERS Query 1 Bufferpool Table pages stored on disk Index structure 8 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Current Index Scan Architecture HJ HJ NLJ … NLJ IXSCAN IXSCAN Query LINEITEM CUSTOMER Execution … Tscan ORDERS Query 1 Index scan process A Bufferpool Table pages stored on disk Index structure 9 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Current Index Scan Architecture A 1 Bufferpool 1 1 Table pages stored on disk Index structure 10 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Current Index Scan Architecture A 5 6 3 4 Bufferpool 5 3 4 6 1 2 5 3 4 6 Table pages stored on disk Index structure 11 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Current Index Scan Architecture HJ HJ NLJ … IXSCAN NLJ IXSCAN Query CUSTOMER CUSTOMER Execution … Query 2 Tscan ORDERS A B Index scan process 5 6 3 4 Bufferpool 5 3 4 6 1 2 1 5 3 3 4 4 6 6 Table pages stored on disk Index structure 12 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Current Index Scan Architecture A B 5 6 1 7 Bufferpool 1 5 3 4 6 1 7 2 1 5 6 6 7 1 Table pages stored on disk Index structure 13 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Current Index Scan Architecture A B 4 8 5 7 Bufferpool 2 3 4 1 5 5 3 4 6 1 7 8 2 4 7 8 5 Table pages stored on disk Index structure 14 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Current Index Scan Architecture Pages read by A have to be re-read by B � extra I/O, slowdown A B 4 8 5 7 Bufferpool 2 3 4 1 5 5 3 4 6 1 7 8 2 4 7 8 5 Table pages stored on disk Index structure 15 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control 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 16 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control SISCAN – “Circular” Index Scan A 5 6 3 4 Bufferpool 5 3 4 6 1 2 5 3 4 6 Table pages stored on disk Index structure 17 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control SISCAN – “Circular” Index Scan Start scan B at A’s key position � bufferpool pages reused A B 5 6 4 7 Bufferpool 1 2 5 3 4 6 1 7 2 5 1 4 6 6 7 1 2 Table pages stored on disk Index structure 18 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control SISCAN – “Circular” Index Scan When B reaches end key � wrap around to start key A B 9 10 8 7 Bufferpool 3 1 2 4 5 5 8 3 4 6 1 7 9 10 2 8 7 9 10 3 2 4 5 Table pages stored on disk Index structure 19 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control SISCAN – “Circular” Index Scan After wrapping, B finishes the remaining key range � break single IXSCAN into two B 9 10 6 7 Bufferpool 3 6 1 7 2 4 5 5 8 3 4 6 1 7 9 10 2 6 7 9 10 4 5 Table pages stored on disk Index structure 20 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Placement: Where to Start with Multiple Active SISCANs? Scan C Scan A Scan B Current Current Current location location location Scan ranges Scan A Scan B Scan C 21 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Placement: Where to Start with Multiple Active SISCANs? Scan C Scan A Scan B Current Current Current location location location Scan ranges Scan A Scan B Scan C New scan Where to start new scan? � need more information 22 Database Research Group / IBM T.J. Watson Research Center

Increasing Buffer-Locality for Multiple Index Based Scans through Intelligent Placement and Index Scan Speed Control Placement: Where to Start with Multiple Active SISCANs? Scan C Scan A Scan B Current Current Current location location location Scan ranges Scan A Scan B Scan C New scan Scan B location Scan C Scan A Current time time 23 Database Research Group / IBM T.J. Watson Research Center