<div dir="ltr">1. Use caching, that will reduce the total db load by ~60%<div>2. Use SSD based setup instead of general use/magnetic HD</div><div>3. AWS RDS has been much better compared to DG</div><div>4. Index on Modified time and Created time</div><div>5. Remove id > 0 clause</div><div>6. Optimize Leads module and Contacts module to have 2 tables, which is currently using 4 tables I guess</div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Oct 5, 2022 at 7:39 AM Rubén A. Estrada Orozco <<a href="mailto:rulotec1@gmail.com">rulotec1@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Thanks again for your answers guys.<div><br></div><div>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?</div><div>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.</div><div><br></div><div>Tom, this is the result of the query:</div><div><br></div><div>+-------------------------+-------------+<br>| Variable_name           | Value       |<br>+-------------------------+-------------+<br>| group_concat_max_len    | 1048576     |<br>| innodb_buffer_pool_size  | 21474836480 |<br>| join_buffer_size                | 2097152     |<br>| max_heap_table_size      | 16777216    |<br>| open_files_limit                 | 25710       |<br>| query_cache_limit              | 1048576     |<br>| query_cache_size              | 1048576     |<br>| sort_buffer_size                 | 2097152     |<br>| tmp_table_size                 | 16777216    |<br>+-------------------------+-------------+<br><div><br></div><div>The server is Rocky Linux 8.6 using PHP 7.4, MariaDB 10.6 with 32GB ram and 4 dedicated virtual CPUs (DigitalOcean).</div><div><br></div><div>These are resource usage graphs for the last 14 days:</div><div><br></div><div><img src="cid:ii_l8uzi36w0" alt="image.png" width="562" height="519"><br></div><div><br></div><div><br clear="all"><div><div dir="ltr"><div dir="ltr">Saludos<div><br></div><div>Rubén</div></div></div></div><br></div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Oct 3, 2022 at 7:23 AM Tom Brown <<a href="mailto:support@vtexperts.com" target="_blank">support@vtexperts.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div class="gmail_default" style="font-family:verdana,sans-serif;color:rgb(68,68,68)">Angelo,</div><div class="gmail_default" style="font-family:verdana,sans-serif;color:rgb(68,68,68)"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:rgb(68,68,68)">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)</div><div class="gmail_default" style="font-family:verdana,sans-serif;color:rgb(68,68,68)"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:rgb(68,68,68)">Ruben,</div><div class="gmail_default" style="font-family:verdana,sans-serif;color:rgb(68,68,68)"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:rgb(68,68,68)">vt_tmp_*** tables are temporary tables generated due to sharing access, you can find the function getNonAdminAccessControlQuery in CRMEntity.php.</div><div class="gmail_default" style="font-family:verdana,sans-serif;color:rgb(68,68,68)"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:rgb(68,68,68)">Can you also provide MySQL cluster specs (cpu/ram/version), as well as provide the output from the query below.</div><div class="gmail_default" style="font-family:verdana,sans-serif;color:rgb(68,68,68)"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:rgb(68,68,68)">SHOW GLOBAL VARIABLES <br>WHERE variable_name IN (<br>'sort_buffer_size',<br>'join_buffer_size',<br>'query_cache_size',<br>'tmp_table_size',<br>'innodb_buffer_pool_size',<br>'max_heap_table_size',<br>'open_files_limit',<br>'group_concat_max_len',<br>'query_cache_limit');<br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:rgb(68,68,68)"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:rgb(68,68,68)">Thanks</div><div class="gmail_default" style="font-family:verdana,sans-serif;color:rgb(68,68,68)"><br></div><div><div dir="ltr"><div dir="ltr"><div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif">---<br></font></div><div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif"><b>Tom Brown</b> / Development Manager</font></div><div><font face="verdana, sans-serif">+1 (818) 495-5557 / <a href="mailto:tbrown@vtexperts.com" target="_blank">tbrown@vtexperts.com</a></font></div><div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif"><b>VTExperts</b></font></div><div><font face="verdana, sans-serif"><a href="https://vtexperts.com" target="_blank">vtexperts.com</a></font></div><div><font face="verdana, sans-serif" size="1"><br></font></div><div><font face="verdana, sans-serif" size="1">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.</font></div></div></div></div><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Sep 30, 2022 at 3:11 PM Angelo Paglialonga <<a href="mailto:info@angelopaglialonga.com" target="_blank">info@angelopaglialonga.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="auto">Hi Ruben I still suggest you to apply indexes to the all  fields in select query and check the results if you are curious<br><br><div dir="ltr"><span style="border-collapse:separate"><div><span style="border-collapse:separate"><div><span style="border-collapse:separate"><div><span style="border-collapse:separate"><span style="border-collapse:separate"><span style="border-collapse:separate"><span style="border-collapse:separate"><span style="border-collapse:separate"><span style="border-collapse:separate"><span style="border-collapse:separate"><span style="border-collapse:separate"><span style="border-collapse:separate"><span style="border-collapse:separate"><span style="border-collapse:separate"><div><span style="border-collapse:separate"><div><span style="border-collapse:separate"><div><span style="border-collapse:separate"><div><span style="border-collapse:separate"><div style="background-color:rgba(255,255,255,0)"><div style="font-family:Helvetica;font-size:14px">_______________<br><br>Angelo Paglialonga<br>Consulente per soluzioni CRM ad alto ROI.<br><br>Telefono: 3386077866<br>Web: <a href="https://www.angelopaglialonga.com" target="_blank">https://www.angelopaglialonga.com</a></div><div style="font-family:Helvetica;font-size:14px"><br></div></div></span></div></span></div></span></div></span></div></span></span></span></span></span></span></span></span></span></span></span></div></span></div></span></div></span></div><div dir="ltr"><br><blockquote type="cite">Il giorno 30 set 2022, alle ore 21:51, Rubén A. Estrada Orozco <<a href="mailto:rulotec1@gmail.com" target="_blank">rulotec1@gmail.com</a>> ha scritto:<br><br></blockquote></div><blockquote type="cite"><div dir="ltr"><div dir="ltr"><div dir="ltr">Adding the modifiedtime index didn't make a difference.<br></div><div>I have this query that is apparently being used to load the listview of the Invoice module:</div><div><br></div><div># Query_time: 10.208737  Lock_time: 0.000086  Rows_sent: 101  Rows_examined: 6813329<br># Rows_affected: 0  Bytes_sent: 11919<br>SELECT <br>  vtiger_invoice.subject, <br>  vtiger_invoice.salesorderid, <br>  vtiger_invoice.invoicestatus, <br>  vtiger_invoice.total, <br>  vtiger_crmentity.smownerid, <br>  vtiger_invoice.invoicedate, <br>  vtiger_invoice.duedate, <br>  vtiger_invoicecf.cf_1498, <br>  vtiger_invoice.invoice_no, <br>  vtiger_invoicecf.spl_payment_id, <br>  vtiger_invoicecf.spl_uuid, <br>  vtiger_invoice.invoiceid, <br>  vtiger_crmentity_user_field.starred <br>FROM <br>  vtiger_invoice <br>  INNER JOIN vtiger_crmentity ON vtiger_invoice.invoiceid = vtiger_crmentity.crmid <br>  LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = <a href="http://vtiger_users.id" target="_blank">vtiger_users.id</a> <br>  LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = vtiger_groups.groupid <br>  INNER JOIN vtiger_invoicecf ON vtiger_invoice.invoiceid = vtiger_invoicecf.invoiceid <br>  LEFT JOIN vtiger_crmentity_user_field ON vtiger_invoice.invoiceid = vtiger_crmentity_user_field.recordid <br>  AND vtiger_crmentity_user_field.userid = 1 <br>WHERE <br>  vtiger_crmentity.deleted = 0 <br>  AND vtiger_invoice.invoiceid > 0 <br>ORDER BY <br>  vtiger_crmentity.modifiedtime DESC <br>LIMIT <br>  0, 101;<br></div><div dir="ltr"><br></div><div>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.</div><div><br></div><div>This is the EXPLAIN output (where I can't really see any obvious improvement opportunities):</div><div><br></div><div>*************************** 1. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: vtiger_invoice<br>         type: range<br>possible_keys: PRIMARY,invoice_purchaseorderid_idx<br>          key: PRIMARY<br>      key_len: 4<br>          ref: NULL<br>         rows: 675575<br>        Extra: Using where; Using temporary; Using filesort<br>*************************** 2. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: vtiger_invoicecf<br>         type: eq_ref<br>possible_keys: PRIMARY<br>          key: PRIMARY<br>      key_len: 4<br>          ref: vt_sandbox.vtiger_invoice.invoiceid<br>         rows: 1<br>        Extra: <br>*************************** 3. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: vtiger_crmentity<br>         type: eq_ref<br>possible_keys: PRIMARY,crmentity_deleted_idx<br>          key: PRIMARY<br>      key_len: 4<br>          ref: vt_sandbox.vtiger_invoice.invoiceid<br>         rows: 1<br>        Extra: Using where<br>*************************** 4. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: vtiger_crmentity_user_field<br>         type: eq_ref<br>possible_keys: record_user_idx<br>          key: record_user_idx<br>      key_len: 10<br>          ref: vt_sandbox.vtiger_invoice.invoiceid,const<br>         rows: 1<br>        Extra: <br></div><div dir="ltr"><br clear="all"><div><div dir="ltr"><div>Any thoughts? </div><div><br></div><div>Right now the only solution I see is to avoid the "All" lists as also suggested by Tom.</div><div dir="ltr"><br></div><div dir="ltr">Saludos<div><br></div><div>Rubén</div></div></div></div></div></div>
<span>_______________________________________________</span><br><span><a href="http://www.vtiger.com/" target="_blank">http://www.vtiger.com/</a></span></div></blockquote></div>_______________________________________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a></blockquote></div>
_______________________________________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a></blockquote></div>
_______________________________________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a></blockquote></div>