IBM DB2性能优化与监控最佳实践

需积分: 12 110 浏览量更新于2024-07-23 收藏 1.07MB PDF 举报

"IBM DB2 for Linux, UNIX, and Windows最佳实践：数据库系统性能调整与监控" 本文档详细阐述了在IBM DB2数据库管理系统上进行性能优化和监控的最佳实践，适用于Linux、UNIX和Windows环境。作者团队由多位资深技术专家组成，他们在DB2性能、SAP集成以及信息管理方面有深厚的专业知识。首先，文章强调了性能调优的第一步是正确配置以确保良好的性能。硬件配置是基础，包括选择合适的服务器硬件、内存大小、磁盘I/O速度等，这些都是决定数据库性能的关键因素。针对不同的操作系统，如AIX、Solaris、HP-UX和Linux，文章提供了特定的配置建议，以充分利用平台特性。在多分区数据库环境中，如何配置以优化性能也是一项挑战。文章提到了分区数据库设计的重要性，以提高并发处理能力和数据分布的效率。同时，选择正确的代码页和排序规则（collation）也是确保数据一致性和查询效率的关键。物理数据库设计是另一个关键点，包括表空间、索引、分区策略等。初始DB2配置设置应根据应用需求进行定制，以确保性能最大化。DB2的自我管理功能（autonomics）和自动参数设置在简化管理的同时，也需要合理调整以适应特定工作负载。统计收集是性能调优中的重要环节，定期更新统计信息有助于DB2做出更准确的查询计划。对于SAP和其他ISV（独立软件供应商）环境，文章还给出了特别的考虑点，因为这些环境通常有特定的性能需求和工作负载模式。接下来，文章转向系统性能的监控。监控是发现性能瓶颈、预测问题并提前采取行动的关键。Delta值分析、性能指标跟踪、警报设置等都是监控工具的重要组成部分，它们帮助管理员识别性能下降的趋势，并实时作出响应。这份文档提供了全面的DB2性能调优和监控策略，旨在帮助数据库管理员提升系统的稳定性和效率，从而实现业务需求。通过遵循这些最佳实践，可以有效地避免性能问题，确保数据库系统的高效运行。

Tuning and monitoring database system performance Page 13 of 80

especially when the temporary tables are very small; however, the performance

advantage of SMS in this case is shrinking over time.

 In the past, DMS raw device table spaces had a fairly noticeable performance

advantage over AS and DMS file table spaces. However, with the introduction of

direct I/O (now defaulted through the NO FILE SYSTEM CACHING clause in

the CREATE TABLESPACE and the ALTER TABLESPACE statements), AS and

DMS file table spaces provide virtually the same performance as DMS raw

device table spaces.

 If the size of a row happens to be just over half the page size of the table space it

uses, this will leave almost half the page empty. While this can be intentional

sometimes (avoiding page-level contention for a particularly hot table), generally

that much waste is not desirable, and it would more efficient to store the table in

a larger page size table space.

Initial DB2 configuration settings

The DB2 configuration advisor

(http://pic.dhe.ibm.com/infocenter/db2luw/v1

0r5/index.jsp?topic=%2Fcom.ibm.db2.luw.

admin.cmd.doc%2Fdoc%2Fr0008960.html ), also known as the AUTOCONFIGURE

command, takes basic system guidelines that you provide, and determines a good

starting set of DB2 configuration values. The AUTOCONFIGURE command can provide

real improvements over the default configuration settings, and is recommended as a way

to obtain initial configuration values. Some additional fine-tuning of the

recommendations generated by the AUTOCONFIGURE command is often required,

based on the characteristics of the system.

Here are some suggestions for using the AUTOCONFIGURE command:

 Even though the AUTOCONFIGURE command is run automatically at database

creation time since DB2 v9.1, it is still a good idea to run the AUTOCONFIGURE

command explicitly. This is because you then have the ability to specify

keyword/value pairs that help customize the results for your system.

 Run (or re-run) the AUTOCONFIGURE command after the database is

populated. This provides the tool with more information about the nature of the

database. Ideally, ‘populated’ means with the amount of active data that you use

