db connection persistence

Hello Team,

Hope you all are doing well. 

We have a SugarCRM instance which have massive data. The instance is connected to a portal which serves the CRM data to our endusers. Also there is customer support team which uses the CRM.

All this causes the database CPU hit 100%.

We have also implemented RDS Read-only DB to balance some load but still the same issue. We were going through the sugarCRM documentation to know if there is any DB config settings which we can use to ease this load.

We came across below, but enabling them increases the number of connections.

$sugar_config['dbconfigoption']['autofree'] = true; //Automatically frees the database reference when it closes the reference.
$sugar_config['dbconfigoption']['persistent'] = true; //Determines whether Sugar should use persistent connection when possible to connecting to the database.


Does anyone know what in more detail what happens when these settings are enabled? Also what can be done to free the DB connection resources once the request is served?
Is there a way to effeciently handle multiple request on the DB via CRM?

Any ideas / thoughts are welcome.

Thank you in advance.

Regards,
Omair Nalkhande
Parents
  • Hi Omair,

    What is the cause of DB usage being 100%? Is it the number of connections, or is it some other long running query due to reports, filters, etc?

    You can check the show full process list to see if there are any long running queries.

    Did you also check for any locks? 

    You could enable printing of slow queries in sugar for Performance tuning:

    $sugar_config['dump_slow_queries'] = true;
    $sugar_config['slow_query_time_msec'] = '2000';

    Finally - you should check the sizing guide - do you think your DB server is capable and sufficient enough to handle the load?

    https://support.sugarcrm.com/Resources/Environments/Sugar_On-Site_Sizing_Guide/

Reply
  • Hi Omair,

    What is the cause of DB usage being 100%? Is it the number of connections, or is it some other long running query due to reports, filters, etc?

    You can check the show full process list to see if there are any long running queries.

    Did you also check for any locks? 

    You could enable printing of slow queries in sugar for Performance tuning:

    $sugar_config['dump_slow_queries'] = true;
    $sugar_config['slow_query_time_msec'] = '2000';

    Finally - you should check the sizing guide - do you think your DB server is capable and sufficient enough to handle the load?

    https://support.sugarcrm.com/Resources/Environments/Sugar_On-Site_Sizing_Guide/

Children
No Data