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

Chris138 · 28 June 2023 06:42

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:03 2023 [2477233][1][FATAL] Mysqli_query failed.
Wed Jun 28 06:56:03 2023 [2477233][1][FATAL]  Query Failed: SELECT favorites.*
                  FROM favorites
                  JOIN config ON ( config.id = favorites.parent_id )
                  WHERE favorites.assigned_user_id = '1'
                    AND favorites.parent_type = 'Administration'
                    AND favorites.deleted = 0
                    AND config.deleted = 0
                  ORDER BY favorites.date_entered DESC LIMIT 0,10: MySQL error 1054: Unknown column 'config.deleted' in 'where clause'

Could that be related to a very slow system?

I’ve moved the DB to my VPS (with another SuiteCRM running swiftly and connected to that one - but the speed is the same. VPS with 10GB RAM, very little traffic, MariaDB - and still the same issue.

On the shared host, there is a

Apache 2.4
NGINX reverse proxy
Caching enabled / disabled for testing purposes (all without changes / improvements)

The shared hosting company was quite helpful on the phone, but couldn’t see an issue as well.

In the browser, it seems like, most things load fast BUT the graphql endpoint:

Any idea, what that could be?

anils · 28 June 2023 10:03

I’m struggling with the same thing, with SuiteCRM 8.1. I have done the following so far:
(a) Checked and changed permissions of folders as required.
(b) removed errors that were showing the log
(c) Repair and rebuild
Nothing so far.

The server environment is perfect, I have other apps working on it fine. Here are some detials:

The CPU, Memory etc are almost idle.

Enabling slow log does not show anything in the log. The loading animation shows for almost 10 second after each click - which is really bugging.

pgr · 28 June 2023 11:55

That MySQL FATAL is solved with this PR

github.com/salesagility/SuiteCRM-Core

Fix #154 - [Legacy] MySQL Error 1054: missing config.deleted columns

salesagility:hotfix ← pgorod:patch-1

opened 10:39AM - 30 Mar 23 UTC

pgorod

+3 -1

## Description Some modules don't have an SQL column that the code below expect…s. ## Motivation and Context See related Issue #154 Note that I am not sure which modules to add to the **`$excludedModules`** list I created. I know that the **Administration** module is the one causing the FATAL. Other modules could be added here later. I added a few that are "special" modules, without a DB table and a **deleted** column there. ## How To Test This I am not sure how to trigger it. I came across this in [this Forum thread](https://community.suitecrm.com/t/databse-error-sending-campaign-mails-from-the-email-queue/88564/6). It seems to happen somewhere along the way in the Campaign wizard. Perhaps the best thing is to first try and reproduce the error, trigger it, and only then try the fix. ## Types of changes - [X] Bug fix (non-breaking change which fixes an issue) - [ ] New feature (non-breaking change which adds functionality) - [ ] Breaking change (fix or feature that would cause existing functionality to change) ### Final checklist - [X] My code follows the code style of this project found [here](https://docs.suitecrm.com/community/contributing-code/coding-standards/). - [ ] My change requires a change to the documentation. - [X] I have read the [**How to Contribute**](https://docs.suitecrm.com/community/contributing-code/) guidelines.

It was merged 3 weeks ago and should be included in the next version.

Chris138 · 28 June 2023 12:02

Thanks.
Do you think that could be the issue of the slow page load? Seems to be far fetched?

pgr · 28 June 2023 12:11

I have no idea. Try it. At least it’s always a good idea to remove FATAL errors, they stop script execution, so you never know what else is broken until you remove them…

anils · 28 June 2023 17:26

I have fixed all FATAL errors, there are none now. Yet, SuiteCRM is very slow. When I enable DEBUG level logs, I see this info:

Query Execution Time:9.9897384643555E-5
Query Execution Time:6.0081481933594E-5
Query Execution Time:7.7009201049805E-5

a basic query like:

Query:SELECT id FROM outbound_email WHERE type = ‘system’ AND deleted = 0
Query Execution Time:6.9141387939453E-5

is this normal? almost all queries are above 6 seconds.

pgr · 28 June 2023 18:01

@anils please start your own thread, it’s difficult to help two people at once.

Run the first query in this post and copy your results into the new thread you create:

You probably have overgrown tables

Chris138 · 29 June 2023 03:06

What happens at the /api/graphql endpoint?
What could it be, when it takes 20 and more seconds to load?
All CSS / JS load instantly, only the graphql takes very long?

@pgr I’ve applied the patch - FATAL error is gone, performance is still bad.

pgr · 29 June 2023 05:56

Don’t know, some web server configuration thing… maybe related to the mod_rewrite.

I know that turning on opcache makes a big difference in terms of performance for v8, but it should never be taking 20 seconds in the first place, on a system that’s not loaded with activity from multiple users, so there’s probably something else going on here.

Chris138 · 29 June 2023 12:42

The hosting company is working on it.
Opcache was switched on already.

In the meantime I’m using a VPS.