Performance is the way a computer system behaves given a particular workload. Performance is measured through the system's response time, throughput, and availability and is affected by the resources available and how well the resources are used.
Performance tuning should be undertaken when you want to improve the cost-benefit ratio of your system. Specific situations include the following.
You want to process a larger, more demanding workload without increasing processing costs that may include having to acquire additional hardware.
You want to obtain faster system response time or higher throughput without increasing processing costs.
You want to reduce processing costs without negatively affecting service to the client(s).
Translating performance from technical terms to economic terms is difficult. Performance tuning costs money through labor and machine resources, so the cost of tuning must be weighed against the benefits tuning may or may not deliver. Some of these benefits, including less resource use and the ability to add more users to the system, are tangible; other benefits, such as increased customer satisfaction, are less tangible from a monetary perspective.
The following guidelines should be considered in developing an overall approach to performance tuning.
Remember the law of diminishing returns. Your greatest performance benefits usually come from your initial efforts. Further changes generally produce smaller and smaller benefits and require greater effort.
Do not tune just for the sake of tuning. Tune to relieve identified constraints. Tuning resources that are not the primary cause of performance problems can have little or no effect on response time until you have relieved the major constraints, and it can make subsequent tuning work more difficult. Any significant improvement potential lies in improving the performance of the resources that are major factors in the response time.
Consider the whole system. You can never tune one parameter or system in isolation. Before you make any adjustments, consider how it will affect the system as a whole.
Change one parameter at a time. Do not change more than one performance-tuning parameter at a time. Even if you are sure that all the changes will be beneficial, you will have no way of evaluating how much each change has contributed. You also cannot effectively judge the tradeoff you have made by changing more than one parameter at a time. Every time you adjust a parameter to improve one area, you almost always affect at least one other area that may not have been considered.
Measure and reconfigure by levels. For the same reasons that you should change only one parameter at a time, tune one level of your system at a time: hardware, operating system, application server and requester, database, SQL statements, and application programs.
Check for hardware and software problems. Some performance problems may be corrected by applying service to your hardware, your software, or both. Do not spend excessive time monitoring and tuning your system if simply applying service may be the solution to the problem.
Understand the problem before upgrading hardware. Even if it seems that an additional storage or processor resource could immediately improve performance, take the time to understand where the bottlenecks are. You may spend money on additional disk storage only to find that you do not have the processor resources to exploit it.
Put fallback procedures in place before you start tuning. Because changes are being made to an existing system, you must be prepared to back out those changes if they do not have the desired effect or have a negative effect on the system.
It is important to take into consideration the cost of detailed performance analysisrunning tracesand the time needed to perform the analysis, because the process of detailed performance analysis can be costly.
Monitoring and tuning a database and its applications should be performed using the following basic process.
Establish performance indicators.
Define performance objectives.
Develop a performance-monitoring plan.
Implement the plan.
Analyze the measurements. Determine whether the objectives have been met. If so, consider reducing the number of measurements to keep to a minimum the amount of resources consumed for monitoring.
Determine the major constraints in the system.
Decide where you can afford to make tradeoffs and which resources can bear an additional load. Most tuning activities involve tradeoffs among system resources and various elements of performance.
Adjust the configuration of the system. If you think that it is feasible to change more than one tuning option, implement one at a time.
Based on the results, start another iteration of the monitoring cycle.
You may want to follow this process for periodic monitoring or when significant changes occur to the system, to the workload taken on by the system, or to both.
Scope of System Tuning
There are limits to how much you can improve the efficiency of a system. Consider how much time and money you should spend on improving system performance and how much the spending of additional time and money will help the users of the system.
Your system may perform adequately without any tuning at all, but it probably will not perform to its potential. Each database is unique. As soon as you develop your own database and applications for it, investigate the tuning parameters available and learn how you can customize their settings to reflect your situation. In some circumstances, tuning a system will yield only a small benefit. In most circumstances, however, the benefit may be significant.
If your system encounters performance bottlenecks, tuning will probably be effective. If you are close to the performance limits and you increase the number of users on the system by about 10 percent, the response time may rise by much more than 10 percent. In this situation, you will need to determine how to counterbalance this degradation in performance by tuning your system. However, there is a point beyond which tuning cannot help. At that point, you should consider revising your goals and expectations within that environment. Or you should change your system environment by considering more disk storage, faster or additional processors, additional memory, or faster networking solutions.
If you do not have enough time to set performance objectives and to monitor and tune in a comprehensive manner, you can address performance by listening to your users. Find out whether they are having performance-related problems. You can usually locate the problem or determine where to start looking for the problem by asking a few simple questions.
What do you mean by slow response? Is it 10 percent slower or 10 times slower than you expect it to be?
When did you notice the problem? Is it recent, or has it always been there?
Do you know of other users who are complaining of the same problem? Are one or two individuals or a whole group complaining?
Are the problems you are experiencing related to a specific transaction or application program?
Do your problems appear during regular periods, such as at lunch hour, or are they continuous?