by eggsurplus

Control what your users can access and save time, money, and frustrations. Lock down sensitive data in SugarCRM or SuiteCRM to specific groups or teams. Supports unlimited assigned users, unlimited group assignments to records, custom layouts for each group, login/sudo capabilities and much more.

Free 30 day trial
Try it Now

By clicking you consent to share your profile with the developer

#1420 - Security group join

Closed General Question created by nathan1 Verified Purchase 7 years ago

We're having some really slow load times in SugarCRM when first logging in and when navigating to the Dashboard. It looks like the dashlets are causing the issue. The following join is being used by some of the dashlets, or EXISTS (SELECT 1 FROM securitygroups secg INNER JOIN securitygroups_users secu ON secg.id = secu.securitygroup_id AND secu.deleted = 0 AND secu.user_id = '58e20515-63c4-d4da-badf-51acfc177b0c' INNER JOIN securitygroups_records secr ON secg.id = secr.securitygroup_id AND secr.deleted = 0 AND secr.module = 'Calls' WHERE secr.record_id = calls.id AND secg.deleted = 0)

I believe this is defined somewhere in the ListView code. Is there any way to keep the dashlets from including this join? This join is an issue because one of our sites has about 29,000 security groups total, and several of their users have 400 or more security groups. That's causing queries to run for 1 to 2 minutes.

  1. eggsurplus member avatar

    eggsurplus Provider Affiliate

    7 years ago

    Hi Nathan,

    SecuritySuite will run into problems if you have thousands of groups. The indexes aren't set up to optimize the queries for that type of usage. Here are some things that can be done to optimize SecuritySuite:

    Use MySQL Tuner and apply any recommended indexes that it generates: https://github.com/rackerhacker/MySQLTuner-perl

    If you are seeing a lot of SchedulersJobs records in the securitygroups_records table then definitely consider upgrading to the latest 2.7.2 SecuritySuite release for SugarCRM 6.5.16. There was a possibility where SchedulersJobs and a couple of other tables to automatically inherit groups causing the securitygroups_records table to inflate quickly. More info: https://www.sugaroutfitters.com/docs/securitysuite/release-notes

    Reduce the number of security groups being used as much as possible. Generally avoid groups for one-off groupings per record. Meaning don't create a group for a single account. Instead create them to represent your organizational groups.

    Remove all of the deleted records in securitygroups_records.

    delete from securitygroups_records where deleted = 1;
    

    Then delete duplicates

    delete r from securitygroups_records r
    inner join securitygroups_records r2 on r.module = r2.module
        and r.record_id = r2.record_id and r2.deleted = 0
        and r.id <> r2.id and r.date_modified > r2.date_modified
    where r.deleted = 0;
    

    You can find records with many groups associated to it with (change table/count as desired):

    select m.name, count(1)
    from meetings m
    inner join securitygroups_records r on m.id = r.record_id and r.module = &#039;Meetings&#039; and r.deleted = 0
    group by r.id
    having count(r.id) > 2;
    

    Some other things to try: 1) Upgrade to at least MySQL 5.5 if you haven't already (200-360% performance boost). 2) Reassess how groups are being used 3) Make sure InnoDB is being used as the db engine 4) Disable list count queries in SugarCRM 5) Reassess and apply tuner recommendations (https://github.com/rackerhacker/MySQLTuner-perl) 6) Optimize MySQL tables (mysqlcheck) 7) See if it's a specific module: select module, count(*) from securitygroups_records group by module; 8) Defrag the securitygroups_records table (after doing deletes):

    http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html ALTER TABLE securitygroups_records ENGINE=INNODB; OPTIMIZE TABLE securitygroups_records;

    I hope something there helps!

    • nathan1 member avatar

      nathan1 Verified Purchase

      7 years ago

      Thank you for all of the suggestions. I think we just need to reassess how the groups are being used, because there are over 59 million rows in the securitygroups_records table.

  2. eggsurplus member avatar

    eggsurplus Provider Affiliate

    7 years ago

    Hi Nathan,

    Just doing some housekeeping. Feel free to follow up if you have any other questions.

  3. mmcaughan member avatar

    mmcaughan

    6 years ago

    Have < 100 groups, 4.5 M securitygroups_records and 1.5 M contacts, seeing slow load times mainly from the join for security groups... any pointers?

    • eggsurplus member avatar

      eggsurplus Provider Affiliate

      6 years ago

      All the tips above I would recommend. Also look at moving the database off of the same server as the application.

This case is public. Please leave out any sensitive information such as URLs, passwords, etc.
Saving Comment Saving Comment...
Rating
Rating
  • "Quite powerful, flexible and easy to use plugin. Works perfectly in SuiteCRM as well as in SugarCRM." - damien8105

    Read More Reviews