Shulou(Shulou.com)06/01 Report--

This article will explain in detail what are the steps to optimize the DB2 database in Windows. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Overview

To better diagnose performance problems, this article discusses an organized process to help determine whether there is a performance problem in the database and to develop remedial measures. When the performance of DB2 or e-commerce applications does not meet expectations, the entire organization and financial bottom line may be affected.

Hypothetical

This article assumes that the reader has a basic understanding of the database, SQL, and DB2 LUW. It may be helpful to have a basic understanding of UNIX performance tuning and monitoring.

Performance issues and their relationship to system resources

Performance issues cover a wide range of scenarios:

SQL query execution is slower than expected

The workload or batch job did not complete within the expected time, or the transaction rate and throughput gradually decreased over a period of time

The overall speed of the system is decreasing.

In most cases, performance problems are due to improper use of system resources or overuse of resources such as CPU, IO, and memory, which often reveals bottlenecks in these system resources. In an appropriately tuned environment, system resources will be used without over-reliance on any of them.

The * * step in diagnosing performance problems is to identify all resource bottlenecks. Windows provides tools to help identify these bottlenecks.

CPU bottleneck

If one or more CPU on the system consistently shows more than 90% utilization, this usually means that there is a CPU bottleneck in the system. The Task Manager can help you find out if there are CPU bottlenecks in your system. Other tools, such as perfmon.exe and Resource Monitor, show CPU utilization and can also help identify CPU bottlenecks.

Memory bottleneck

Memory bottlenecks are not very common, mainly because the heap and parameters of the database are usually configured based on available memory. However, if you see very low available memory in perfmon and resource monitors, this may indicate that there is a memory bottleneck. Sometimes, when using STMM, the available memory on the system may be very low, but this does not always mean that the system has a memory bottleneck.

Network bottleneck

If you see a very high network utilization in the Resource Monitor, this may indicate a network bottleneck. Resource Monitor displays network utilization as a percentage, which helps to quickly identify network bottlenecks. If the Resource Monitor shows that the network utilization is above 80%, this usually indicates that there is a network bottleneck.

Ipaw O bottleneck

"if one or more disks on the system have been busy for more than 90% of the time, or if the disk queue length continues to show a high number, this usually means that there is an Iwhite O disk bottleneck in the system." Windows tools, such as Resource Monitor and perfmon, can help identify I Candle O bottlenecks. It is true that the task manager can display iUnip O activity, but the Resource Monitor and perfmon can display iUnip O details for each disk, as well as the percentage of active time, which can help identify whether there is a bottleneck on any particular disk.

There are a variety of Windows tools that can help determine if the system has one or more resource bottlenecks.

Step 1: use Window tools to identify bottlenecks

Task Manager

The task manager is the fastest way to get information about the usage of the entire system. For example, figure 2 is a screenshot of the Processes tab of the task manager, where the columns provide CPU, memory, and View O statistics for each process (View > Select Columns). The task manager provides a good summary of CPU, IZP O, memory, and network utilization. The task manager also provides detailed information about processes to help find out which processes are consuming the most CPU, which processes are performing the most IBO, and so on.

If the task manager shows that the overall CPU utilization has been more than 90%, then this is a sign that there is a CPU bottleneck. The Task Manager also displays each CPU activity on the Performance tab. If the utilization of any of these CPU is always close to 100%, this may also mean that there is a CPU bottleneck. Typically, this means that the workload in the database is single-threaded and cannot take advantage of all the CPU available on the system. Figure 1 is an example of a single-threaded application running. Even if only one CPU is busy and no other CPU is used, it is still a CPU bottleneck.

The task manager also displays details of the amount of data that each process reads / writes from disk. This information is useful in itself, but it does not show the percentage utilization of each disk. This makes it difficult for users to determine whether there is an Icano bottleneck in the system just through the task manager.

Figure 1. Task Manager-sample single-threaded workload

Figure 2. The task manager shows the process of memory utilization

Resource monitor

