As you mentioned:
The 7 x simultaneous Cashier SQL query is one bottleneck. But how the sales invoice SQL query plays with all the other current back end SQL queries is probably a bigger bottleneck.
As all your other Doctype submissions are fine I wonder why sales invoice SQL gets locked out and chokes. 30 sec to only have the sales invoice fail is in my opinion non functional. Your servers are ridiculously well specced.
It would be a major pain to upgrade but it would be interested in seeing if V9 POS (and all other transactions) are faster or slower than V6. If you could setup a V9 test bench and V9 is much more efficient then maybe sell your client the new V9 Super Software Upgrade Plan ! They don’t like to pay for IT support so bill them for new software. Good Luck
Silly Idea:
Are all the cashier tills down one end of the shop? If so are they all strung off one crappy 10/100 switch or something. Have seen networks like that.
Thanks Rick; its most crucial for the cashiers because that’s the customer interface; but even stock people have an issue submitting purchase rcpts especially when they’re a few days old as draft, it just becomes so tough injecting the purchases and balancing out against sales and all over a few days period; its a lot easier if you’re submitting for the current day; so at times they’re advised to bring the date current and it submits.
For switches; everything is gigabit with cat 6 cabling. Network is very fast; we also made new cables where necessary.
For upgrades; I thing the v9 pos is still in the works, correct? If it’s ready I could think of that, it would be a royal pain to upgrade though lol.
Today I took a copy of the vm so I can do some of the suggestions offered here; I’ll update.
@System19 now if we used the period closing voucher to close a period and enter the profit or loss; does this imply that the system will not dig back to check balances to make payment entries? I may be thinking weird stuff; I’m thinking if we can close the period so far, it may help to reduce the balancing and look ups done; I assume this will help the invoices post with just the period opening balances so it may speed things up; because the issue is actually the script analyzing so much data just to conclude a sale. What do you think?
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.
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!
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.
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.
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.
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.
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
@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.
