Empress Technical News - Nov 2009

                                               Empress In-Memory Tables  
                         Boost Performance in Your Embedded Database Application

Introduction

Embedded devices present new challenging areas of data storage and retrieval for an Embedded Database System. More and larger persistent storage devices are used for storing device data and that is where an Embedded Database comes into play. Typically flash technology devices (CF card, SD card) are widely used as persistent storage in devices such as digital cameras, digital camcorders, handheld computers, PDAs, media players, mobile phones, car infotainment systems or GPS receivers. Even tiny sized disks are heavily used nowadays in embedded systems.

When building an embedded system moving data from a persistent storage to memory can create bottlenecks. Embedded Database Systems can take advantage of large memory blocks with uniform access time instead of making page-by-page access to file system storage on a flash device. Using memory instead of persistent storage can boost performance.

Empress Embedded Database V10.20 offers many new features for application developers that can help improve database application performance. One of these features is  Empress In-Memory Tables.

Empress In-Memory Tables

Empress In-Memory tables are created by using a simple extension to the SQL CREATE TABLE command. Once the table is created, the definition of the table is stored in Empress system tables as meta-data and the table definition is made persistent. However, the actual table exists only in memory and only within a single database application process environment. If this database application spawns multiple threads, each of the threads will be able to access the Empress In-Memory Table. The Empress In-Memory Table is not visible to other processes or threads in other processes.

For non-process based environments such as VxWorks this is a very natural situation. Since this is a single process environment, every task created in the system will be able to access Empress In-Memory Tables.

The main benefit of using Empress In-Memory Tables is to improve performance. Using memory directly eliminates most file system access, eliminates caching overheads and involves less operating system calls.

Empress In-Memory Tables and persistent tables can co-exist in the same database. Once in memory, all associated database table objects such as indexes will automatically be created in-memory.

The SQL syntax for a persistent table and an In-Memory table are below:

Persistent Table SQL Example:

CREATE TABLE  t1 (
 id integer PRIMARY KEY pk_ind,
 Latitude   integer, Longitude  integer);
 

In-Memory Table SQL Example With Optional “Locate In Memory” Syntax:

CREATE TABLE  t1 (
 id integer PRIMARY KEY pk_ind,
 Latitude   integer, Longitude  integer)
 LOCATE IN MEMORY OPTION MEMORYSIZE=250M;

Empress In-Memory tables are non-persistent. In some cases, database applications cannot afford to lose the data located in memory. Empress provides a number of ways of making copies of the in-memory table data onto persistent storage, thus assuring persistency.

Possible ways to make In-Memory data persistent include usage of:

• Empress Export(only in non-process based operating systems)
• Empress Dump-into
• Application Level Synchronization

Robustness and ease of maintenance are typically very important for embedded systems. Embedded database users must put trust in the database management system at all times and cannot afford to manually administer the database. In mission critical embedded systems, database administration problems are to be avoided at all costs.

Keeping data in memory decreases the chances of media wear or corruption in the file system compared to a continually updated persistent storage device such as a CF, SD card or other flash card technology.

For operating systems such as Linux that provide good in-memory caching, the benefit of using In-Memory Tables might not be as obvious. However, embedded systems that perform limited caching or do not use caching at all can benefit even more from the Empress In-Memory Table feature.

The following table shows typical performance results comparing tasks done in persistent storage and in-memory. The performance ratio was about 4:1 giving good performance reasons to use In-Memory Tables as part of your application. 

What Makes a Good Candidate for In-Memory Tables

• Read-Only Data

• FrequentlyUpdated Data

• Data Sets thatFit into Available Memory

Read-Only data is a natural candidate for In-Memory Tables, since the data can easily be re-generated from a snapshot sitting in persistent storage.

Frequently updated data is also a good candidate for In-Memory Tables for a different reason, namely database robustness. Heavy updates make their toll on the longevity of persistent storage devices. Most of these devices have a limited life span in regard to the number of updates they can achieve without failure. Keeping data in memory and updating data in persistent storage less often can significantly prolong the life of the embedded device. Data can be synchronized to persistent storage as needed, in order not to lose data when the application/system is down.

Data Sets that Fit into Available Memory are another good candidate for In-Memory Tables. With memory chips getting larger and less expensive, more and more data can be stored in-memory cost effectively in Embedded systems. Empress provides an option to specify the data capacity of the in-memory database table in a developer controllable way.

Instead of Summary

Empress In-Memory Table feature is included in the latest Empress Ultra Embedded V10.20.  

Empress In-Memory Table allows you to use database schema to transparently put data either in persistent storage or in memory.

Empress In-Memory Table is an attractive option for embedded developers who search for new efficient and more robust solutions using an embedded database.

Empress Software Inc.
www.empress.com