Delivering High Performance with Zero Administration

By Jose Ramos


Advances
in self-management capabilities are making it possible to maximize performance
and prescribe changes and ongoing maintenance tasks with fewer demands on IT
staff.

With
IT hiring flat, it’s no secret that organizations have been under downward
pressure to increase IT efficiency. This has lead to the rapid adoption of
technologies and applications that help systems administrators do more with
less. Indeed, automation has become a serious business, particularly for
software vendors who rely on a database as an integral component of their
solution.

In
direct opposition to the IT labor trend, the amount of digital information
collected and stored each year keeps growing at an exponential rate. In 2009 it
grew 62 percent, to about 800,000 petabytes, and it will grow by a factor of 44
by 2020, says IDC1. To manage these much
larger and more complex data ecosystems companies are turning to autonomic
systems that can perform diagnostics, tuning, maintenance and deployment—among
other things—with minimal human intervention.

These
self-managing databases can diagnose conditions and make adjustments to
optimize performance, availability and resource utilization, ensuring that they
are efficient and meet service-level agreements. And because they can do this
without increasing IT labor costs, they provide a very low total cost of
ownership as compared to traditional databases.

Essential for Embedded, Small and Medium Businesses

The
hands-off nature of self-managing database systems make them a handy investment
for any organization but there are two common scenarios in which they are
essential:

1. 
Organizations
that want to embed a database in a turnkey software solution or an autonomous
embedded system

2. 
Small
and medium businesses with minimal or no dedicated IT staff

The
concept of an embedded database is inherently zero maintenance. Devices such as
kiosks, parking meters and some consumer electronics are deployed with embedded
database applications that will never be monitored or managed by an
administrator. In other cases, ISVs create and sell software applications that
are wholly and indefinitely self-sustaining and self-contained.

Because
self-managing database systems are inherently zero maintenance, they are a
perfect solution for small businesses that don’t want to hire a data management
expert. This is a very common scenario today with businesses such as private
practice law firms, physicians and independent retailers. Unlike traditional
client-server databases, a self-managing database is very easy to set up, and
once the initial set up is complete, there’s very little else that will ever
need to be done.

Assuring High Performance

If
a database doesn’t perform well, little else matters. It’s no surprise, then,
that performance optimization tops the list of self-managing capabilities.

Cost-based optimization

Self-managing
databases use a cost-based optimizer that chooses the execution strategy with
the lowest estimated cost for each query upon invocation. This feature is
particularly important for ISVs that market applications to a broad spectrum of
customers with varying database sizes and workloads.

The
cost-based optimizer in SQL Anywhere, for example, is highly adaptive and uses
sophisticated algorithms to make its decisions. It utilizes a variety of
metrics to estimate a query’s execution cost. These metrics include the size of
the available buffer pool, estimated disk transfer time, the server
multiprogramming level, table cardinality and row length, and the number of
tables, table functions or complex subqueries involved in the join strategy.
Many of these metrics are automatically adjusted by the database server during normal
operation.

Dynamic memory management

When
a database system is embedded in an application as part of an installable
package, other processes may be executing concurrently, such as an email
client, anti-virus software, and management services. This makes it more
difficult to predict the system load or the amount of memory that will be
available to the database server at any point in time. To combat this, SQL
Anywhere possesses several techniques designed to ensure that its buffer pool
is kept appropriately sized in relation to its workload and the amount of
available physical memory (see Figure 1).

The Feedback Control Loop

Figure 1. The Feedback Control Loop

The
database server maintains a single buffer pool to handle all requests, which is
advantageous to ISVs as there is no need to pre-configure the number and size
of separate buffer pools when the workload is unknown. The database server
attempts to set its buffer pool size to match the current server workload, and
at the same time respect any request by the operating system to give up
physical memory for use by other applications.

In addition, the database server supports adaptive query execution
techniques that enable the server to continue to execute SQL requests when the
amount of available memory is reduced. Moreover, the database server can
intelligently swap internal data structures from the buffer pool to disk, thus
permitting the server to continue operation even when the amount of available
memory reaches a critical point.

Self-managing and self-healing statistics

