Sharing innodb config
innodb_buffer_pool_size = 1024M
innodb_log_buffer_size = 8M
innodb_file_per_table = 1
innodb_open_files = 800
innodb_io_capacity = 800
innodb_flush_method = O_DIRECT
so I will increase to 4000M or 4G and see what difference it makes
This is waste of everybody time trying to investigate bottle necks and performance issues in an old version 6. If you are serious about this issue, then set a testbed with a copy of your database in v9 and report back based on that. This is the real objectivity we are looking for; otherwise, ERPNext has thousands of new code lines and fixes since the version you are running on and your issue may not exist now.
I disagree with your statement. Understanding the bottlenecks and performance issues on V7 will help in identify similar issues on V9. Yes, there are thousands of new codes and fixes that is now V9, the fact that remains is that the core code has not changed. ERPNext V9 is not a rewrite from V7.
Please continue to work on resolving the issues on V7 or V8 or whatever version as it is and will be helpful in solving future problems with V9 on onwards. It is appreciated and will help the community to learn more about the optimal configuration to get the maximum performance from our limited hardware.
Just my 2 cents.
Thanks you.
Sure. Will do that. But the problem is updating the live clients. You know that’s not going to be easy right? 6 to 9, let’s be practical; in business it’s like that sometimes; its hard to upgrade; sometimes you need a full day shutdown, even if I can do this locally to test; how do I do this for the clients? If I export a .sql file; can someone here help me through teamviewer to restore to a v9 installation?
Like windows xp; some people couldn’t leave it and still can’t. Some clients can’t take 1 day down time.
I don’t think it’s right to abandon older versions of a business software like this. Version 6/7 is not so old that we should all abandon, can’t we fix issues for people running these versions? For instance operating systems are supported for years; if people have issues with win 7 ms doesn’t tell them to go to 10, there are still updates for 7, 8 and 10. Fact.
@noetico oh you also on V6 so worse than i thought, you basically 3 versions behind. I’m not asking you to update live system, and that also not the way you do it. I’m asking you to take a snapshot of your system, then in a test environment update this snapshot to v9 (this will also help later when migrating your live system) and report from there.
No thats not realistic, you can’t expect small frappe team and limited community to maintain performance fixes for three old versions. Thats why you put all your customizations in separate app so you can update easily with new versions, or if your customization are generic you have more advantage of pushing them to the core with a Pull request. Sorry, but in my opinion there is never a good reason to stay on old versions except bad integration of customizations or bad implementation structure.
@H_N No totally disagree with you. For example, look at the current sales_invoice.py history changes, and you realized that more than 35% of this file has changed since v6 and this code called dozen other code files that also changed. So going back trying to optimize v6 code will not over you great help in fixing v9.
@ganas I hope you know that updates have been very painful in the community; even minor updates. You end up with internal server error 80% of the times. Then you need someone very good at the core to fix it. Restoring sql backups also does the same; last one was within v7 to v8, Bobby had to help finish the update because it broke; in fact he had a real battle where mysql kept going away until he edited some files in the update script.
increased innodb buffer size to 4Gig, no noticeable difference, to test, I set it as low as 125M, restarted, made a few sales, and got the same 8 to 12 secs (depends on number of items in cart!! keeps getting more interesting). I put the slow queries lines in myself section of my.cnf, I can’t get any logs outputted to the file. am I getting something wrong?
I CAN SET UP A TEAMVIEWER SESSION IF ANYONE WANTS TO HAVE A LOOK INSIDE
Just FYI, I have tried updating this image before; it failed and ended with internal server error; I couldn’t solve it. It asked me to stash 4 lines of code in the pricing_rule.py or reset it. Bench reset --hard or so; that too failed. Don’t know what to do. I tried to also update a v8, clean v8, untouched. Failed. Bench update --upgrade
open a new topic and include the error logs and we will gladly help you workout the update. so basically you will be updating version by version so all the patches get applied v6=>v7=>v8=v9
Ok; let’s close this one if we all agree its over stretching. I can also drop the sqldump of this site so the masters can load it on v9; the full data as is and then see how it performs, I don’t have the tech savvy for that lol.
Ok, I’m closing this for now. @noetico you can open a new topic so we can help you update your test environment to version 9, and then we can carry the performance dissuasion from there.
@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
@noetico can you setup slow query logs in your system so we get an idea where to optimise?
(In a new thread)
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.
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