DB2性能调优指南：Linux/Unix/Windows问题排查与优化技巧

performance

tune

需积分: 7 89 浏览量更新于2024-07-28 收藏 4.89MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

本资源是一份针对IBM DB2 9.7版本的性能调优与故障排查文档（SC27-2461-00），适用于Linux、UNIX和Windows操作系统。这份详细的文档旨在帮助用户解决在使用DB2数据库时可能遇到的性能问题，并提供了一系列的故障排除步骤和优化建议。首先，文档强调在使用前应阅读附录B中的通用信息，特别是注意事项部分，这可能包括版权声明、法律条款以及产品支持的限制。用户需了解提供的信息并不包含产品保修，任何手册中的陈述都不得视为保修承诺。在DB2 9.7中，性能调优涉及的关键知识点可能包括但不限于： 1. **性能指标监控**：文档可能介绍了如何监控数据库的性能指标，如CPU使用率、内存使用、磁盘I/O和网络流量等，以便识别瓶颈。 2. **查询优化**：对于SQL查询的优化是性能调优的重要环节，可能涵盖分析查询计划、索引优化、统计信息维护和查询语句改写等内容。 3. **存储管理**：存储设备的管理和配置对性能有很大影响，如表空间设计、分区策略和缓存设置。 4. **并发控制**：处理高并发场景下的事务处理和锁定机制优化，确保并发访问的效率。 5. **硬件调优**：针对特定硬件环境，可能指导用户调整硬件配置，如增加内存、优化CPU设置或使用更快的硬盘。 6. **数据库配置**：调整DB2的系统参数，如连接池大小、事务日志设置、数据页大小等，以适应应用需求。 7. **备份与恢复**：高效的数据备份和恢复策略也是性能优化的一部分，以减少对生产环境的影响。 8. **故障排查工具**：文档会介绍使用IBM提供的诊断工具和技术来定位和解决性能问题，如使用DB2 Profiler或Vizual Explain等。 9. **案例研究和最佳实践**：通过实际案例，文档可能会分享已知问题的解决方案和性能优化的最佳实践，供用户参考。 10. **持续监控与调整**：强调性能调优是一个持续的过程，需要定期评估和根据业务需求进行调整。这份文档为DB2 9.7用户提供了全面的性能优化和故障排除指南，是任何希望提高数据库性能和维护系统稳定性的管理员不可或缺的参考资料。

资源详情

资源推荐

Benchmark preparation

There are certain prerequisites that must be satisfied before performance

benchmark testing can be initiated.

Before you start performance benchmark testing:

v Complete both the logical and physical design of the database against which

your application will run

v Create tables, views, and indexes

v Normalize tables, bind application packages, and populate tables with realistic

data; ensure that appropriate statistics are available

v Plan to run against a production-size database, so that the application can test

representative memory requirements; if this is not possible, try to ensure that the

proportions of available system resources to data in the test and production

systems are the same (for example, if the test system has 10% of the data, use

10% of the processor time and 10% of the memory that is available to the

production system)

v Place database objects in their final disk locations, size log files, determine the

location of work files and backup images, and test backup procedures

v Check packages to ensure that performance options, such as row blocking, are

enabled when possible

Although

the practical limits of an application might be revealed during

benchmark testing, the purpose of the benchmark is to measure performance, not

to detect defects.

Your benchmark testing program should run in an accurate representation of the

final production environment. Ideally, it should run on the same server model with

the same memory and disk configurations. This is especially important if the

application will ultimately serve large numbers of users and process large amounts

of data. The operating system and any communications or storage facilities used

directly by the benchmark testing program should also have been tuned

previously.

SQL statements to be benchmark tested should be either representative SQL or

worst-case SQL, as described below.

Representative SQL

Representative SQL includes those statements that are executed during

typical operations of the application that is being benchmark tested. Which

statements are selected depends on the nature of the application. For

example, a data-entry application might test an INSERT statement, whereas

a banking transaction might test a FETCH, an UPDATE, and several

INSERT statements.

Worst-case SQL

Statements falling under this category include:

v Statements that are executed frequently

v Statements that are processing high volumes of data

v Statements that are time-critical. For example, statements in an

application that runs to retrieve and update customer information while

the customer is waiting on the telephone.

v Statements with a large number of joins, or the most complex statements

in the application. For example, statements in a banking application that

produces summaries of monthly activity for all of a customer’s accounts.

6 Troubleshooting and Tuning Database Performance

A common table might list the customer’s address and account numbers;

however, several other tables must be joined to process and integrate all

of the necessary account transaction information.

v Statements that have a poor access path, such as one that is not

supported by an available index

v Statements that have a long execution time

v Statements that are executed only at application initialization time, but

that have disproportionately large resource requirements. For example,

statements in an application that generates a list of account work that

must be processed during the day. When the application starts, the first

major SQL statement causes a seven-way join, which creates a very large

