Slow ERPNext? Tune MariaDB With These my.cnf Lines

A General Ledger that takes 40 seconds on an 8-vCPU cloud server is rarely a hardware problem. It usually traces back to the same few untuned InnoDB settings. I lead ERPNext and Frappe programmes at MithTech, a team of Frappe specialists in Bengaluru. This is the tuning pass our team runs on a self-hosted instance before touching anything else.

What should your my.cnf say for your server?

Type in your VPS RAM, your current database size and your realistic peak of concurrent users. The calculator writes a drop-in .cnf file from the sizing guidance in the MySQL manual on the InnoDB buffer pool and the long-running Frappe forum thread on ERPNext slow performance.

# /etc/mysql/mariadb.conf.d/60-erpnext.cnf
# Generated for 8 GB RAM host, ~6 GB DB, 30 concurrent users.
# Drop this file in and restart MariaDB. See MySQL 8.0 Reference §15.8.3.1
# for buffer-pool sizing rationale.

[mysqld]
# --- InnoDB memory ------------------------------------------------------
innodb_buffer_pool_size       = 5.5G
innodb_log_file_size          = 1.4G
innodb_log_buffer_size        = 32M

# --- Write / flush behaviour -------------------------------------------
innodb_flush_method           = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_io_capacity            = 400
innodb_io_capacity_max        = 800

# --- Connection / packet -----------------------------------------------
max_allowed_packet            = 64M
max_connections               = 151
thread_cache_size             = 32

# --- Frappe charset (already set by Frappe's own conf.d file) ----------
character-set-server          = utf8mb4
collation-server              = utf8mb4_unicode_ci

Why does a default install make ERPNext slow?

Debian and Ubuntu ship MariaDB with a configuration sized for a laptop, not a production database. innodb_buffer_pool_size defaults to 128 megabytes. That is all the RAM MariaDB will use to cache data pages, index pages, adaptive-hash-index entries and change-buffer state combined.

A year of live transactions takes an ERPNext database into gigabytes, and an import-heavy trading company with GRN, landed cost and stock ledger volume gets there faster. The slow-ERPNext case in the Frappe forum had about 570,000 Journal Entries and 3.3 million GL Entries.

With a 128 MB buffer pool, any query that reads more than a few thousand rows across tabGL Entry, tabStock Ledger Entry, tabPurchase Receipt Item or tabSales Invoice Item evicts its working set on every run. The pool cannot hold enough index pages to answer a General Ledger for a full financial year without going back to disk repeatedly.

Doesn't bench set this up?

It does not, which surprises people.

The MariaDB step in Frappe's installation guide asks you to add this, in full:

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set = utf8mb4

That is the whole server-level configuration Frappe asks for at install time, and the guide notes that from Frappe v15.21.0 even this step isn't required. Buffer pool, log file, flush behaviour and connection limits are left at whatever your distribution shipped.

bench setup production likewise configures nginx, supervisor, Redis and systemd, but not my.cnf. Frappe doesn't fight the distribution's package manager over a shared config file, so the tuning is on you.

The five (well, seven) my.cnf lines

Here is the block. The tuner above generates one for your inputs; the version below is a starting point for a typical 8 GB VPS running one ERPNext site with a 4–6 GB database, on MariaDB 10.6 or later.

# /etc/mysql/mariadb.conf.d/60-erpnext.cnf
[mysqld]
# --- InnoDB memory ---------------------------------------------
innodb_buffer_pool_size       = 5G      # 60–70% of RAM
innodb_log_file_size          = 1G      # ~25% of the pool
innodb_log_buffer_size        = 32M

# --- Write / flush behaviour -----------------------------------
innodb_flush_method           = O_DIRECT
innodb_flush_log_at_trx_commit = 2      # trade durability for throughput
innodb_io_capacity            = 400     # 200 = HDD, 400+ = SSD baseline

# --- Connection / packet ---------------------------------------
max_allowed_packet            = 64M     # required for large report imports
max_connections               = 200
thread_cache_size             = 32

What the five that matter do:

• innodb_buffer_pool_size is the most important variable in the file. It sets how much RAM InnoDB uses to cache data pages. The MySQL manual notes that dedicated database servers often give it up to 80% of physical memory. On an ERPNext VPS, MariaDB shares the box with Frappe workers, Redis, nginx and supervisor, so 60% is a sensible target.
• innodb_log_file_size sets how much dirty-page work the redo log can absorb before a checkpoint stalls writes. Too small and you get flush storms during write bursts (imports, month-end postings). About 25% of the buffer pool is a common rule of thumb; above 2G, crash recovery gets long.
• max_allowed_packet at 64M is the value the Frappe forum keeps arriving at (discuss.frappe.io/t/50411). Below it, large report exports, bulk imports and some background jobs fail mid-query with an unhelpful error.
• innodb_io_capacity tells InnoDB how many IOPS your storage can sustain, which sets the background flush rate. The default 200 suits a spinning disk. On an SSD (NVMe or a cloud gp3 volume) use 400 or higher; the calculator scales it with concurrent users.
• innodb_buffer_pool_instances used to split the pool into shards on older MariaDB. From 10.5 the pool is a single instance, and in 10.6 the variable is gone, which is why it is not in the block above. Only on MariaDB 10.4 or older, add it at about one per GB of pool, up to 8.

