ERPNext runs very slow

Hi @avaiskhatri

Have you installed erpnext using the easy install or the manual install ??

I have been using the develop version of erpnext on DigitalOcean for over a year now, with 2GB.

I had used the combination of the article below and manual install to install erpnext.

Have a look at the section for mysql.

though I have to say I have around 12000 products but no stock balance as we mostly do dropship…so your mileage may vary.

this is the htop at the moment

Its not report always which causes hang, infact when 2-4 users are on ERP and they click on random documents, like one is creating items, other one generating sales order and 3rd is on Work order part, It causes a delay/hangs for unknown time.

My question when a specific users is hitting any operation which may take a longer time, like reports are taking upto 7-9 secs, so other users shouldn’t be experiencing any delay/hang becasuse of this.

@centaur Now the system is behaving weirdly with other docuement type as well.

Too much slow on saving and submit as well.

Slow query log:

select
            sle.item_code, warehouse, sle.posting_date, sle.actual_qty, sle.valuation_rate,
            sle.company, sle.voucher_type, sle.qty_after_transaction, sle.stock_value_difference,
            sle.item_code as name, sle.voucher_no
        from
            `tabStock Ledger Entry` sle force index (posting_sort_index)
        where sle.docstatus < 2   and sle.posting_date <= '2020-12-07' and sle.company = 'mycompany'
        order by sle.posting_date, sle.posting_time, sle.creation, sle.actual_qty;

What version of erpnext are you using ???

@centaur

frappe 12.11.0
erpnext 12.13.0

@centaur I just checked that Stock Ledger entry contains 428,919 entries at the moment.

Bump.

1CPU with 2GB of RAM…really isn’t ideal for a multi-user environment.
If you have a relatively fast PC/laptop, try creating a VM on VirtualBox and giving it say 4GB - and compare the performance

I have updated to 8GB with 4CPU, but the response time is same like of 4GB and 2CPU.

Now the system is behaving weirdly with other docuement type as well, too much slow on saving and submit as well.

I am attaching current htop screenshot.

htop

@trentmu I am using Digital ocean VPS, with 8GB ram and 4 CPU.

hi @avaiskhatri

Which version of Mariadb are you using with Erpnext 12 ??

@centaur

Server version: 10.2.27-MariaDB-10.2.27+maria~xenial-log mariadb.org binary dist ribution

What about Gunicorn Workers? have you setup up 2*Number of Processors +1 as the number of Gunicorn Workers? And did you run bench setup supervisor and sudo service reload supervisor. That’s when the changes kick in I think. Even on those Mariadb config settings.

Hope this helps.

Thanks

Jay

1 Like

@JayRam

I am using 8GB with 4 core CPU for this:

File: common_site_config.json

    {
     "auto_update": false,
     "background_workers": 9,
     "dns_multitenant": true,
     "file_watcher_port": 6787,
     "frappe_user": "frappe",
     "gunicorn_workers": 9,
     "maintenance_mode": 0,
     "pause_scheduler": 0,
     "rebase_on_pull": false,
     "redis_cache": "redis://localhost:13000",
     "redis_queue": "redis://localhost:11000",
     "redis_socketio": "redis://localhost:12000",
     "restart_supervisor_on_update": true,
     "restart_systemd_on_update": false,
     "serve_default_site": true,
     "shallow_clone": true,
     "socketio_port": 9000,
     "update_bench_on_update": true,
     "webserver_port": 8000
    }

frappe-bench/config/supervisor.conf

[program:frappe-bench-frappe-web]
command=/home/frappe/frappe-bench/env/bin/gunicorn -b 127.0.0.1:8000 -w 9 -t 120 frap$
priority=4
autostart=true
autorestart=true
stdout_logfile=/home/frappe/frappe-bench/logs/web.log
stderr_logfile=/home/frappe/frappe-bench/logs/web.error.log
user=root
directory=/home/frappe/frappe-bench/sites

/etc/mysql/my.cnf

 **printf "$innodbBufferPoolSizeM\t$innodbLogFileSizeM\t$innodbLogBufferSizeM\n";#4DBG#**
**    4096M   1024M   512M**

@trentmu @smino @centaur

I have moved to local server, with 24GB of RAM and 8 cores.

Performance is 3 times better, reports are much faster but not as expected.

Previously it was taking 2.5 minutes (minimum) to complete a work order without any operation.
Just submitting the work order, start & end along with 2 stock entries ( one for material transfer and one for manufacturing), now on Local it’s taking around a minute.

Some one recommended me to use Sqltunner, the problem is it’s keep asking me to increase sort_buffer_size. read_rnd_buffer_size & join_buffer_size.

Right now, the values are:

sort_buffer_size: 2G
read_rnd_buffer_size: 1G
join_buffer_size: 1G

@Emmanuel_Anthony sorry to tag you, any recommendation for above issue.

Once your database becomes very large, the performance drop.
What I did was to truncate The Email Queue, global version and search history table. But first check the table sizes before doing the above.

1 Like

In my case the point we want to make the system to act quickly is during production process at the moment, (reports too obiviosuly) but manufacturing is continous process.

As per my detailed observation, system is taking time while creating stock entries, and the table is with around 0.5 million entries, and we can’t remove any data from it.

Any other recommendation ?

You can check Saurabh Palande, Server Management at Frappe Technologies for 2 servers option

https://erpnext.org/blog/announcements/developers-talk-from-erpnext-conference-2017

image

credit to @Huawei_Hackace

3 Likes