Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

Main Memory Database - DOC by hcj

VIEWS: 112 PAGES: 10

									    A Comparison Study of Main Memory Databases
              Disk-Resident Databases
                       Fahimeh Raja      Niloofar Razavi       Melody Siadaty

                                           Department of ECE
                                           University of Tehran
                                              Tehran, Iran
                               {f.raja, n.razavi, m.siadaty}

1. Abstract
Main Memory Database systems (MMDB) store their data in main physical memory and
provide very high-speed access. Conventional database systems are optimized for the
particular characteristics of disk storage mechanisms. Memory resident systems, on the other
hand, use different optimizations to structure and organize data, as well as to make it reliable.
This paper provides a brief overview on MMDBs and one of the memory resident systems
named FastDB and compares the processing time of this system with MS-SQL Server based
on the results of the implementation of TPC benchmark on both.

2. Introduction
The idea of Main Memory Database (MMDB), using physical memory as primary storage
and probably a disk subsystem for backup, has recently been an active research topic.
MMDBs can achieve significant improvements in performance, processing time and
throughput rates over conventional database systems by eliminating the need for I/O to
perform database applications.
In contrast with the conventional DBMS systems, some problems are introduced in the
MMDB environment. The major problem deals with the volatility of main memory; so in this
framework the issues concerned with efficient database recovery are more complex than in
traditional DBMS systems [13].
To facilitate this shift from conventional databases to MMDBs, database systems must be
redesigned to not only take advantage of the performance benefit but also to handle
implementation issues surrounding the inherent differences between disk and memory
storage [11].
In this paper we study the MMDB systems and their differences with DRDBs; we use
FastDB, a highly efficient main memory database system, to implement TPC benchmark on
and compare its performance with a DRDB such as MS-SQL.FastDB assumes that the whole
database is present in RAM and optimizes the search algorithms and structures according to
this assumption. Our performance results indicate that elimination of the overhead time
caused by transferring database files to the buffer pool and vice versa, makes FastDB work
significantly faster than a traditional database, therefore it fits more efficiently to the
requirements of today’s real time applications.

The remainder of this paper is organized as follows: in section 3 MMDB and its
corresponding issues are described; section 4 and 5 will provide details of FastDB as an
efficient main memory database and TPC as a performance benchmark; Section 6 details the
system, environment, applications under which the experiments are conducted; Section 7
discusses the experimental results. At the end, section 8 outlines the conclusions and section 9
represents future directions of the work.

3. Main Memory Database (MMDB)
During the mid-1980s falling DRAM prices seemed to suggest that future computers would
have such huge main memories that most databases could entirely be stored in them [4]. In
such situations, it would be possible to eliminate all (expensive) I/O from DBMS processing.
This seriously changes the architecture for a DBMS. An important question in a MMDBMS
is how to do transactions and recovery in an efficient way. Some of the proposed algorithms
assume that a (small) stable subset of the main memory exists. These stable memories can be
used to place, e.g., a redo log. Others do not assume stable memories, and still use I/O to
write transaction information to stable storage. These algorithms hence do not eliminate I/O,
but minimize it, as the critical path in a MMDBMS transaction only needs to write the log;
not data pages from the buffer manager.
A general MMDB architecture consists of a main memory implemented via standard RAM
and an optional non-volatile (stable) memory. The main memory holds the primary copy of
the database, while the stable memory holds the log and a backup copy of the database. A
logger process flushes the logs asynchronously to the disk. Alternatively, an additional
nonvolatile memory may be used as a shadow memory. This memory is intended to hold
updates performed by active transactions and the tail end of the log that contains information
about those updates.
The main asset of a MMDBMS is its unparalleled speed for querying and update. It turns out,
that simple data structures like the binary AVL tree, called T-Tree, and simple bucket-
chained hash outperform bread-and-butter disk-based structures like B-tree and linear hash,
due to the fact that the only costs involved in index lookup and maintenance are CPU and
memory access. The T-Tree is an order-preserving tree structure designed specifically for use
in main memory whose primary goals are to reduce overall computation time while using as
little memory as possible [4].
A specific problem in MMDBMS is query optimization. The lack of I/O as dominant cost
factor means that it is much more difficult in a MMDBMS to model query costs, as they
depend on fuzzy factors like CPU execution cost of a routine. Therefore, DBMS query
optimization tends to make use of simple cost models that contain “hard” constants obtained.
One challenge in this area is to model the interaction between coding style, hardware factors
like CPU and memory architecture and query parameters into a reliable prediction of main
memory execution cost.
Because access to main memory is so much faster than disk access, we can expect
transactions to complete more quickly in a main memory system. In systems that use lock
based concurrency controls, this means that locks will not be held as long, and suggests that
lock contention may not be as important as it is when the data is disk resident. Systems that
choose small locking granules (fields or records) do so to reduce contention. If contention is
already low because data are memory resident, the principal advantage of small lock granules
is effectively removed.

