Sybase ASE 15.5 -- The Need for Speed

May 26, 2010

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 Levels

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.

The Technology

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 after shutdown:

full

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.

no_recovery

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.

at_shutdown

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 shutdown.

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 sub-system.

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.

Conclusion

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:pdobler@doblerconsulting.com or 813-322-3240.

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.

Additional Resources

Overview of Sybase ASE In-Memory Database Feature
Sybase Getting Started with In-Memory Databases in Adaptive Server® Enterprise 15.5
Sybase Sybase ASE In-Memory Databases