Conducted load testing SuiteCRM (30-700 users) on a bunch:
-APP
OS Debian 9
Web apache 2.4
PHP 7.1
-BD MS sql server
According to the results of the analysis of the results of stress testing, one problem was revealed - a description of the problem below.
Identified Issue:
When switching to the FL client list, we get a long response (1-15 seconds) from SuiteCRM to ~ 70 users, with 160 or more users (from 20 seconds to 5 minutes) that the load generation tool perceives as a session break.
We found out the cause of this problem, these are queries of the form:
SELECT TOP (21) *
FROM
(
SELECT
, ROW_NUMBER () OVER (ORDER BY ) AS row_number
FROM contacts
where contacts.deleted = 0
) AS a
WHERE row_number> 0
Then we found out that this request is generated automatically — more precisely, Suite CRM wants to execute the query “SELECT FROM contacts where contacts.deleted = 0 ORDER BY ” to display the first “page” of the client list; for this, it calls the procedure from package for working with MS SQL server limitQuery. In limitQuery, the query is sent, the number of records on page (21) and the initial line to page 0. Inside the limitQuery procedure, the query text changes:
• the function of obtaining a line number is added to the request
• inside the database server, the query in italics is executed
• The server inside itself makes a selection of all rows returned by the request (in our case, all records about clients, which are now more than 2 million)
• An external query from all records selects records with a line number greater than the initial one (in our case, 0)
The problem also lies in the fact that the limitQuery function is used to retrieve data for all lists in the system - these are all lists (including search results), all subpanels, and all lists in dashlets on the start page. Accordingly, with an increase in the number of entries in any module, there will be a guaranteed problem with opening its list.
Is there anyone on the forum who has encountered similar difficulties and / or knows how to get around this?
Mac-Rae
commented
5 years ago
Good day.
Conducted load testing SuiteCRM (30-700 users) on a bunch: -APP OS Debian 9 Web apache 2.4 PHP 7.1
-BD MS sql server
According to the results of the analysis of the results of stress testing, one problem was revealed - a description of the problem below.
Identified Issue:
When switching to the FL client list, we get a long response (1-15 seconds) from SuiteCRM to ~ 70 users, with 160 or more users (from 20 seconds to 5 minutes) that the load generation tool perceives as a session break. We found out the cause of this problem, these are queries of the form:
SELECT TOP (21) * FROM ( SELECT
, ROW_NUMBER () OVER (ORDER BY ) AS row_number
FROM contacts
where contacts.deleted = 0
) AS a
WHERE row_number> 0
Then we found out that this request is generated automatically — more precisely, Suite CRM wants to execute the query “SELECT FROM contacts where contacts.deleted = 0 ORDER BY ” to display the first “page” of the client list; for this, it calls the procedure from package for working with MS SQL server limitQuery. In limitQuery, the query is sent, the number of records on page (21) and the initial line to page 0. Inside the limitQuery procedure, the query text changes:
• the function of obtaining a line number is added to the request • inside the database server, the query in italics is executed • The server inside itself makes a selection of all rows returned by the request (in our case, all records about clients, which are now more than 2 million) • An external query from all records selects records with a line number greater than the initial one (in our case, 0) The problem also lies in the fact that the limitQuery function is used to retrieve data for all lists in the system - these are all lists (including search results), all subpanels, and all lists in dashlets on the start page. Accordingly, with an increase in the number of entries in any module, there will be a guaranteed problem with opening its list.
Is there anyone on the forum who has encountered similar difficulties and / or knows how to get around this?