Query
optimization is dependent on the creation and storage of quality statistics
about tables and indexes. Self-managing databases such as SQL Anywhere
automatically collect and maintain statistics as part of query execution, for
use in subsequent queries. A built-in statistics governor plays the role of the
DBA by automatically evaluating the health and
usefulness of each statistic in the database. When necessary, self-healing
actions are undertaken to correct and/or repair this statistical metadata
without impacting database performance. These self-healing actions include:

  • Recording
    statistics usage and estimation errors using query feedback
  • Automatically
    repairing or recreating statistical metadata that is inaccurate
  • Ceasing
    automatic maintenance of statistics as appropriate
  • Creating
    potentially useful statistics when they are missing from the database
  • Deleting
    unused statistics

Automatic tuning of the multiprogramming level

The
multiprogramming level (MPL) of a database controls how many requests are
allowed to execute concurrently. Traditionally, DBAs run load tests on their
data to find the optimal MPL level, as setting it improperly can take a toll on
application performance. Leading self-managing databases have developed
sophisticated algorithms to calculate the optimal concurrency level
automatically. Self-managing MPL offers similar performance to the best
hand-tuning and can be a boon to ISVs as once again the server can
automatically determine the right MPL for any given workload.

Minimizing Administration

Low
maintenance is the hallmark of the self-management value proposition. Thus it
makes perfect sense that the most common administrative events occur regularly
without intervention in a self-managing database.

Triggers
and schedules for event-based maintenance

In
a self-managing database, events take the place of manual DBA involvement. SQL
Anywhere, for example, uses events to automate:

  • Backups
  • Rebuilds
  • Notifications
  • Validations
  • Fragmentation
    reductions

Such
routine tasks are automated by adding an event to the database and providing a
schedule for that event. The schedule instructs the database when to perform
regular maintenance; similarly, irregular events such as notifications, which
are traditionally used to let administrators know when problematic conditions
may soon arise, can be automated by defining trigger conditions within a system
event. This eliminates the need for DBAs to manually monitor database health
statistics.

Automatic recovery from OS or server failure

In
the case of an operating system or server failure, the database must be
recovered from backup. In a self-managing database, such recovery is entirely
automated. At startup, the database makes a self-check to see if the most
recent database session shut down cleanly. If not,
the database server executes an automatic recovery process to restore all
changes up to the most recently committed transaction.

Automatic startup and shutdown

Finally,
a self-managing database does not require explicit startup and shutdown. SQL
Anywhere, for example, can be started via a simple client API call from the
application, and can shut down automatically when the last connection
disconnects.

Effortless Deployment and Ongoing Maintenance

It
doesn’t matter how great the ongoing self-management features of a database are
if its deployment is complex and costly. A painless deployment is essential to
the low TCO of self-managing solutions, as high deployment costs will quickly
negate all the other benefits of self-management.

No need for specialized install program

Because
self-management begins with deployment, a proper self-managing database should
deploy using a minimal set of files. The location of these files defines where
data and configuration alike will reside. A SQL Anywhere database deployment
needs less than a dozen files and requires less than 20 MB of available disk
space, allowing for quick and effortless distribution.

Portability of database

Because
there is no need to create a specialized installation package, a user can
easily move a database instance from one server to another, without platform
compatibility issues. The database file doesn’t need to be converted when moved
from Windows to Linux, for example. The entire database can be moved from one
machine to another with plug-and-play-like simplicity, which is especially
useful during troubleshooting or routine maintenance where the administrator
may be running a different OS than the end user.

Design and workload analysis tools

Schema

definition is a requirement of database deployment, and self-managing databases
are no exception. A poorly designed schema will have a limiting effect on query
optimization and performance in general. Sybase has made schema design and
workload analysis another facet of self-management. While hand-tuning of the
schema is still possible, for those who lack the expertise or motivation to
spend time on ongoing database definition, the Application Profiler in SQL
Anywhere will make recommendations for schema improvements as conditions
change.

Moving Toward an Automated Future

With
high performance and low TCO, self-managing databases are an ideal solution for
a wide variety of enterprise data uses, not just environments that require zero
administration. For more information on Sybase SQL Anywhere 12, visit www.sybase.com/sqlanywhere.

About the Authors

Jose Ramos, Product Manager, SQL Anywhere, Sybase, with contributions from Mohammed
Abouzour and Glenn Paulley


1. The Digital Universe Decade – Are You Ready?

May 2010

By John Gantz and David Reinsel

Latest Articles