MMDB systems are ideal for applications that require high throughput and a fast response
time. With the increasing demand for high-performance systems and the steady decrease in
memory cost, MMDBs have become an attractive alternative to DRDBs.
While meeting the high-performance demand of many real-time applications, MMDBs are
naturally more vulnerable to failures than conventional DRDBs. Thus, the recovery
component of an MMDB system must be properly designed, implemented and maintained.
Three aspects of the recovery subsystem serve to ensure that the MMDB can recover from
any failure: logging, check pointing and reloading.
Logging maintains a log of update activities that occur during normal transaction execution.
Checkpointing takes a snapshot of the database periodically and copies it onto stable storage
for backup purposes. By reducing the number of log records that must be examined during
system restart, checkpointing limits the amount of work necessary to recover from failures.
Following a system crash, reloading restores the database to a consistent state. It first reloads
into the main memory the backup copy that is recorded during the last checkpoint. A
consistent state of the database is then achieved by applying information in the undo and
redoes logs to the reloaded copy [11].

4. FastDB
FastDB is a highly efficient main memory database system with realtime capabilities and
convenient C++ interface. FastDB is optimized for applications with dominated read access
pattern. High speed of query execution is provided by the elimination of data transfer
overhead and a very effective locking implementation. The Database file is mapped to the
virtual memory space of each application working with the database. So the query is
executed in the context of the application, requiring no context switching and data transfer.
Synchronization of concurrent database access is implemented in FastDB by means of atomic
instructions, adding almost no overhead to query processing. FastDB assumes that the whole
database is present in RAM and optimizes the search algorithms and structures according to
this assumption. Moreover, FastDB has no overhead caused by database buffer management
and needs no data transfer between a database file and buffer pool. That is why FastDB will
work significantly faster than a traditional database with all data cached in buffers pool [1].
FastDB supports transactions, online backup and automatic recovery after system crash. The
transaction commit protocol is based on a shadow root pages algorithm, performing atomic
update of the database. Recovery can be done very fast, providing high availability for
critical applications. Moreover, the elimination of transaction logs improves the total system
performance and leads to a more effective usage of system resources [1].
Although FastDB is optimized in the assumption that database as a whole fits into the
physical memory of the computer, it is also possible to use it with databases, the size of
which exceeds the size of the physical memory in the system. In the last case, standard
operating system swapping mechanisms will work. But all FastDB search algorithms and
structures are optimized under the assumption of residence of all data in memory, so the
efficiency for swapped out data will not be very high.
FastDB is an application-oriented database. Database tables are constructed using
information about application classes. FastDB supports automatic scheme evaluation,
allowing users to do changes only in one place: in users’ application classes. FastDB
provides a flexible and convenient interface for retrieving data from the database. A SQL-
like query language is used to specify queries. Such post-relational capabilities as non-atomic

