I’m encountering poor performance after upgraded from V10 to V12.
Submitting stock transactions via Stock Entry, Delivery Note, Sales Invoice is very poor…it takes 20-30 secs each. It doesn’t happen for other than stock transactions.
My server is 8GB RAM, 4 cores. Innodb_buffer is set to 6GB, 4 gunicorn workers.
It should be enough…in V10 everything was working very normal
show full processlist on mariadb console showed every stock transaction doing this query:
SELECT item_code, stock_value, name, warehouse FROM tabStock Ledger Entry
sle WHERE posting_date <= '2020-07-31' AND warehouse = 'XXX' ORDER BY timestamp(posting_date, posting_time) DESC, creation DESC;
It iterates over all warehouses.
tabStock Ledger Entry has 1M+ records currently
Also added index for (posting_date, warehouse) but doesn’t help
Is there special database config for V12? Anyone has clues? Thanks