Performance of complex queries in Main Memory Database Systems

Abstract

Memory residence can buy both functionality and performance for a database management system. In this paper, we present a description and a benchmark of an experimental implementation of a Main Memory Database System (MMDBS) that was designed to support complex interactive queries. We describe and evaluate the main mem ory database structures and query processing algorithms implemented in this prototype. Our measurements and analysis, focused on aggre gates and joins, include both memory requirements and response time, since there is a clear trade-off between space and time in the design of a MMDBS. In contrast to conventional Disk-based Database Systems (DDBS's), we found that an MMDBS can efficiently execute complex relational queries. We identify strategies that exploit memory residence effectively. We also identified a number of performance problems re lated to query optimization in main memory and memory management for MMDBS's. INTRODUCTIONTwo major factors are defining new directions of research in data base management systems. One is the availability of very large and in expensive main memories. In the last year alone, the size of memory boards has doubled, and over the past 20 years, the price of main memory has decreased by a factor of 100. The other factor is the de mand for increased functionality and performance, particularly from new types of applications. Traditionally, database systems were used in large business applications. Today, integrated office systems, engi neering applications and artificial intelligence also require database management support. The "wish-list" for this new type of database systems include the support of complex objects [SR86], time manage ment [LDE84] and more powerful query languages [BR86].While a new generation of database systems seems necessary to answer these needs, this generation will most likely try to build on and extend relational database systems, which are mature and successful.However, the profile of a "typical" query mix will include queries that may be far more complex and unexpected than the "debit-credit" benchmark transaction (An85]. In particular, complex relational que ries involving multiple joins and aggregates, ad hoc queries and recur sive queries must be efficiently supported. Coupling the use of large main memories and support for complex queries requires a new ap proach to physical database design and query optimization. One ap proach that is being currently investigated is to make the database memory resident [DK084, Gr85], and design a query optimizer that takes advantage of memory residency [AHK85, Bi86, LC86, Sh86].In this paper, we present an experimental performance study of a Main Memory Database System (MMDBS) prototype, which is the foundation of the Office-By-Example (OBE) system [Z182, WAB86,Wh85] developed at the IBM T. J. Watson Research Center. To our knowledge, this is the first benchmark of a MMDBS. We focus on three problems:1.How much more feasible are complex, ad hoc interactive queries on a MMDBS (than on disk based database...

OLTP through the looking glass, and what we found there

“…Work on main memory indexing structures has included AVL trees [AHU74] and T-trees [LC86]. Other techniques for main memory applicability appear in [BHT87]. Complete systems include TimesTen [Tim07], DataBlitz [BBK+98], and MARS [Eic87].…”

Office-by-example: an integrated office system and database manager

“…In Table II, since the elapsed time for the shared machine varied depending on the system load, we presented only an approximate upper bound. Finally, we note that a formal benchmarking has been done, and the results can be found in [4] and [6]. The contents in Table II came partially from those results [5].…”

Query optimization in a memory-resident domain relational calculus database system

“…The performance estimation of the I/O bound system indicates that a demand paging should not be used in a memory-resident database system. Formal benchmarking has been done independently of the tests presented in this paper, and the results can be found in Bitton et al [3] and Bitton and Turbyfill [4]. We briefly summarize the results here.…”