I came upon an interesting issue last week. I had a 126.96.36.199.0 content server configured with a ADF UI domain with ~850,000 documents. Users were reporting that any new documents that were being checked-in using the interface or Desktop Integration Suite (DIS) - Windows Explorer Integration (WEI) were not immediately available. In fact they were taking far too long, at times up to 15 minutes, to become available.
Monitoring the indexer, it became clear that the indexer thread, which is expected to start at a checkin/update or every 5 minutes by default, was not firing. I was able to manually start the Automatic Update Cycle though and it would pick up the documents in queue and index them immediately. It became pretty evident that something was holding back the indexer thread. There was also a general slowness with search query performance.
The content server systemdatabase traces were reporting massive amounts of database transactions to the CACHESTORE table, especially for Auto-Suggest index update.documents.doriginalname.
>systemdatabase/7 05.25 12:21:45.396 Auto-Suggest index update.documents.doriginalname (start) SELECT dCacheValue FROM CacheStore WHERE dRegionName='autosuggestindexprimary' AND dCacheKey='autosuggestindexprimary.documents.doriginalname:OccurrenceStorage.2E85C9E57D12A7E9182954E57BC0707C'
>systemdatabase/6 05.25 12:21:45.398 Auto-Suggest index update.documents.doriginalname 2.51 ms. SELECT dCacheValue FROM CacheStore WHERE dRegionName='autosuggestindexprimary' AND dCacheKey='autosuggestindexprimary.documents.doriginalname:OccurrenceStorage.2E85C9E57D12A7E9182954E57BC0707C'[Executed. Returned row(s): true]
>systemdatabase/7 05.25 12:21:45.399 Auto-Suggest index update.documents.doriginalname (start) Executing PreparedStatement (UPDATE CacheStore SET dCacheValue=?, dCreateOrUpdateTime=?, dEntryStatus=?, dAutoExpiryTime=? WHERE dRegionName=? AND dCacheKey=?)
>systemdatabase/6 05.25 12:21:45.403 Auto-Suggest index update.documents.doriginalname 3.72 ms. Executing PreparedStatement (UPDATE CacheStore SET dCacheValue=?, dCreateOrUpdateTime=?, dEntryStatus=?, dAutoExpiryTime=? WHERE dRegionName=? AND dCacheKey=?)[Executed. 1 row(s) affected.]
The content server system logs were also reporting the following message frequently:
!csSubjectMonitorStop!csUnableToLoadSubject,idccacheevent-ucm-persistent.autosuggestindexprimary,intradoc.server.cache.IdcCacheEventSubjectCallback!csIdcCacheNotificationError!syJavaExceptionWrapper,java.io.IOException: ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
!syJavaExceptionWrapper,java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
The database was reporting huge amounts (in GB) of REDO logs being generated within hours. The following SQL statements were being executed millions of times:
UPDATE CacheStore SET dCacheValue=:1 , dCreateOrUpdateTime=:2 , dEntryStatus=:3 , dAutoExpiryTime=:4 WHERE dRegionName=:5 AND dCacheKey=:6
SELECT dCacheValue FROM CacheStore WHERE dRegionName=:"SYS_B_0" AND dCacheKey=:"SYS_B_1"
The AutoSuggest feature is required for the ADF UI domain to work and so the AutoSuggestConfig component on the content server cannot be disabled. However, you can disable the auto suggest activity by adding the following configuration entry into your config.cfg file and restarting the content server.
Sure enough, after making the changes, the system became much more responsive and the indexer thread was firing off as expected. This, however, did disable all auto suggest type-ahead fields, like the User ACL fields in the ADF UI. There is another configuration variable that can be used to disable auto suggest indexing of certain fields. As the issue is with the update.documents.doriginalname field, adding the following configuration entry into your config.cfg file and restarting the content server will also help without disabling auto suggest.