list of all the accounts for which this application user is responsible. This

statement might only run a few times each day, but it takes several

minutes to run if it has not been tuned properly.

Benchmark test creation

You will need to consider a variety of factors when designing and implementing a

benchmark testing program.

Because the main purpose of the testing program is to simulate a user application,

the overall structure of the program will vary. You might use the entire application

as the benchmark and simply introduce a means for timing the SQL statements

that are to be analyzed. For large or complex applications, it might be more

practical to include only blocks that contain the important statements. To test the

performance of specific SQL statements, you can include only those statements in

the benchmark testing program, along with the necessary CONNECT, PREPARE,

OPEN, and other statements, as well as a timing mechanism.

Another factor to consider is the type of benchmark to use. One option is to run a

set of SQL statements repeatedly over a certain time interval. The number of

statements executed over this time interval is a measure of the throughput for the

application. Another option is to simply determine the time required to execute

individual SQL statements.

For all benchmark testing, you need a reliable and appropriate way to measure

elapsed time. To simulate an application in which individual SQL statements

execute in isolation, measuring the time to PREPARE, EXECUTE, or OPEN,

FETCH, or CLOSE for each statement might be best. For other applications,

measuring the transaction time from the first SQL statement to the COMMIT

statement might be more appropriate.

Although the elapsed time for each query is an important factor in performance

analysis, it might not necessarily reveal bottlenecks. For example, information on

CPU usage, locking, and buffer pool I/O might show that the application is I/O

bound and not using the CPU at full capacity. A benchmark testing program

should enable you to obtain this kind of data for a more detailed analysis, if

needed.

Not all applications send the entire set of rows retrieved from a query to some

output device. For example, the result set might be input for another application.

Formatting data for screen output usually has a high CPU cost and might not

reflect user needs. To provide an accurate simulation, a benchmark testing program

should reflect the specific row handling activities of the application. If rows are

sent to an output device, inefficient formatting could consume the majority of CPU

time and misrepresent the actual performance of the SQL statement itself.

Chapter 1. Performance tuning tools and methodology 7

Although it is very easy to use, the DB2 command line processor (CLP) is not

suited to benchmarking because of the processing overhead that it adds. A

benchmark tool (db2batch) is provided in the bin subdirectory of your instance

sqllib directory. This tool can read SQL statements from either a flat file or from

standard input, dynamically prepare and execute the statements, and return a

result set. It also enables you to control the number of rows that are returned to

db2batch and the number of rows that are displayed. You can specify the level of

performance-related information that is returned, including elapsed time, processor

time, buffer pool usage, locking, and other statistics collected from the database

monitor. If you are timing a set of SQL statements, db2batch also summarizes the

performance results and provides both arithmetic and geometric means.

By wrapping db2batch invocations in a Perl or Korn shell script, you can easily

simulate a multiuser environment. Ensure that connection attributes, such as the

isolation level, are the same by selecting the appropriate db2batch options.

Note that in partitioned database environments, db2batch is suitable only for

measuring elapsed time; other information that is returned pertains only to activity

on the coordinator database partition.

You can write a driver program to help you with your benchmark testing. On

Linux

or UNIX

systems, a driver program can be written using shell programs.

A driver program can execute the benchmark program, pass the appropriate

parameters, drive the test through multiple iterations, restore the environment to a

consistent state, set up the next test with new parameter values, and collect and

consolidate the test results. Driver programs can be flexible enough to run an

entire set of benchmark tests, analyze the results, and provide a report of the best

parameter values for a given test.

Benchmark test execution

In the most common type of benchmark testing, you choose a configuration

parameter and run the test with different values for that parameter until the

maximum benefit is achieved.

A single test should include repeated execution of the application (for example,

five or ten iterations) with the same parameter value. This enables you to obtain a

more reliable average performance value against which to compare the results

from other parameter values.

The first run, called a warmup run, should be considered separately from

subsequent runs, which are called normal runs. The warmup run includes some

startup activities, such as initializing the buffer pool, and consequently, takes

somewhat longer to complete than normal runs. The information from a warmup

run is not statistically valid. When calculating averages for a specific set of

parameter values, use only the results from normal runs. It is often a good idea to

drop the high and low values before calculating averages.

For the greatest consistency between runs, ensure that the buffer pool returns to a

known state before each new run. Testing can cause the buffer pool to become

loaded with data, which can make subsequent runs faster because less disk I/O is

required. The buffer pool contents can be forced out by reading other irrelevant

data into the buffer pool, or by de-allocating the buffer pool when all database

connections are temporarily removed.

8 Troubleshooting and Tuning Database Performance

After you complete testing with a single set of parameter values, you can change

the value of one parameter. Between each iteration, perform the following tasks to

restore the benchmark environment to its original state:

v If the catalog statistics were updated for the test, ensure that the same values for

the statistics are used for every iteration.

