SYBASE IMDB dreams come true. Rev. 8.2012. Andrew Melkonyan Senior Database Architect Ness Pro Division, NESS Professional Services, Israel. Are you multitasking?.
Senior Database Architect
Ness Pro Division, NESS Professional Services, Israel
How often do you switch to another task waiting for DB operation to finish? The truth is, it has nothing to do with multitasking. It is a waste of time. A necessary evil. Hmm, is it?…
Imagine loading your half-terabyte database in 5 minutes instead of two hours…
Imagine running reorg rebuild or building clustered index on a 10 GB table within 2 minutes instead of half a day…
Imagine your batches that “cursor” on 100K-s of inserts & 100K-s of updates return within minutes, instead of hours… days…
We are not talking about dreams. We are talking about IMDB – an internal feature available to any ASE customer out of the box.
Let’s inspect some numbers:
The idea of In-memory DB (Sybase IMDB) or Main Memory DB (MMDB technology) is not new. Neither it is unique to Sybase.
First technological attempts go back to mid 80s. Today, vendors other than Sybase have come out with their MMDB solutions (Oracle, IBM, Open-Source Community):
The need for fast-performing DBMS is well understood.
What is unique to Sybase is that Sybase has chosen not to treat this technology as a separate product, with its SQL semantics, its connectivity drivers, it own optimizer or it own data/index structure.
Having done that may have brought Sybase MMDB solution more performance advantages in certain circumstances. As it is, Sybase greatest advantage is that its IMDB solution is integral to ASE. As such it may be implemented within seconds with almost zero learning curve.
In fact, rather than inventing new architecture Sybase rethought basic RDBMS principle of ACID transactional integrity and made it customizable within ASE server itself, with full ACID set as default.
The ACID principle has been 'sacred' for RDBMS for years:
From ASE 15.5 on, ASE allows to relax Atomicity and Durability in the existing ASE DB environment. This flexibility allowed two high-performance configurations to surface: IMDB and RDDB.
Data resides completely in memory. No I/O. No intermediate layer to the data.
Data resides on a physical disk and is accessed through memory layer (LIO – PIO chain).
Getting rid of physical storage has its price:
IMDB is fully consistent on index/page level, very fast – can be loaded within minutes, but its data is volatile.
In addition to having zero I/O footprint, IMDB avoids some bottlenecks typical to DRDB databases:
Since IMDB dies on shutdown, support for various replicated architectures have been developed in the recent ASE/RS versions.
As of today, IMDB supports functioning as both primary and replicate site.
To the left is architecture with IMDB functioning as Primary ASE:
Below is replicate architecture with IMDB serving as Replicate ASE (replication to the template DB is used for recovery from IMDB shutdown):
These are the only steps needed to setup IMDB database:
Load your data in minutes and start performing in seconds:
Imagine the following situation: your batch process runs 24 hours processing 100s of tables with 100GB+ of data & indices. What is the most of its time spent on? I/O, poor indexing, transaction log management, data cache management, page splits, &c.
Setting up the IMDB database will take you 5 minutes.
Loading 100GB DB will take a couple more minutes.
BCP in (in the worst case) 100GB+ will take you less than an hour.
Running the same batch on the freshly created IMDB will probably end well before the same batch running on DRDB has done half of its work. And if while running the batch you suddenly discover that you are missing some crucial index and the optimizer goes wild building it will be a matter of minutes.
This is amazing flexibility and amazing freedom compared to the fear of ever touching poorly performing production batch…
Imagine the following situation: you are facing a new project for which mining the current data is crucial. You have hundreds of gigs of it. You need to be able to group, test, select appropriate index structures. Each empirical test on DRDB takes lots and lots of time…
It is a matter of having enough RAM and an available IMDB database to turn this into a playground for kids. Mining data within seconds or minutes. Testing index structures. Rebuilding. No fear. No waste of time. You don’t have to think twice before changing existing legacy SQL code which was written for small tables and now runs badly against huge tables. You may just test and decide based on empirical data.
Again, no fear of change. No waste of time…
IMDB opens the horizons for performance and allows flexibility and freedom unthought-of before.
You are dictating the rules, not your storage.
It is DB than serves your needs, not you who is serving the needs of your DB…