Slow SuiteCRM with large database

👨‍💻 Development Help
1

Hi All,

Using suitecrm 7.5.3 with approx database size of 850MB and the average load time for a request to complete is around 8 sec.

Hosted on shared hosting with 1GB ram, php 5.6 with maria db on same server.

Already tried most of the performance tweaks , php, mysql, suite config variables, with no luck (shared hosting has many limitations for php and mysql! ) :frowning:

To debug speed issue, already tried following.

  1. Did a fresh install of suite 7.5.3 and linked the live database to this fresh code, to see if its due to customized code or not but still it was slow, so probably might due to database.

  2. linked to new database (of the fresh install) with existing live code, and seems working speedy!!

  3. Then did a fresh install of suite 7.6.1 and copied all the modules data from live db to this new db one by one and was working very fine, <1 sec, until imported notes and calls data which has huge amount of records and the slow down begun. surprisingly, it is slowing all other modules, i think it is doing join query with all other module records!!

Here is table statistics…

table name = no of records

calls_contacts = ~241,638
calls = ~232,278
notes = ~116,011

accounts, contacts etc too have records around ranging from 30000 to 70000 but works good without note and calls.

Is there any way to boost the performance?? or any other alternative to solve the speed issue?

Thanks
Dhaval

2

One setting I’ve found helps speed up an instance of Suite with a lot of data is to disable Advanced Open Discovery. You can do this by going to admin then AOD settings and unchecking the box or by adding the following line to config_override.php (both effectively do the same thing).


$sugar_config['aod']['enable_aod'] = false;

This turns indexing for global searches off. May slow down searching, but will speed everything else up. If you do this it is also worthwhile to go to schedulers (again in admin) and disable any scheduled tasks related to AOD since you’re not using it.

3

Hi ewanmcrobert,

Thanks for the direction!!

Just tried it by, disabled 2 schedule related to aod, deleted index directory on modules/AOD_index and disabled aod in admin, with no luck on speed :frowning:

4

Sorry it wasn’t much help, made a massive difference to me on an instance with lots of data.

Couple of other settings that may help:

$sugar_config['developerMode'] = false;

When developer mode is on Suite doesn’t cache any pages. Think it’s off by default anyhow, but worth checking in case you have enabled it at some point

$sugar_config['disable_count_query'] = true;

This stops counting the number of pages you have in views, so instead of saying 1-4 it will say something like 1-many. Saves a query so should speed it up a bit if you have lots of data.

$sugar_config['disable_vcr'] = true;

By default opening the detail view of a record from the list view will also load the other records in the list to allow for easy moving through records. If you do not use this feature, or, if loading the detail view for some records has become slow, you can disable this feature.

$sugar_config['list_max_entries_per_page'] = '10';

The number of records shown in each page of the list view can be decreased. This will result in a slight increase in performance on list view pages.

$sugar_config['logger']['level'] = 'fatal';

Lowering the log level means that there will be less log messages to write to disk on each request.This will slightly (very slightly) increase performance.

$sugar_config['save_query'] = 'populate_only';

Don’t automatically run previous search on list view

$sugar_config['hide_subpanels_on_login'] = true;

Sub panels will initially be collapsed and the data wont be loaded until you expand them.

5

Hi ewanmcrobert,

Already tried all this setting but no major speed up :slight_smile:

I am sure it had worked great for you, happens to me lot, things work on one machine and not on other, specially when delivering to client … lol … :smiley:

Thanks.

6

Sorry I couldn’t be more help, but I’m pretty much out of suggestions now. The only other thing I can say is 1Gb of RAM isn’t much for a server in this day and age. May be worth considering getting better hosting?

7

Hm… make sense…

Thanks ewanmcrobert :slight_smile:

8

Is there anything else that can be done here, without changing the hosting? say mysql table optimization? looking at the mysql partitioning but am not expert with mysql :frowning:

Thanks.

9

There is an option under settings to log slow queries. This may help you identify a query that’s causing the issue and help you work out if there are any indexes you can add to your tables to speed it up.

10

There are many queries when i set the log mode to info, showing “Query Execution Time:9.5129013061523E-5”, not sure if 9 is in sec or msec, but when i run them in phpmyadmin they run very fast.

Thanks.

11

This is a concern we are having before we even start our project with SuiteCRM.

It is not unique to SuiteCRM or MySQL. Complex datasets with lots of rows puts database servers under considerable strain. Our project will probably scale to several hundred thousand accounts and there are custom tables that may potentially have millions of rows.

The only thing we are hoping is that throwing a lot of hardware at it and using a scaled up AWS RDS MySQL instance will help. MySQL can be clustered with one master node handling writes while multiple slaves can have read queries off loaded to them. With several 16Gb 4 core boxes dedicated to MySQL we hope page loads will not exceed 2-3 seconds.

12

Actually turned out to be issue with reminder which looks like to be processed on each view (edit, detail , list etc)… and we have almost 3k rows in reminder, so each time page is loaded it was processing (loading) all these reminders which was taking time (and they were not actually for the logged in user)…

Clearing the reminder table bring back the speed to around 0.4 sec.

Thanks

1 Like