In-Memory Database option for Sybase Adaptive Server Enterprise
February 2, 2010
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, its quick, easy, and integrated. Read on to learn more.
Why cant I just cache the whole database?
I dont care about recovery, I want to turn off logging, I cant afford the cost of the commits. Ill restart everything in case of a failure.
If I had a nickel for every time Ive heard these requests over the years, I could buy a lot of coffee -- even at todays 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 dont 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, youre potentially really writing 40 gigabytes . 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 hadnt 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:
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 (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). Its 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
Theres a reason for an ACID test for databases; theyre 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 ASEs default behavior.
When to use IMDB
IMDB is best for situations where you dont 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; theres 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:
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. doesnt 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 its RDDB rather than IMDB for size reasons, use no_recovery only. If its RDDB rather than IMDB for persistence reasons, use at_shutdown as well.
 A detailed discussion of this topic is available in SyBooks.