Database Tuning as a process and automation

14 July,2011 by Jack Vamvas

High speed

Online inspection,  statistics gathering,  query usage and resource consumption are common database server monitoring activities.  On the basis of monitoring ,  depending on the diagnosis database tuning activity occurs.

Automating database tuning is the goal of many Systems Managers.  The issue is : Can all types of tuning activity be automated?

Two key factors generate Database Tuning requirements


1)  Regular System changes

-          users

-          data volume changes

-          number of transactions

-          availability requirements (HA,DR)

-          server consolidation

2)   Database server Requirements change

-          company acquisition

-          application changes

 

 This requires a Tuning process. Some DBAs  think of Database Tuning as reactive task.  i.e   a user reports performance degradation, and the DBA applies  database tuning methods , solves the problem and closes the Helpdesk request.

 Abstracting the user and DBA the steps are:

1)   Identify existing Problem  (user call, metrics )

2)   Monitor the system and define  cause (root) of problem (performance methodology)

3)   Make changes to problem  (adjust configuration, increase capacity, clear bottleneck)

4)   Problem Solved

 The four steps are repeated.  Some problems are straightforward – such as Log files have filled and no more transactions can be written.  Other problems are more complicated – such as memory bottlenecks.

 An interesting aspect of abstracting the process  is considering automation.  To what extent can a root cause be identified or if not identified?

Can automated responses be created  to common performance problems?

 I spend hours\days on some performance issues , and automating that process would be difficult.  The other day a battery stopped working on a mirrored SVC, forcing traffic to reroute onto another SVC.  The bottleneck caused write activity performance to degrade.  Write activity continued but  10 times slower.

The battery is monitored , but Operations did not act quickly enough,  treating it as a minor.

A DBA,  SAN engineer and network engineer spent a few hours diagnosing the problem and fixing.  Better monitoring and a faster response would have assisted,  but human intervention is required.

 Related posts:

Database Tuning – Five Basic Principles according to Shasha
SQL Server Rapid Tuning

Author: Jack Vamvas(http://www.dba-db2.com)
Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Database Tuning as a process and automation

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright