Can someone please tell me the purpose of the tracker table in the database?
What is this table keeping a track of?
Is it safe to empty it out every so often?
It reaches 2mil records or more every so often.
Does it need to be this big?
The scheduled task of trim table usually at best cuts it in half at best.
it’s just a per-record audit of user activity in the CRM.
2 million is a lot, which kinds of records do you see there? Can you understand what actual user activity it corresponds to?
There’s probably a way to disable it for specific modules, although I never looked into this, and I don’t have much time right now to figure it out.
Mostly alerts. I would say that is the bulk of it.
Scheduled task was also a very large portion. I removed them.
Will emptying the tab have any side effects for the users?
I don’t expect any problems for removing those records from those modules. I’m not 100% sure if any other mechanism in the CRM expects that information to be there; I think it’s just for auditing and reporting, but I might be wrong regarding some particular module.
I found manually truncating the Tracker table resolved a lot of performance problems for me and had no effect on the end users.
FYI, I was on 7.10.x at the time and at some stage the Prune Tracker job in Scheduler stopped working, the Tracker Table became huge > 2GB, normally for our site it’s under 120MB. I performed a truncation of Tracker, rebuilt the Scheduler tasks, and the Tracker Prune job has basically kept the Tracker table under control since.
I’ve never looked into whay the Prune job sometimes fails to prune the table in that circumstance, and I do not get any log messages to help. I still find occasionally after some unplanned event like a crash/power fail, that the tracker table might get stuck with a bunch of redundant records and need manual truncation to get it back to working as expected. So there is obviously something there that sporadically causes the Tracker to hold onto old records.
This is what the pruning job does:
→ https://github.com/salesagility/SuiteCRM/blob/master/modules/Schedulers/_AddJobsHere.php#L373
I don’t think there’s any reason for it to get stuck and then unstuck as you describe, unless it is some sort of database table corruption…
Yes I believe you are correct, I don’t think it’s a problem with SuiteCRM but perhaps MySQL.
Initially I modified the job code to reduce the Trim interval, but that had no effect and revealed we had many older/ancient records accumulating in the table. So I truncated the table using from MySQL Workbench.
I have a sneaking suspicion the crash and Tracker record accumulation may be related to timestamps, I’ve recently put into place a delay to ensure the server time is synced before the site comes up, as there is a bug present that can cause a Windows server VM to initially launch with incorrect time, but it’s soon corrected after launch. If a Tracker records are created either side of that event with an ancient or future timestamp I wonder if that is the roadblock to completing a successful Trim.
It’s not beyond possible that there is code somewhere that assumes table records are created with sequential timestamps, which might not be the case if the server launches with the wrong time and the site comes up before the time is corrected.