Basic performance adjustment
Roger Sanders
Laughing paste
Original source: "DB2 Magazine" Quarter 3, 2003 · Vol. 8, Issue 3
English original (due to unauthorized article translation, please retain the original link when reprint)
Your platform - Clear specification can achieve good performance from DB2 UDB
DB2 UDB 8.1 for Linux, UNIX, Windows versions can exist on a simple single system, or in the existence of complex client-server environments where there are in various platforms. But no matter what environment, users always tend to care about such a problem: the performance of the database application. So what is performance, how can I improve it?
Simply put, performance is a specific manifestation that reflects the computer system to perform a specified task. It is mainly used to measure system response time, computing power and availability. Each measure may be affected by several factors, including hardware, systems (and database) configurations, types, and simultaneous number of users, and load of each user application.
If the system performs low efficiency, you can usually have several adjustments to choose from. Due to different choices, you always tend to choose an organized, concise way, there is a specific goal in your heart; and that target should be realistic, quantitative, and can still be measured; otherwise, Performance adjustment will become a hit-or-miss exercise. (Note: I don't know how to explain, so reserved the original)
So, where should DBA start? Through the observation of a database, performance issues are more than one or more factors in the following:
System (environment) configuration
Instance configuration
Database configuration
Database Design
Application design.
Focus on the initial adjustment effect of each of your elements, let them run under every factor until you have expected those properties. In this chapter, I will describe how to check Linux, UNIX, and Windows platform system environment configurations. The subsequent chapter will override the remaining part. Before the specifically introduced, although I will introduce some adjustments to all platforms.
General adjustment rules
If you can pay attention to these guidelines before you start, you will be more easier and more likely to succeed.
1. Check known hardware and software issues. Some performance issues can be corrected by simply updating software patches or upgrading hardware. Since it is possible to solve the problem through a simple service pack, why should I waste time and energy to check the other parts of the system? That is to say, please make sure you have learned the problem before you decide the hardware. In the case where you find that the system needs more memory, you will have an expensive price before you get more memory, which is actually no effect on improved performance.
2. Based on the entire system considering. Typically, if you don't work on at least one component of this system, you cannot adjust any of this system. For example, if you reserve a big memory space for the background process for the DB2 Database Manager, you don't have enough remaining space to perform your stored procedure. Therefore, before you change, you should consider these changes will affect the system.
3. Do as measured and reconfigured according to different levels. Do not change a system level or more in one adjustment. Even if you make sure your plan is beneficial, you will have to assess how much each change will contribute to the results of performance improvement. If you are wrong, performance has no reduction, so you can't know which change makes it a negative impact. In the database server environment, the following is the level that can be considered: hardware, operating system, communication software, database, SQL statement, and application.
4. One thing to change each time. Because you should only adjust a system level each time, when you adjust each system level, you should only change a feature (registry variable, instance configuration variable, database configuration variable, etc.). 5. Place your tracking and feedback prior before starting. Performance adjustments are not a specific discipline. Some changes you have done will harm rather than contributing to performance. If this happens, if you have a way to revoke each change, you can spend less time to try to make the system back to the state before modification. I like to use the shell script or batch file to change. In that, I will be able to store a command (can return a configuration value of an equivalent to the original state) and put it as a comment line over the command to confer the new value of the configuration parameters. Then, if I need to cancel the change, I will make this trip to cancel the comment, and the command that causes the change comes out and re-runs this script or batch file so that you can. If you are forcibly exiting some changes, please prepare to re-apply each necessary change.
6. Don't deliberately do it because of the benefits that feel adjusted. The adjustments performed must be able to solve a clear problem
. If your adjustment strategy does not have a direct relationship with the root causes of the problem to solve the problem, you will harvest minimally or unless the roots of the problem are finally resolved. From a point of view, such behavior has brought more troubles for later adjustments.
7. Remember the return of the regulation. Remember that the earnings of the most efficient performance adjustment results are usually from your initial efforts. Subsequent adjustment will result in step-by-step revenue and need to pay more efforts.
Adjustable DB2 UDB system configuration
DB2 UDB after the DB2 UDB is installed to configure the system. Some variables play a key role in performance; while others are minimal, even no effect. Next I will explain which registration variables can have a major impact on each operating system.
Remember that changes to these variables will affect the entire system, so be careful when changing the registered variables.
For all platforms
The recommendation of the following variables is suitable for Linux, UNIX, and Windows platforms.
DB2_APM_PERFORMANCE OFF is the default value of the registered variable. This parameter specifies whether the corresponding adjustment can be made in the Access Scheme Manager (APM), which can have an impact on the action of the SQL cache. It also expounds whether the global SQL cache can work without any package lock, which is determined by the internal system lock mechanism that prevents the cache from not paying attention to unaffected.
In the nonProduction environment, this variable can only be set to ON. When set to ON, you can see the error message of the Out of Package Cache and the memory usage will increase. Precompile, bind, and rebound operations cannot be done or not, these packages are invalid or cannot be executed.
DB2_AVOID_PREFETCH This variable specifies whether prefetching is performed during catastrophic recovery. The default is OFF; if set to ON, PreFetching will not be executed.
DB2BPVARS supports the parameters of DB2bpvars, which explicitly points out the position of the file containing the parameter value when the buffer pool is adjusted, including:
NO_NT_SCATTER
NT_SCATTER_DMSFILE
NT_SCATTER_DMSDEVICE
NT_SCATTER_SMS
Numprefetchqueues
PrefetchqueueSize
For each parameter of each belt _Scatter, the default value is 0 (or OFF), allowing the value of 0 (or OFF) and 1 (ON). For Numprefetchqueues parameters, the default is 1; the range of parameter values is 1 to Num_ioservers. For PrefetChQueueSize parameters, the default value is the maximum value: 100 or 2 * num_ioservers. The range is from 1 to 32, 767. Each _scatter parameter is used to open or close the Scatter Read (or close all containers Scatter Read) for opening or closing the SCATTER READs of each tablespace container. Other parameters can be used to increase prefetching of buffer pool data.
Note: When using the Windows operating system and the DB2NTnocache parameter is set to ON, the parameters of _scatter can only be set to ON.
DB2chkptr This variable specifies whether the input pointer check is performed; the default value is OFF.
The DB2_ENABLE_BUFPD default is OFF, which indicates whether DB2 will use the intermediate buffer to improve the performance of the query.
DB2_EXTENDED_OPTIMIZATION This variable specifies whether the query optimizer uses optimized extensions to improve query performance; the default is OFF.
DB2MAXFSCRSEARCH This variable may be set to -1, or any one from 1 to 33554, in order to specify the number of searches available space control records when adding a record to a table. He allows you to go to balance the insert speed (small value to optimize the insertion speed, the large value makes the space to be optimized). If set to -1, the DB2 Database Manager will search for all available space control records. The default is 5.
DB2MEMMAXFREE This variable specifies that each DB2 agent will retain the number of memory available; the value ranges from 0 to 2.0e 32 bytes. The default is 8,388,608 bytes.
DB2_OVERRIDE_BPF This variable can be set to an integer 4K page, which specifies the size of the buffer pool when it is created in the data width or the first time (in the page). When the occurrence of the occurrence of memory restrictions, DB2_OVERRIDE_BPF is very useful. Such memory restrictions may occur during the true memory (rarely) or because the DB2 database manager is tried to allocate too much or inappropriately configured. The default is empty.
DB2PRIORITIES This variable is related to the platform. DB2Priorities controls DB2 process and thread priority.
DB2_SORT_AFTER_TQ DB2_SORT_AFTER_TQ specifies how the DB2 optimizer and the boot queue work together when the receiving end requests data sorting and the number of received nodes and the transmitted number of nodes are synergistic. When set to NO (default), the DB2 optimizer tends to be sorted at the transfer end and consolidated on the receiving end. When set to YES, the optimizer transmits unsorted rows in the receiving end after all rows are completed, and sort them.
DB2_STPROC_LOOKUP_FIRST This variable specifies whether the DB2 UDB server performs a catalog look for all DARIs (outdematic terms of the database application remote interface, stored procedure) and stored procedures before viewing the SQLLIB / FUNCTION directory and the SQLLIB / FUNCTION / UNFENECED directory. The default is OFF.
DB2_HASH_JOIN YES or NO value indicates whether it can be used when compiling an access plan (Access Plan). The default is NO.
DB2_PARALLEL_IO Possible Values include * and NULL (default), specify whether DB2 uses parallel I / O when reading and write data from the tablespace container.
However, because of the one-page container tag, the extents will not line up with the RAID stripes. It may be necessary to access more physical disks than would be optimal during an I / O request unless this registry variable is set to ON.
DB2_striped_containers This variable is set to ON or NULL Specify whether the tablespace container ID tag uses some or all RAID disk data strips. When using the RAID device, the table space is used to use the size of the RAID data block or the size of the size of the size of the data strip. However, due to the presence of the page container tag, the extension cannot be arranged in accordance with the RAID data strip. Accessing more physical hard drives are more necessary than optimizing during the I / O request, unless the variable is set to ON.
For UNIX platforms
The following variables apply to UNIX platforms (AIX, HP-UX, or some other mentioned).
DB2MEMDISCLAIM This variable indicates whether the AIX operating system should stop over the page no longer occupy any actual storage space. Setting DB2MEMDISCLAIM to YES indicates that DB2 UDB releases part or all memory when the program is closed, depending on the value determined by the DB2MemMaxFree registration variable. If DB2MemMaxFree is empty, all memory will be released when the program is turned off. If DB2MemMaxFree is assigned, only partially (up to DB2MEMAXFREE set value) memory release. When the program ends, the release ensures that the memory can be used by other processes.
DB2_MMAP_READ It is used with DB2_MMap_Write, the default value YES allows DB2 for AIX to use MMAP as an interleaven method of I / O. In most environments, MMAP should avoid the case where the operation system lock caused by multiple processes in different sectors of the same file will be used.
DB2_MMAP_WRITE It is used with DB2_MMap_read, the default is YES; at this time he allows DB2 for AIX to use MMAP as an I / O preparation method.
DB2_PINNED_BP This AIX and the variable on the HP-UX platform specifies whether the global memory (including buffer pools) used by the database will be retained in the system main memory to achieve more stable database performance.
For Windows Platform
The following variables apply to the Windows NT operating environment.
DB2NTMEMSIZE WINDOWS NT requires that all shared memory segments are stored in the dynamic connection library when initialization is initialized to ensure that there are matching addresses during the entire process run. If necessary, the default value of DB2 is not considered under Windows NT; however, in most cases, the default value should be sufficient. The default is as follows:
DBMS: 16,777,216;
FCM: 22, 020, 096;
DBAT: 33, 554, 432;
APLD: 16, 777, 216.
DB2NTNOCACHE Specifies whether a file system cache is executed. This variable applies to any type of data other than long or LOB type data. Remove the system cache memory allows the database to use more available memory to add more buffer pools or sortheaps. The default is OFF.
DB2NTPriclass is used to associate a priority of each stand-alone thread (settings use DB2Priorities), which is the absolute priority of the DB2 instance program (DB2Syscs.exe) Settings GM to determine the absolute priority of the DB2 thread relative to other threads. The following three priorities are available: Normal_Priority_Class (default)
RealTime_Priority_Class (R)
High_Priority_Class (H).
The value can be R, H, and other values, the default is NULL.
DB2NTWORKSET This variable indicates the minimum and maximum workspace sizes that DB2 can be utilized. By default, when Windows NT is not in the state of page scheduling, the workspace of a process may increase with your heart. However, once in page scheduling, the largest work area can only be approximately 1MB. This variable allows you to ignore this default. Possible values include PositiveNumber, PositiveNumber0. (By default is 1, 1)
Modify DB2 registration variable
How do you determine if these variables have been set and they are set to? DB2 UDB V8.1 gives you two ways to view and change variables: by using the DB2 Registration Management Tool (can be found from Configuration Assistant) or execute the system command db2set. Listing 1 shows the syntax of that command.
Note: The parameters (<>) displayed in the corner bracket are optional; the parameters or options displayed in square brackets ([]) are required.
Listing 1: Syntax for viewing and changing variable values
Table 1 describes other available options for this command.
Table 1: db2set command option:
If the db2set command that does not add option will return a list containing the variables already set and their current value,
Continue to adjust
The performance adjustment of DB2 UDB may be a long process because many factors may affect performance. This column has reported a general performance adjustment and improvement of the DB2 UDB system configuration. In the next column, I will explain the way to adjust the instance and the database configuration as optimal performance.
About author
Roger E. Sanders is a database maintenance engineer of Network Appliance Inc.. He is also the author of all-in-one db2 administration Exam Guide (McGraw-Hill / Osborne Media, 2002). If you want to get in touch with him, please write a letter Roger.Sanders@NetApp.com.
About myself
Then there is nothing to say; the first translation has a lot of strength, I hope everyone will refer to the original text of English while reading, welcome criticism.