fields, nested arrays, user-defined types and methods, direct interobject references simplifies
the design of database applications and makes them more efficient. FastDB uses a notation
more popular for object-oriented programming than for a relational database. Table rows are
considered as object instances, the table is the class of these objects. Unlike SQL, FastDB is
oriented on work with objects, instead of SQL tuples. So the result of each query execution is
a set of objects of one class [1].
The main differences of the FastDB query language from standard SQL are:
 There are no joins of several tables and nested subqueries. The query always returns a set
    of objects from one table.
 Standard C types are used for atomic table columns.
 There are no NULL values, except null references.(due to agreement with C.J. Date's
    criticism of three-value logic and his proposal to use default values instead)
 Structures and arrays can be used as record components. A special exists quantor is
    provided for locating elements in arrays.
 Parameterless user methods can be defined for table records (objects) as well as for
    record components.
 User functions with (only) one single string or numeric argument can be defined by the
 References between objects are supported including automatic support for inverse
 Construction of start from follow by performs a recursive records traversal using
 Because the query language is deeply integrated into C++ classes, a case sensitive mode
    is used for language identifiers as well as for keywords.
 No implicit conversion of integer and floating types to string representation is done. If
    such conversion is needed, it must be done explicitly.

The following rules in BNF-like notation specify the grammar of the FastDB query language
search predicates:

select-condition ::= ( expression ) ( traverse ) ( order )
expression ::= disjunction
disjunction ::= conjunction
        | conjunction or disjunction
conjunction ::= comparison
        | comparison and conjunction
comparison ::= operand = operand
        | operand != operand
        | operand <> operand
        | operand < operand
        | operand <= operand
        | operand > operand
        | operand >= operand
        | operand (not) like operand
        | operand (not) like operand escape string
        | operand (not) in operand
        | operand (not) in expressions-list
        | operand (not) between operand and operand
        | operand is (not) null

operand ::= addition
additions ::= multiplication
        | addition + multiplication
        | addition || multiplication
        | addition - multiplication
multiplication ::= power
        | multiplication * power
        | multiplication / power
power ::= term
        | term ^ power
term ::= identifier | number | string
        | true | false | null
        | current | first | last
        | ( expression )
        | not comparison
        | - term
        | term [ expression ]
        | identifier . term
        | function term
        | exists identifier : term
function ::= abs | length | lower | upper
        | integer | real | string | user-function
string ::= ' { { any-character-except-quote } ('') } '
expressions-list ::= ( expression { , expression } )
order ::= order by sort-list
sort-list ::= field-order { , field-order }
field-order ::= [length] field (asc | desc)
field ::= identifier { . identifier }
traverse ::= start from field ( follow by fields-list )
fields-list ::= field { , field }
user-function ::= identifier

Identifiers are case sensitive, begin with a a-z, A-Z, '_' or '$' character, contain only a-z, A-Z,
0-9, '_' or '$' characters, and do not duplicate a SQL reserved word.
List of reserved words in FastDB is: {abs, and, asc, between, by, current, desc, escape, exists,
false, first, follow, from, in, integer, is, length, like, last, lower, not, null, or, real, start, string,
true, upper}.
ANSI-standard comments may also be used. All characters after a double-hyphen up to the
end of the line are ignored. FastDB extends ANSI standard SQL operations by supporting bit
manipulation operations. Operators and/or can be applied not only to boolean operands but
also to operands of integer type. The result of applying the and/or operator to integer
operands is an integer value with bits set by the bit-AND/bit-OR operation. Bit operations
can be used for efficient implementation of small sets. Also the raising to a power operation
(x^y) is supported by FastDB for integer and floating point types [1].

5. TPC Performance Benchmark
The Transaction Processing Performance Council (TPC) has defined a series of benchmark
standards for database systems.
The performance metric is throughput, expressed as transaction per second (TPS). Further,
for business applications, cost is of great importance. Hence the TPC benchmark also
measures performance in terms of price per TPS. A large system may have a high number of
transactions per second, but may be expensive (that is, has a high price per TPS).
The first in the series was the TPC-A benchmark, which was defined in 1989. This
benchmark simulates a typical bank application by a single type of transaction that models
cash withdrawal and deposit at a bank teller. The transaction updates several relations such as
the bank balance and the customer’s balance. The benchmark also incorporates
communication with terminals, to model the end to end performance of the system
realistically. The TPC-B benchmark was designed to test the core performance of the
database system. It removes the part of the TPC-A benchmark that deal with users,
communication, and terminals, to focus to the bank-end database server.

