Delivering High Performance with Zero Administration
February 11, 2011By 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.
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 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:
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.
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:
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