ERPNext slow after months of transactions on v6

Update: see

Hello, I have 2 clients in the retail segment, they have been using erpnext since Feb this year, for their six and location they are making good sales, 1 makes an average of 3000usd daily sales (sadly I have no retainership or contract save the initial 400usd charge to setup and train staff :frowning: )

Over time the system has become very slow, most painfully, submitting a sale from pos can take 18seconds! causing queues to build up especially in the evenings when theres a high footprint. we got a Dell virtualization server for one of them as they run on a vm and to my amazement, it was still slow, I understand the former serveris a 2.9ghz core i7 and this is an 8 core xeon at 2.5ghz.

its my thinking that the code needs to be optimized for heavy transactions. i dont believe mariadb should be burdened so heavily with this amount of transactions.

Negative stock.
Like most stores, keeping accurate balances is a major challenge, so theres negative stock, a lot, can this put pressure on the code trying to balance the ledgers for negative inventory?

We have tried to optimize the mysql database and increase the resources available to it and it helps very little.

keep in mind that running queries directly against the mysql from terminal is snappy, i have renamed or entered new data for 20k+ lines in seconds, but submitting a purchase reciept of 20 items may take minutes and sometimes fail, databases truncate easily but clearing company data from accounts for instance when transactions have piled up is nearly impossible so we manually empty tables and ensure we dont create errors.

removing old data will help but that defeats the aim of an erp system so I’m thinking as there’s work going on, the teams can also look at how to make erpnext faster. the client is looking at a turbo boost core i7 that can hit 3.6ghz and i’ll update when they have that.

What kind of daily volumes are you talking about ? I mean receipt’s issued

I’ll have to check that part but anywhere between 200 and 700; I can assume. I’ll visit them tomorrow to get that piece of information. but that shouldnt hurt a mysql database.

All your terminal mysql commands are snappy while all client UI commands are slow?
Other than frappe have you checked out all your network connection speeds/stability during rush hour?
Server sounds new and flash and I guess its got lots of ram so thats not an issue.
Check ERPNext server usage/apps/ram/network graph stats.
Check browser activity monitor and ram
Do you use a VPN?
8 Core server and mysql should handle this job and much more.

If bench is the resource hog I guess try
bench clear-cache
bench clear-website-cache
And see if issue returns,

Is ERPNext initially fast after the first login at the start of the day then progressively getting slow?

I think this is related to browser and internal cache exeprienced same issue in odoo pos.
Maybe free cache browser solve problem.


Thanks for your response, we clear the cache all the time, it’s not the the browser cache, also note that this is v6, little to no dependence on cache AFAIK. Regardless, we always clear the cache, its worse on submitting sales; I also appreciate erpnext making deep checks in accounts and stock to submit the sale, but it has become very slow.

Yeah. Exactly what I suspect. Can be temporarily fixed by restarting the browser.

… also, we got an 8 core server (16 core total) and gave 8 processor cores to the vm and yet the system was very slow. Note that the hard drive is an ssd. Like I said; running a query, even against over 20k items finishes in seconds but running stuff from erpnext grind slow.

Yeah. That’s why I’m suspecting the UI

Nope, far from it, trust me, I’ve used erpnext for a while, this is not browser cache issue. It happens when they have high customer volumes. Its purely the script. The current server is a core i7 and that helped shorten submission to about 11 to 13 seconds, the new xeon was like 21 seconds. For now I’m proposing a turbo boost 3.6 ghz quad core i7. Erpnext loves fast processors.

looks like some configuration issue.
In my case, most of time Regenerate-Production-Config-Files-solves our problem.

Can you post output of sudo supervisorctl status

Now let me also present a few points on their daily ops, 7 cashiers work in different sections, supermarket, restaurant, pharmacy, ‎a small snack bar. A total of about 5 people take care of inventory (purchases and receiving). 3 accountants do periodic sales checks and pull reports for instance on a cashier’s close of shift.

A total of about 21k items exist on the system with balances for over 10k of these. They do their best to run the store with all the available features erpnext offers. So they have piled on a lot of transactions.

For the second client, we just had to back up everything they had and reset the transactions. Submissions became instant. No cache needed to be cleared

Ok. I will touch the store tomorrow and get that status posted here. Can you give details on how to use this config regeneration feature? Haven’t used it before. Thanks.

And RAM… with lots of users throw a decent amount of RAM in your server. Although this doesn’t sound like the issue here, Lots of RAM is always good :grinning:

Thanks Rick, we have 12gb or ram and we gave the vm 8, on the dell we have 32gb or ram, storage is also ssd which is extremely helpful. If you use htop from terminal you will see its using just about 1.5gb; and we have extended mysql to use a lot of resources. Like I tried to explain; running queries from mysql prompt is superfast; but submitting docs is slow.

I also appreciate the fact that submitting sales in erpnext is a complex transaction; it checks a lot of things against other doctypes and all conditions must pass. If there’s no way to improve the script then we’ll just go head-on with getting an even higher processor a turbo boost i7 at 3.6ghz.

Woah… 32GB of RAM should cover your requirements… And then some :laughing:
If you are forced to sledgehammer the issue with a faster server are any of the new AMD Ryzen/Threadripper/EPYC offerings worth looking at? You get a few more cores for the same price as Intel provide.
Will wait and see what Regenerate-Production-Config-Files does.

At the end of the day …what’s the problem? Why is so slow?

I might be wrong but I do not think RAM or processor speed is your problem. My experience with ERPNext does not show it’s a resource hog. I have another solution that’s Oracle based and 32 GB RAM would be more than enough, and that solution is a resource hog.

I think you need to search a bit deeper to find the proble. It is not system resources.