DBA Call to Action: Zeroing in on Performance Problems
February 13, 2004
Have you ever been asked to help with the evaluation of a database that is not performing optimally? Here is a quick guide to help you if you just do not know where to start.
Thrown Into the Fire
In my early days of being a database administrator, I was thrown straight into the fire of database performance issues. Now, I did not have any experience, except for the education of database systems from my local college, and was at the mercies of other professionals around me. The problem was that many of them did not have much experience either. I would often asked myself why in the world someone would ask me to work on database systems that were the lifeline of their company and would trust someone with such little experience. The reason they let me 'play' with their systems was that they were at my mercy combined with a few things happening with their database systems in general. Users were beginning to ask more and more of their databases, they were storing more and more data, the amount of people with real credentials to tune a database was low and computer hardware was still not quite robust enough for the solutions in which they were asked to take part. Today, all of these issues are still true for the most part except for computer hardware. The hardware of today can mask the performance problems of the typical database environment for quite some time. The simplistic nature of buying hardware, throwing a database on it, creating objects to store the information, and users creating applications against the database does create a false sense of stability that typically will only break down over time when true scalability of the system comes into question.
What does this all mean? Well, if you stick in this field of database administration long enough, you will in due time be faced with the issue of having to investigate a performance problem. What makes matters worse is that no one will typically know where the problem arose from or where to start. It is your duty as a database administrator of the system to track down the problem so that you can effectively come up with a solution. So where do you start? This article will give you an approach to use where you can begin to investigate the problem and hopefully come up with your own feelings and convictions about what must be done with this highly stressful and possibly confrontational issue.
Do not underestimate the information the users of database systems have when first determining the problem with a system that is not performing well. It is true they cannot tell you anything about memory consumption, how the data is laid out across your storage subsystem or how an application is written, however, what they can do is verbally explain to you the pain they are experiencing when they use the system. They have a unique and distinct experience that can help you zero in on the use cases that are experiencing problems. Zeroing in on the events or tasks that cause the problem will help you later down the path when you want to re-create the problem. These problems are what you should concentrate on, and since they are the pain points experienced by users, should be your first line of attack to begin easing the perceived performance problems. You may still have many issues that are occurring that you will need to fix but if some of your users are happy you will have a better, and less stressful, chance of sleep at night.
When talking to users, it is very important for you to have a set of questions that you can ask to extract all possible information on the system from these users. In Listing 1, I have given you a few questions that you can incorporate when you talk to the users of the system. The idea you should come away with is that you need to get as much information as possible about how users are feeling about the system while instilling in them the importance of the problem to you and your commitment to getting it resolved. In addition, you will notice that these questions are geared to extract information about how the system is behaving now and relating it to how the system has behaved in the past. By doing this you will begin to get an idea for the change of events that might have happened between a system performing properly and poorly from the users' perspective.