6. Implementation Methodology and Measurement Environment
In order to compare performance time between MMDB and DRDB, we implemented a
Visual C++.NET interface in conjunction with FastDB, and then using TPC A benchmark.
We used a set of evaluation queries (including select, insert, update and delete) for variable
number of table records and variable number of updates which were implemented by the
same interface, both for main memory and MS-SQL databases .Notice that in the case of
update, number of updates is equal to the value, entered by user. Also the recorded time is
calculated from the beginning of an “existing check” –which checks whether the produced
random source/destination account/branch exists or not -to the end of the query execution.
Selective query is designed so that it selects for half of the branches, all of the accounts
which have a balance less than the original account balance. The experiment was performed
on a Pentium IV (2.4 GHz) system with Windows XP as operating system and 256 MB of

7. Experimental Results
Below are some of the results of our evaluation tests on the comparison of FastDB and MS-

7.1.1. Insert time measurements
To have some observations about differences in performance time between MMDB and
DRDB, we tested our benchmark in insert status with varying number of account table
records, in the range of 1000 to 100000 tuples. The results of these measurements are shown
in the diagram below. As can be seen from this diagram, by increasing the number of table
records, the required time for performing update transactions, increases. Insertion time for
larger amounts of records can be estimated by scaling the following diagram, also it is
noticeable that FastDB insertion time is about 1000 times less than MS-SQL’s. As a result, it
is predictable that in applications which require short and predictable response times,-such as
real-time applications-conventional disk-based database systems, will not suit their
performance requirements. Therefore for such problems MMDBs are somewhat more
appropriate solutions.

                  m Sec(log)                       Insert Time
                                 0     20000    40000     60000   80000 100000 120000
                                                       # of records

    Figure 1: Comparison of insert time between FastDB and MS-SQL for varying number of records.

7.1.2. Update time measurements:
For the update case, we performed the test with constant number of updates and variable
number of records for the table on which updates are performed. As the diagram below
shows, FastDB has approximately a steady response time, although MS-SQL reaches a final
steady status too, but its performance time is much longer than FastDB’s. Actually its
performance time grows linearly with the number of records, and certainly it will not afford
today real time applications with huge amount of data which are much higher than our
experimental pilot.

                                                       Update Time
                 m Sec(log)
                     1000                                                                  FastDB

                      100                                                                  MS-SQL

                            0        20000     40000     60000    80000   100000 120000
                                                   # of records

        Figure 2: Comparison of update time between FastDB and MS-SQL for varying number of
                               records,with constant number of updates.

7.1.3. Query time measurement:
As described in section 6, we used a sample range query in this test, which selects the
accounts whose account balance is less than the original amount. In the following diagram,
results of this test are shown. Here again like the case of update, FastDB has a more steady
state than MS-SQL, and the main important difference is the noticeable ratio of the
performance times. Since query time has an important role in today’s real-time applications,
it seems more efficient to use memory resident data bases.

                                           Query Time
                 m Sec(log)
                    1000                                                       FastDB
                     100                                                       MS-SQL
                           0   20000   40000   60000   80000   100000 120000
                                           # of records

    Figure 3: Comparison of query time between FastDB and MS-SQL for varying number of records.

8. Conclusion
A wide range of application areas such as real-time/military applications require very short
and predictable response time. Disks have high response time, and are not suitable as primary
storage media for them. Main Memory has short response time, and its decreasing cost makes
it affordable and suitable for these applications. Volatility of memory raises concerns about
durability of in-memory data. Main Memory DBMS manages in-memory data and ensures
ACID properties on it.
In this paper, we have discussed the need behind MMDBs and its differences from
conventional disk-based DBMS. Our goal was to have some comparison experiments
between a disk resident DBMS and a main memory one, so we introduced one of the existing
MMDBs, called FastDB., and then designed and implemented an interface to compare it with
MS-SQL. After taking an overview of the used methodology and benchmark, results of the
evaluation tests are explained. As the results show, FastDB is an application-oriented
database, and is optimized for applications with dominated read access pattern. FastDB
assumes that the whole database is present in RAM and eliminates the overhead time due to
transferring database files to the buffer pool and vice versa, therefore FastDB will work
significantly faster than a traditional database which caches all of its data in buffers pool.

