Loading lists within CRM is very slow (Leads/Users/etc)

SuiteCRM Forum - English Language SuiteCRM General Discussion
#1

Good Afternoon,

  Something I have not been able to overcome within SuiteCRM is terribly slow list loads. See the following for examples:

-Sales>Leads (This will load a list of leads, 90000 records, 72.5 seconds to load the first page of the list)
-Admin>User Management (This will load the list of users, 48 records, 45.7 seconds to load)

Specs and Version:

Windows Server 2012 R2 Datacenter, 64 Bit
8-cores, 12GB Ram
IIS 8.5
PHP 7.2.7 64bit
SuiteCRM Version 7.10.7
Sugar Version 6.5.25 (Build 344)
SecuritySuite - Enhanced 3.1.12
DashboardCopyManager 1.6
Dropdown Importer 1.1
MySQL
Database and CRM reside on the same server, but spec wise, should be able to cover this.

Please let me know if you guys have any tips or anything I can look at to speed up list loads. The rest of the CRM is snappy, only when loading a list does it grind to a halt. Thanks in advance for any info

#2

The delay on the Leads is a bit strange, but it is a large number of records… the delay for the Users is definitely weird.

Which settings do you have for memory_limit in your php.ini?

Do you see any messages in your logs at the time of the delays?

Please run the query at the top of this article and share the results here:

https://pgorod.github.io/Database-tables-size/

#3

Good Afternoon,

Thanks for the reply! Currently, the memory_limit is set to 128M. The results of the query are below:

CONCAT(table_schema, '.', table_name)	rows	DATA	idx	total_size	idxfrac
suitecrm_sales.leads	0.223M	0.111G	0.191G	0.302G	1.72
suitecrm_sales.securitygroups_records	0.343M	0.104G	0.160G	0.264G	1.547
suitecrm_sales.job_queue	0.112M	0.082G	0.052G	0.134G	0.633
suitecrm_sales.aod_indexevent	0.191M	0.062G	0.043G	0.106G	0.698
suitecrm_sales.pm_processmanager_entry_table	0.519M	0.059G	0.038G	0.097G	0.638
suitecrm_sales.leads_cstm	0.200M	0.090G	0.000G	0.090G	0
suitecrm_sales.tasks	0.075M	0.043G	0.041G	0.083G	0.96
suitecrm_sales.leads_audit	0.180M	0.046G	0.025G	0.071G	0.55
suitecrm_sales.email_addr_bean_rel	0.096M	0.022G	0.027G	0.049G	1.203
suitecrm_sales.email_addresses	0.079M	0.017G	0.019G	0.036G	1.099

Nothing showing in the PHP or SuiteCRM logs when running the lists loads. Aside from loading lists, the application is snappy. It’s only when records need to be displayed in a list on the page.

Please let me know what you think. Thanks in advance!

#4

You can turn on an option called “Log slow queries” in Admin / System settings. This should generate a message in the logs whenever a query takes a long time, and then you can direct your database maintenance to those specific tables.

Different screens will use different queries “under the hood”, and it’s possible that some are much heavier than others, even if they both produce a simple list of 20 lines on the screen. We need to understand exactly which queries are running slow.

Now, I am not sure how much of your large database is actually composed of data that is interesting to you. We’ll have to look at that in depth, sometimes tables get overgrown, or there are orphaned records (that point to a deleted record), or simply too many records with “deleted=1” which you can eliminate immediately if you’re not worried about the possibility of undeleting them one day.

Do you have the Scheduler job “Prune at 1st of month” active? What does it say in the “Last ran successfully” field? This job cleans up some records with “deleted=1”, though not all.

#5

Oh, and memory_limit=128MB ?? :ohmy:

Please increase that to 512MB (or at least 256MB) and restart your web browser. This, alone, might make a huge difference.

1 Like
#6

Some notes I took along the way including from what pgr wrote in other threads and his site. Also to refer to some points in this reply you will need to get Jim’s book and the SugarCRM guide (which is free)

Helpful forum topic:

https://suitecrm.com/suitecrm/forum/suitecrm-7-0-discussion/11204-what-is-making-suite-crm-run-so-slowly

General suggestions:

  • Switch to nginx
  • Turn off Ajax
  • Prune the database
    • Delete records with the flag “delete = 1”
  • Turn the log level to debug, load one of the slow screens, and see if I can determine what step is slow (More details here (p. 101 “Jim’s book”)

Additional references:

  • Review the Settings section on (p. 121 “Sugar Developer Guide 6.5”) some of them deal with performance optimization, some examples below:

  • disable_vcr option which make it load less records at a time (p. 141 “Sugar Developer Guide 6.5”)

  • dump_slow_queries will log slow queries (p. 141 “Sugar Developer Guide 6.5”) slow_query_time_msec affects what we determine to be a slow query

  • memcache settings (p. 144 “Sugar Developer Guide 6.5”)

  • hide_subpanels (p. 152 “Sugar Developer Guide 6.5”)

  • Instead of executing the before_save hook actions right away. We can schedule them so the user doesn’t need to wait. Especially relevant for more heavy jobs (p. 200 “Sugar Developer Guide 6.5”)

  • Improve performance chapter in SugarCRM 6.5 developer’s guide (p. 256)

  • Increase php memory to 512MB

Hardware setup
https://pgorod.github.io/Architecture/

1 Like
#7

Thanks for the write-up, it’s useful

There’s good news, “Jim’s book”, the full text, is now part of our Docs site, it’s called the “Developer Guide” there:

https://docs.suitecrm.com/developer/

The only thing I would add to that is checking also orphaned records, with queries like these:

SELECT * 
FROM contacts_cstm ChildTable    
LEFT JOIN contacts ParentTable 
ON ChildTable.id_c = ParentTable.id
WHERE ParentTable.id IS NULL

This will allow you to clean up a lot of stuff from cstm tables (custom fields of regular modules), from relationship tables, etc. Lots of these get left behind as you delete records in SuiteCRM.

1 Like
#8

All,

You guys are awesome! Reading through all of the suggestions now, I will keep the thread updated with any success! Thanks!!!

Best Regards,

Steven Stewart

1 Like