Resource Monitor is another Windows tool available on Windows 2008 and Windows 7. It provides detailed information about Imax O, CPU, memory, and network usage. This tool displays real-time information about all processes running on your system and provides the ability to filter data based on user requirements. This can be done based on memory, CPU, disk, and network usage. The Overview tab shows the activity of the entire system and provides a snapshot of the bottleneck in the system. Icano and network usage are displayed as a percentage of the utilization of available bandwidth. This helps to identify whether there is an Ihammer O bottleneck or network bottleneck in the system, which is impossible to confirm from the task manager. In addition, the Resource Monitor shows the disk queue length for each disk, which is useful for determining whether the disk has enough bandwidth to address the system's Ibig O requirements.

Figure 3. Resource monitor

Figure 4. Resource Monitor DB2 disk activity

Perfmon

Although Task Manager and Resource Monitor are useful tools for determining system activity, you cannot use them to log system activity for later analysis. The Perfmon tool records system activity in a log file. This provides flexibility for administrators and DBA to collect perfmon data at different times of the day and use them for analysis later. The perfmon tool included with Windows can be used to capture performance data and statistics on resource usage. For many types of problem surveys, it is critical to understand how to set up and capture perfmon logs. One thing to note when monitoring diskperf O is that you need to enable disk counters by running disk-y (- ye for band set), and then restart. On Windows 2008 or Windows 7, you need to run perfmon to capture the activity to a log file:

Run perfmon from a command prompt.

Select Performance Monitor from the frame on the left.

Right-click it and select New > Data Collector set. Create an appropriate name and click Next.

Provide a directory name that will save the log.

Data Collector set appears in the frame on the left. Select Data Collector Set > User Defined in the frame on the left, and select the name you selected in step 4. Its state should be stopped because we want to add the required counters before the collection begins.

Right-click the defined Data collector set and select New > Data Collector. Provide a name, select Performance counter data collector, and then click Next. Select the sampling frequency and increase the performance counter. The Perfmon tool provides many counters to monitor a variety of parameters, and here are some of the most useful counters. This is a good starting point for collecting data. According to the specific requirements and circumstances, users can collect and monitor other counters.

7. Once you have completed the performance counter selection shown in the figure below, you can collect data for the required time interval. You can modify this using the properties section of the selected data collector set. The data collected for the selected counter can be saved in a table or spreadsheet format. Under the File tab, in the properties section, you can specify the format of the output.

Figure 5. Perfmon performance counters

8. Once you are ready for data collection, you can start your workload or query and start data collection by clicking Start on the selected data collector set.

When the query / workload is complete, stop monitoring data collection and check the collected data.

Choose the right diagnostic tool

Perfmon is a useful tool for general surveillance. You can also save its logs to make it easier to compare system activity when the system works as expected and when the system has performance problems. This can often provide valuable clues to the problem at hand. However, a quick look at data from task managers and resource monitors can sometimes help you find bottlenecks in your system in real time. Once the bottleneck is identified, corresponding measures can be taken to eliminate the bottleneck.

Step 2:I/O bottleneck-detailed study

"if perfmon shows that one or more disks have disk time above 80%, or Resource Monitor shows that one or more disks are active at more than 80%, this usually means that there is an I bottleneck in the system." One or more disks with high utilization can be determined from perfmon or Resource Monitor. Once you have identified a heavily used disk, you can find out what is placed on the disk.

Are there any DB2 tablespace containers placed on disk?

Db2 list tablespace containers for

Repeat this command for all tablespaces in the database.

Or is the DB2 log file placed on a heavily used disk?

Db2 get db cfg for

Search for newlogpath database configuration parameters.

Or do these disks contain utility files, such as backup targets or load files? View the backup / load commands that have been executed. Depending on the content on the heavily used disk, the solution will also vary.

Disk bottleneck on tablespace container

If you allocate heavily used disks to a tablespace container, find the objects in the tablespace. If the tablespace corresponds to a data tablespace, find the table created in the tablespace.

Db2 select tabname from syscat.tables where tbspaceid =

Find the most active table from the MON_GET_TABLE table function. The following query lists the tables with the most row reads.

Listing 1. The most active table in the tablespace

