Use this query in your Advanced Reports Custom Queries to check the status of your fts_queue table. The contents of this table shows what yet needs to be indexed.
SELECT * FROM ( SELECT bean_module AS Module, COUNT(*) AS Count, MIN(date_created) AS First_Created, MAX(date_created) AS Last_Created, MIN(date_modified) AS First_Modified, MAX(date_modified) AS Last_Modified FROM fts_queue GROUP BY bean_module UNION SELECT '* ALL MODULES *' AS Module, COUNT(*) AS Count, MIN(date_created) AS First_Created, MAX(date_created) AS Last_Created, MIN(date_modified) AS First_Modified, MAX(date_modified) AS Last_Modified FROM fts_queue ) REPORTOUTPUT ORDER BY Module;
This will produce the following fields/columns:
- Module: this is the module being indexed.
- Count: this is number of records in the Module that are still left to be processed.
- First Created: the creation date of the oldest record for that module.
- Last Created: the creation date of the newest record for that module.
- First Modified: the change date of the oldest record for that module.
- Last Modified: the change date of the newest record for that module.
The first record is a summary of ALL of the modules. The Count value in that record is equal to the sum of the Count values of the specific module records. Manually re-run the query to get more current values.
Background
When a record in a module that is marked for Global Search is changed or when the instance Global Search is re-indexed, the fts_queue table receives a record for each record in the instance that needs to be re-indexed. As the records are indexed, the record in the fts_queue table are deleted.
About The Table Contents
In general, the table dates should all be within a few minutes of current time. Anything more than a day old is usually cause for concern. High numbers in any module Count field will mean the search results in that module may be incomplete or unreliable.
Here are some specific things to look for:
- If any date is more than a few hours old, something is not indexing properly. Contact SugarCRM Technical Support with your observation for more help.
- If the ALL Count does not decrease by a few thousands every few minutes, there is probably a problem with the re-indexing process. Make sure the “Elasticsearch Queue Scheduler” is enabled and is set to an Interval of “As often as possible”. In general, the ALL Count should decrease an average of 250K-500K records per hour.
- If the All Count or any of the individual module Count values seem to decrease by small numbers over a 15 minute period, this may this may be an indication of the an adjustment that needs to be made to your config_override.php file. Contact SugarCRM Technical Support with your observation details for more help.
If you see any of these problems, check the “Elasticsearch Queue Scheduler” to see if the Job Log is updating every few minutes. If it is not, either the instance’s CRON (Scheduler runner) is not enabled or there is another scheduler that is taking too much time and interfering with Scheduler progress. Either way, contact SugarCRM Technical Support with your observation details for more help.