[Vtigercrm-developers] vtiger slowing down (a lot)
Rubén A. Estrada Orozco
rulotec1 at gmail.com
Wed Oct 5 02:07:44 GMT 2022
Thanks again for your answers guys.
Alan, in the mentioned 20-million records DB, how much does it take to load
the "All" listview for the module with the most records? What are the
server specs?
I think the modifiedtime index doesn't make a difference because of the
joins. I made a test where I created an inner join with a subquery of
vtiger_crmentity being ordered and getting rid of the general ORDER BY. The
time dropped to 5 seconds. But that would imply modifying vtiger corefiles.
Tom, this is the result of the query:
+-------------------------+-------------+
| Variable_name | Value |
+-------------------------+-------------+
| group_concat_max_len | 1048576 |
| innodb_buffer_pool_size | 21474836480 |
| join_buffer_size | 2097152 |
| max_heap_table_size | 16777216 |
| open_files_limit | 25710 |
| query_cache_limit | 1048576 |
| query_cache_size | 1048576 |
| sort_buffer_size | 2097152 |
| tmp_table_size | 16777216 |
+-------------------------+-------------+
The server is Rocky Linux 8.6 using PHP 7.4, MariaDB 10.6 with 32GB ram and
4 dedicated virtual CPUs (DigitalOcean).
These are resource usage graphs for the last 14 days:
[image: image.png]
Saludos
Rubén
On Mon, Oct 3, 2022 at 7:23 AM Tom Brown <support at vtexperts.com> wrote:
> Angelo,
>
> The index is definitely the #1 thing Ruben should try, however in your
> case, did it work for 400k plus records? (assuming default listview sort &
> no filter)
>
> Ruben,
>
> vt_tmp_*** tables are temporary tables generated due to sharing access,
> you can find the function getNonAdminAccessControlQuery in CRMEntity.php.
>
> Can you also provide MySQL cluster specs (cpu/ram/version), as well as
> provide the output from the query below.
>
> SHOW GLOBAL VARIABLES
> WHERE variable_name IN (
> 'sort_buffer_size',
> 'join_buffer_size',
> 'query_cache_size',
> 'tmp_table_size',
> 'innodb_buffer_pool_size',
> 'max_heap_table_size',
> 'open_files_limit',
> 'group_concat_max_len',
> 'query_cache_limit');
>
> Thanks
>
>
> ---
>
> *Tom Brown* / Development Manager
> +1 (818) 495-5557 / tbrown at vtexperts.com
>
> *VTExperts*
> vtexperts.com
>
> The information transmitted, including attachments, is intended only for
> the person(s) or entity to which it is addressed and may contain
> confidential and/or privileged material. Any review, retransmission,
> dissemination or other use of, or taking of any action in reliance upon
> this information by persons or entities other than the intended recipient
> is prohibited. If you received this in error, please contact the sender and
> destroy any copies of this information.
>
>
> On Fri, Sep 30, 2022 at 3:11 PM Angelo Paglialonga <
> info at angelopaglialonga.com> wrote:
>
>> Hi Ruben I still suggest you to apply indexes to the all fields in
>> select query and check the results if you are curious
>>
>> _______________
>>
>> Angelo Paglialonga
>> Consulente per soluzioni CRM ad alto ROI.
>>
>> Telefono: 3386077866
>> Web: https://www.angelopaglialonga.com
>>
>>
>> Il giorno 30 set 2022, alle ore 21:51, Rubén A. Estrada Orozco <
>> rulotec1 at gmail.com> ha scritto:
>>
>>
>> Adding the modifiedtime index didn't make a difference.
>> I have this query that is apparently being used to load the listview of
>> the Invoice module:
>>
>> # Query_time: 10.208737 Lock_time: 0.000086 Rows_sent: 101
>> Rows_examined: 6813329
>> # Rows_affected: 0 Bytes_sent: 11919
>> SELECT
>> vtiger_invoice.subject,
>> vtiger_invoice.salesorderid,
>> vtiger_invoice.invoicestatus,
>> vtiger_invoice.total,
>> vtiger_crmentity.smownerid,
>> vtiger_invoice.invoicedate,
>> vtiger_invoice.duedate,
>> vtiger_invoicecf.cf_1498,
>> vtiger_invoice.invoice_no,
>> vtiger_invoicecf.spl_payment_id,
>> vtiger_invoicecf.spl_uuid,
>> vtiger_invoice.invoiceid,
>> vtiger_crmentity_user_field.starred
>> FROM
>> vtiger_invoice
>> INNER JOIN vtiger_crmentity ON vtiger_invoice.invoiceid =
>> vtiger_crmentity.crmid
>> LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id
>> LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid =
>> vtiger_groups.groupid
>> INNER JOIN vtiger_invoicecf ON vtiger_invoice.invoiceid =
>> vtiger_invoicecf.invoiceid
>> LEFT JOIN vtiger_crmentity_user_field ON vtiger_invoice.invoiceid =
>> vtiger_crmentity_user_field.recordid
>> AND vtiger_crmentity_user_field.userid = 1
>> WHERE
>> vtiger_crmentity.deleted = 0
>> AND vtiger_invoice.invoiceid > 0
>> ORDER BY
>> vtiger_crmentity.modifiedtime DESC
>> LIMIT
>> 0, 101;
>>
>> As you can see it took ~10 seconds. Just removing the ORDER BY clause
>> makes it take 0.001 seconds. So, as suggested by Tom, the ORDER BY is
>> causing the slowness.
>>
>> This is the EXPLAIN output (where I can't really see any obvious
>> improvement opportunities):
>>
>> *************************** 1. row ***************************
>> id: 1
>> select_type: SIMPLE
>> table: vtiger_invoice
>> type: range
>> possible_keys: PRIMARY,invoice_purchaseorderid_idx
>> key: PRIMARY
>> key_len: 4
>> ref: NULL
>> rows: 675575
>> Extra: Using where; Using temporary; Using filesort
>> *************************** 2. row ***************************
>> id: 1
>> select_type: SIMPLE
>> table: vtiger_invoicecf
>> type: eq_ref
>> possible_keys: PRIMARY
>> key: PRIMARY
>> key_len: 4
>> ref: vt_sandbox.vtiger_invoice.invoiceid
>> rows: 1
>> Extra:
>> *************************** 3. row ***************************
>> id: 1
>> select_type: SIMPLE
>> table: vtiger_crmentity
>> type: eq_ref
>> possible_keys: PRIMARY,crmentity_deleted_idx
>> key: PRIMARY
>> key_len: 4
>> ref: vt_sandbox.vtiger_invoice.invoiceid
>> rows: 1
>> Extra: Using where
>> *************************** 4. row ***************************
>> id: 1
>> select_type: SIMPLE
>> table: vtiger_crmentity_user_field
>> type: eq_ref
>> possible_keys: record_user_idx
>> key: record_user_idx
>> key_len: 10
>> ref: vt_sandbox.vtiger_invoice.invoiceid,const
>> rows: 1
>> Extra:
>>
>> Any thoughts?
>>
>> Right now the only solution I see is to avoid the "All" lists as also
>> suggested by Tom.
>>
>> Saludos
>>
>> Rubén
>> _______________________________________________
>> http://www.vtiger.com/
>>
>> _______________________________________________
>> http://www.vtiger.com/
>
> _______________________________________________
> http://www.vtiger.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20221004/1b9582a8/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 105482 bytes
Desc: not available
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20221004/1b9582a8/attachment-0001.png>
More information about the vtigercrm-developers
mailing list