In-Memory Database option for Sybase Adaptive Server Enterprise


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.


“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

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

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

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.

Latest Articles