Db2 "select varchar (tabschema,20) as tabschema, varchar (tabname,20) as tabname, table_scans, sum (rows_read) as total_rows_read, sum (rows_inserted) as total_rows_inserted, sum (rows_updated) as total_rows_updated, sum (rows_deleted) as total_rows_deleted FROM TABLE (MON_GET_TABLE (',',-2)) AS t WHERE TBSP_ID = hot tablespace id GROUP BY tabschema Tabname ORDER BY total_rows_read DESC "

Note: the MON_GET_TABLE function provides a lot of useful information. It tracks the number of table scans on the table. If there are more table scans, it may mean that the table does not have an appropriate index, or that the query does not use an existing index on the table.

Once you have identified the active table, you can use the MON_GET_PKG_CACHE_STMT table function to easily find the SQL statements executed on the table.

Listing 2. Find the query on a given table

Db2 "select section_type, executable_id, package_name,num_executions, char (stmt_text, 100) from table (MON_GET_PKG_CACHE_STMT ('dating, NULL, NULL,-2)) as T where stmt_text like'% hot table name%'"

In this way, it is easy to find SQL statements on active tables that cause a large number of read / write operations. Use Design Advisor to determine whether the SQL statement uses the correct index on the table. If there are appropriate metrics in the table, but they are not used, check that the statistics on the table are * information. Incorrect or outdated statistics may cause the optimizer to choose a sub-optimal access plan.

Disk bottleneck on temporary tablespace

If heavily used disks are allocated to temporary tablespaces, this means that there is a lot of sorting activity on the database. The high-level temporary tablespace IMaple O activity often occurs with large queries with large result sets or a large number of sorts. In this case, you need to determine if a large number of sorts have overflowed to disk. MON_GET_PKG_CACHE_STMT can provide SQL statements that cause sort overflows.

Listing 3. Queries with a large number of sorting activities

Db2 "select section_type, package_name,num_executions, total_sorts, sort_overflows, char (stmt_text, 100) from table (MON_GET_PKG_CACHE_STMT ('dating, NULL, NULL,-2)) as T where sort_overflows > 1 order by sort_overflows desc"

If there are too many sort overflows, check that the SORTHEAP or SHEAPTHRES configuration parameter is set to a lower value. If the relevant parameters for sorting are set correctly, determine whether it is possible to avoid a large number of sorting by creating an index. Use Design Advisor for queries with a large amount of sorting to see if sorting can be avoided or reduced by creating additional indexes.

Disk bottleneck on transaction log

In an OLTP environment, the performance of transaction logs is very sensitive. The MON_GET_TRANSACTION_LOG table function provides detailed activities about the transaction log.

Listing 4. Transaction log activity

Db2 "select log_reads, log_read_time, log_write, log_write_time, num_log_buffer_full, num_log_data_found_in_buffer from table (MON_GET_TRANSACTION_LOG (- 1)) as T"

Num_log_buffer_full shows the number of times the log buffer is full and must be flushed to disk before a new log is written to the buffer. If this number has been increasing for some time, it means that the log buffer is too small for the workload on the database. Increasing the LOGBUFSZ database parameter values can help improve log performance and reduce the number of Icano on the transaction log disk.

Num_log_data_found_in_buffer shows the number of times the agent reads log data from the buffer. Reading log data from a buffer is better than reading log data from disk because the latter is slower. You can use this element in conjunction with num_log_read_io element to determine whether you need to further increase the LOGBUFSZ database configuration parameters.

Step 3:CPU bottleneck-detailed study

If perfmon or Resource Monitor shows that one or more CPU is more than 90% used, this usually means that there is an CPU bottleneck in the system. Like the CPU O bottleneck, the * step is to identify database operations that consume a lot of UBG. In general, some database operations are known to consume a large amount of CPU:

Statement compilation

LOAD, BACKUP, runstats and other utilities

A large number of sorting activities

To determine whether a large amount of CPU is spent in query compilation, query the MON_GET_WORKLOAD table function.

Listing 5. CPU time spent in different activities

Db2 "select varchar (workload_name,30) as workload_name, sum (total_cpu_time), sum (total_compile_proc_time), sum (act_rqsts_total), sum (total_compilations), sum (total_act_time), sum (pkg_cache_inserts), sum (pkg_cache_lookups) from TABLE (MON_GET_WORKLOAD (',-2) as T group by workload_name"

