Key Performance Metrics
When doing any form of database performance tuning, consider these categories.
Workload:
How much is the database being asked to do? Do you know? You should! Workload is
the combination of requests made upon the database. It is a great indicator for
determining if everything is normal. If workloads are the same as always, and
users are experiencing problems, it is safe to assume that something down the
line processing user requests is not functioning properly.
If the workload has increased and people are having problems, the assumption can be made that an increase in workload will produce more of a strain on the system.
Workload is the collection of DDL (Data Definition Language) statements--the select, insert, update, and delete queries--which users and applications submit. Don't forget, it is also the administrative tasks that DBAs request of the database, everything from the scheduled backups, to the table that needs reorganization, to the indexes that have been rebuilt.
Response Time:
Once the database acknowledges the request for work, it begins the work, and in
a perfect world will complete the request. The time interval between the request
and completion is the response time.
Throughput:
As the database receives and processes requests, there is an underlying
statistic that signifies the amount of requests that a database can handle over
a period of time. This rate at which the requests can be performed is known as
throughput. Throughput is a great indicator of productivity.
Utilization
By definition, once work has been submitted to the database and before the
throughput and response time can be measured, something must be done to process
the request. This "something" in the database performance world is
called resource usage. In an attempt to over simplify things, there are two
types of resource usage--good and bad. The good resources are those that are
used in a positive way to produce the results; the bad are those resource usages
that get in the way of good resources being used. The good could be actual disk
reads that need to be done. The bad could be locks on objects that are in the
way of reading data.
The good use of resources can be further broken into two types of usage, normal use and over use. Normal use is the perfect, limited consumption of a resource to produce a result. Over use is the bad side of resource usage where a resource is used beyond the desired amount. Whether a lot of good resources or a lot of bad resources are used, when mapped against time, that usage results in utilization. Typically, the resource hog or bottleneck in the system can be found if one looks at the resource with the largest utilization.
Availability:
It is common knowledge that databases tend to be temperamental at times,
networks go down, disks crash, and even databases crash sometimes. When
resources, for whatever reason, take a vacation and inhibit any work from
getting done, it is called a resource availability issue. A resource can only be
available or unavailable and is measured as the amount of time it is available
to work on requests. When a resource is available, it is known as uptime. When a
resource is unavailable, it is downtime.
Reliability:
Not only do database systems crash, from time to time they produce errors. This
has a direct correlation to database reliability. The ability to detect and
predict the probability of errors is paramount.