ERPNext Running Very Slow

[rmehta]

Setup slow query log and share your results.

[keshav]

Here is the log after the creation, followed by cancellation and deletion of a Sales Invoice .

# Time: 180103 18:09:03
# User@Host: 1bd3e0294da19198[1bd3e0294da19198] @ localhost []
# Thread_id: 220  Schema: 1bd3e0294da19198  QC_hit: No
# Query_time: 10.126399  Lock_time: 0.000042  Rows_sent: 2  Rows_examined: 434303
# Rows_affected: 0
# Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes
use 1bd3e0294da19198;
SET timestamp=1515002943;
select name, owner, creation, data from `tabVersion` where `tabVersion`.docname = "INV/MAA/09222" and `tabVersion`.ref_doctype = "Sales Invoice"
                          order by creation desc limit 0, 10;
# Time: 180103 18:11:16
# User@Host: 1bd3e0294da19198[1bd3e0294da19198] @ localhost []
# Thread_id: 260  Schema: 1bd3e0294da19198  QC_hit: No
# Query_time: 10.149511  Lock_time: 0.000030  Rows_sent: 3  Rows_examined: 434306
# Rows_affected: 0
# Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes
SET timestamp=1515003076;
select name, owner, creation, data from `tabVersion` where `tabVersion`.docname = "INV/MAA/09222" and `tabVersion`.ref_doctype = "Sales Invoice"
                          order by creation desc limit 0, 10;
# Time: 180103 18:22:59
# User@Host: 1bd3e0294da19198[1bd3e0294da19198] @ localhost []
# Thread_id: 320  Schema: 1bd3e0294da19198  QC_hit: No
# Query_time: 13.235107  Lock_time: 0.000026  Rows_sent: 0  Rows_examined: 434303
# Rows_affected: 3
SET timestamp=1515003779;
delete from tabVersion where ref_doctype='Sales Invoice' and docname='INV/MAA/09222';

[rmehta]

As a quick fix, disable versioning for sales invoice, since there is a save/submit trail.

Also create a bug report on GitHub. I am surprised docname is not indexed!

Cc @nabinhait

[keshav]

Thanks @rmehta. Will open the github issue and disable versioning for Sales Invoice.

I found the issue with mysql not picking up the settings for innodb_buffer_pool_size , instead of picking it from /etc/mysql/my.cnf it was picking up the value from /etc/mysql/conf.d/frappe.cnf, which I went ahead and changed to 10G.

Now the system is performing much faster. Though the issues indicated by you still remain.

[sjain]

Version Table is still having the issue and keeps on slowing the system. I have 8.8 Million lines in my table and the system is taking a lot of time to save any document.

Any suggestions would be appreciated.

[keshav]

Please take a look at this post. You can delete the rows in the Version table.

Is there any data loss by deleting version list?

I have switch off versioning using customise form. However it take much time to find out which table is using large size in database. [untrackchanges] I am sharing query for those who want to check each table size in database. SELECT TABLE_NAME AS "Table Name", table_rows AS "Quant of Rows", ROUND( ( data_length + index_length ) /1024, 2 ) AS "Total Size Kb" FROM information_schema.TABLES WHERE information_schema.TABLES.table_schema = '4c4d239fbd480c8d' order by ROUND( ( data_length + …

[sjain]

After deletion of data from the version table also there is no effect on slowness. It is still dead slow even saving expense claims is taking more than 30 seconds.

[SubhajitDey]

which version are you using ? How many records in version table ?
Can you make sure there is a index on ref_doctype+docname for version table ?
Regards,
Subhajit

[sjain]

I am using version-13 frappe and erpnext both. I have deleted all the data from Version table already but still loading and data writing speed is insanely slow.

[SubhajitDey]

Sounds like different issue, not because version table.
I haven’t tried v13, but you might wanna check the gunicorn/background worker setting.
Also can use recorder app to find out the performance bottleneck

[Jermaine_Gray]

Am having the smae problem with version 13. I ask for a recommend size document and someone told me to get more CPU and RAM. Am runn the same amount of user in a t2.medium instant in AWS. Am current building a new server with 20 vCPU and 30Gb RAM. am concern this will not fix my problem.

[moe01325]

is it running slow all the time or just sometimes? how many users does your system have?
i am currently investigating a similar (?) issue: How to pinpoint CPU spike? - Developers - Discuss Frappe/ERPNext

[sjain]

@rmehta I identified one major issue / bug in connections (Dashboard), if the number of records in Journal Entry are huge then system search becomes too slow as everytime you open a doctype with Journal entry in connections it starts searching for the same record in system and it slow downs the system.

I have experienced the slowness major into Expense Claim, Employee Advance, Purchase Invoice, Sales Invoice (All Doctypes having Journal Entry in Dashboard/Connections)

After commenting Jounal Entry from Dashboard the submission of document showed a tremendous change i.e. from 600+ seconds to 26 seconds.

Please help in understanding the slowness in search?

Below are some references -

1. 32 Core CPU
2. 64 GB RAM
3. Intel e7 Processor (Physical Server)
4. Ubuntu 20.04
5. ERPNext: v13.5.0 (version-13)
6. Frappe Framework: v13.5.0 (version-13)
7. Journal Entry Count - 2.3M+
8. Purchase Invoice - 100k+
9. Company - 96 (Multi Company)

[Not_a_countant]

The general rule here is not to tag anyone. If you feel someone MUST help you because your problem is very important, then you need to pay for it. If you want ERPNext to fix your issues, here is a link you can sign up. Best wishes.

ERPNext for Enterprise

[sjain]

My intend was not to tag anyone or pinpoint anything. However, I was trying to seek help and have found a major issue and that’s why I tagged. And I wasn’t aware about tagging policy as well. So please take it otherwise.

[Jermaine_Gray]

Thank you for share that link. I try meny time to contact ERPNext for enterprise support and no one reply to my request. We are are running a huge instant and you are having problem all you want it help or some one to fix it for you. Am having the same problem with my server. I just recently increase the resource in the data center and thing start working better however we cant just increase resource everytime. I as if there is any white paper on how to sized erpnext server but did not get any answer. This will help with ensure thet we alway have the correct hardware requirement.

My new envoirement is:
ERP App Server
-8 CPU (65 vCore)
-240 Gb RAM
-SSD
MySQL Server (Galera Cluster)
-6 CPU (21 vCore) x3
-81 Gb RAM x3
-100 Gb SSD for Data Only x3
-50 Gb SSD for OS x3
HA Proxey
-6 CPU (21 vCore)
-102 Gb RAM
-SSD

[olamide_shodunke]

That is a huge set up!!

Are you serving the whole of Jamaica!!!

Lol

[Jermaine_Gray]

I have over 20 clinet runnig a minimum of 17k SKU with about 100K transaction per day.

[olamide_shodunke]

Impressive, but still…that is a huge server for that number of instances.

Maybe the concurrent user count is in the thousands ??

[Jermaine_Gray]

No it not. but i also want room for growth. And my customer have millions of records in there database. So far it working better after adding more hardware but am not sure if when i add more customer and more data it will start slow down again. This is why i need to undersatnd the size requirement so i can know hown how to invest in the hardware.