By Peter Dobler
The slogan “Bigger, Better, Faster” was always the driving
force behind the database technology evolution. The latest release of Sybase
ASE 15.5 is no exception.
Sybase ASE 15.5 introduces many new features and several
highly anticipated improvements to features introduced in previous Sybase
releases, and the most exciting new feature is the in-memory database (IMDB).
Performance Gains with New Databases and Durability
The other new feature of interest is the Relaxed Durability
Database (RDDB). This database’s best use is when you need more persistence
than within the IMDB’s ASE’s memory. This database is disk-based and not
limited by memory size.
To fully understand Sybase’s adaptation of IMDB, you need to
understand the impact of the new durability levels that can be set for each database
individually. These durability levels make the new IMDB and Relaxed Durability
databases possible. With these new options, the performance gain goes way
beyond in-memory databases. It brings in-memory style performance gains to disk
bound databases as well.
Improving the Read/Write Situation
When confronting the performance challenges, there’s always
the question “Do you want to improve the read or the write performance?”
Obviously there’s a big difference between the two.
Traditional relational database systems must adhere to one
simple rule when processing transactions: ACID (Atomicity, Consistency,
Isolation, and Durability). In short, every transaction must wait for the disk
sub system to confirm a write to disk. Over the years, Sybase developed highly
sophisticated cache management procedures and file I/O management functions to
mitigate this obvious performance bottleneck.
Read/Write, ACID, and tempdb
Even the tempdb is not totally immune to the ACID
requirement. Substituting tempdb for an IMDB doesn’t really produce the same
results. That’s why it is important to understand how ACID interferes with
write performance improvements. You can only push write performance to a
certain limit and adhere to ACID at the same time.
The Value of the IMDB Solution
In-memory databases (IMDB) bypass this disk writing
requirement, and that’s what improves the speed. Designed for high volume
transaction systems like e-commerce shopping carts, IMDB are unbeatable when it
comes to writing transaction data. This is fundamentally different to data
caching of traditional database engines. Data caching improves read
performance, but does nothing to improve write performance.
Running the entire database in memory, including transaction
writes, bypasses the hardware architecture constraints, and takes full
advantage of the best performing parts of any hardware configuration, the CPU
and the physical RAM. Essentially, IMDB cuts out the weakest links in the chain
when it comes to performance, and physical disk I/O.
In addition to running everything in memory, all the waits
for write confirmation in any transaction processing have been eliminated.
Sybase introduced 3 new durability levels for databases.
These durability levels indicate the ability of a database to retain its state
This is the standard durability
level and it reflects the ACID recovery model. It is the default if no other
durability level is selected during database creation.
This does exactly what it says.
There will be no recovery and, in essence, no data; not even DDL, will be saved.
This applies during a crash or during regular shutdown.
To mitigate the loss of DDL and
data, IMDB can be created based on a template database. This database can
contain anything the IMDB needs: DDL, stored procedures, triggers and data.
This will create a clean IMDB at every system start.
This reflects the so called Relaxed
Durability Level database. This will not protect data and DDL during a system
crash or unfriendly shutdown, but it preserves all data and DDL during normal
With the ASE 15.5 release, IMDB can only have no_recovery
durability, which limits its use. Hopefully the at_shutdown option will be
available soon. With a relaxed durability level IMDB, more applications can
take advantage of this technology.
Benefits of the no_recovery Option
As an interesting side effect; you can use no_recovery with
a normal disk bound database and you will get the same effect as the IMDB. Why
should you consider this option? Sometimes the cost of transferring a 200 GB
database into an IMDB does not outweigh the performance gains. Or, you simply
cannot afford the significant cost of converting 200 GB cheap disk space into
very expensive RAM modules. Despite the fact that it is also the fastest,
spending this money is not always possible. That’s why applying no_recovery
durability is the next best thing. You will still benefit from a dramatically
improved write performance, while saving money utilizing your existing disk I/O
Applications That Benefit the Most from IMDB
E-Commerce applications and especially shopping carts are prime
examples for IMDB implementations.
Shopping carts are traditionally
very write-intensive applications that create a lot of contention in
traditional databases. The nightmare scenario for every e-commerce application
is not keeping up with the demand from increased traffic to the website,
overwhelming the application server. The database spends more time blocking
sessions due to I/O waits. The result is always the same – lost revenue. With
IMDB you can put all these worries to rest.
Web applications that dynamically compile content based on user
profiles, like portfolios or historical sales records.
Every time you have a database as a
potential bottleneck to quickly assemble content and provide customized data to
thousands of concurrent sessions, IMDB will lessen that bottleneck to a large
degree. Keeping the data lookup to a minimum during page changes is the key to
performance. IMDB can provide the answer to serve the data from your data
warehouse to the client web application.
Hub and aggregate, any aggregate database that can reproduce the
load process from the source to the missing ACID compliance of IMDB.
In case of a catastrophic failure,
the source data must be reloadable. A good example would be any form of a ticker
application, where data from multiples sources is being compiled in real time,
aggregated, and then served to thousands or millions of consumers.
High read volume; Named cache vs. IMDB.
Tuning the cache goes a long way for read-intensive applications, but recent
performance benchmarks showed that IMDB improves the read performance by a
factor of 2 compared to a named cache setup with the same data. This is mostly
due to reduced overhead in IMDB. If you have a reference database which is
currently fully cached in a named cache, converting this database into an IMDB
is very easy. A simple dump and load from the reference database into the IMDB
will prep the data.
As a rule of thumb, small reference databases and lookup data stores currently kept
in a named cache environment for performance reasons are ideal candidates to be
converted into an IMDB. You can keep the data current with dump and load from a
traditional database, and for added convenience, use the reference database as
a template when creating the IMDB.
Mixed Use Transactions
With the introduction of IMDB and RDDB, Sybase also made sure that these new
database durability options are fully integrated. A single transaction can
spawn across all three database types, which are the full ACID standard
database, RDDB and IMDB. This means that applications can take full advantage
of the high speed IMDB for reference data and temporary data store, the still
fast, but more persistent RDDB and the security of the full ACID standard
database, and never leave a single transaction.
Developers can save time and effort by not re-inventing the
wheel of handling temporary persistent data that is shared among the
application modules. Typically, these data stores have been handled in shared
memory constructs and inter-process communication. Previously tempdb was just
not fast enough, even if most of the tempdb files could be held in memory by
placing them into the /tmp filesystem on UNIX, and having the system administrator
make sure the /tmp is mostly kept in memory. Even in this construct, tempdb
must adhere to ACID and needs a lot of processing power to comply. IMDB does
not have to worry about this overhead. Writes are processed as fast as reads.
There’s no more worrying about memory leaks or memory
overflows. Simply hand the temporary persistent data over to the IMDB engine.
Applications Where IMDB is Not a Good Fit
While IMDB is a great contribution to the overall database architecture,
it is not a good fit in certain cases.
Applications where durability is not strictly enforced, and where
the pure temporary approach of IMDB does not align with business needs.
Relaxed Durability databases are
better suited for these applications, if some transactions can be lost in case
of a catastrophic system failure.
Applications that must guarantee permanent storage of the data.
In simple terms, airline ticketing
systems, stock market trade executions, financial transactions of any kind, and
other data- sensitive applications rely on a rigid ACID implementation to
confirm transaction execution. This is especially true in a catastrophic system
crash scenario where confirmed transactions need to be reconfirmed, and
unconfirmed transactions need to be reversed to their original state.
Data warehouses are not a fit at all.
Data warehouses and IMDB are on the
opposite ends of the database architecture scale and do not mix well.
Sybase ASE 15.5 IMDB vs. its Competitors
Sybase always manages to analyze the market, analyze the
competition, and learn from their experiences along the way. There are not many
true IMDB systems available. One of the longest running IMDB systems is Times
Ten, now part of Oracle. Although Times Ten is labeled as an IMDB system, the
underlying architecture is more closely aligned to Sybase’s ASE 15.5 new
feature RDDB, the relaxed durability database. Adding the at_shutdown
durability to the IMDB in combination with the multiple database type spawning
transaction capability would place Sybase ASE IMDB ahead of Times Ten.
MS SQL Server does not offer an IMDB engine at all. This
most probably will change in the future. SQL Server 2008 offers a variation of
the relaxed durability database with its new database recovery models: bulk-logged,
simple, and full.
The SQL Server database recovery option bulk-logged is
similar to at_shutdown of Sybase ASE 15.5, and the database recovery model simply
aligns with Sybase’s full durability level. SQL Server’s full recovery model
features go beyond the full durability level that Sybase offers. This is not a
1:1 comparison, but explains briefly how the systems compare.
Some of the major advantages of Sybase’s IMDB are that you
can apply normal dump and load database commands. You can dump an existing ASE
database and load it into an IMDB. Only Sybase offers these features.
IMDB is arguably, the new frontier of database architecture,
and satisfies the demand for the ever present “need for speed.” How well this
technology will find widespread adaptations with software architects and
developers has to be seen. For decades, the database was always the safe
storage place due to the rigid ACID compliance requirement. Embracing IMDB also
means embracing temporary persistent database data that will be lost in a
catastrophic event, or during normal system shutdown. New processes around
database management must be implemented to successfully manage IMDB in
conjunction with traditional databases.
About the Author
Peter Dobler is an accomplished IT database professional who
“makes a difference” by improving efficiencies and reducing costs for small and
medium-sized businesses. He founded Dobler Consulting, (www.doblerconsulting.com)
a Tampa, Florida consulting firm that delivers implementation expertise for
Oracle, Sybase, and MS SQL Server in 2000. His 25 plus years in technology
started in Basle, Switzerland in 1985. Peter uses his extensive experience to
hone his talent as a proven resource for producing streamlined IT solutions. He
currently engages in strategic alliances and special projects with Sybase, the
enterprise software and services company. He can be reached at: mailto:[email protected]
Read his Database Trends Blog (Behind the Scenes of Database
Evolution) at: http://www.peterdobler.com,
and his Technology Tips Blog (Step-by- step Instructions on Today’s Challenging
Technology) at http://www.techtipsntrick.com.
Sybase Getting Started with In-Memory Databases in Adaptive Server® Enterprise 15.5
Sybase Sybase ASE In-Memory Databases