Chapter 5. Monitoring and Optimization

In this chapter, I explain the tools and methods you can use to monitor and optimize your database servers. Monitoring is more than just observing the results of activity. Monitoring helps track down issues, determines growth patterns, and aids in optimization. I explain what you should monitor and why, and I discuss the tools at your disposal to help you along the way.

Before you begin monitoring the system for performance, growth, or other changes, you should have an idea of its original state, or at least the state it was in when you inherited it. These measurements are called a baseline, and having one is a bit like insurance: invaluable only when you need it. I show you what information to capture to form a baseline for your database servers. I also give you practical advice on broad concepts you can use to enhance your troubleshooting skills.

Systems are made up of applications, networks, clients, and servers. Although the developers have the biggest impact on performance, in the second half of the chapter I explain what steps you can take to optimize the database platform and environment. I also show you some useful tools to help you and your developers make the system more responsive.

The "Take Away" section explains one of the methodologies that I use to monitor and optimize the systems I deal with on a daily basis. Using this outline, you can take some of the mystery out of a poorly running application.

The job of a database administrator (DBA) can be boiled down to just three primary tasks: create the system, make it safe and secure, and make it go fast. In the previous chapters, I have shown you how to create the system and how to make it safe and secure. In the first part of this chapter, I cover most of what you will need to monitor your system, which is an integral part to making it go fast.

Your managers (hopefully) spent quite a bit of time planning before they decided on a particular architecture, software vendors, and tools for your organization. They probably spent more than a little money to buy it all.

If you have read this far in the book, you have probably spent a lot of time to learn how to properly size a system, and you have learned how to configure it to be responsive. The developers spent quite a bit of effort writing the best code they know how, using the latest coding techniques, languages, and tools.

With all this time, effort, and money invested, everyone has an expectation that the system will be responsive. When quality or performance issues occur, the users want to know why. They ask the managers, and the managers ask the technical team. Sometimes the answer is not easy to find. If the answer is hard to come by, it is often because there has not been enough time, effort, and money spent on periodic monitoring and tuning the system.