Optimizing Database Performance, part 6: Automation

Parts 1, , 3, 4, and 5 of this series are also available.

Today’s post, the final one in this series, will briefly discuss automation… and autonomics.

Setting the Stage

Implementing, managing and maintaining complex database applications spread throughout the world is a difficult task. To support modern applications a vast IT infrastructure is required that encompasses all of the physical things needed to support your applications. This includes your databases, desktops, networks, and servers, as well as any networks and servers outside of your environment that you rely upon in today’s connected world. These things, operating together, create your IT infrastructure. These disparate elements are required to function together efficiently for your applications to deliver service to their users.

But these things were not originally designed to work together. So not only is the environment increasingly complex, it is inter-related. But it is not necessarily designed to be inter-related. When you change one thing, it usually impacts others. What is the impact of this situation on DBAs?

Well, for starters, DBAs are working overtime just to support the current applications and relational features. But new RDBMS releases are being made available faster than ever before. Many organizations are not running with the most current version of the DBMS.

But technology is not the only challenge facing modern DBAs. Knowledge of the business is also mandatory. Companies that implement ERP applications require their DBAs to understand the business as they implement and administer the databases for these systems. Furthermore, failing to understand how technology and business interact can result in lost business. A DBA must know how each database and transaction impacts the business in order to prioritize his workload. Otherwise, confronted with two database outages, how would the DBA know which one to work on first? Obviously, the one that is most critical to the business should be the priority, but this requires knowledge of the business, doesn’t it?

So, the job of database administration is getting increasingly more difficult as database technology rapidly advances adding new functionality, more options, and more complex and complicated capabilities. But DBAs are overworked, under-appreciated, and lack the time to gain the essential skills required to support and administer the latest features of the RDBMS they support. What can be done?

Intelligent Automation

One of the ways to reduce these problems is through intelligent automation. As IT professionals we have helped to deliver systems that automate multiple jobs throughout our organizations. That is what computer applications do: they automate someone’s job to make that job easier. But we have yet to fully intelligently automate our DBA jobs. By automating some of the tedious day-to-day tasks of database administration, we can help to simplify this complex environment and reduce manual errors.

Although simple automation is helpful, it is not likely to be sufficient. Modern administration software is adopting autonomics that can intelligently monitor, analyze, and optimize applications using past, present, and future analysis of collected data. Simply stated, the software mimics the way a consultant works — fulfilling the role of a trusted advisor. But we’ll talk more about autonomics in the next section.

First, let’s extol the virtues of simple automation… starting with utility automation.

Whenever possible, the DBA should look into using database utilities and third-party administration tools to automate database maintenance. Automation tools can be used to query the database statistics and trigger reorganization only for those database objects that have surpassed the high-water mark for a combination of statistics. For example, the DBA may want to automatically reorganize all table spaces where the cluster ratio is below 85 percent and all indexes where the leaf distance has a value greater than 100.

Reorganizations can be costly in terms of downtime and computing resources. And, at times, determining when reorganization will actually create performance gains can be difficult. By automating reorganizations based on actual database statistics, though, your success rate should be higher than simply waiting for a problem to occur before running a reorg.

But utilities and reorganization jobs are not the only thing that can be automated! Automation tools can be used to intercept error messages and performance metrics that can improve the visibility of problems — and hopefully the reaction time to their resolution. The more polished the automation, the better the chance that a script or action can be run to resolve the problem based on the identifying information. By automating such things before they occur reactive response to problems diminishes.

But What About Autonomics?

So what are autonomics? Autonomic computing refers to the self-managing characteristics of distributed computing resources, adapting to unpredictable changes while hiding intrinsic complexity to operators and users. Autonomics implies much more than simple automation.

The goal of autonomics is to enable computer systems and software capable of managing themselves. It requires an awareness of the environment, knowledge of changing usage patterns and resources, and the ability to adapt to shifting requirements. The primary benefit is to optimize manual tasks and eliminate the growing levels of complexity.

And let’s face it, complexity is a significant driving issue in today’s modern organizations. The number of computing devices continues to grow rapidly and the average complexity of each device is increasing.

And as we mentioned earlier, the number of systems that interoperate with each other continues to grow. Connections are made between systems that were never originally intended to share data or processing. Legacy systems rapidly are being transformed to work with modern, distributed applications adding layers of complexity behind the scenes, even as the user interface becomes easier. When you look at all of these trends, you have to admit that it is next to impossible to master all  of the interconnected technologies at your shop today, let alone into the future as the trends continue and pick up speed.

So what makes up an autonomic system? At a high-level, there are four aspects of autonomic computing:

  1. It is automatic, meaning it can make decisions on its own, using accumulated performance and usage metrics and high-level policies developed by administrators.
  2. It is adaptive, meaning it can automatically adjust to changing conditions.
  3. It is aware, meaning the system can monitor (or sense) its operational context as well as its current state to determine if it is reaching its specific purpose.
  4. It is self-managing, meaning it can optimize and administer itself without human interaction being required.

Of course, there are many aspects to “self” managing, and autonomic systems can support varying degrees of self-management capabilities, including:

  • Self-configuration: Automatic configuration of the system and its components;
  • Self-healing: Automatic discovery, and correction of faults;
  • Self-optimization: Automatic monitoring and control of resources to ensure the optimal functioning with respect to the defined requirements;
  • Self-protection: Proactive identification and protection from arbitrary attacks.
  • Self-inspection: Understands itself and its interactions with other systems in order to make intelligent decisions;
  • Self-organization: Proactive modification of data structures and organization to optimize access.

From the DBA’s perspective, autonomic administration solutions should be able to collect data about the IT environment from the systems (OS, DBMS, OLTP, etc.), objects, and applications. It should require very little initial configuration, so that it is easy to use for novices and skilled users alike. It should detect conditions requiring maintenance actions, and then advise the user of the problem, and finally, and most beneficial to the user, optionally perform the necessary action to correct the problems it identifies. Many management tools (without autonomics capabilities) leave this analysis and execution up to the user. But intelligent automation solutions should be smart enough to optimize and streamline your IT environment with minimal, perhaps no, user or DBA interaction.

The end result, software that functions like a consultant, enables the precious human resources of your organization to spend time on research, strategy, planning, and implementing new and advanced features and technologies. Only through intelligent automation with autonomic computing will we be able to deliver on the promise of technology.


Applications and data are constantly changing. Users require instant response time and 24/7 availability. The database structures that support these applications must be properly maintained to ensure optimal application performance. Proper database design, appropriate choice of clustering, and reorganizing databases based on statistics help to deliver efficient databases. Furthermore, DBAs can ensure database performance by automating these processes to reduce the risk and errors associated with manual database maintenance.

As IT tasks get more complex and IT professionals are harder to employ and retain, more and more IT duties should be automated using intelligent management software and autonomics. This is especially true for very complex jobs, such as DBA. Automation through autonomics can reduce the amount of time, effort, and human error associated with managing databases and complex applications.

Portions of this post were excerpted from my book Database administration: The complete guide to DBA practices and procedures (2nd edition).


About craig@craigsmullins.com

I'm a strategist, researcher, and consultant with nearly three decades of experience in all facets of database systems development.
This entry was posted in DBA. Bookmark the permalink.

One Response to Optimizing Database Performance, part 6: Automation

  1. Pingback: DB2 Hub | Optimizing Database Performance, part 6: Automation

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s