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.

Cancel at any time!
Free Trial

#496 - Security Suite: securitygroups_records file very large (2,159,462 records)

Closed General Question created by ssaddler 5 years ago

My securitygroups_records table has 2.159,462 records (1.9Gb size) while the rest of the database has only 87,000 records. Is there a problem or have I implemented group features ineffectively? I would like to know if there is a way to reduce the size on disk and hence positively impact performance.

Thank you.

  1. eggsurplus member avatar

    eggsurplus Provider Affiliate

    5 years ago

    First thing to check is if you really need all of the groups that you have on each record. Try to minimize that need as much as possible. Also see if you have added some custom logic that may be adding duplicates to the table.

    Then once you have cleaned that up 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 = 'Meetings' 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) Reasses 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!
    -Jason

    • ssaddler member avatar

      ssaddler

      5 years ago

      Jason,

      Thank you. Your suggestions look interesting and I will give them a try. BTW we have no custom code involved, I am hoping the duplicate cleanup might be the gem I am looking for.

  2. eggsurplus member avatar

    eggsurplus Provider Affiliate

    5 years ago

    And the formatting of the reply completely got hosed. The content is there at least. Hopefully that is usable. Otherwise I can email you that same reply.

    -Jason

  3. eggsurplus member avatar

    eggsurplus Provider Affiliate

    5 years ago

    Following up here as we found that the issue was due to the SchedulersJobs getting related to groups which caused the table to inflate greatly. This was fixed as of 2.7.2 (https://www.sugaroutfitters.com/docs/securitysuite/release-notes).

This case is public. Please leave out any sensitive information such as URLs, passwords, etc.
Saving Comment Saving Comment...
Rating
Rating
  • "The perfect solution for managing security. Very stable and easy to implement. Must-have add-on for every CE implementation!"

    Read More Reviews

Keep up to date on the latest additions

We'll send you an email every month with handpicked add-ons, reviews, tricks and tips. Don't worry, we hate spam as much as you do.