Accounts List View taking so long to load

[lukeonorato]

Hello guys. I’m using suite v8.4.2 and after i putted some accounts records by API, i’m facing this problem:

Accounts’ screen keep loading forever.
I’ve noticed that when i deleted some accounts using dBeaver, the time to load the restain accounts records became quicker, but still taking some time to load everything.
I need to fix this slowness, is there someway?
I already tried this post, but without success: https://store.outrightcrm.com/blog/tips-that-can-help-in-performance-enhancement-of-suitecrm-sugarcrm/

[chris001]

What’s your:

1. PHP memory_limit?

2. Database type and version?

3. OS type and version?

4. Go to Admin, System Settings, Advanced, Enable Log Slow Queries, Save, and then use the app as normal and see what it logs to suitecrm.log as a FATAL:
   "Log slow queries": Where are they logged?

[lukeonorato]

Hi @chris001. Thanks for helping!
My php memory_limit is: 2048M
Database type is: MariaDb version: 11.3
OS type: Ubuntu version: 22.04
Message Log after enable Log Slow Queries:

[chris001]

What PHP version is it using?
What size is your database (in MB or GB)?
Are you able to upgrade to 8.4 or 8.5? They have fixes that are not in your 8.3.
This post may have some helpful steps you can do.

SuiteCRM 8.3 very slow - one page load ca. 81 seconds

Hello, I’ve installed several SuiteCRM instances since years on VPS and shared hosting and it was always acceptably quick. On one new shared hosting environment I’m running a small and fast website (Google Pagespeed 98 on mobile) and SuiteCRM 8.3 (new installation with 2 leads manually created). One page load takes around 83 seconds PHP 8.0 MySQL 8.0 opcache.enable true max_memory_limit 512M no 404 / 403, everything loads nicely - just very slow suitecrm.log is almost empty Wed Jun 28 06:56…

Post back your results.

[lukeonorato]

Hello.
PHP Version is 8.1 and 8.2, and my CRM version s 8.4.2
I’m not able to upgrade to 8.5
Database size is 16 GB

[anthony.oak.castro]

Hey @lukeonorato,

30GB database is a large db. How many records has the accounts module table?

I imagine the major slowness is mostly on list view right? After loading the list, when clicking a record is it relatively fast?

[lukeonorato]

Hi @anthony.oak.castro

1,5 million accounts records.
When the list view loads, the access to a record is fast indeed.

[anthony.oak.castro]

Hey @lukeonorato,

That is kind of what I was expecting…

The bottle-neck is clearly the db. The list view db query is what is making that view slow. To make the app faster, the optimizations will have to be done on the db engine side (mysql, mariadb, etc).

I’m not an expert on this area, though I know there are some optimizations you can do:

• on mysql/mariadb use InnoDB or another engine that is good for big volumes of data
  • Choosing the Right Storage Engine - MariaDB Knowledge Base
• if using InnoDB, increase innodb_buffer_pool_size to a lot more. Warning you will need a lot of RAM
• some resources:
  • Ten MySQL performance tuning settings after installation

Warning: as I said, my knowledge with fine-tunning mariadb/mysql is very limited, better to check with someone that is an expert.

[pgr]

Run the first query in this post and tell us your results:

Size of Tables and Indexes in Database

[chris001]

1. You can run a percona benchmark of your mariadb, see how it’s performing in general. Run this test first, then do step 2 (MySQLTuner), then come back and do this step 1 and compare before/after:

a. install sysbench

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
sudo apt -y install sysbench

b. install percona sysbench

git clone https://github.com/Percona-Lab/sysbench-tpcc /usr/share/sysbench/percona
cd /usr/share/sysbench/percona

c. prepare a test database

sysbench /usr/share/sysbench/percona/tpcc.lua --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root --mysql-password='password' --mysql-db=test --time=300 --threads=48 --report-interval=1 --tables=10 --scale=100 --db-driver=mysql prepare

d. run the test

sysbench /usr/share/sysbench/percona/tpcc.lua --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root --mysql-password='password' --mysql-db=test --time=300 --threads=48 --report-interval=1 --tables=10 --scale=100 --db-driver=mysql run

e. cleanup the test database

sysbench /usr/share/sysbench/percona/tpcc.lua --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root --mysql-password='password'--mysql-db=test --time=300 --threads=48 --report-interval=1 --tables=10 --scale=100 --db-driver=mysql cleanup

2. Run this MySQLTuner, it gives suggestions to improve your mariadb settings:

wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
chmod +x mysqltuner.pl

Commands to run it are here:
GitHub - major/MySQLTuner-perl: MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability..