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
  • I have a certain suspect, how huge is your database? Can you run a table size query e.g. the following to calculate the size of the different tables? Then look on the largest tables why are there so many records?

    SELECT
      TABLE_NAME AS `Table`,
      ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
    from  information_schema.TABLES
    where TABLE_SCHEMA = "sugarent1010"
    ORDER by 2 DESC;
    

    Very often performance issues are based on audit-tables, activity_log and other not periodically cleaned administration tables. I know customers where these tables where 80% of the database space because the database was never cleaned up.

    As far as I experienced, database connections are reused because Sugar always uses the same database user. SO the above options do not really help to improve the performance.

    Harald Kuske
    Principal Solution Architect – Professional Services, EMEA
    hkuske@sugarcrm.com
    SugarCRM Deutschland GmbH

Reply
  • I have a certain suspect, how huge is your database? Can you run a table size query e.g. the following to calculate the size of the different tables? Then look on the largest tables why are there so many records?

    SELECT
      TABLE_NAME AS `Table`,
      ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
    from  information_schema.TABLES
    where TABLE_SCHEMA = "sugarent1010"
    ORDER by 2 DESC;
    

    Very often performance issues are based on audit-tables, activity_log and other not periodically cleaned administration tables. I know customers where these tables where 80% of the database space because the database was never cleaned up.

    As far as I experienced, database connections are reused because Sugar always uses the same database user. SO the above options do not really help to improve the performance.

    Harald Kuske
    Principal Solution Architect – Professional Services, EMEA
    hkuske@sugarcrm.com
    SugarCRM Deutschland GmbH

Children
No Data