DB2 performance problems

Basic Performance Adjustment Roger Sanders Slogan translation Source: DB2 magazine Quarter 3, 2003 · vol. 8, issue 3 Original english text (because the article translation is unauthorized, Please retain the original link when reprinting) Your platform-explicitly specifying that DB2 UDB can achieve good performance DB2 UDB 8. 1 For Linux, UNIX, and Windows versions can exist on a simple single system, or in a complicated client-server environment that runs on a variety of platforms. However, no matter what the environment is, users tend to care about the performance of database applications. So what is performance? How can we improve it? Simply put, performance reflects the performance of a computer system when executing a specified task. It is mainly used to measure the system response time, computing capability, and availability. Each metric can be affected by several factors, including hardware, system (and Database) configuration, type, and number of users working at the same time, and the load of each user application. If the system execution efficiency is low, you can choose from several adjustment methods. Because you have different choices, you are always inclined to choose an organized and concise method with a specific goal in your mind. And that goal should be realistic and quantitative, it can also be measured. Otherwise, the performance adjustment will become an hit-or-miss exercise. (Note: I don't know how to explain the original text, so I keep the original text) So where should DBA start? By observing a database, performance problems typically occur from one or more of the following factors: System (Environment) Configuration Instance Configuration Database Configuration Database Design Application Design. Focus on the initial adjustment effects of each of the above elements, and let them gradually run under each factor until they get the expected performance. In this section, I will describe how to check Linux, UNIX, and Windows platform system environment configurations. The remaining parts will be covered in subsequent sections. Prior to the specific introduction, I will introduce some adjustment guides that are suitable for all platforms. General adjustment rules If you can follow these guidelines before you start, the project you want to adjust will be easier and more likely to succeed. 1. Check for known hardware and software issues. Some performance problems may be corrected by simply updating the Software patch or upgrading the hardware. Since it is possible to solve the problem through a simple service pack, why is it a waste of time and energy to check and adjust other parts of the system? That is to say, before you decide to upgrade the hardware, make sure you understand the problem. It will be expensive to blindly add other network interface cards before you find that the system actually needs more memory. It will not actually improve performance. 2. Based on the overall system considerations. Generally, if it does not work on at least one part of the system, you cannot adjust any aspect of the system. For example, if you reserve a large memory space for the background processes of the DB2 database manager, you will not have enough space to execute your stored procedures. Therefore, before you make changes, consider the impact of these changes on the system as a whole. 3. Perform measurement and Reconfiguration Based on different levels. Do not change the system level or above during one adjustment. Even if you are sure that your plan is helpful, you will have to evaluate how much contribution each change will contribute to the performance improvement results. If you do something wrong, the performance will be reduced without increasing, so that you cannot know which change has caused it to have a negative impact. In the database server environment, the following levels can be considered independently: hardware, operating system, communication software, database, SQL statements, and applications. 4. change one thing at a time. For the same reason, you should adjust only one system level at a time. When you adjust each system level, you should change only one element at a time (Registry variable, instance configuration variable, database configuration variable, and so on ). 5. Put your tracking and feedback programs in place before you start. Performance adjustment is not a specific discipline. Some changes you make will damage the performance rather than help. If this happens, if you have a way to cancel each change, you can spend less time trying to bring the system back to the status before modification. I like to use shell scripts or batch files for changes. In this way, I will be able to store a command (which can return a configuration value equivalent to the original state) and place it as a comment line directly on the command that gives the configuration parameter a new value. Then, if I want to cancel the change, I Will uncomment the line explicitly, comment out the changed command, and re-run the script or batch file. If you are forced to exit by some changes, be prepared to apply them again to every necessary change. 6. Do not deliberately make adjustments because you feel the benefits of adjustment. The adjustments made must solve a clear problem. . If your adjustment strategy is not directly related to the root cause of your attempt to solve the problem, you will gain little or nothing until the root cause of the problem is finally solved. From a certain point of view, such behavior does bring more trouble for subsequent adjustments. 7. Keep in mind the decrease rule of return. Remember, the most efficient performance tuning results often come from your initial efforts. Subsequent adjustments will result in a gradual reduction in earnings and more efforts. Adjusted DB2 UDB System Configuration After DB2 UDB is installed, DB2 UDB uses a set of registration variables to configure the system. Some variables play a key role in performance, while others have little or no effect. Next, I will explain which registration variables can have a significant impact on every operating system platform. Remember, changes to these variables will affect the entire system, so be especially careful when changing the registration variables. For all platforms The following variables are recommended for Linux, UNIX, and Windows platforms. Db2_apm_performance off is the default value of the variable to be registered. This parameter specifies whether the corresponding adjustment can be made in the access plan Manager (APM), which can affect the action of the SQL high-speed buffer memory. It also describes whether global SQL high-speed buffer storage can work without any package locking, this is determined by the internal system lock mechanism that prevents high-speed buffer packets from being ignored and irrelevant. In the nonproduction environment, this variable can only be set to on. When set to on, you can see the error message of out of package cache, and the memory usage will increase. Pre-compilation, binding, and rebinding (precompile, bind, and rebind) operations cannot be performed, and these packages cannot be invalidated or executed. Db2_avoid_prefetch this variable specifies whether prefetching is performed during catastrophic recovery ). The default value is off. If it is set to on, prefetching will not be executed. Db2bpvars supports the db2bpvars parameter to specify the location of the file containing the parameter value used when adjusting the buffer pool (buffer pools). The parameters include: No_nt_scatter Nt_scatter_dmsfile Nt_scatter_dmsdevice Nt_scatter_sms Numprefetchqueues Prefetchqueuesize For each parameter with _ scatter, the default value is 0 (or off), and the allowed values are 0 (or off) and 1 (on ). For the numprefetchqueues parameter, the default value is 1; the value range is 1 to num_ioservers. For the prefetchqueuesize parameter, the default value is the maximum value: 100 or 2 * num_ioservers. The range is 1 to 32,767. Each _ scatter parameter is used to enable or disable the scatter read of the respective tablespace container (or disable the scatter read of all containers ). Other parameters can be used to improve the pre-access (prefetching) of the buffer pool data ). Note: When the Windows operating system is used and the db2ntnocache parameter is set to on, the parameter with _ scatter can only be set to on. Db2chkptr this variable specifies whether to perform the input pointer check; The default value is off. The default value of db2_enable_bufpd is off, which indicates whether DB2 will use intermediate buffering to improve query performance. Db2_extended_optimization this variable specifies whether the query optimizer uses optimization extensions to improve query performance. The default value is off. Db2maxfscrsearch the variable may be set to-1, or any one from 1 to 33554. In order to specify the number of available space to be searched when a record is added to a table. It allows you to balance the insert speed used by space (small values optimize the insert speed, and large values optimize the space usage ). If it is set to-1, DB2 database manager searches for all available space control records. The default value is 5. Db2memmaxfree this variable specifies that each DB2 agent retains the amount of memory available; value range: 0 to 2.0e + 32 bytes. Default Value: 8,388,608 bytes. Db2_override_bpf this variable can be set to an integer 4 K page, which specifies the size of the buffer pool (on the page) that will be created when the database is activated north or when a connection is established for the first time ). When a fault occurs due to memory restrictions, the db2_override_bpf parameter is very useful. In this way, the memory limit may occur due to real insufficient memory (rarely occurring) or attempts to use the DB2 database manager to allocate too much buffer pool or use improper configuration. The default value is null. Db2priorities the value of this variable is platform-related. Db2priorities controls the priority of DB2 processes and threads. Db2_sort_after_tq db2_sort_after_tq specifies how the DB2 optimizer works together with the boot table queue when the receiving end requires data sorting and the number of received nodes is equal to the number of sent nodes in the partitioned database environment. When it is set to no (default value), the DB2 optimizer tends to sort data on the transmitter and merge rows on the receiver. When set to yes, after all rows are received, the optimizer transmits unordered rows at the receiving end and sorts them. Db2_stproc_lookup_first this variable specifies whether the DB2 UDB server executes all Dari statements before viewing the sqllib/function directory and sqllib/function/unfenced directory (remote interfaces of database applications, outdated terms of stored procedures) and the stored procedure. The default value is off. The values of db2_hash_join yes or no indicate whether hash join can be used to compile an access plan. The default value is no. Possible values of db2_parallel_io include * and null (default value), specifying whether DB2 uses parallel I/O when reading and writing data from a 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 wocould 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 (default) to specify whether the tablespace container ID tag uses partial or all RAID disk data entries. When a RAID device is used, the tablespace is created with an extended size of Data bars of the same or several times the size of RAID data blocks. However, due to the existence of the page container tag, the extended part cannot be arranged according to raid data entries. It is more necessary to access more physical disks than to optimize 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 others ). Db2memdisclaim this variable specifies whether the AIX operating system should stop page memory so that it no longer occupies any actual storage space. Setting db2memdisclaim to Yes (default) indicates that DB2 UDB releases part or all of the memory when the program is closed, depending on the value determined by the db2memmaxfree variable registration. If db2memmaxfree is empty, all memory will be released when the program is closed. If db2memmaxfree is assigned a value, only some (up to the value set by db2memmaxfree) memory is released. When the program ends, the release ensures that the memory can be used by other processes. Db2_mmap_read is used with db2_mmap_write. The default value is yes, which allows DB2 for Aix to use MMAP as an I/O staggered method. In most environments, MMAP should avoid operating system locking when multiple processes perform write operations on different sectors of the same file. Db2_mmap_write is used with db2_mmap_read. The default value is yes. In this case, DB2 for AIX is allowed to use MMAP as an I/O preparation method. Db2_pinned_bp the variables on the AIX and HP-UX platforms specify whether the global memory used by the database (including the buffer pool memory) will be retained in the system's primary memory for more stable database performance. For Windows Platforms The following variables apply to the Windows NT operating environment. Db2ntmemsize Windows NT requires that all shared memory fragments are stored in the dynamic connection library during initialization to ensure that the matching address is included throughout the program running. If necessary, the default value of DB2 is not considered in Windows NT; however, in most cases, the default value should be sufficient. The default value is as follows: DBMS: 16,777,216; FCM: 22,020,096; Dbat: 33,554,432; APLD: 16,777,216. Db2ntnocache specifies whether the file system's high-speed buffer memory is executed. This variable applies to all types of data except long or lob data. In addition to the system's high-speed buffer memory, the database can use more available memory to increase the buffer pool or sortheap ). The default value is off. Db2ntpriclass is used to associate the priority of each independent thread (set to use db2priorities). This variable is the DB2 instance Program (db2syscs. EXE) sets the priority group and determines the absolute priority of DB2 threads in the system relative to other threads. The following three priority groups are available: Normal_priority_class (default) Realtime_priority_class (r) High_priority_class (h ). The value can be r, H, or other values. The default value is null. Db2ntworkset this variable specifies the minimum and maximum workspace size that DB2 can use. Lack of time-saving, when Windows NT is not in the page scheduling status, the workspace of a process may increase as you like. However, once a page is scheduled, the maximum workspace size is only about 1 MB. This variable allows you to ignore this default state. Possible values include positivenumber and positivenumber0. (default value) Modify DB2 registration Variables How do you determine whether these variables have been set and what they have been set? DB2 UDB v8.1 allows you to view and change variables in two ways: by using the DB2 registration management tool (available from configuration Assistant) or executing the system command db2set. Listing 1 shows the syntax of that command. Note: parameters displayed in angle brackets (<>) are optional. 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 Options:

Code:

If you run the db2set command without the option, a list containing the configured variables and their current values will be returned, Continue Adjustment Performance Tuning for DB2 UDB may be a long process because many factors may affect performance. This column describes how to adjust the general performance and improve the DB2 UDB system configuration. In the next column, I will explain how to adjust the instance and database configuration to the best performance. About the author Roger E. Sanders is a database maintenance engineer for 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 contact him, please send a letter to the roger.sanders@netapp.com.