Also friends; we had made certain small adjustments to reports, like the sales register, we edited the report to pull in just the current day on loading, same with stock bal, stock ledger, etc; this was a great relief. Because the way the reports are designed to pull the full year of current month is a serious resource hog; months ago; if anyone just launches a report; the entire system hangs up. Setting the reports to current day only; helped. I think this should be done as a standard because data grows daily; let the user choose the range; this way a decision can be made to do big reports say; at night only or first thing before trade begins and run daily reports with little impact on critical customer transactions.
IMPRESSIVE UPDATE
So I did as earlier hinted:
Set a new fiscal year from today; closed and disabled the previous fiscal year. Chart of accounts are zeroed for the new fiscal year.
Submission time has been cut to 6 to 8 seconds from 19+ seconds!! So this is a life saver. I’ll implement this fix for the client and see how live sales go. Already excited!
Thanks all.
What have you done or considered to profile your bottleneck suspects?
This may help see if your performance problem is with slow db response
How to speed up your MySQL queries 300 times
You could add a timer to identify suspect functions - this discusses several tool options
To profile memory usage might help you get a handle on your problem - A module to profile peak memory usage of Python code - Stack Overflow
Once you collect response times and table size counts in a systematic way, you will have baseline measures to objectively gauge results or your attempts to improve performance.
Still 6 to 8 seconds is quite slow
And you had to start a new fiscal year mid year? That does not sound like a sustainable solution.
Do we have any one using ERPNext in a transaction heavy environment that can let us have another opinion? None of my sites is data heavy or even up to 6 months,
It will also be nice if the ERPNext team can chime in on this matter.
Regards
In fact, 6 to 8 seconds is actually slow but coming from where we are, its heavenly.
Yep; we have to do a financial year; its better than the drag; after all we can always select dates for reports. So this ‘evil’ is better.
6 to 8 seconds is quite slow - Agreed
A max 3 Sec load/save time is about the slowest you would want to go without all the users wanting to kill you. The errors you are experiencing are varied and not immediately connected. (Cravat I have never seen frappe/erpnext dealing with a workload this size. And its an old version.)
Possible Ideas?
1: I would keep the current financial year. - 365 days is nothing. Retail shops often “cash up” at the end of the day. So treating accounting on a per day basis sounds like it helps. Then enter it daily into a 365 day second master frappe install.
2: Run two copies of erpnext. One purely cashiers, One Back end. Automate after hours one way cashier to back end sync.
3: Check this discussion out. @ganas is doing this currently. Looks well thought out.
Supply each cashier with a copy of this on a tablet. It only sends cashier data. Could limit some sql traffic. Have a full install of frappe for back end.
V9 is full of very cool features and is progressing very rapidly. Some features are diamonds in the rough/unfinished. Once a page/module has been opened once that day they reload fast. Good caching somewhere. For you I don’t see any option but to spin up a V9 instance on one of your many servers and test it. Can’t be much worse than now. Yes upgrading could be a major PIA. But maybe not??? But probably yes.
Wow; sounds nice! Let me have a look. Thanks a whole lot. Thanks.
ok so closing the period and using a fresh fiscal year was like an axe down solution but its better; let me share some images from htop, now we have the processors peaking at about 97.3% when submitting a sales invoice and its between 8 and 11 seconds, less than half what it took on this same machine before the period closing. the mysqld service is the main consumer here. This a 2.3 Ghz processor on a Mac mini, core i5; I have made the same updates to the store tonight after closing so I’ll see how it goes; hopefully it will be a lot better on the 2.9Ghz processor; I’m hoping for 4 to 6 seconds; keeping my fingers crossed.
This is reading like a “super story” saga. 97.3% processor utilization just to submit an invoice? WOW! and still takes 8 seconds?
It will be quite interesting to know what is the main problem here.
8 secs while maxing out your server points towards a major install issue…either that or a major underlying frappe/erpnext code issue. I would have thought other ERPNext users would have reported this slow speed issue as well.
How many concurrent users at the time of test?
As you have been moving/fixing data via xls and DB Gui I would strongly suggest installing V9 and testing with a fresh new Company account. The setup shouldn’t take too long as all the data details exist in your current live production instance. What you are trying to pat
Thoughts:
(Probably not related at all)
1: You don’t have large (say 1MB) images attached to each item do you?
2: mariaDB type was changed about a year ago to an updated version so create a fresh new install for V9.
Thanks as always everyone! @System19 we didn’t make any installation changes, no configuration updates, at the core we have official release configuration.
Point is you don’t have this issue until your data and transactions really grow. If I delete the sales invoices and even the stock ledger entries, it will solve the issue; but that’s not a solution; just saying; although one client had to take this path; immediately we backed up and cleared their 2+ years of transactions (using truncate queries) leaving only master data; system was perfect and fast again.
For number of users during this test? Just me!! On my core i5 2.3ghz mac!
SOME INSIGHTS INTO DATA SIZE
Images: none at all.
Total for item master: 20k+ skus
Total for item price master: 40k+
Sales invoices estimate: around 500k+ invoices.
Stock ledger entries: I will check but you can tell it will be extremely large
GL entries: extremely large.
It would appear we are crossing the small/medium scale business segment of erpnext; are we?
But we’ll get the new core i7 system and see. For now; our closing voucher and new fiscal year has given us plenty of improvement. We’ll be keeping tabs on it.
Its an interesting problem. Am just trying think of a way of testing that would lead us to the bottleneck. All the while hoping it is just a single choke point. Say if the issue is caused by querying/loading all the items and prices into POS then the link above to @ganas apps might be able to cure the problem by off loading that process to SQLite in the apps. But its just as possible that its caused by the warehouse and accounting reconciliation with every transaction. In which case it becomes more complicated to seperate the POS process from the ERP backend. Good Luck.
There are some quires in the system that are overkill and their running time will grow exponentially with number of recorded in the system. Their performance is tolerable in small database, but as your data grow the performance will degrade. For example, see my post here where this query will be run N times (where N is the number of customers) just to get their primary addresses. Also sometime the design always consider small database; for example, in my post here the API for syncing master data in the offline POS will always return the whole data every time you sync instead of just returning the differential data. Fo a system with few hundred items, customers, …etc it not a big deal but when you start growing to thousands like in you case, imaging how your system perform during syncying
2 Likes
@System19 exactly; its all the accounting and warehouse checks, validation and entries, coupled with checking for any pricing rules, customer terms etc etc. So it gets overwhelming when the data grows. That’s why I just think it can be tweaked a little; our fastest route is to get the best core i7 from ebay and then keep going; let me also hop someone on the team can have some inputs here. I will keep updating the thread.
Thanks @ganas you just confirmed my conclusions, too many checks and validation runs before submission. So in this case; our current direction is good for an immediate solution. Thanks.
It is really hard to tell in your case since you are two versions behind, I’m sure many things have been optimized since. why don’t you to import your data on clean v9 version and do real test and benchmarking there.
Yes, I’ll do it, will be tedious lol, but I can’t use v9 non production; I will import just to bring useful feedback here, except the online pos has been perfected, the pos is why I stayed on v6. Its a solid version, serving extremely well just these shops that have grown so large slowing down. Many of our clients have dumped quickbooks and sage for erpnext and they keep loving the feature set.
1 Like
I was shocked at the instability of v9 offline pos and the dependence on an open source, user accessible cache. but once we cross this completely in the community; I can migrate the clients. I will get v9 and confirm the online pos has no dependency on the cache especially for critical things like the invoices themselves or even prices!
Can you confirm at what point you started noticing the slow down in invoice submission, was it the first or third month of operations?
Do you know if this also affects submission of non PoS sales invoices ?
You said one of the affected clients has six locations, are all six locations using one database or do you have each location on separate database ?
If on one database how have you structured access by the individual locations?
Regards