# Design of Main Memory Database System/Overview of MMDB

Chapter 3: Introduction to Main Memory Database Systems
Table of Contents — Previous: Introduction to DBMS — Next: Storage Engine

## 3.1 Overview

Many applications like telecom, process control, airline reservation, stock market, etc. require real time access to data. In addition to maintaining data consistency, these applications require timely response for the transactions accessing database. Main memory databases, which became feasible recently with the increasing availability of large cheap memory, can provide better performance and consistent throughput than disk based database systems. As the data resides permanently in main memory, it will not involve any disk I/O that affects the throughput. In effect, for disk-based systems, standard deviation for throughput will be huge and real time applications will not be able to benchmark against that unpredictable throughput. This led to the development of new type of database management system called, Main memory database (MMDB) system. It is also called as “In-Memory database (IMDB) management system” and “Real Time Database (RTDB) management systems”.
As the basic underlying assumption changed in this type of database management system, it leads to research and design of each and every component of storage, relational and sql engine of the traditional disk based management system.
One disk block transfer takes approximately 5 milliseconds whereas main memory access takes 100 nanoseconds. By keeping the whole database in memory, these disk I/Os are converted to memory access, thereby improves the throughput by many-folds. This leads to an interesting question “In DRDBs if all the data is cached in buffer pool, then they will perform as good as MMDBs?” Unfortunately the answer is No. This is because the data structures and access algorithms are designed for disk based system and cannot work well when whole database is completely in memory. There are some common misconceptions about main memory databases
Do they support durable transactions?
Yes. Though the complete database is in main memory, backup copy is kept in disk to enable recovery in the event of crash.
Can multiple users concurrently access the database?
Yes. Multiple users and multiple threads shall access the database and are synchronized through latches and locks.
The main memory resident of data have important implications in
* Data Representation
* Data Access Algorithms – Query Processing
* Recovery
* Concurrency control
This book will discuss more in detail about the difference between DRDB and MMDB implementations and how MMDB is times faster than DRDB.
Figure 4, depicts a main memory database management system. This has nearly all the components, which are present in disk resident database management system. Implementations of components under SQL Engine, Relational Engine, and Storage Engine differ heavily from the DRDB components.
Fig 4: MMDB System Architecture
In case of MMDB, physical entity that corresponds to database is either shared memory segment or memory mapped file. These Inter Process Communication (IPC) mechanisms allow sharing of memory across processes. There are some JAVA embedded main memory databases that require only multi-thread access. These DBMSs use heap memory as database. For multi process access, we should either go with shared memory IPC or memory mapped IPC mechanism. Usually the control information is stored separately from the data records to avoid data corruption of control information.

## 3.2 Memory Segment

Memory is divided into three main segments.
Control Segment containing lock table, process table, transaction table, undo logs and redo logs. These structures are transient and are required for the operation of the database management system.
Catalog Segment containing meta data about tables, indexes, fields, etc.
User Segment containing records of all the tables and index records of those tables, if any.

## 3.3 SQL Engine

Optimization and execution of the SQL statements change drastically in case of MMDBs. Cost based optimization is mainly based on the number of disk I/Os rather than the CPU cycles. These cost based optimization is the complex system in the whole DBMS. Both cost and rule based optimizers are used in case of DRDBs. But in case of MMDB rule based optimizer would suffice.

## 3.4 Relational Engine

All the relational operation algorithms change as we do not need to use two pass algorithms for implementing the operations. Moreover in case of DRDBs when we do first pass we copy the data from disk to memory for all the fields. Incase of MMDB, we shall use pointer indirection and avoid data copy and disk I/O.
T-Tree index structures are better in case of space and cpu cycles for MMDB than B-Tree of DRDB.

## 3.5 Transaction Engine

As MMDB transactions no more wait for disk I/O, it will lead to lot of contention issues and deadlocks, which will hamper the throughput. This needs to be avoided and given more importance in case of MMDB than DRDB

## 3.6 Storage Engine

In DRDB, allocations happen based on the disk blocks. But in MMDB, it should happen in memory. This leads to lot of optimization in the allocation algorithms, which shall save space as well as CPU cycles. Database modules works with File Manager module in case of DRDB, whereas it works with OS IPC abstraction layer in case of MMDB.
Table of Contents — Previous: Introduction to DBMS — Next: Storage Engine