Slow re-indexing due to SQL queries

  • 7023643
  • 16-Jan-2019
  • 08-Feb-2019

Environment

Filr
MySQL

Situation

In a large expandable Filr setup, the indexing and re-indexing on both Search Index (Lucene) nodes appear to fail or at least take abnormally long.
MySQL queries in various tables takes a (very) long time.

Resolution

The fix is two part, currently.
One part is to reconfigure the MySQL server, by making sure that in the /etc/my.cnf these values are set:
transaction-isolation=READ-COMMITTED
skip-external-locking
key_buffer_size=32M
sort_buffer_size=32M
join_buffer_size=32M
read_buffer_size=32M
read_rnd_buffer_size=32M
query_cache_size=32M
query_cache_type=1
max_allowed_packet=16M

In case MySQL is not running on the Micro Focus MySQL Appliance, these changes must be performed manually on the MySQL server. The MySQL appliance should already have these values set, but it does not harm to double check.

In addition, there is a java jar file that addresses a memory leak in one of the indexing and re-indexing processes.
This is currently available as part of Filr 3.4.3 (and later).

Be aware that from filr 3.4.3 and onwards the Filr update channel is signed with a new build key.
This is addressed with the following Filr 3.0 FTF.

Cause

The cause is two part, one part is the tuning, configuration of the MySQL server, for sure if the MySQL appliance is not used.
In addition to the mentioned tunings, make sure that the already documented required changes are performed as well to the MySQL server.

The second part was a memory leak in one of the java processes when indexing vast amounts of data.

Status

Reported to Engineering

Additional Information

When Contents indexing is required, it could be of uses to exclude several file types that make no sense to be content indexed.
This can be accomplished by:
  1. Access the Filr server prompt, either via the hypervisor or over ssh
  2. vi /opt/novell/filr/apache-tomcat/webapps/ssf/WEB-INF/classes/config/ssf-ext.properties and go to the bottom of the file (G)
  3. add the following line: exclude.from.stellent.indexing.extensions=snm,chm,ini,cdr,class,tmp,ttf,tif,mp4,swf,arw,dat,tar,cr2,own,myd,db,DB,mpg,m4a,lock,msf,stderr,stdout,lnk,xms,xcd,xsd,ps,cda,ovf,msi,hkl,mp4,ht
  4. Close and save the file (:wq)
  5. python /opt/novell/base_config/zipVAConfig.py