ERPNext slow after months of transactions on v6

ERPNext
81

@ganas: I hear you and I agree. But with another client, upgrading did not exactly solve the problems. The slowness continued.

@noetico: If your client is okay with it, we can take a backup of your client’s data, restore it on a VPS and give @Chude_Osiegbu the opportunity to play with it directly after we upgrade it to the latest version.

This is how an ERP system will get used. The hope is that our SMB clients grow their businesses manifold and ERPNext needs to enable and empower that growth. We cannot and should not be the bottleneck. Not saying we are, currently, but we can imagine being the bottleneck if these kind of performance issues continue.

You have my and the Foundation’s support to expend energies and resources to solve this problem.

@noetico: Let me know if you can give us your backup files.

@Chude_Osiegbu: Please let me know if you have the bandwidth to spend time on this. If not, let me know if you are interested in working on this and I will work with Rushabh to free up your bandwidth.

Thanks

Jay

2 Likes
82

@ganas totally agree.

83

@noetico can you setup slow query logs in your system so we get an idea where to optimise?

(In a new thread)

1 Like
84

Having gone through the V 5,6,7,8,9 update process I recommend:
1: Take a complete OS snapshot of your slow live v6 ERPNext instance
2: Export your Customer, Items, invoices, Prices…basically all of your large tables via mySQL to .CSV files.
2: ssh into slow ERPNext instance and
cd /home/frappe/
sudo rm -rf frappe-bench
sudo rm -rf bench-repo
DROP your current DB…Yep I said it DROP your DB.
sudo yum update/upgrade server OS
Create a fresh new V9 install and make sure you select the correct domain option during setup wizard
3:Enter company,email, tax settings etc etc… All this is pretty easy… Takes 3 hours tops including snack breaks.
4: Export new V9 Doctype templates as CSV files to desktop. Copy and past all old Customer, Items, invoices, Prices.etc etc data into correct new columns. Import into V9.
5: Create snapshot of V9 instance.

I reckon this will be quicker than bench update due to massive changes in frappe code since V6 and up resulting upgrade issues.

With cash register sales taking 20 sec and sometimes failing you will have no problem billing the client for time spent on this
If the client can not see the value then run, run as fast as can away from then.

1 Like
86

SOLVED BY COMMUNITY, SOLUTION DETAILS BELOW

Thanks to everyone who helped with this, this is indeed a great community. Refer also to:Performance Upgrade, moving slowing install to v9 - #26 by noetico

We upgraded the instance to v9 as follows:

head to the terminal and navigate to frappe-bench folder, prompt should look like
frappe@erpnext:~/frappe-bench$

install psutil if absent or fails…

sudo pip install psutil

remove nodejs, sudo apt-get remove nodejs

install nodejs v6 or v8, the newer the better
– install curl, sudo apt-get install curl
THEN…
curl -sL https://deb.nodesource.com/setup_8.x | sudo -E bash -
THEN
sudo apt-get install -y nodejs

bench update --reset
You may have errors with new columns in doctype, add columns and proceed…
so… from frappe-bench do
bench mysql

THEN… these 2 were needed for my case… so…

ALTER TABLE tabDocType ADD COLUMN restrict_to_domain VARCHAR(40)
ALTER TABLE tabDocType ADD COLUMN in_dialog VARCHAR(40)
THEN optionally:
7. bench build
THEN
8. bench migrate, [not recommended, but I found no issues yet] skip carefully patches in apps patches.txt files found in apps/frappe/frappe/patches.txt, similar location for erpnext patches. ** The skipping proved a necessary evil, because I realized this is the reason I never got any updates to work** thanks to @H_N for this insight. So edit document, save and enter bench migrate again, update will progress.

once done, you’re ready.

depends on your data, some patches will take a while, like some invoice and inventory patches such as adjusting valuation for negative inventory, took a while due to the sheer amount of negative inventory.

System was greatly improved and resilient
Testing data:

SOME TESTING UPDATES

System spec: core i5, 2.3GHz (2 cores allocated to vm), SSD HD, 8gb RAM allocated to VM

Data Summary:

Database size: 1.19GB
Items: 25,562
Item Price: 40,145
Sales invoices: 188541, items in SINV: 438,908
Stock ledger entries: 461,704
GL Entries: 1,050,311
Items in the feed table: 374049

Performance estimates, will depend on number of items and posting time for any transaction:

Submitting Sales invoice: 3 seconds average, 8 to 15 on v6
Submitting old PRs and POs: 4 seconds average, 12+ on v6
submitting new PRS, POs: 2 seconds, 5 to 15 on v6 (depends on items)
Cancelling old Purchase Receipts, up to 1 week old or more: 21 seconds+ (still cool for such an intensive adjustment)
, very good AFAIK, any improvements needed team?

SYSTEM IS GOOD TO GO, WE WILL UPDATE ON CLIENT SITE AND UPDATE AS WELL

7 Likes