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
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.
My advice. Have different instance per client. I am also facing similar issue but its only with one client. By having independent VPS the issue can be fixed easily. I have tried same as what you did. Increased all parameters, tweaked sql, heck I moved the client to another instance in hope that hardware is failing. No luck. Same issue cropped up in new VPS after migration of db. Itās got something to with how MySQL functions.
Can we come to gether as community and create document? May be we would need some technical detail from ERPNext, am not sure. The point is all major ERPs have sizing document. What make SAP run good is before you install SAP what to ensure you have the required hardware so they provide you with sized document. I have no issue working with some one to create size document with ERPNext support.
On ERPNEXT website you can find a whitepaper called " Scaling ERPNext". Please check it out it may be useful to find some idea related to scaling up the system
Iām using V12, around 3-5K sales orders/invoice with stock moving on daily basis. So my database grows rapidly.
Standard VPS digital ocean 8/16GB RAM 4/8 Cores
What I did to improve performance, beside increasing innodb buffer to 70% of RAM.
Refine and add more indexes on transaction tables if you create many custom fields/doctypes. This will be relevant as per your use case
disable/replace erpnext controller (located on erpnext.accounts.utils.general_ledger.py) which validating all stock records to cross-check stock is in balance with accounts, everytime on submitting stock documents. On early stage this is not detected but in my case, database grows very rapidly and this validation is nightmare for your instance performance
Regularly delete version, log tables. I only keep certain period of these records.
I move submission as background job process, because submitting 5K of sales Invoice daily surely will eat up your resources. It does help a lot in performance
Delete/archive old transaction records so your database keeps slim. I only keep 2-3 fiscal records
See delete_company_transaction method in erpnext, modify and put it on your custom app and pass date parameter so it deletes only transactions before this date e.g. 2 year backdate from today. Then delete glentry and stock ledger entry table linked with matched sales order, invoice transactions etc
Would you be kind enough to share your code? Many community members have been looking for ways to archive old data but so far no solution has been forthcoming.
If i may ask though, if you need to search that archived record how do you do it?
