In-Memory Database option for Sybase Adaptive Server Enterprise

February 2, 2010

by Jeffrey R. Garbus

In-memory database (IMDB) is a technique used to place an entire database – data, logs, and all – in cache. This capability enables you to treat memory-resident data the same way you treat any other ASE data. Plus, it’s quick, easy, and integrated. Read on to learn more.

Introduction

“Why can’t I just cache the whole database?”

“I don’t care about recovery, I want to turn off logging, I can’t afford the cost of the commits. I’ll restart everything in case of a failure.”

If I had a nickel for every time I’ve heard these requests over the years, I could buy a lot of coffee -- even at today’s Starbucks prices.

There are some good, valid reasons for wanting to leverage cache and turn off logging. Caching a database provides a significant performance gain. If 100% of all activity is in memory (which might be 1000s of times faster than disk), you not only don’t have to worry about any physical IO (and the corresponding waits and process-swapping that go along with it), but you also have the benefit of reduced optimization time as Sybase Adaptive Server Enterprise (ASE) can skip the costing of IO buffering etc. This is all accomplished with the ASE in-memory database (IMDB) option.

What about logging? ASE has a write-ahead log. First ASE logs the changes it needs to make, then it makes the changes, then it logs the fact that the transaction is to be committed; when that is flushed to disk, the commit has completed successfully. This means if you are adding 20 gigabytes of data, you’re potentially really writing 40 gigabytes[1] . For loads, in particular loading a data mart and/or warehouse or other reporting database, it is useful to be able to disable the commits (i.e. the physical writes to the transaction log). There are now 2 ways of doing this, by using either an IMDB or a relaxed-durability database (RDDB).

A bit about transactions

A transaction is an atomic unit of work. In simpler terms, any activity or combination of activities that needs to be performed as a unit may be bundled into a transaction. For example, when you transfer money from your checking account to your savings account using an ATM card, you want to know that in case of a sudden server crash, the money that came out of your checking account, and hadn’t yet made it to your savings account, finds its way back into the checking account. This is the purpose of a transaction.

From a more technical standpoint, transactions are supposed to pass the “ACID test,” this acronym is used to describe transactional properties:

• Atomic
• Consistent
• Isolated
• Durable

IMDB and RDDB relax “durable” and “atomic” by partially or completely eliminating transaction logging and writes to disk. The result is significant performance gains.

In-Memory Database

In-memory database (IMDB) is a technique used to place an entire database – data, logs, and all – in cache. This capability is a licensing add-on that enables you to treat memory-resident data the same way you treat any other ASE data (all T-SQL statements work). It’s quick, easy, and integrated.

Because it is memory-resident, everything should be treated the same way you would treat tempdb -- the database disappears completely when the server is shut down.

There is no disk storage and no permanence whatsoever. In other words, “durable” has been sacrificed for performance. All IO is eliminated. Transaction logging is still enforced for rollbacks, triggers, and replication, but it is entirely in memory.

When NOT to use IMDB

There’s a reason for an “ACID test” for databases; they’re built to house permanent data. You need to know, when your transaction commits, that you can later retrieve the information. This is where ASE shines.

If you need data permanence, and have no tolerance for data loss, use a disk-resident database (DRDB), which is ASE’s default behavior.

When to use IMDB

IMDB is best for situations where you don’t care about data persistence and where performance is a priority. Ease-of-use is another benefit of an IMDB. It is accessed exactly the same as any other database; there’s nothing new for developers to learn; business analysts simply need to remember that the data is ephemeral.

Use cases abound for IMDB. These include, but are not limited to:

• Frequently accessed look-up tables and cross-database or cross-server reference data
• Mass data loads and data cleansing have historically been performed in tempdb; you can now do that in a memory-resident database. You can even take this a step further by creating a memory-resident tempdb, create a tempdb group which includes this database, and place the data load user into that group (tempdb groups are also a feature of ASE 15.5)
• Data feeds can sometimes overwhelm physical storage, or transaction logs. Frequently, customers have acquired solid-state disk (SSD) for the very purpose of managing transaction-log performance problems.
• Short-term, transient data, like e-commerce shopping carts, where the data (for the cart) is temporary, and persistence of the data is irrelevant until the order is placed

Relaxed-Durability Database

Memory is less expensive today than ever before. However, there are physical limitations. A 1-terabyte database may not fit in cache. Or, you may simply not have sufficient memory. There may be times where you want or need to create an amalgam of an IMDB and DRDB; hence, the relaxed-durability database (RDDB).

RDDB gives you the logging performance of an IMDB (i.e. doesn’t write physical commits to disk), and the persistence of a DRDB; at least, as long as the system is shut down cleanly (if it is not, the database gets marked as suspect at startup).

The RDDB has two options, ephemeral and “mostly” permanent. The ephemeral option (no_recovery) means it is recreated at startup, like the IMDB. The “mostly” permanent option (“at_shutdown”) means that as long as the server is shut down nicely (i.e. it is not shut down with nowait) the database will remain available.

So, if it’s RDDB rather than IMDB for size reasons, use no_recovery only. If it’s RDDB rather than IMDB for persistence reasons, use “at_shutdown” as well.

[1] A detailed discussion of this topic is available in SyBooks.