The remaining two, innodb_flush_method = O_DIRECT and innodb_flush_log_at_trx_commit = 2, trade a slice of durability for throughput. = 2 writes the log to the OS on every commit but fsyncs it only once per second, so a hard crash can lose up to one second of committed transactions. Most self-hosted companies with daily backups accept that; where sub-second durability is a contractual requirement, stay on = 1.

Where does the config file go?

Do not edit the main my.cnf or mariadb.cnf. Package upgrades overwrite them and your tuning disappears without a warning. Drop an override file into the conf.d directory instead. MariaDB reads every .cnf there in alphabetical order, so 60-erpnext.cnf wins against the shipped defaults.

The 60- prefix matters. Package configs usually use 50-server.cnf; a higher number makes sure your file is read last and wins on a conflict.

How do you benchmark before you touch anything?

Most people skip this step. Don't: do not restart a production MariaDB for a speed-up you cannot measure. Without a baseline you can't tell whether the tuning helped, hurt or did nothing, and you can't answer the finance head when the same report is slow next month.

Pick one of the three recipes below (bench, sysbench or a slow-log digest) and run it twice: once now, once after the restart. Report only differences you measured on your own box.

# Save as scripts/time_report.py in your bench directory.
# Run with:  bench --site erp.yourco.in execute scripts.time_report.run
import time, statistics, frappe

REPORT = "General Ledger"        # any Query/Script report
FILTERS = {
    "company": "Your Company",
    "from_date": "2025-04-01",
    "to_date":   "2026-03-31",
    "group_by":  "Group by Voucher (Consolidated)",
}
RUNS = 5

def run():
    from frappe.desk.query_report import run as run_report
    frappe.set_user("Administrator")

    # Warm the buffer pool with one throwaway pass.
    run_report(REPORT, filters=FILTERS)

    timings = []
    for i in range(RUNS):
        t0 = time.perf_counter()
        res = run_report(REPORT, filters=FILTERS)
        dt = (time.perf_counter() - t0) * 1000
        timings.append(dt)
        print(f"run {i+1}: {dt:8.1f} ms  ({len(res.get('result', []))} rows)")

    print("--- summary ---")
    print(f"p50 = {statistics.median(timings):.1f} ms")
    print(f"p95 = {sorted(timings)[int(len(timings)*0.95)-1]:.1f} ms")
    print(f"min = {min(timings):.1f} ms   max = {max(timings):.1f} ms")

How do you restart MariaDB without breaking Frappe?

A cold MariaDB restart with Frappe workers still running fills the error log with MySQL server has gone away for a few seconds until each worker reconnects. It looks bad but does no harm. Still, the clean sequence is:

Two failure modes:

• The innodb_log_file_size change doesn't apply. Very old MariaDB releases needed a clean shutdown and the old ib_logfile* files deleted before a new size took effect; current releases resize the log on the next start. If you are on an old distribution release, upgrade MariaDB first or leave the log-file change out of the first pass.
• The service won't start after the edit. Usually a typo (an extra M, a missing quote) or a variable your version doesn't know, such as innodb_buffer_pool_instances on 10.6. sudo journalctl -u mariadb --no-pager | tail -50 shows the exact error. Roll back by deleting 60-erpnext.cnf and restarting; restore from the snapshot only if that fails.

When isn't tuning enough?

Tuning fixes "the database had enough RAM for its working set and you weren't letting it use it". It does not fix a database that is too big for one machine, or a query pattern that no buffer pool will save. The signals:

• Buffer-pool hit ratio near 100% and the CPU still saturated. Check with SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'. If almost every read request is served from the pool and reports are still slow, you are CPU-bound on query execution, not I/O-bound on fetching pages. More RAM won't help.
• A handful of reports dominate the slow log. pt-query-digest (the third recipe above) surfaces them. Fix them with indexes or report code, not InnoDB settings.
• Writes are the bottleneck. InnoDB tuning helps reads far more than writes. If bulk imports or month-end postings are what's slow, look at batching, a read replica for reporting, or partitioning.

The order we work in: tune first, index second, replica third, partition last. Going straight to a replica because "the database is slow" is a common, expensive mistake when tuning alone would have been enough.

At a glance

Common questions from the IT team

What is the bottom line?

Self-hosted ERPNext often gets called slow when the application is fine and the database is running with laptop settings, because the installer never touched them.

Compute your values with the calculator, benchmark against your own workload, restart in a maintenance window with a backup in hand, and benchmark again. If the numbers move, the tuning was the problem; if they don't, the slow log tells you where to look next.