SuiteCRM interface slowness

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:

Example with the filter:

Check php.ini and database config on your server.

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?

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.

SuiteCRM server:
image

MariaDB server:
image

I will look for the ‘log slow queries’ option to run the EXPLAIN.

1 Like

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:


Not working:

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?

more…

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