Forums - Thread Details
  • State Not Answered
  • Replies 3 replies
  • Subscribers 322 subscribers
  • Views 1143 views
  • Users 0 members are here
Forum Actions
Related

db connection persistence

Omair Nalkhande

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
  • 0

    What is the main task that is done by users who are connected using the portal? Is that mostsly reading things? Or mainly creating? Same goes for the  customer support users. Are these actual users in sugar? 

    Did you implement some sort of caching mechanism like Redis/Memcache? More information here (point 6): gadoma.com/.../

  • 0

    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/

  • 0

    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