9. Open Problems and Future Work
Like almost every research also the work presented here leaves some questions unanswered
and even discovers new problems. Still many areas for future work remains; some of these
open issues shall be mentioned here:
 FastDB is not a true real time system, because it is based on non-realtime operating
   systems (like NT or UNIX), which mostly doesn't fit realtime requirements; it is
   recommended to evaluate some other main memory DBMSs, which are truly realtime
 FastDB uses T-tree for indexing the database and some mechanism like “shadow paging”
   to guarantee quick and accurate recovery, therefore it is desirable to evaluate some other
   systems or improve FastDB to use newly designed index structures and recovery

   A major area of study will address the issue of efficient loading for MMDBs including
    the idea of partitioning. This will examine methods for distributing log and archive
    database information across multiple secondary storage devices.

10. Acknowledgements
We would like to thank Konstatin Knizhnik for providing the latest version of FastDB online
and for his e-mail support and help with development of FastDB applications. We also wish
to thank Dr. Rahgozar for his encouragement and many helpful comments, suggestions, and
directions. It was always encouraging and motivating to see his smiling face and his
eagerness to help us, even when we barged into his room without an appointment.

11. References
-[1] FastDB: a main-memory database object-relational database system. July 2005

-[2] Inseon Lee, Heon Y.Yeon, Taesoon Park, “A New Approach for Distributed Main
Memory Database Systems: A Causal Commit Protocol”, IEICE TRANS. INF. & SYST.,

-[3] Nicholas Carriero, Michael V. Osier, Kei-Hoi Cheung, Peter Masiar, Perry L. Miller,
Kevin White, Martin Schultz, “ Exploring the Use of Main Memory Database (MMDB)
Technology for the Analysis of Gene Expression Microarray Data”, Technical report, April

-[4] Stefan Manegold, “Understanding, Modeling, and Improving Main-Memory Database
Performance”, November 2002

-[5] Philip Bohannon, Peter McIlroy, Rajeev Rastogi,”Main Memory Index Structures with
FixedSize Partial Keys”, ACM SIGMOD 2001 May 2124,Santa Barbara, California, USA

-[6] S. Manegold, P. A. Boncz, and M. L. Kersten. “Optimizing Main-Memory Join on
Modern Hardware”, IEEE Transactions on Knowledge and Data Engineering (TKDE),
14(4):709–730, July 2002.

-[7] J. Rao and K. A. Ross. “Making B+-Trees Cache Conscious in Main Memory”, In
Proceedings of the ACM SIGMOD International Conference on Management of Data
(SIGMOD), pages 475–486, Dallas, TX, USA, May 2000.

-[8] Tobin J. Lehman, Michael J. Carey, “A Study of Index Structures for Main Memory
Database Management Systems”, Proceedings of the Twelfth International
Conference on Very Large Data Bases, Kyoto, August, 1986

-[9] Rajeev Rastogi, S. Seshadri, Philip Bohannon, Dennis Leinbaugh, “Logical and
Physical Versioning in Main Memory Databases”, Proceedings of the 23rd VLDB
Conference, Athens, Greece, 1997

-[10] Hector Garcia-Molina, Kenneth Salem, “Main Memory Database Systems: An
overview”, IEEE 1992

-[11] Piyush Burte, Boanerges Aleman-Meza, D. Brent Weatherly, Rong Wu, “Transaction
Management for a Main-Memory Database”

-[12] Tobin J. Lehman, Michael J. Carey, “Query Processing in Main Memory Database
Management Systems”, ACM, 1986

-[13] Margaret H. Eich, “Main Memory Database Recovery”, IEEE 1986

-[14] J. Baulier, P. Bohannon, S. Gogate, C. Gupta, S. Haldar, S. Joshi, A. Khivesera, H. F.
Korth, P. McIlroy, J. Miller, P. P. S. Narayan, M. Nemeth, R. Rastogi, S. Seshadri, A.
Silberschatz, S. Sudarshan, M. Wilder, and C. Wei. “DataBlitz Storage Manager: Main-
Memory Database Performance for Critical applications”, In Proceedings of theACM
SIGMOD International Conference on Management of Data (SIGMOD), pages 519–520,
Philadephia, PA, USA, June 1999.


To top