Suitecrm: Alerts killing performance - Improve/Reduce Retrieval of Alerts

[nelem]

Issue

Alerts are killing performance in one particular instance. Queries are taking 8 seconds for a fairly complex Home dashboard vs 4.3 seconds without Alerts. Contact DetailView 5.7s vs 1.39s.
A whole four seconds for the Alerts - which I don't use (yet?).

Alerts are also really annoying while debugging and there have been many requests to disable them. I have used a config variable to disable alerts which must be set TRUE to actually disable them. Alerts are already disabled for mobile devices.

Possible Fix

in include/MVC/View/SugarView.php line 177

        // We have to update jsAlerts as soon as possible
        if ($this instanceof ViewList || $this instanceof ViewDetail || $this instanceof ViewEdit)
        {
            if (!isset($_SESSION['isMobile']) && 
                (!isset($GLOBALS['sugar_config']['disable_alerts']) || !$GLOBALS['sugar_config']['disable_alerts']))
            {
                $jsAlerts = new jsAlerts();
                echo $jsAlerts->getScript();
            }
            else 
            {
                // Stop the periodic Alerts.updateMissed
                echo "<script>SUGAR.config.disable_alerts = true;</script>";
            }
        }

and in include/SubPanel/SubPanelViewer.php line 100

if (!isset($_SESSION['isMobile']) &&
   (!isset($GLOBALS['sugar_config']['disable_alerts']) || !$GLOBALS['sugar_config']['disable_alerts'])) {
    echo $jsAlerts->getScript();
}

plus the periodic Alerts query in jssource/src_files/include/javascript/alerts.js line 210

var updateMissed = function () {
      // check if Alerts are disabled in config
      if (typeof SUGAR.config.disable_alerts !== "undefined")
          return;
      Alerts.prototype.updateManager();
      setTimeout(updateMissed, 60000);
}

To actually disable Alerts
$sugar_config['disable_alerts'] = true;

Context

My context may be unusual because I have 9 custom relate fields relating to further contacts (with 9 relate fields). I haven't traced the queries because there are thousands of them - all on Contacts.

Your Environment

• SuiteCRM Version used: 7.8.15
• Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)):
• Environment name and version (e.g. MySQL, PHP 7):
• Operating System and version (e.g Ubuntu 16.04):

[pgorod]

Please fill in the version details asked in the Issue template!

There was an old issue with this, but it was fixed long ago. Since I don't know your version of SuiteCRM, it's hard to tell if it's the same thing...

I do agree a flag to disable alerts is a good idea, though. But we should start by making sure they don't degrade performance anyway.

[nelem]

Updated Issue. SuiteCRM 7.8.15

[pgorod]

Can you run this SQL

SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  20;

To see if you have overgrown tables, especially Alerts? Maybe it just needs trimming.

[nelem]

As requested. I run multiple instances s I have selected to one of interest. I have noticed email_addr_bean_rel seems to have a huge number of rows.
What is the importance of the other results?
BTW, the other instances all have massive numbers of rows in job_queue.

suitecrmbnidemo.job_queue                       0.04M   0.05G   0.03G   0.08G   0.66
suitecrmbnidemo.email_addr_bean_rel             0.06M   0.01G   0.02G   0.03G   1.72
suitecrmbnidemo.emails_text                     0.00M   0.01G   0.00G   0.01G   0.01
suitecrmbnidemo.contacts                        0.01M   0.00G   0.01G   0.01G   3.22
suitecrmbnidemo.aod_indexevent                  0.01M   0.00G   0.00G   0.01G   1.20
suitecrmbnidemo.contacts_cstm                   0.01M   0.01G   0.00G   0.01G   0.00
suitecrmbnidemo.email_addresses                 0.01M   0.00G   0.00G   0.00G   2.00
suitecrmbnidemo.aow_processed_aow_actions       0.00M   0.00G   0.00G   0.00G   2.00
suitecrmbnidemo.emails_email_addr_rel           0.01M   0.00G   0.00G   0.00G   2.00
suitecrmbnidemo.emails                          0.00M   0.00G   0.00G   0.00G   0.59
suitecrmbnidemo.aow_processed                   0.00M   0.00G   0.00G   0.00G   0.58
suitecrmbnidemo.calls                           0.00M   0.00G   0.00G   0.00G   0.53
suitecrmbnidemo.emails_beans                    0.00M   0.00G   0.00G   0.00G   1.82
suitecrmbnidemo.calls_contacts                  0.00M   0.00G   0.00G   0.00G   1.59
suitecrmbnidemo.user_preferences                0.00M   0.00G   0.00G   0.00G   0.21
suitecrmbnidemo.contacts_audit                  0.00M   0.00G   0.00G   0.00G   0.80
suitecrmbnidemo.config                          0.00M   0.00G   0.00G   0.00G   0.04
suitecrmbnidemo.acl_roles_actions               0.00M   0.00G   0.00G   0.00G   2.00
suitecrmbnidemo.calls_users                     0.00M   0.00G   0.00G   0.00G   2.20
suitecrmbnidemo.acl_actions                     0.00M   0.00G   0.00G   0.00G   0.88

[nelem]

I have run this query and getting 53,000 (orphaned?) rows. Is it safe to delete them?

select count(*) 
from email_addr_bean_rel 
where bean_module='Contacts'
and bean_id not in (select id from contacts);

[nelem]

I deleted them. No effect. Contact detailView 5.62s vs 1.5s.

[pgorod]

I had more hopes for that job_queue to be related to your issue. Alerts are a Scheduler job, and it needs to write to that table...

What do you get when you do

select count(*) from job_queue where status = 'done';

[nelem]

count=43,373

[pgorod]

Maybe try deleting all of those, leaving only the ones in the latest month, for example, just in case. (make sure you have backups of it all, of course)

[nelem]

I deleted them to no avail. However, I deleted 78 reminders (which we don't use because we never saw them) and performance has returned to normal. The function Reminders::addNotifications was taking all the time. Any idea how to disable reminders?

There is also a little more to do to stop Alerts completely. There is a periodic query every 60 seconds by javascript loaded independently of the above code in sugar_grp1.js (from jssource/src_files/include/javascript/alerts.js). I have updated the code above to use the config to disable this periodic query by setting a javascript flag.

[samus-aran]

Hi @nelem We'll look at this area (improving the retrieval of alerts) for the coming releases. Thanks for raising the issue.

[Dillon-Brown]

Resolved here: #5370