cd ..

ERPNext and MariaDB performance

Fixing report and request timeouts across a fleet of Frappe/ERPNext sites.

MariaDBFrappeERPNextGunicorn

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_size sized 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.