Sybase ASE 15.5 -- The Need for Speed
May 26, 2010By 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 databases best use is when you need more persistence than within the IMDBs ASEs memory. This database is disk-based and not limited by memory size.
To fully understand Sybases 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, theres always the question Do you want to improve the read or the write performance? Obviously theres 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 doesnt really produce the same results. Thats 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 thats 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 after shutdown:
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 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. Thats 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
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.
Theres 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.
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 Sybases 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 Sybases full durability level. SQL Servers 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 Sybases 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:firstname.lastname@example.org 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.