[Vtigercrm-developers] vtiger slowing down (a lot)
nilay khatri
nilay.spartan at gmail.com
Wed Oct 5 06:36:45 GMT 2022
1. Use caching, that will reduce the total db load by ~60%
2. Use SSD based setup instead of general use/magnetic HD
3. AWS RDS has been much better compared to DG
4. Index on Modified time and Created time
5. Remove id > 0 clause
6. Optimize Leads module and Contacts module to have 2 tables, which is
currently using 4 tables I guess
On Wed, Oct 5, 2022 at 7:39 AM Rubén A. Estrada Orozco <rulotec1 at gmail.com>
wrote:
> 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/
>
> _______________________________________________
> http://www.vtiger.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20221005/e80ffd87/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/20221005/e80ffd87/attachment-0001.png>
More information about the vtigercrm-developers
mailing list