ERPNext and MariaDB performance
Fixing report and request timeouts across a fleet of Frappe/ERPNext sites.
Context
A fleet of Frappe/ERPNext sites (v12 and v16) on MariaDB. Users hit report timeouts and general request timeouts.
What I did
- Turned on the MariaDB slow query log to find the worst offenders.
- Tuned MariaDB:
innodb_buffer_pool_sizesized to the working set, InnoDB log file and flush settings, and temp table and sort buffer sizes. - Rewrote report queries. The worst ones were recursive SQL, and I replaced them with queries that scale.
- Moved heavy reports to background jobs so they no longer hold a web request open.
- Adjusted Gunicorn worker counts and timeouts for the remaining request timeouts.
Outcome
- Reports that were timing out because of recursive SQL now finish in a matter of seconds.