Try ERPNext Buy Support Partners Foundation

Slow query using v10 Shopify Connector

Hi I have a customer on v10 and am using Shopify Connector.

I am encountering a slow query performing the following in any doctype:

Select name from tabShopify Log where status=Queued order by modified desc;

How can I rectify this?

Hi a search like ‘mysql performance’ should land you assorted key pointers and clues here!

Hi Clarke, I did. Increased innodb buffer. Problem is there is no status column in tabShopify Log to do indexing.

I need to find where this query is called to see where is the problem.

FYI I have 2 sites on 1 machine and the other one runs just fine.

Try a search like this:

frappe@ubuntu1804lts:~/frappe-bench$ find apps/ -name shopify_log.py
apps/erpnext/erpnext/erpnext_integrations/doctype/shopify_log/shopify_log.py
frappe@ubuntu1804lts:~/frappe-bench$ find apps/ -name *.py | xargs grep resync
apps/erpnext/erpnext/erpnext_integrations/doctype/shopify_log/shopify_log.py:def resync(method, name, request_data):
apps/frappe/frappe/event_streaming/doctype/event_producer/event_producer.py:def resync(update):
1 Like

Thank you boss!

How and what did you do to resolve this? Whatever specific notes or pointers you contribute - say for eg to add an index - will help all concerned and even remind you too some day :wink:

1 Like

Hi,

Server is Ubuntu.

There are 2 parts to this, database settings and the table itself.

  1. Increase innodb_buffer_size to recommended 80% of your total RAM. Restart mysql service and make sure that your changes stays. If not, you have to find out where is the config for your database.

  2. Try to have a swap space, as mine is 1GB RAM.

  3. Check mysql slow query log and identify the slow quey.

  4. Add index to affected table, in my case, it is tabVersion. The affected query has 2 where, ref_doctype and domain so I used multi-column indexing with ref_doctype (docname, name, …)

Now, the select queries improves from 30s to 1s

Ref: https://github.com/frappe/erpnext/wiki/ERPNext-Performance-Tuning

Thank you!

1 Like