v The test data must be consistent if it is being updated during testing. This can

be done by:

– Using the restore utility to restore the entire database. The backup copy of the

database contains its previous state, ready for the next test.

– Using the import or load utility to restore an exported copy of the data. This

method enables you to restore only the data that has been affected. The reorg

and runstats utilities should be run against the tables and indexes that contain

this data.

In summary, follow these steps to benchmark test a database application:

Step 1 Leave the DB2 registry, database and database manager configuration

parameters, and buffer pools at their standard recommended values, which

can include:

v Values that are known to be required for proper and error-free

application execution

v Values that provided performance improvements during prior tuning

v Values that were suggested by the AUTOCONFIGURE command

v Default values; however, these might not be appropriate:

– For parameters that are significant to the workload and to the

objectives of the test

– For log sizes, which should be determined during unit and system

testing of your application

– For any parameters that must be changed to enable your application

to run

Run

your set of iterations for this initial case and calculate the average

elapsed time, throughput, or processor time. The results should be as

consistent as possible, ideally differing by no more than a few percentage

points from run to run. Performance measurements that vary significantly

from run to run can make tuning very difficult.

Step 2 Select one and only one method or tuning parameter to be tested, and

change its value.

Step 3 Run another set of iterations and calculate the average elapsed time or

processor time.

Step 4 Depending on the results of the benchmark test, do one of the following:

v If performance improves, change the value of the same parameter and

return to Step 3. Keep changing this parameter until the maximum

benefit is shown.

v If performance degrades or remains unchanged, return the parameter to

its previous value, return to Step 2, and select a new parameter. Repeat

this procedure until all parameters have been tested.

Benchmark test analysis example

Output from a benchmark testing program should include an identifier for each

test, iteration numbers, statement numbers, and the elapsed times for each

execution.

Chapter 1. Performance tuning tools and methodology 9

Note that the data in these sample reports is shown for illustrative purposes only.

It does not represent actual measured results.

A summary of benchmark testing results might look like the following:

Analysis shows that the CONNECT (statement 01) took 1.34 seconds to complete,

the OPEN CURSOR (statement 10) took 2 minutes and 8.15 seconds, the FETCH

(statement 15) returned seven rows, with the longest delay being 0.28 seconds, the

CLOSE CURSOR (statement 20) took 0.84 seconds, and the CONNECT RESET

(statement 99) took 0.03 seconds to complete.

If your program can output data in a delimited ASCII format, the data could later

be imported into a database table or a spreadsheet for further statistical analysis.

A summary benchmark report might look like the following:

Test Iter. Stmt Timing SQL Statement

Numbr Numbr Numbr (hh:mm:ss.ss)

002 05 01 00:00:01.34 CONNECT TO SAMPLE

002 05 10 00:02:08.15 OPEN cursor_01

002 05 15 00:00:00.24 FETCH cursor_01

002 05 15 00:00:00.23 FETCH cursor_01

002 05 15 00:00:00.28 FETCH cursor_01

002 05 15 00:00:00.21 FETCH cursor_01

002 05 15 00:00:00.20 FETCH cursor_01

002 05 15 00:00:00.22 FETCH cursor_01

002 05 20 00:00:00.84 CLOSE cursor_01

002 05 99 00:00:00.03 CONNECT RESET

Figure 1. Sample Benchmark Testing Results

PARAMETER VALUES FOR EACH BENCHMARK TEST

TEST NUMBER 001 002 003 004 005

locklist 63 63 63 63 63

maxappls 8 8 8 8 8

applheapsz 48 48 48 48 48

dbheap 128 128 128 128 128

sortheap 256 256 256 256 256

maxlocks 22 22 22 22 22

stmtheap 1024 1024 1024 1024 1024

SQL STMT AVERAGE TIMINGS (seconds)

01 01.34 01.34 01.35 01.35 01.36

10 02.15 02.00 01.55 01.24 01.00

15 00.22 00.22 00.22 00.22 00.22

20 00.84 00.84 00.84 00.84 00.84

99 00.03 00.03 00.03 00.03 00.03

Figure 2. Sample Benchmark Timings Report

10 Troubleshooting and Tuning Database Performance

剩余572页未读，继续阅读

muxtou

粉丝: 0
资源: 10

DB2性能调优指南：Linux/Unix/Windows问题排查与优化技巧

DSI405:Performance Tune

nokia tune.zip

sizeof(tune) / sizeof(tune[0]

hdtunepro免安装

ffmpeg低延时tune

介绍fine-tune

不能启动A-Tune服务的命令

hd tune pro key

fio参数auto tune

yolov8使用ray tune

ffmpeg -tune zerolatency

tune.rfsrc函数

hdtunepro5

hdtunepro 575

s-tune软件下载

yolov5 fine-tune

fine-tune是什么

hd tune 专业版 v5.0

hdtunepro_v575

PLL 电容矩阵 finetune

最新资源