The MemSQL Query Optimizer: A modern optimizer for real-time analytics in a distributed database

Real-time analytics on massive datasets has become a very common need in many enterprises. These applications require not only rapid data ingest, but also quick answers to analytical queries operating on the latest data. MemSQL is a distributed SQL database designed to exploit memory-optimized, scale-out architecture to enable real-time transactional and analytical workloads which are fast, highly concurrent, and extremely scalable. Many analytical queries in MemSQL's customer workloads are complex queries involving joins, aggregations, sub-queries, etc. over star and snowflake schemas, often ad-hoc or produced interactively by business intelligence tools. These queries often require latencies of seconds or less, and therefore require the optimizer to not only produce a high quality distributed execution plan, but also produce it fast enough so that optimization time does not become a bottleneck. In this paper, we describe the architecture of the MemSQL Query Optimizer and the design choices and innovations which enable it quickly produce highly efficient execution plans for complex distributed queries. We discuss how query rewrite decisions oblivious of distribution cost can lead to poor distributed execution plans, and argue that to choose high-quality plans in a distributed database, the optimizer needs to be distribution-aware in choosing join plans, applying query rewrites, and costing plans. We discuss methods to make join enumeration faster and more effective, such as a rewrite-based approach to exploit bushy joins in queries involving multiple star schemas without sacrificing optimization time. We demonstrate the effectiveness of the MemSQL optimizer over queries from the TPC-H benchmark and a real customer workload.

[1]  Volker Markl,et al.  Parallelizing query optimization , 2008, Proc. VLDB Endow..

[2]  Jack Chen,et al.  Query Optimization Time: The New Bottleneck in Real-time Analytics , 2015, IMDM '15.

[3]  Joseph M. Hellerstein,et al.  Parallelizing extensible query optimizers , 2009, SIGMOD Conference.

[4]  Malcolm Singh,et al.  Introduction to the IBM Netezza warehouse appliance , 2011, CASCON.

[5]  Adam Prout,et al.  A column store engine for real-time streaming analytics , 2016, 2016 IEEE 32nd International Conference on Data Engineering (ICDE).

[6]  Wolfgang Lehner,et al.  SAP HANA database: data management for modern business applications , 2012, SGMD.

[7]  Volker Markl,et al.  A First Step Towards GPU-assisted Query Optimization , 2012, ADMS@VLDB.

[8]  David J. DeWitt,et al.  Query optimization in microsoft SQL server PDW , 2012, SIGMOD Conference.

[9]  Patricia G. Selinger,et al.  Access path selection in a relational database management system , 1979, SIGMOD '79.

[10]  Marcin Zukowski,et al.  Vectorwise: A Vectorized Analytical DBMS , 2012, 2012 IEEE 28th International Conference on Data Engineering.

[11]  Raghunath Othayoth Nambiar,et al.  The making of TPC-DS , 2006, VLDB.

[12]  Ramakrishna Varadarajan,et al.  The Vertica Analytic Database: C-Store 7 Years Later , 2012, Proc. VLDB Endow..

[13]  George C. Caragea,et al.  Orca: a modular query optimizer architecture for big data , 2014, SIGMOD Conference.

[14]  Nicolas Bruno,et al.  Polynomial heuristics for query optimization , 2010, 2010 IEEE 26th International Conference on Data Engineering (ICDE 2010).

[15]  Arun N. Swami,et al.  Optimization of large join queries: combining heuristics and combinatorial techniques , 1989, SIGMOD '89.

[16]  Guy M. Lohman,et al.  Measuring the Complexity of Join Enumeration in Query Optimization , 1990, VLDB.

[17]  Leonidas Fegaras,et al.  A New Heuristic for Optimizing Large Queries , 1998, DEXA.

[18]  Guido Moerkotte,et al.  Constructing Optimal Bushy Processing Trees for Join Queries is NP-hard , 1996 .

[19]  Hamid Pirahesh,et al.  Complex query decorrelation , 1996, Proceedings of the Twelfth International Conference on Data Engineering.

[20]  Meikel Pöss,et al.  Of Snowstorms and Bushy Trees , 2014, Proc. VLDB Endow..