AnsweredAssumed Answered

FULL Index rebuild performance

Question asked by oznevo on Nov 19, 2008
Hi,

For all the reader's benefit - here is how to improve the FULL index rebuild.

F.Y.I:
We are using Alfresco version 2.1.6 (via weblogic 9.2) and Oracle 10.2.0.4.0 - 64bi.
We currently have a repository with ~3m documents. The Content Store is on NAS and index is on SAN. We use HP-UX 11i machines.
We recently upgraded to 2.1 sp6 (we had 2.1.0) in order to solve index issues we had and mainly the FULL index rebuild time that took more than 10 days.

The upgrade improved that, and according to our checks it needs half of the time to do the same (FULL index rebuild).

During the rebuild we checked the DB performance and found out that Alfresco is missing a very important index:
CREATE INDEX BC_ALFRESCO_WORK.IDX_COMMIT_TIME_2IX ON BC_ALFRESCO_WORK.ALF_TRANSACTION
(COMMIT_TIME_MS, id, server_id) LOGGING TABLESPACE BC_ALFRESCO_DATA NOPARALLEL ONLINE;

It creates a new index on commit_time_ms, id, server_id

On top of the improvement of 2.1.6, this change to the DB index on ALF_TRANSACTION table improved the FULL index rebuild by 2750% (~28 times faster)

The following issue was created: https://issues.alfresco.com/jira/browse/ETWOTWO-797

Oz

Outcomes