If the compile_proc_time is higher than 5-10% total_cpu_time and the pkg_cache_inserts/pkg_cache_lookups is higher than 4-5%, the database spends too much time compiling statements. This may be because the query is compiled repeatedly, or because the package cache is too small and the query must be moved somewhere else to make room for the new query. If the application uses string text instead of parameter markers, the SQL statement is repeatedly compiled. In DB2 9.7 and 10.1, DB2 provides a database configuration parameter STMT_CONC (statement concentrator). When this parameter is enabled, the statement concentrator modifies dynamic statements to allow more package cache entries to be shared and to reduce statement compilation.

To find out all the utilities that cause high CPU utilization, query the MON_GET_WORKLOAD table function. The metrics returned show a summary of all metrics for submitted requests submitted by connections mapped to identified workload objects. During the execution of the request, the metrics are summarized at the work unit boundary or periodically into a workload. The values reported by the table function reflect the current status of the system at the time of the most recent summary. Indicators are strictly incremental values. To determine how much time is spent in utilities such as LOAD and reorg during an interval, you can use the metrics used by the MON_GET_WORKLOAD query at the beginning and end of the interval and calculate their differences.

Listing 6. CPU time spent in LOAD and runstat utilities

Db2 "select varchar (workload_name,30) as workload_name, sum (total_loads), sum (total_load_proc_time), sum (total_runstats), sum (total_runstats_proc_time) from TABLE (MON_GET_WORKLOAD (',-2) as T group by workload_name"

Utilities such as LOAD are designed to make full use of available resources and improve performance to a limited extent. If these utilities consume more CPU than expected, you can throttle the utility by setting the util_impact_limit database configuration parameters. Another database operation that consumes a lot of CPU is sort. As described in the Ibottleneck O section, find the query that performs the most sorting activities and use Design Advisor to determine whether sorting can be avoided by creating additional indexes.

Step 4: memory bottleneck

Memory bottlenecks are not very common, mainly because the heap and parameters of the database can be set according to available memory. Most DB2?? The heap is automatic and provides allocation values based on available memory. STMM does a good job of leveraging available memory and allocating memory to the heap that needs it most. However, without STMM, it is possible to use memory improperly, and if too much memory is allocated (that is, the allocation value is higher than the available memory), it can lead to a lot of paging activity. If the Perfmon or Resource Monitor shows a lot of paging activity, this is usually because the memory allocated to different heaps exceeds the actual memory. In this case, * opens the STMM and lets DB2 tune the memory of the buffer pool, sort heap, and other heaps.

Step 5: network bottleneck

Network bottlenecks are usually caused by a large amount of data moving around (such as very large result sets and client loads, etc.), or applications that manipulate LOB are located in a client-server architecture. The MON_DB_SUMMARY management view is a good illustration of the time spent waiting for different resources. The NETWORK_WAIT_TIME_PERCENT field provides the percentage of time to wait for a network response. Typically, the time spent waiting for the network should be less than 1%. If this value is a few percentage points higher, and perfmon and resource monitors show that the network bandwidth is being heavily consumed, then the system may encounter a network bottleneck. In this case, the application can move some application logic to the server in the form of stored procedures or UDF. In the case of client load, you can split the load into smaller parts and execute them at different times, rather than loading them all at once, which reduces network traffic.

Step 6: lock the problem

If the system does not have any resource bottlenecks, but performance is still poor, this may be due to locking problems. The LOCK_WAIT_TIME_ PERCENT field in the MON_DB_SUMMARY management view provides a high-level view of the time spent waiting for locks at the database level. To get a detailed view of the workload that has spent time waiting for the lock, query the MON_GET_WORKLOAD monitor table function.

Listing 7. The time spent waiting for the lock

Db2 "SELECT varchar (workload_name,30) as workload_name, sum (lock_wait_time) as total_lock_wait_time, sum (lock_waits) as total_lock_waits, sum (lock_timeouts) as total_lock_timeouts, sum (lock_escals) as total_lock_escals FROM TABLE (MON_GET_WORKLOAD (',-2) AS t GROUP BY workload_name ORDER BY total_lock_wait_time DESC"

