Hi,
For some time, we observe bad performance on web search : sometimes, it takes multiple minutes to get some results.
With latest update, it becomes worst : search is not working anymore, cpu is constantly à 100%.
I spent some time on this problem this weekend and I found some interesting behaviors. Every index was regenerated from scratch before testing :
systemctl stop grommunio-index grommunio-index.timer
rm -rf /var/lib/grommunio-web/sqlite-index/*
systemctl start grommunio-index grommunio-index.timer
If I run a search on a whole mailbox without folder constraint, search is almost instantly (it takes some ms in command line). If I run the same search on a specific folder, search takes sometimes more than 5 minutes (with cpu at 100%)! It seems that there is a problem with index relating to folder_id...
After some trials, I discovered that calling 'ANALYZE' on index sqlite database resolved the problem : search is also very fast with folder constraint (some ms)!
This command is well documented here : https://sqlite.org/lang_analyze.html
We can also read this recommendation from sqlite : "The PRAGMA optimize command will automatically run ANALYZE on individual tables on an as-needed basis. The recommended practice is for applications to invoke the PRAGMA optimize statement just before closing each database connection."
Is this a known problem on sqlite database? Are you running analyze/optimize on your databases at regular interval? Should we also run analyze/optimize on exchange.sqlite3 databases?
Cheers,
Nicolas
