aimage scan verbose

Обсуждение вопросов по администрированию СУБД Progress OpenEdge
Аватара пользователя
dmikai
Старожил
Сообщения: 517
Зарегистрирован: 20 сен 2006, 23:19
Откуда: Рига, Латвия

aimage scan verbose

Сообщение dmikai » 21 ноя 2013, 00:59


Аватара пользователя
dmi
Старожил
Сообщения: 1523
Зарегистрирован: 27 сен 2001, 03:00
Откуда: Москва

Re: aimage scan verbose

Сообщение dmi » 21 ноя 2013, 01:32

After Image (AI) extents are switching rapidly when adding 15,000 new records to a table.

Environment
RL_CXREM represents a 'remove index entry' note.
Command: proutil dbname -C dbanalys > filename
All Supported Operating Systems
OpenEdge 10.1x

Question/Problem Description
After Image (AI) extents are switching rapidly when adding 15,000 new records to a table.
1,500 AI log files created (totaling 21GB) during rapid ai switch interval
A scan of the ai files using the command: rfutil dbname -C aimage scan verbose -a <ai file name> shows a large amount of code = RL_CXREM
Output from aimage scan command shows that 98% of the notes within the ai files are referencing index areas that are associated with the records that were deleted.
Trid: <number> area = <area> dbkey = <dbkey> update counter = <number> (12529)
Trid: 1831818283 area = 64 dbkey = 164718080 update counter = 1919 (12529)
Index Delete Chain Analysis portion of the dbanalys output is showing 0 blocks for most index objects.
Index Delete Chain Analysis portion of the dbanalys output is showing a large number of blocks for a few index objects (608, 616, 627 and 619)
Example Index Delete Chain Analysis: 53912 block(s) found in the Index Delete chain of Index object 627
Index objects that contain large amount of blocks on the Index Delete Chain are Unique indexes.
The unique indexes are associated with the 70 million records that were deleted recently

Clarifying Information
Recently deleted 70 million records across 3 different tables.

Cause
When records are deleted, index-trees are not rebuilt. The entire B-tree is left intact. During a transaction, when a record is deleted, if there is a unique index, a delete place holder is put in the place of the RECID for the deleted record. Index blocks that contain these deleted place holders appear on the index delete chain. Every time an index entry is about to be added, the index delete chain will be checked. If there are blocks on the chain, one block will be removed and processed for deleted entries. Over time as records are being added to the table, index blocks containing these deleted place holders will be processed and written out to disk.

The addition of 15,000 records to the table (that contained index delete blocks), resulted in the processing of a large subset of these index delete blocks to disk. The processing of these index delete blocks resulted in significant growth and increase in the number of AI (After Image) files being used for the period when the new records were being added to the table.

Resolution
If you are deleting/archiving a lot of records from a table (that contains a unique index), you now know that you will have delete place holders associated with each of these deleted records. These delete place holders reside in blocks on the index delete chain. The blocks on the index delete chain will need to be processed. The goal is to minimize the impact of processing these blocks.

You have two options:

1. You can either process a subset of these blocks over time as records are added to the table.

This option requires minimal involvement from you except to realize that bigger transactions will require more AI (After Image) and BI (Before Image) space due to processing these index delete place holders. We reuse space within the bi file by making use of clusters. The AI file does not however make use of clusters. This means that you may need to monitor and prepare your AI files for growth and excessive switching as a result of processing these index delete blocks.

2. You can use idxbuild, idxcompact, idxmove or truncate area to process the entire index delete chain for a unique index.

The following is a list of some considerations involved with some of these options:

A "proutil -C idxbuild" does not make use of the AI or BI file and therefore can be ran offline against the unique indexes to address this issue.

Idxcompact and idxmove does make use of the BI and AI files. If you execute these commands against a database with After Imaging enabled, you may need to monitor and prepare your AI files for growth and excessive switching as a result of processing these index delete blocks.

If you want to use "proutil -C idxcompact" online, the recommendation would be that you get to 10.1C04 and later or 10.2A01 or later, due to a known issue that resulted in the index delete chain being processed in a large transaction scope. This resulted in excessive bi file growth due to our not being able to reuse bi clusters when running this utility.
/dmi

http://pro4gl.ru - 4gl блог

Аватара пользователя
George
Старожил
Сообщения: 2871
Зарегистрирован: 12 май 2004, 17:03
Откуда: Питер

Re: aimage scan verbose

Сообщение George » 21 ноя 2013, 02:09

Приведенный пример - это случайно выбранный фрагмент скана AI файла или же результат какой-то выборки (например grep "dbkey = 1163")?

RL_CXREM - это удаление индексных ключей. В то, что все ключи удалялись в рамках одной транзакции, - в это легко поверить. То, что видим только заметки RL_CXREM, - это говорит, что записи удалялись, а не модифицировались. Но то, что 18 ключей лежали в одном блоке, означало бы, что либо у таблицы, записи которой удалялись этой транзакцией, есть только один индекс или только один индекс этой таблицы лежит в области 14. При этом записи перед удалением читаются именно по этому индексу. Индекс не уникальный - иначе были бы заметки RL_CXINS, описывающие вставки индексных place holder'ов, о которых упоминается в статье, процитированной Дмитрием. Либо речь идет не об удалении записей, а об удалении этих самых place holder'ов в уникальном индексе, но тогда это может быть делом рук только idxcompact. Нормальные процессы удаляют старые place holder'ы только когда меняют индексных блок ради своих задач.

Склоняюсь к мысли, что в качестве примера приведен крайне нехарактерный фрагмент скана.

Аватара пользователя
dmikai
Старожил
Сообщения: 517
Зарегистрирован: 20 сен 2006, 23:19
Откуда: Рига, Латвия

Re: aimage scan verbose

Сообщение dmikai » 02 дек 2013, 13:12

Пойдём другим путём - как распознать, кто и что вносит больший вклад в AI?

Аватара пользователя
George
Старожил
Сообщения: 2871
Зарегистрирован: 12 май 2004, 17:03
Откуда: Питер

Re: aimage scan verbose

Сообщение George » 02 дек 2013, 14:22