(which affects buffer pool size calculations, for example). Significantly too much

or too little data makes these calculations less accurate.

 Try different values for important AUTOCONFIGURE command keywords,

such as mem_percent, tpm, and num_stmts to get an idea of which, and to what

degree, configuration values are affected by these changes.

 If you are experimenting with different keywords and values, use the apply

none option. This gives you a chance to compare the recommendations with the

current settings.

Tuning and monitoring database system performance Page 14 of 80

 Specify values for all keywords, because the defaults might not suit your system.

For example, mem_percent defaults to 25%, which is too low for a dedicated DB2

server; 85% is the recommended value in this case.

DB2 autonomics and automatic parameters

Recent releases of DB2 database products have significantly increased the number of

parameters that are either automatically set at instance or database start-up time, or that

are dynamically tuned during operation. For most systems, automatic settings provide

better performance than all but the most carefully hand-tuned systems. This is

particularly due to the DB2 self-tuning memory manager (STMM), which dynamically

tunes total database memory allocation as well as four of the main shared memory

consumers in a DB2 system: the buffer pools, the lock list, the package cache, and the sort

heap.

Because these parameters apply on a partition-by-partition basis, using the STMM in a

partitioned database environment should be done with some caution. On partitioned

database systems, the STMM continuously measures memory requirements on a single

partition (automatically chosen by the DB2 system, but that choice can be overridden),

and ‘pushes out’ heap size updates to all partitions on which the STMM is enabled.

Because the same values are used on all partitions, the STMM works best in partitioned

database environments where the amounts of data, the memory requirements, and the

general levels of activity are very uniform across partitions. If a small number of

partitions have skewed data volumes or different memory requirements, the STMM

should be disabled on those partitions, and allowed to tune the more uniform ones. For

example, the STMM should generally be disabled on the catalog partition.

For partitioned database environments with skewed data distribution, where continuous

cross-cluster memory tuning is not advised, the STMM can be used selectively and

temporarily during a ‘tuning phase’ to help determine good manual heap settings:

 Enable the STMM on one ‘typical’ partition. Other partitions continue to have the

STMM disabled.

 After memory settings have stabilized, disable the STMM and manually ‘harden’

the affected parameters at their tuned values.

 Deploy the tuned values on other database partitions with similar data volumes

and memory requirements (for example, partitions in the same partition group).

 Repeat the process if there are multiple disjointed sets of database partitions

containing similar volumes and types of data and performing similar roles in the

system.

The configuration advisor generally chooses to enable autonomic settings where

applicable. This includes automatic statistics updates from the RUNSTATS command

(very useful), but excludes automatic reorganization and automatic backup. These can be

very useful as well, but need to be configured according to your environment and

Tuning and monitoring database system performance Page 15 of 80

schedule for best results. Automatic statistics profiling should remain disabled by

default. It has quite high overhead and is intended to be used temporarily under

controlled conditions and with complex statements.

Explicit configuration settings

Some parameters do not have automatic settings, and are not set by the configuration

advisor. These need to be dealt with explicitly. We only consider parameters that have

performance implications.

 logpath or newlogpath determine the location of the transaction log. Even the

configuration advisor cannot decide for you where the logs should go. As

mentioned above, the most important point, from a performance perspective, is

that they should not share disk devices with other DB2 objects, such as table

spaces, or be allowed to remain in the default location, which is under the

database path. Where possible, transaction logs should ideally be placed on

dedicated storage with sufficient throughput capacity to ensure that a bottleneck

won’t be created.

 logb

ufsz determines the size of the transaction logger internal buffer, in 4KB

pages. The default value of 256 pages is too small for good performance in many

production environments. The configuration advisor always increases it, but

possibly not enough, depending on the input parameters. A value of around

1024 pages is a good general range, and represents only a very small total

amount of memory in the overall scheme of a database server.

 buffpage determines the number of pages allocated to each buffer pool that is

defined with a size of -1. The best practice is to ignore buffpage, and either

explicitly set the size of buffer pools that have an entry in

