jump to navigation

How is an in-memory database (IMDB) different form binding your entire database to a Cache March 28, 2012

Posted by Mich Talebzadeh in In-Memory databases.

This is a question that is frequently asked. What happens if I pin my whole database into a cache. Will that make it an in-memory database?

The answer below is aimed at Sybase ASE whereby you can bind  a full database to a  named cache. The principal can apply to any other DBMS.

The short answer is no. The reasons are:

  •  Putting your entire database in cache only improves read performance, not write performance
  • Pages in cache are a mirror image of the page in the file system
  • Transactional activities have to be flushed to the log (transaction log or redo log) on disk
  • Data activities have to be flushed to disk
  • The DBMS engine doesn’t know that you have created a cache large enough for the entire database and hence has to execute cache lookup logic regardless

With your in- memory database

  •  Database is fully memory resident
  • Read performance and more importantly write performance as there is no data or log flushing to disk
  • Sybase have modified ASE-IMDB internal logic to eliminate irrelevant processing when maintaining an in-memory database . These include LRU/MRU chain of pages, and the concepts of large I/O buffer pools, ‘wash marker’, asynchronous prefetch, and buffer replacement policy fetch and discard
  • To best of my knowledge Sybase have not updated the optimizer to take into account different cost metrics for IMDB. As far as I know, the most visible change that optimizer has made for IMDB is costing the plan assuming the user IMDB table is fully cached in memory (i.e. the physical IO cost for the table access is 0)
  • The transaction log is moved from disk to in-memory, and Sybase have done some optimizations on transaction logging for IMDB, again, avoiding irrelevant processing
  • There has been no change in the index algorithms.  So in other words, there has not been any attempt to reduce storage space consumption when the database is in-memory
  • When dealing with very large amounts of data this can mean significant overhead and additional memory for processing in-memory

I think it is also important to clarify the indexing in IMDBs. In normal database like ASE classic or Oracle an index stores an ordered replica of a subset of the information stored in the rows of a table, as well as a pointer to the source row.  In other words, it contains copies of certain columns from the row as well as a pointer to the row.  If you create a composite  index on each column referenced in the query’s select list and in clauses like “having”, “group by”, and “order by”, the query can be satisfied by accessing the index only without going to the base table. The so called index covering. So there comes the debatable points about ASE IMDB. When a DBMS designed as a container for disk based bases also contains an IMDB, often by simply redeploying the original database system with memory-based analog of a file system or raw partition if you like, artefacts of its origins remain. These can inhibit performance and waste system resources. For example, traditional DBMSs store redundant data  in their indexes. This is useful for on-disk databases: if sought-after data resides in the index, there is no need to retrieve it from the data file, and *I/O* is prevented. But in this type of IMDB, this redundant data is typically still present in the indexes, consuming storage space even though the entire table is now in memory. There is no longer any performance advantage from the redundant data—it just wastes memory. In contrast, a database designed from the ground up as an in memory construct does not store redundant data in its indexes.


No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: