| 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. If you tune resources that are not the primary cause of performance problems, this can have little or no effect on response time until you have relieved the major constraints, and it can actually make subsequent tuning work more difficult. If there is any significant improvement potential, it 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. You can use the following list of levels within a system as a guide: -
- Hardware -
- Operating system -
- Application server and requester -
- Database -
- SQL statements -
- Application programs -
Check for hardware and software problems: Some performance problems may be corrected by applying service to your hardware, your software, or to both. Do not spend excessive time monitoring and tuning your system when 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 resource 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. Performance Improvement Process 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 if 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 the above process for periodic monitoring or when significant changes occur to the system, to the workload taken on by the system, or to both. How Much Can a System Be Tuned? 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, there will be only a small benefit from tuning a system. In most circumstances, however, the benefit may be significant. If your system encounters performance bottlenecks, it is likely that tuning will 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. A Less Formal Approach 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 if 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. For example, you can ask your users -
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 those complaining one or two individuals or a whole group ? -
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? |
