I have a 7.8.31 install on IIS 8.5 / MS Server 2012 R2, MySQL 5.7.18 and PHP 7.1.1. I have this unusual problem unique to this one site, I get a slow query to this SQL statement, the query always shows in the log as about 8.5 seconds.
Mon May 25 11:40:34 2020 [4476][b345f608-7a73-6c6f-2876-44aa711c7a2b][FATAL] Slow Query (time:8.5696849822998
SELECT item_id, item_summary, module_name, id FROM tracker WHERE id = (SELECT MAX(id) as id FROM tracker WHERE user_id = ‘b345f608-7a73-6c6f-2876-44aa711c7a2b’ AND deleted = 0 AND visible = 1) LIMIT 0,50
I’ve noticed tracker is quite large, 1.8GB with a 1.1GB Index, if I clone the site and do repairs and maintenance the table shrinks on the clone to about 50% of the above size. Even in Developer mode the query performance on the clone is much better, but still several seconds.
Mon May 25 11:57:53 2020 [2412][b345f608-7a73-6c6f-2876-44aa711c7a2b][FATAL] Slow Query (time:3.8679010868073
SELECT item_id, item_summary, module_name, id FROM tracker WHERE id = (SELECT MAX(id) as id FROM tracker WHERE user_id = ‘b345f608-7a73-6c6f-2876-44aa711c7a2b’ AND deleted = 0 AND visible = 1) LIMIT 0,50
Nothing I do shrinks the table on the live instance, I’m not an expert, any advice about were to head to resolve this problem?
SELECT item_id, item_summary, module_name, id FROM tracker WHERE user_id = ‘b345f608-7a73-6c6f-2876-44aa711c7a2b’ AND deleted = 0 AND visible = 1 ORDER BY id DESC LIMIT 0,50
Thank-you for your advice, you have pointed me in the correct direction.
I’ve tested the two queries through MySQL Workbench and the modified query executes in 200mS compared to 8000mS for the default.
10:27:56 SELECT item_id, item_summary, module_name, id FROM tracker WHERE user_id = “b996f608-7a73-6c6f-2267-44aa711c7a2b” AND deleted = 0 AND visible = 1 ORDER BY id LIMIT 0,50 50 row(s) returned 0.203 sec / 0.000 sec
10:28:55 SELECT item_id, item_summary, module_name, id FROM tracker WHERE id = (SELECT MAX(id) as id FROM tracker WHERE user_id = “b996f608-7a73-6c6f-2267-44aa711c7a2b” AND deleted = 0 AND visible = 1) LIMIT 0,50 1 row(s) returned 8.875 sec / 0.000 sec
I’m not sure where the problem query is generated from within SuiteCRM, it seems to appear across several modules and fields.
I think I have two problems, this slow query and possibly the crazy size of our tracker table. The tracker table is nearly 2GB, our whole database and SuiteCRM folder system is only 3GB. We only have a dozen users and 2000 accounts, I think tracker should not be anywhere near 2GB in size. It looks like tracker table is not being pruned, and that some other error has generated too many tracker table entries. All the other tables seem to be correctly pruned.
If you have any other advice it would be greatly appreciated.
Ok…
Why you want to query this tracker table?
Are you going to generate any report out of that?
This table should be purged peridiocally. You can check the ‘Purne Tracker Table’ schedular job from CRM schedular jobs.
For error checking, if you say i can take a look into your table can tell you the reason. You can skype me in that case.
This query runs automatically on some page loads, it is not something we have deliberately created. It may be a legacy query from when performed our upgrade/crossgrade from SugarCRM.
At the moment I am looking into why our Tracker Tables are not being pruned correctly, it appears that ‘function::trimTracker’ has never run correctly.
At this time I cannot grant you access even though you are very knowledgeable, I do not have permission.
Ok no problem
Actually tracker table data is loaded in sidebar at list view and on some other views. So this can slow down you CRM.
You can do 1 thing out of 2:
1: Truncate the tracker table manually from database
2: Run tracker purne cron manually
For future reference you can archieve the tracker table. May be you would like to make the copy of original tracker table and then perfome these actions if required.
Thanks, I’ll post an update here once I’ve made some progress.
I have function::trimTracker running now, but on my cloned instance it did not reduce the tracker table size by very much, down from 600MB to 500MB.
Because the prune function will be slow on the 2GB table I’ve scheduled to run over the coming weekend.
In the meantime I will investigate the tracker table on the cloned instance. I suspect there will be records with column deleted = 0 as default or maybe null or something like that, which is preventing them from being correctly pruned.
After completing MySQL Workbench maintenance it was obvious some tracker table corruption was present. The table on my cloned instance reported as 500MB, but exported as only 400kb, maintenance reports did not find any problems. I backed up the database, and exported the tracker table before performing a truncation. After truncation all the previous slow query problems and now resolved, and the the site appears to be working correctly and is now free of error messages in the log files. The Prune Tracker function is enabled and working, I’ve set it to operate on a monthly schedule.
I’ll repeat this process on the live site in coming days, and won’t post any further here unless specific problems are encountered.