Step 7: tune the page cleanup activity

In addition to checking system resource bottlenecks and locking issues, there are other important things to pay attention to in all database environments. Page cleanup and prefetching are two important activities that need to be tuned to achieve * performance. In some cases, if the page cleanup is not tuned correctly, it is possible to have an Imax O bottleneck. The watch table MON_GET_BUFFERPOOL provides some indicators for finding page cleanup and prefetch activities.

Listing 8. Page cleanup activity

Db2 "WITH BPMETRICS AS (SELECT bp_name, pool_data_writes, pool_async_data_writes, pool_index_writes, pool_async_index_writes, pool_no_victim_buffer, pool_lsn_gap_clns, pool_drty_pg_steal_clns, pool_drty_pg_thrsh_clns FROM TABLE (MON_GET_BUFFERPOOL ('',-2) AS METRICS) SELECT VARCHAR (bp_name,20) AS bp_name, pool_data_writes, pool_async_data_writes CASE WHEN pool_data_writes > 0 THEN DEC ((FLOAT (pool_async_data_writes) / FLOAT (pool_data_writes)) * 100FLOAT 5) 2) ELSE NULL END AS PAGE_CLN_RATIO, pool_index_writes, pool_async_index_writes, CASE WHEN pool_index_writes > 0 THEN DEC ((FLOAT (pool_async_index_writes) / FLOAT (pool_index_writes)) * 100 5) ELSE NULL END AS IND_CLN_RATIO, pool_no_victim_buffer Pool_lsn_gap_clns, pool_drty_pg_steal_clns, pool_drty_pg_thrsh_clns FROM BPMETRICS. "

The Data and Index page cleanup rates in the above query should be close to 100%. If the value is less than 90%, the speed of page cleaning will not be able to keep up with the growth rate of dirty pages in the system.

Dirty_page_steal_clns shows dirty page theft. Ideally, the value must be zero. If it exceeds a small portion of the number of logical reads, more pages need to be cleaned. Please lower the CHG_PGS_THRESHOLD and make sure there are enough Icano cleaners.

Pool_no_victim_buffer shows the number of times the agent could not find available pages in the list of available pages. If this number exceeds the logical reading by a few percentage points, then it is harmful. Please lower the CHG_PGS_THRESHOLD and make sure there are enough Icano cleaners.

Step 8: tune the prefetch activity

Similar to page cleanup, we also need to tune the prefetch activity. In a real OLTP environment, prefetching may not be useful. However, prefetching plays an important role in DSS-like workloads. Ideally, we want the IO_SERVERS IO_SERVERS (prefetcher) to take care of all reads, which are essentially asynchronous. The following query shows the percentage of I _ swap O reads completed by IO_SERVERS.

Listing 9. Prefetch activity

Db2 "WITH BPMETRICS AS (SELECT bp_name, pool_data_p_reads, pool_async_data_reads, pool_temp_data_p_reads, pool_index_p_reads, pool_async_index_reads FROM TABLE (MON_GET_BUFFERPOOL ('',-2) AS METRICS) SELECT VARCHAR (bp_name,20) AS bp_name, pool_data_p_reads, pool_async_data_reads CASE WHEN pool_data_p_reads > 0 THEN DEC ((FLOAT (pool_async_data_reads) / FLOAT (pool_data_p_reads + POOL_TEMP_DATA_P_READS)) * 100 THEN DEC 5) ELSE NULL END AS PREFETCH_RATIO, pool_index_p_reads, pool_async_index_reads, CASE WHEN pool_index_p_reads > 0 THEN DEC ((FLOAT (pool_async_index_reads) / FLOAT (pool_index_p_reads)) 2) ELSE NULL END AS PREFETCH_IDX_RATIO FROM BPMETRICS "

Values greater than 90% are appropriate for PREFETCH_RATIO.

Concluding remarks

Although these steps do not cover all the performance problems that may arise, the above approach focuses primarily on the principles and strategies used to solve performance problems. Following these steps will help you narrow the scope of the problem and ultimately help you solve the problem.

So much for sharing the steps of optimizing the DB2 database in Windows. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.