SYSCAT.BUFFERPOOLS, or let the STMM tune buffer pool sizes automatically.

 logfilsiz determines the size of each transaction log file on disk. The log files

should be sized so that log switches do not occur more frequently than every few

minutes at their fastest. The rate of change of active log numbers can be obtained

by querying MON_GET_TRANSACTION_LOG. If a larger logfilsiz causes an

unacceptably long time between log switches during quieter times, you may

choose to schedule some manual switches to trigger archiving.

 diagpath determines the location of various useful DB2 diagnostic files. It

generally has little impact on performance, except possibly in partitioned or

clustered database environments. The default location of diagpath on all

partitions is typically on a shared, NFS or GPFS-mounted path. The best practice

is to override diagpath to a local, non-shared directory for each partition. This

prevents all partitions from trying to update the same file with diagnostic

messages. Instead, these are kept local to each partition, and contention is greatly

reduced.

Tuning and monitoring database system performance Page 16 of 80

 DB2_PARALLEL_IO is not a configuration parameter, but a DB2 registry

variable. It is typical for DB2 systems to use storage consisting of arrays of disks

(which are presented to the operating system by the storage controller as a single

device) or to use file systems that span multiple devices. The consequence is that

by default, a non-pureScale DB2 database system makes only one prefetch

request at a time to a table space container. This is done with the understanding

that multiple requests to a single device are serialized anyway. But if a container

resides on an array of disks, there is an opportunity to dispatch multiple prefetch

requests to it simultaneously, without serialization. This is where

DB2_PARALLEL_IO comes in. It tells the DB2 system that prefetch requests can

be issued to a single container in parallel. The simplest setting is

DB2_PARALLEL_IO=* (meaning that all containers reside on multiple –

assumed in this case to be six – disks), but other settings also control the degree

of parallelism and which table spaces are affected. For example, if you know that

your containers reside on a RAID-5 array of four disks, you might set

DB2_PARALLEL_IO to “*:3”. Whether or not particular values benefit

performance also depends on the extent size, the RAID segment size, and how

many containers use the same set of disks. Note that on DB2 pureScale systems,

DB2_PARALLEL_IO defaults to ‘*’, whereas on non-pureScale configurations it

defaults to off. See “Database Storage” (https://ibm.biz/Bdx2My

) for more

information on storage configuration and DB2_PARALLEL_IO.

Statistics collection

It’s no exaggeration to say that having the right statistics is often critical to achieving the

best SQL performance, especially in complex query environments. For a complete

discussion of this topic, see “Writing and Tuning Queries for Optimal Performance”

(https://ibm.biz/Bdx2ng

Considerations for SAP and other ISV environments

If you are running a DB2 database server for an ISV application such as SAP, some best

practice guidelines that take into account the specific application might be available. The

most straightforward mechanism is the DB2 registry variable DB2_WORKLOAD, which

has to be set to the value SAP in SAP environments. This will enable aggregated registry

variables that are optimized for SAP workloads.

In SAP envi

ronments, the database configuration has to follow SAP standards, for

example in regards of database code page, table space page and extent size as well as

naming conventions. During the installation of a SAP NetWeaver system an initial set of

DB2 configuration parameters is applied. In addition, SAP Notes describe the preferred

DB2 parameter settings for each supported DB2 version. For example, recommended

minimum parameters settings for SAP NetWeaver systems based on DB2 10.1 are

described in, SAP Note 1692571 (“DB6: DB2 10.1 Standard Parameter Settings”). In

addition to configuration recommendations, you will also find best practices regarding

the administration of DB2 databases in various SAP notes.

剩余79页未读，继续阅读

robertoroberto

粉丝: 0
资源: 6

IBM DB2性能优化与监控最佳实践

DB2 Performance export

DB2安装与数据迁移

ddlmig:通过HPU进行Db2 DDL迁移和数据迁移

DB2 Performance db2调优

DB2 performance tune

DB2 Performance In Development.pdf

Administration Guide: Performance DB2

SAP Performance Tuning for db2

DB2 9 for LUW Advanced Performance and Tuning

IBM Optim PerformanceManager for DB2 for LUW

最新资源