Robert Schneider spotlights capabilities present in Sybase Adaptive Server Enterprise (ASE) 15.5, paying particular attention to the new enhancements that help support better performance (via lower latency and very high transactional throughput), augment efficiency, and increased availability.
Introduction
In this article, we spotlight
a series of new capabilities present in Sybase Adaptive Server Enterprise (ASE)
15.5. In earlier editions of ASE 15, Sybase incorporated numerous mission-critical,
enterprise-class capabilities, such as:
- User-defined SQL functions
- Statistical aggregate
functions - Row-locked system catalogs
- Augmented security and
encryption - SQL statement replication
- QP Tune tuning utility
- Shared-disk clustering
Version 15.5 continues this momentum.
Throughout this overview, we’ll pay particular attention to the new enhancements
Sybase has added that help support better performance (via lower latency and very
high transactional throughput), augment efficiency, and increased availability.
Supporting better performance
While data reliability is
always a requirement for a modern relational database management system, not
every application and operation needs to incur the full overhead costs embodied
in rigid enforcement of transactional ACID (Atomicity, Consistency, Isolation,
and Durability) principles. For example, industries as diverse as finance,
government/defense, and telecommunications all experience very high operational
loads. Many of these interactions are short-lived and not intended for
persistence. In these scenarios, providing traditional ACID support often
unnecessarily taxes the database server, thereby diminishing both throughput
and performance.
Since transaction logging is
a major component of ACID support, these extra burdens are especially
pronounced in terms of disk operations. Unfortunately, given that disk I/O is
far more time-intensive than CPU-based operations, this means that the effects
of full compliance with the ACID principles are magnified, imposing a particularly
expensive performance cost.
To help ameliorate these
performance bottlenecks, ASE 15.5 inaugurates two new classes of database:
in-memory (IMDB) and relaxed durability (RDDB). Both databases let
administrators more effectively balance the need for full ACID compliance with
their site-specific usage profiles.
In-memory databases
This class of database runs
completely within ASE’s memory space. This approach requires no disk storage
whatsoever: neither data nor logs are saved to disk. Although it mandates only
minimal transaction logging overhead, rollbacks, triggers, and replication are
still supported within the memory cache. It’s fully integrated with ASE,
offering the same look-and-feel and full programming language compatibility,
and is ideal for scenarios where high data durability isn’t the overarching
concern. You’re free to select this type of database for both permanent as well
as temporary databases.
IMDB databases achieve their
dramatic performance improvements in several ways. Simply eliminating disk
storage yields significant throughput gains; there’s no need to wait for I/O.
In addition, there are a number of internal optimizations such as faster data
access, streamlined buffer and user log cache management and query optimizer
adjustments that also contribute to the overall advancement in response times.
When deciding whether to use
this option, what’s important to remember is that since both data and
transaction logs are written to the memory-based cache and not disk, an
unplanned database or server failure will result in the loss of any data that
was in the cache at the time of the unexpected shutdown. Thus, it’s best to
utilize in-memory databases for situations where long-term persistence isn’t
important. For example, you could use this type of database as a transient
workspace to capture short-lived data that will then be summarized and written
into a standard, disk-based ASE database. By following this approach, you would
reduce the overall workload on your server, thereby benefitting all ASE-based
applications.
Since your in-memory database
must fit in available cache space, it’s also important to only use this type of
storage for information that will be able to comply with this requirement. However,
note that while in-memory databases must be hosted by a single cache, you’re
free to assign multiple memory devices to each cache.
Relaxed durability databases
While in-memory databases can
substantially improve performance, their diminished durability means that they
aren’t suitable for every application. To help extend the performance benefits
of in-memory databases to traditional disk-based databases, ASE now enables
administrators to opt for a relaxed durability database. For these types of
databases, you’re free to fine-tune ASE’s behavior via two durability levels.
For the first level, the durability is identical to an in-memory database:
should the server shut down unexpectedly, all data will be lost. For the second
level, a standard database shutdown causes all committed data to be written to
disk. Note that for both in-memory and relaxed durability databases, ASE neither
ensures that log records are ordered correctly nor flushes the transaction log
to disk.
The benefit of this approach
is that it sports the same tight integration with the full ASE product line,
just like the aforementioned in-memory database, yet guarantees transaction
durability as long as a normal shutdown has been performed.
Minimally logged Data Manipulation Language (DML)
DML statements (i.e. SQL
commands that alter information) normally mandate significant logging overhead.
Each data modification is traditionally registered in a disk-based transaction
log. This log is then available to assist in restoring the database to an
orderly state should a transaction failure or unexpected shutdown occur.
ASE 15.5 lets database
administrators control the amount of logging for their in-memory and reduced
durability databases. If the full logging option is chosen, ASE will log all
transactions. On the other hand, if the minimal logging option is selected, ASE
will not log row or page changes unless triggers or replication are involved. These
options are available for INSERT, UPDATE, and DELETE statements, at the
database, table, or session granularity.
Incremental data transfer utility
As data volumes grow, even
simple tasks such as exporting information can become too time-consuming and
unwieldy. In many scenarios, just exporting only altered information is all
that’s necessary. This can result in a much smaller, more manageable data set.
To support this more
efficient approach, ASE 15.5 introduces a new Transact-SQL statement that only
retrieves information that has changed since the last time the command was run.
Data integrity is preserved; only information contained in committed
transactions is exported. Furthermore, eligible tables are read without the
normal locking overhead, and other operations are able to proceed normally.
Exported rows may be written
to an output file for designated receivers such as Sybase IQ, ASE, bulk copy,
or character-encoded output. The destination may be local, or on a remote
server as long as the file is an NFS file visible to ASE.
This new technique is
especially relevant in the context of environments using in-memory databases.
Transaction-safe, consistent incremental data modifications can be captured and
inserted into a source ASE table. The extracted data can then be applied to
another ASE table, thus allow for a fast data transfer mechanism from an
in-memory database table to an on-disk ASE table.
Augmenting efficiency
Now that we’ve explored new
techniques to drive additional performance from ASE 15.5, let’s examine a
series of fresh capabilities that help streamline operations.
Enhanced archiving
As mission-critical data
volumes have continued expanding, sophisticated storage solutions have kept
pace. In response to the demands imposed by information journaling requirements,
many organizations have selected the IBM Tivoli Storage Manager (TSM) as their
archiving platform.
Beginning with ASE 15.5,
Sybase has fully integrated TSM with the ASE Backup Server. This optional
add-on is known as the Advanced Backup Services – Tivoli Storage Manager.
Administrators are free to continue using the familiar Sybase backup interface,
yet are able to archive information onto any TSM-supported media. ASE is
responsible for dumping and loading databases to and from TSM; TSM takes care
of the storage and retrieval of this information from the physical storage
media, delivering significantly more efficient archiving and restoration.
The Adaptive Server and TSM
integrated architecture includes Adaptive Server, Backup Server, the TSM
client, the TSM server, and the TSM client API. The TSM client API enables the
client application to call TSM functions.
In addition to the
integration with TSM, ASE 15.5 also offers additional compression options for
backing up databases and transaction logs. Two new compression levels (100 and
101) are now available via the dump_database and dump_transaction commands. The first level offers faster compression,
while the other delivers more complete compression. The existing compression
levels of zero through nine continue to be supported: the new levels have no
effect on these levels.
User-defined tempdb groups
Beginning with ASE 12.5.03,
Sybase offered support for user-defined temporary databases. These transitory
structures are helpful for storing data that doesn’t require the overhead
mandated by permanent information storage. However, as administrators and
developers have come to rely on temporary databases, it’s become apparent that
they require additional control over these provisional structures.
In version 15.5,
administrators are now free to define site-specific temporary database groups.
Based on user requests, ASE will now assign temporary databases to these
administrator-defined groups, as well as continue to utilize the default
temporary database group in situations where no preference is expressed.
For additional efficiency,
note that in-memory databases may be used as temporary databases and managed
accordingly, including being added to user-defined tempdb groups.
Tracking time-sensitive information
Certain classes of database
applications and industries generate massive amounts of timestamped information.
For example, financial services and telecommunications enterprises are just two
instances of organizations that not only create substantial time-focused data,
but also must track this data to a very fine granularity.
To store information to the
micro-second, ASE 15.5 introduces two new data types: Bigtime and BigDatetime.
Increasing availability
In the past few years, a
number of business and technology trends have placed increasing pressure on the
IT organization. These strains have included relentless cost control mandates,
space and power constraints, and the desire to wring additional usage from the
enterprise’s hardware while boosting availability.
To address these needs,
Sybase introduced ASE Cluster Edition in version 15. It provides a shared-disk
cluster implementation of ASE that helps manage costs by leveraging commodity
hardware. Up to 32 concurrent ASE servers are presented as a single system,
with built-in workload management capabilities to maximize performance and
availability. Significantly, no application logic changes are required to
leverage all of this functionality.
ASE Cluster Edition supports
today’s demanding Service Level Agreements (SLA) via automated failover and
load balancing. Administrators may easily bring standby nodes online, as well
as use these servers to eliminate downtime during migration, all through an
easily-managed user interface.
Version 15.5 enhances ASE
Cluster Edition by providing multi-simultaneous failover for uninterrupted
operations on multi-node implementations, elevating continuous availability to
new levels.
Conclusion
The new capabilities present
in ASE 15.5 demonstrate its continued evolution and attention to meeting the specific
needs of mission-critical database-driven applications. Administrators now have
supplemental alternatives to help increase performance, enhance operational
effectiveness, and maximize the availability of their ASE implementations.
In a follow-on article, we’ll
explore the in-memory and reduced durability databases in more detail.
About the author
Robert Schneider is a Silicon
Valley-based technology consultant. He has written six books and numerous
articles on advanced technical topics such as Cloud Computing, Service Oriented
Architecture (SOA), and high-performance relational database design and optimization.
He may be reached at Robert.Schneider@think88.com
