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

What is REORG?

We know that there are many tables in the database, and we may often need to add, delete and modify the table data, and after a series of changes, logically continuous data may be on discontiguous physical data pages. this is especially true when many inserts create overflow records. When data is organized in this way, the database manager must perform other reads to access sequential data. After deleting a large number of rows, you also need to perform other read operations.

REORG (reorganization) table operations defragment data to reduce wasted space and reorder rows to merge overflow records, thus speeding up data access and ultimately improving query performance.

When do you need to do REORG?

When the records in a table in the database change greatly, you need to do REORG operations on the table to optimize the performance of the database.

A large number of operations on database objects, such as repeatedly deleting tables and stored procedures, will cause frequent changes in the data in the system tables. In this case, we should also consider REORG operations on the system tables.

The process of a complete REORG table

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

RUNSTATS-> REORGCHK-> REORG-> RUNSTATS-> BIND or REBIND

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

We won't introduce REORG too much here, and this time we will mainly share the reasons for the slow REORG.

Scenario:

A telecom network invoice system starts to do database maintenance at 9: 00 p.m. every Friday, including REORG,RUNSTAT,REBIND. The maintenance time is about 10 hours, which will affect the normal operation of the system. After optimizing the script to generate detailed maintenance logs, check the maintenance of some tables of the REORG time is too long. The entire database size is 500GB.

Operating system version: AIX 7.1

Database version: DB2 V9.7

Question:

Scripts that currently use serial REORG take about 10 hours to maintain

Switching to parallel REORG scripts, the maintenance time is still about 9 hours

Revert to the serial REORG script and observe the system resource consumption. At first, the REORG speed is very fast, but soon after, the table with slow REORG is reached. At this time, use topas to view the system resources and find that the resource consumption has dropped to a low value, such as IO throughput:

Ideal throughput

Current slow throughput of REORG

Analysis:

1. First of all, check the diagnostic log (db2diag.log) of DB2 and the management notification log (db2inst1.nfy) of DB2, and no error is found.

two。 List the possible reasons for the low amount of data in REORG

1) tablespace parameter limit (PREFETCH) read and write speed

2) operating system parameter bottleneck

3) Storage performance bottleneck

4) DB2 Bug

5) data corruption

The direction of the next step

-operating system

-Stora

-DB2

-Network

-memory

Since we are in charge of the database, let's start with the database.

Resolve:

1. Starting with DB2, monitor the monitoring progress of DB2

Db2pd-d sample-reorg

Observe the growth of CurCount field. If the growth is slow, the speed of REORG is very slow.

two。 Find the statement being executed, the REORG statement db2 reorg table schema.tablename longlobdata

The function of table reorganization in DB2 9.7 is also expanded accordingly, and the LONGLOBDATA parameter is added to the REORG command. The LONGLOBDATA parameter is valid only for long and LOB columns. LONGLOBDATA is not enabled by default because the reorganization of long and LOB columns is time-consuming.

For official instructions, please see the following link:

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

We know that DB2 table ordinary data and LONG/LOB data are stored separately, ordinary data only store the data pointer and length of LONG/LOB, and in the processing of LOB data are parsed record by record, so it will be very slow to parse record by record when REORG. (use this parameter if you need to convert separate LOB to inline LOB; when the table is very large, the reorganization of LOB will take a long time)

3. Remove longlobdata and REORG again

The REORG time decreased from 6 hours to 10 minutes, and the system throughput returned to the ideal state.

Summary:

Here is a list of preliminary solutions to problems in the database--

1. Check the diagnostic log (db2diag.log) of DB2 and the management notification log (db2inst1.nfy) of DB2 to see if there is an error. If there is an error message, you can know the direction of the problem that can be analyzed according to the corresponding error information.

two。 If there is an error in the database, make assumptions about the cause of the problem according to the error information; if the database does not report an error, analyze the possible causes combined with the indicators of the system.

3. Make step-by-step inferences about the assumptions made, demonstrate all the possibilities, and find out the root causes.

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.