DB2 performance optimization-reorg slow analysis

What is REORG?

We know that there are many tables in the database, and we may often need to do operations such as pruning table data, and after a series of changes, logically contiguous data may be on a discontinuous physical data page, especially when many insert operations create overflow records. When you organize data in this way, the database manager must perform additional read operations to access the sequential data. When you delete a large number of rows, you also need to perform additional read operations.

REORG table Operations defragment data fragments to reduce wasted space and reorder rows to merge overflow records to speed data access and ultimately improve query performance.

when do I need to do REORG?

when the amount of records in a table in a database varies greatly, you need to do it on the table REORG operation to optimize database performance.

for database Objects a large number of operations, such as deleting a table repeatedly, Stored Procedures will cause frequent changes in the data in the system tables, in which case the system tables should also be considered REORG operation.

Full- REORG the process of the table

a complete REORG The procedure for a table should consist of the following steps:

BIND, RUNSTATS, REORG, REORGCHK, RUNSTATS , or REBIND

Note: You need to connect to the database before executing this part of the command

We're not right here . REORG do too much introduction, the main share of the cause of REORG slow.

Scene:

a telecommunication network invoice system starts to do database maintenance every Friday night at 9, includingREORG,runstat,REBIND, and maintenance time takes about ten hours. , the period will affect the normal operation of the system. A detailed maintenance log is generated by the optimized script, which checks the REORG of the maintenance portion of the table for a long time. The entire database size is 500GB.

Operating system version: AIX 7.1

Database Version : DB2 V9.7

Problem:

currently using serial REORG script, maintenance time takes about ten hours;

instead, use parallel REORG scripts, maintenance time still takes about 9 hours;

revert to the use of serial REORG script, and observe the system resource consumption, at the beginning of the REORG speed, but after a short time, reached the REORG slower table using Topas to view system resources, Discover that resource consumption drops to a lower value, such as IO throughput:

The ideal throughput

Current REORG Slow Throughput

Analysis:

1. First look at DB2 diagnostic log (Db2diag.log) and DB2 Management notification log (db2inst1.nfy), no error was found;

2. List The reasons for the low amount of data that may have caused REORG

1) Table space parameter limit (PREFETCH) Read and write speed

2) operating system parameters bottleneck

3) Storage performance bottlenecks

4)DB2 Bug

5) Data corruption

Next Planning direction

-Operating system

-Storage

-db2

-Network

-Memory

Since we are in charge of the database, start with the database,

Solve:

1. from DB2 Start, monitor DB2 the monitoring Progress

Db2pd-d sample-reorg

observed Curcount field growth, slow growth means REORG is slow

2. Locate the statement that is executing, REORG Statement DB2 reorg table Schema.tablename longlobdata

DB2 9.7 Table Reorganization function is also expanded accordingly,REORG command more longlobdata parameters. the Longlobdata parameter is valid only for long and LOB columns. By default, Longlobdata is not enabled because the reassembly of long and LOB columns is time consuming.

Please see the following links for official instructions:

Https://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1008kongzh/index.html

We know DB2 table Normal data and Long/lob data are stored separately, the normal data only holds Long/lob data pointers and lengths, and in the processing of LOB data is recorded by one-by-one, so when REORG, it becomes very slow to parse by record. ( This parameter is used if you need to convert a separately stored lob- to-inline lob , and when the table is particularly large,the reassembly of the LOB takes longer)

3. Remove Longlobdata, again REORG

The REORG time is reduced from 6 hours to ten minutes, and the system throughput is restored to an ideal state.

Summarize:

Here is the initial solution to the problem of the database-

1. Check the DB2 diagnostic log (Db2diag.log) and DB2 's Management notification log (db2inst1.nfy) to see if there are any errors, If there is an error message, according to the corresponding error information to understand the current problem direction can be analyzed.

2. If the database has an error, according to the error information on the cause of the problem can be made assumptions, if the database does not have an error, then combined with system indicators to analyze possible causes.

3. Take a step-by-step deduction of the assumptions made, demonstrating all possibilities and identifying the underlying causes.

DB2 performance optimization-reorg slow analysis