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
@noetico Install NewRelic APM (you can use their free plan) and see where the slowdowns are. Its been extremely helpful for us.
Ok; started say 6 months after. But that’s because of their rapid growth; we have over 13 more clients with nearly 3 years of transactions even on wireless and they’re still good and going. Just these 2 with very high sales.
Then; it affects submission of invoices, pos or form view, also old PRs an POs take a while, same day; maybe 5 to 10 seconds to submit; I’m particular about pos cus that’s whr speed is critical. It’s really slow on every submission due to the size of data; a lot of queries as @ganas pointed out; the queries haven’t been optimized for bigger businesses with thousands of items. Mind you this is still medium size afaik.
No; ok; the client with 6 locations is not affected; not at all; they’re running strong; in fact theirs is still a v4 installation, running in their own pentium processor server (basic system). With the other branches linking by public ip address. Their total sales is over 1.3 billion naira for the past 3 years; impressive; but the difference is; its not supermarket style retail; its furniture; so they may not have up to 20 to 30 invoices daily; they have high value and slow paced items so everything is cool.
ok thanks, will do. though this is a local installation.
v4 is still a very lovely implementation, solid performance and quality, they also have huge item list with images on many and a lot of stock entries made moving stock around, big accounting figures and transactions, a lot of pending invoices as they have credit sales etc![01 AM|612x500] over 20 cashiers; all running from a pentium processor, windows 7, 4 gb ram, 7200 rpm hdd, this system is a ‘toy’ compared to the one running v6, yet it handles this so well and I don’t even hear from these guys in a whole 9 months. runs like a horse, obviously the best build when you talk about quality and performance. Pic attached:
1 Like
v4 looks so good, so ERP!! why didn’t we just build on it lol lol 
I might have skipped this info from the thread but can you check the slow logs in mariadb.