SuiteCRM interface slowness

šŸ’¬ General Discussion
1

I added 1.5 million accounts via the database across all related tables:

  • accounts
  • accounts_cstm
  • email_addresses
  • email_addr_bean_rel
  • contacts
  • contacts_cstm
  • accounts_contacts

After the insertion, the SuiteCRM interface just keeps loading even after repairing. Apparently, it seems that it wants to display all the data, despite having pagination. If I use a filter, it displays normally, but the issue arises the first time I open the suite. This occurs in the accounts module, contacts, and on the home page.
example of slowness:

image
image1896Ɨ1029 35 KB

Example with the filter:

image
image1140Ɨ642 62 KB

2

Check php.ini and database config on your server.

3

Thank you, @rsp . You partially solved the issue with the tutorial. I applied all the recommended settings:

config_override.php

$sugar_config['verify_client_ip'] = false;
$sugar_config['disable_count_query'] = true;
$sugar_config['save_query'] = 'populate_only';
$sugar_config['hide_subpanels'] = true;
$sugar_config['hide_subpanels_on_login'] = true;
$sugar_config['logger']['level'] = 'fatal';
$sugar_config['disable_vcr'] = true;
$sugar_config['list_max_entries_per_page'] = '500';

php.ini

memory_limit = 3072
post_max_size = 3072
max_execution_time = 3600

my.cnf

query_cache_type = 1
query_cache_size = 16M

However, Iā€™m still experiencing instability. Sometimes it instantly loads the 500 accounts, and sometimes it takes an eternity to process. Do you know if I should modify any of these configurations?

4

What is the hardware on your server? Thatā€™s a very big database.

You should also turn on ā€œlog slow queriesā€ and see exactly which queries are breaking the server. Then you can run them directly in SQL with EXPLAIN to see if you can optimize indexes.

5

SuiteCRM server:
image

MariaDB server:
image

I will look for the ā€˜log slow queriesā€™ option to run the EXPLAIN.

1 Like
6

Iā€™m still testing the indexes and trying to fix the error, but Iā€™m facing the following situation. This query from the accounts insight is in the serverā€™s slow log. Up to 10 accounts inserted into the database, the index works; however, from 10 onwards, it simply stops working. Iā€™m attaching screenshots of both cases; I couldnā€™t identify why it stops using the index.
Working index:

image
image1815Ɨ116 38.8 KB

Not working:
image
image1234Ɨ93 28.6 KB

7

I donā€™t know much about that sort of DB thing. But it sounds weird. Could you have a buggy database version? Or a corrupted DB file somewhere?

8

moreā€¦

9

I believe I may have resolved it; Iā€™ll leave all the configurations I applied. The last changes I made were to increase the innodb and the query_cache_type. Itā€™s loading practically instantly and isnā€™t becoming heavy on the browser. Thank you very much for the help, @rsp and @pgr.

ANALYZE TABLE suitecrm.accounts;
CHECK TABLE suitecrm.accounts;
REPAIR TABLE suitecrm.accounts;
OPTIMIZE TABLE suitecrm.accounts;

ANALYZE TABLE suitecrm.accounts_cstm;
CHECK TABLE suitecrm.accounts_cstm;
REPAIR TABLE suitecrm.accounts_cstm;
OPTIMIZE TABLE suitecrm.accounts_cstm;

ANALYZE TABLE suitecrm.users;
CHECK TABLE suitecrm.users;
REPAIR TABLE suitecrm.users;
OPTIMIZE TABLE suitecrm.users;

ANALYZE TABLE suitecrm.campaigns;
CHECK TABLE suitecrm.campaigns;
REPAIR TABLE suitecrm.campaigns;
OPTIMIZE TABLE suitecrm.campaigns;

ANALYZE TABLE suitecrm.email_addr_bean_rel;
CHECK TABLE suitecrm.email_addr_bean_rel;
REPAIR TABLE suitecrm.email_addr_bean_rel;
OPTIMIZE TABLE suitecrm.email_addr_bean_rel;

ANALYZE TABLE suitecrm.email_addresses;
CHECK TABLE suitecrm.email_addresses;
REPAIR TABLE suitecrm.email_addresses;
OPTIMIZE TABLE suitecrm.email_addresses;

ANALYZE TABLE suitecrm.accounts_opportunities;
CHECK TABLE suitecrm.accounts_opportunities;
REPAIR TABLE suitecrm.accounts_opportunities;
OPTIMIZE TABLE suitecrm.accounts_opportunities;

ANALYZE TABLE suitecrm.accounts_contacts;
CHECK TABLE suitecrm.accounts_contacts;
REPAIR TABLE suitecrm.accounts_contacts;
OPTIMIZE TABLE suitecrm.accounts_contacts;

SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
SET GLOBAL innodb_buffer_pool_size=1932735283;

SHOW VARIABLES LIKE 'query_cache_size';
SET GLOBAL query_cache_size=8000000;

SHOW VARIABLES LIKE 'query_cache_type';
SET GLOBAL query_cache_type=1;

SHOW STATUS LIKE 'Qcache%';

config_override.php

<?php
/***CONFIGURATOR***/
$sugar_config['default_export_charset'] = 'ISO-8859-1';
$sugar_config['dump_slow_queries'] = true;
$sugar_config['system_name'] = 'SuiteCRM Zaz';
$sugar_config['verify_client_ip'] = false;
$sugar_config['disable_count_query'] = true;
$sugar_config['save_query'] = 'populate_only';
$sugar_config['hide_subpanels'] = true;
$sugar_config['hide_subpanels_on_login'] = true;
$sugar_config['logger']['level'] = 'fatal';
$sugar_config['disable_vcr'] = true;
$sugar_config['list_max_entries_per_page'] = '200';
$sugar_config['developerMode'] = false;
/***CONFIGURATOR***/


php.ini

memory_limit = 3072
post_max_size = 3072
max_execution_time = 3600
2 Likes