Migrate slow v9 to v11

Hi friends; we are planning to migrate one of our stores to v11 as we have huge performance problems as data grew. On migrating from 6 to 9 there was a big improvement which started to dip after some months.

This thread is just to update and share any problems and also insight for those who have clients that are growing; so we may/will need help here, we will be upgrading using a clean install; and importing this database… hopefully that will make a lot of difference.

Some Environment Details:
4 high volume check out points (over 1,200 invoices)
5 slower checkout (restaurant, snack bar, hair salon, pharmacy, )

calculated mean number of daily invoices 1,387

Accounting entries: 779,704

Stock Ledger Entries: 1,870, 836 entries

MYSQL dump size: 2.8GB, contains only 3 months of sales data. Other sales were archived purged on 18th Nov 2018, that helped quite considerably to speed things up. Stock ledger wasn’t touched; GL entry table was archived and purged as well, so essentially this is 3 months worth of sales data

  • Perpetual inventory is turned off to help with performance

Computer
Core i7, 2.9GHz with turbo boost up to 3.2GHz, late generation core i7.
12GB RAM
500GB SSD Hard disk

erp_n03

Experience: Invoices take close to 1 minute to submit, even on local server, with no reports running. Most reports when attempted early in the day before cashiers start duty; do not display.

Worst situation is with sales at peak hours from 5pm when all 10 Tills are running and there are a lot of customers; problems begin.

I shouldn’t be worried about this because year after year these guys do not pay anything fr support; I am only looking at this so it doesn’t give room for bad testimonies.

Will update soon…

Have you checked your mysql performance documentation?

Thanks, all improvements done, I will post link to other topic on this issue. I think its the sheer size of data vs number of queries erpnext uses for each sale or stock doc submitted. Like I said, its the same on the local server, with nothing else running. If certain tables are cleared; it speeds up