Related tasks Developing a performance-improvement process"on page 5 Performance-tuning guidelines The following guidelines should help you develop an overall approach 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 more and more 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 has 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 syste isolation. Before you make any adjustments, consider how it will affect ystem 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 changing more than one parameter at a time Every time you adjust o ade by contributed. You also cannot effectively judge the trade-off you have m parameter to improve one area, you almost always affect at least one other area that you may not have considered. By changing only one at a time, this allows you to have a benchmark to evaluate whether the change does what you want Measure and reconfigure by levels: For the same reasons that you should only change 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 Application Server and Requester Database Manager · SQL Statements Application Programs 4 Administration Guide. Performance
Related tasks: v “Developing a performance-improvement process” on page 5 Performance-tuning guidelines The following guidelines should help you develop 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 more and more 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 has 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 contributed. You also cannot effectively judge the trade-off 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 you may not have considered. By changing only one at a time, this allows you to have a benchmark to evaluate whether the change does what you want. Measure and reconfigure by levels: For the same reasons that you should only change 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: v Hardware v Operating System v Application Server and Requester v Database Manager v SQL Statements v Application Programs 4 Administration Guide: Performance
Check for hardware as well as software problems: Some performance problems may be corrected by applying service either to your hardware, or to your software, or to both. Do not spend excessive time monitoring and tuning your system when simply applying service may make it unnecessary. Understand the problem before you upgrade your hardware: Even if it seems that additional storage or processor power could immediately improve performance, take the time to understand where your bottlenecks are. You may spend money on additional disk storage only to find that you do not have the processing power or the channels to exploit it Put fall-back procedures in place before you start tuning: As noted earlier, some tuning can cause unexpected performance results. If this leads to poorer performance, it should be reversed and alternative tuning tried. If the former etup is saved in such a manner that it can be simply recalled, the backing out of the incorrect information becomes much simpler. elated concepts: Elements of performance"on page 3 Quick-start tips for performance tuning "on page 7 Related tasks Developing a performance- improvement process" on page 5 The performance-tuning process You develop a performance monitoring and tuning plan, taking user input into account, and recognizing the limits of tuning in your system eveloping a performance- improvement process The performance-improvement process is an iterative long-term approach to monitoring and tuning aspects of performance. Depending on the result of monitoring, you and your performance team adjust the configuration of the database server and make changes to the applications that use the database server Base your performance monitoring and tuning decisions on your knowledge of the kinds of applications that use the data and the patterns of data access Different kinds of applications have different performance requirements Consider the following outline of the performance- improvement process as a Procedure Chapter 1 Introduction to performance 5
Check for hardware as well as software problems: Some performance problems may be corrected by applying service either to your hardware, or to your software, or to both. Do not spend excessive time monitoring and tuning your system when simply applying service may make it unnecessary. Understand the problem before you upgrade your hardware: Even if it seems that additional storage or processor power could immediately improve performance, take the time to understand where your bottlenecks are. You may spend money on additional disk storage only to find that you do not have the processing power or the channels to exploit it. Put fall-back procedures in place before you start tuning: As noted earlier, some tuning can cause unexpected performance results. If this leads to poorer performance, it should be reversed and alternative tuning tried. If the former setup is saved in such a manner that it can be simply recalled, the backing out of the incorrect information becomes much simpler. Related concepts: v “Elements of performance” on page 3 v “Quick-start tips for performance tuning” on page 7 Related tasks: v “Developing a performance-improvement process” on page 5 The performance-tuning process You develop a performance monitoring and tuning plan, taking user input into account, and recognizing the limits of tuning in your system. Developing a performance-improvement process The performance-improvement process is an iterative, long-term approach to monitoring and tuning aspects of performance. Depending on the result of monitoring, you and your performance team adjust the configuration of the database server and make changes to the applications that use the database server. Base your performance monitoring and tuning decisions on your knowledge of the kinds of applications that use the data and the patterns of data access. Different kinds of applications have different performance requirements. Consider the following outline of the performance-improvement process as a guideline. Procedure: Chapter 1. Introduction to performance 5
To develop a performance- improvement process 1. Define performance objectives 2. Establish performance indicators for the major constraints in the system 3. Develop and execute a performance monitoring plan 4. Continually analyze the results of monitoring to determine which resources require tuning 5. Make one adjustment at a time. Even if you think that more than one resource requires tuning, or if several tuning options are available for the resource you want to tune make only one change at a time so that you can make sure that your tuning efforts are producing the effect you want. At some point, you can no longer improve performance by tuning the database server and applications. Then you need to upgrade your hardware Actual performance tuning requires trade-offs among system resources. For example to provide improved 1/0 performance you might increase buffer pool sizes, but larger buffer pools require more memory, which might degrade Related concepts Elements of performance"on page 3 Performance-tuning guidelines"on page 4 Quick-start tips for performance tuning"on page 7 Performance-tuning limits"on page 7 Performance information that users can provide"on page 6 erformance information that users can provide The first sign that your system requires tuning might be complaints from users. 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. You can usually determine where to start looking for a problem by asking a few simple questions. For example, you might ask your users. What do you mean by"slow response"? Is it 10 slower than you expect it to be or tens of times slower? When did you notice the problem? Is it recent or has it always been there? Do other users have the same problem? Are these users one or two individuals or a whole group? If a group of users is experiencing the same problems, are they connected to the same local area network? 6 Administration Guide. Performance
To develop a performance-improvement process: 1. Define performance objectives. 2. Establish performance indicators for the major constraints in the system. 3. Develop and execute a performance monitoring plan. 4. Continually analyze the results of monitoring to determine which resources require tuning. 5. Make one adjustment at a time. Even if you think that more than one resource requires tuning, or if several tuning options are available for the resource you want to tune, make only one change at a time so that you can make sure that your tuning efforts are producing the effect you want. At some point, you can no longer improve performance by tuning the database server and applications. Then you need to upgrade your hardware. Actual performance tuning requires trade-offs among system resources. For example, to provide improved I/O performance you might increase buffer pool sizes, but larger buffer pools require more memory, which might degrade other aspects of performance. Related concepts: v “Elements of performance” on page 3 v “Performance-tuning guidelines” on page 4 v “Quick-start tips for performance tuning” on page 7 v “Performance-tuning limits” on page 7 v “Performance information that users can provide” on page 6 Performance information that users can provide The first sign that your system requires tuning might be complaints from users. 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. You can usually determine where to start looking for a problem by asking a few simple questions. For example, you might ask your users: v What do you mean by “slow response”? Is it 10 % slower than you expect it to be, or tens of times slower? v When did you notice the problem? Is it recent or has it always been there? v Do other users have the same problem? Are these users one or two individuals or a whole group? v If a group of users is experiencing the same problems, are they connected to the same local area network? 6 Administration Guide: Performance
Do thethe problems seem to be related to a specific transaction or application program? Do you notice any pattern in the problem occurrence? For example, does the problem occur at a specific time of day, such as during lunch hour, or is it more or less continuous? Related concepts: Performance-tuning guidelines"on page 4 Related tasks Developing a performance- improvement process"on page 5 Performance-tuning limits Tuning can make only a certain amount of change in the efficiency of a system. Consider how much time and money you should spend on improving system performance, and how much spending additional time and money will help the users of the system For example, tuning can often improve performance if the system encounters a performance bottleneck. If you are close to the performance limits of your system and the number of users increases by about ten percent, the response time is likely to increase by much more than ten percent. In this situation, you 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 this point, consider revising your goals and expectations within the limits of your environment For significant performance improvements, you might need to dd more disk storage, faster CPU, additional CPUs, more main memory. faster communication links, or a combination of these Related concepts Management of database server capacity"on page 337 Related tasks. Developing a performance-improvement process"on page 5 Quick-start tips for performance tuning When you start a new instance of DB2, consider the following suggestions for a basic configuration Chapter 1. Introduction to performance 7
v Do the the problems seem to be related to a specific transaction or application program? v Do you notice any pattern in the problem occurrence? For example, does the problem occur at a specific time of day, such as during lunch hour, or is it more or less continuous? Related concepts: v “Performance-tuning guidelines” on page 4 Related tasks: v “Developing a performance-improvement process” on page 5 Performance-tuning limits Tuning can make only a certain amount of change in the efficiency of a system. Consider how much time and money you should spend on improving system performance, and how much spending additional time and money will help the users of the system. For example, tuning can often improve performance if the system encounters a performance bottleneck. If you are close to the performance limits of your system and the number of users increases by about ten percent, the response time is likely to increase by much more than ten percent. In this situation, you 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 this point, consider revising your goals and expectations within the limits of your environment. For significant performance improvements, you might need to add more disk storage, faster CPU, additional CPUs, more main memory, faster communication links, or a combination of these. Related concepts: v “Management of database server capacity” on page 337 Related tasks: v “Developing a performance-improvement process” on page 5 Quick-start tips for performance tuning When you start a new instance of DB2, consider the following suggestions for a basic configuration: Chapter 1. Introduction to performance 7
Use the Configuration Advisor in the Control Center to get advice about reasonable beginning defaults for your system. The defaults shipped with DB2 should be tuned for your unique hardware environment. Gather information about the hardware at your site so you can answer the wizard questions. You can apply the suggested configuration parameter settings immediately or let the wizard create a script based on your answers and run the script later. This script also provides a list of the most commonly tuned parameters for later reference Use other wizards in the Control Center and Client Configuration Assistant for performance-related administration tasks. These tasks are usually those in which you can achieve significant performance improvements by spending spend a little time and effort Other wizards can help you improve performance of individual tables and general data access. These wizards include the Create Database, Create Table, Index, and Configure Multisite Update wizards. The Workload Performance wizard guides you through tasks that help tune queries. The Health Center provides a set of monitoring and tuning tools Use the design Advisor tool (db2advis)to find out what indexes will Use the activate database command to start databases in a partitioned database, this command activates the database on all partitions and avoids the startup time required to initialize the database when the first application connects Note: If you use the ACTIVATE DATABASE command, you must shut down the database with the deactivate database command The last appl connects cts from the database does not shut Consult the summary tables that list and briefly describe each configuration parameter available for the database manager and each database. These summary tables contain a column that indicates whether tuning the parameter results in high, medium, low, or no performance changes, either for better or for worse. Use this table to find the parameters that you might tune for the largest performance improvements. Related concepts The database system-monitor information"on page 316 Related tasks. database with default or tailored settings using a wizard Control Center help"in the Help: Control Center 8 Administration Guide. performance
v Use the Configuration Advisor in the Control Center to get advice about reasonable beginning defaults for your system. The defaults shipped with DB2® should be tuned for your unique hardware environment. Gather information about the hardware at your site so you can answer the wizard questions. You can apply the suggested configuration parameter settings immediately or let the wizard create a script based on your answers and run the script later. This script also provides a list of the most commonly tuned parameters for later reference. v Use other wizards in the Control Center and Client Configuration Assistant for performance-related administration tasks. These tasks are usually those in which you can achieve significant performance improvements by spending spend a little time and effort. Other wizards can help you improve performance of individual tables and general data access. These wizards include the Create Database, Create Table, Index, and Configure Multisite Update wizards. The Workload Performance wizard guides you through tasks that help tune queries. The Health Center provides a set of monitoring and tuning tools. v Use the Design Advisor tool (db2advis) to find out what indexes will improve query performance. v Use the ACTIVATE DATABASE command to start databases. In a partitioned database, this command activates the database on all partitions and avoids the startup time required to initialize the database when the first application connects. Note: If you use the ACTIVATE DATABASE command, you must shut down the database with the DEACTIVATE DATABASE command. The last application that disconnects from the database does not shut it down. v Consult the summary tables that list and briefly describe each configuration parameter available for the database manager and each database. These summary tables contain a column that indicates whether tuning the parameter results in high, medium, low, or no performance changes, either for better or for worse. Use this table to find the parameters that you might tune for the largest performance improvements. Related concepts: v “The database system-monitor information” on page 316 Related tasks: v “Creating a database with default or tailored settings using a wizard: Control Center help” in the Help: Control Center 8 Administration Guide: Performance