The Five my.cnf Lines That Make ERPNext Feel Like a Different Product

A 40-second General Ledger on a c5a.2xlarge that costs 150 USD a month should insult you. It almost always traces back to the same handful of untuned InnoDB settings. I am Manoj, Frappe / ERPNext implementation lead at MithTech in Bengaluru — this is the tuning pass we run on every self-hosted client before we touch anything else.

Try it: your my.cnf, computed from your box

Type in your actual VPS RAM, your current DB size, and your realistic peak concurrent users. The calculator emits a drop-in .cnf file grounded in the sizing rules from the MySQL 8.0 Reference §15.8.3.1 and the canonical Frappe discuss 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_buffer_pool_instances  = 6
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 the default install is starving your ERPNext

Debian and Ubuntu ship MariaDB with a config aimed at a laptop, not a production database. innodb_buffer_pool_size defaults to 128 megabytes. That is the total amount of RAM MariaDB will use to cache data pages, index pages, adaptive-hash-index entries, and change-buffer state combined.

Your ERPNext database, by the end of year one on a live company, is not 128 megabytes. Realistic sizes we see:

• A single-company SME with a year of transactions: 2–4 GB.
• An import-heavy trading company with GRN + LCV + Stock Ledger volume: 6–12 GB.
• A three-year-old multi-company deployment with heavy journal traffic (like the c5a.2xlarge case documented in the forums — 570k Journal Entries, 3.3M GL Entries): 17+ GB.

On a 128M buffer pool, any query that touches more than a few thousand rows across tabGL Entry, tabStock Ledger Entry, tabPurchase Receipt Item or tabSales Invoice Item will evict 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.

But surely bench sets this up correctly?

It does not. This surprises people, so let's ground it.

Frappe's own repository, in the one MariaDB config file shipped with the ecosystem — raven/.github/mariadb-frappe.cnf used to bootstrap CI containers — contains, in full:

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

[mysql]
default-character-set = utf8mb4

Charset. That is the entire server-level configuration Frappe cares about at install time. Everything else — buffer pool, log file, flush behaviour, connection limits — is left to whatever your distribution shipped, which is invariably tuned for a laptop.

The bench setup production command likewise touches nginx, supervisor, redis, and systemd — but not my.cnf. This is a deliberate separation: Frappe doesn't want to fight the distribution's package manager over a shared config file. It just means the tuning is on you.

The five (well, seven) my.cnf lines

Here is the block. The tuner above generates one calibrated to your inputs; the version below is a good starting point for a typical 8 GB VPS running a single ERPNext site with a 4–6 GB database.

# /etc/mysql/mariadb.conf.d/60-erpnext.cnf
[mysqld]
# --- InnoDB memory ---------------------------------------------
innodb_buffer_pool_size       = 5G      # 60–70% of RAM
innodb_buffer_pool_instances  = 5       # ~1 per GB of pool
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 each of the "five that matter" actually does:

• innodb_buffer_pool_size is the single most important variable in the file. It sets how much RAM InnoDB will use to cache data pages. The MySQL 8.0 Reference §15.8.3.1 recommends 50–75% of physical memory on a dedicated DB host, 25–50% on a shared host. For a typical ERPNext VPS where MariaDB shares the box with Frappe workers, Redis, nginx and supervisor, 60% is a sensible target.
• innodb_log_file_size governs how much dirty-page work the log can absorb before a checkpoint stalls writes. Too small and you get flush storms during heavy write bursts (imports, month-end postings). ~25% of the buffer pool is the standard heuristic; 2G is a reasonable cap beyond which crash recovery gets uncomfortably long.
• innodb_buffer_pool_instances splits the pool into shards to reduce mutex contention. One instance per gigabyte of pool, up to about 8, is fine for VPS-scale ERPNext.
• max_allowed_packet at 64M matches the value the Frappe community consistently converges on (discuss.frappe.io/t/50411). Below this, large report exports, bulk imports, and some background jobs will crash mid-query with an opaque error.
• innodb_io_capacity tells InnoDB how many IOPS your storage can sustain, which governs the background flush rate. The default 200 is right for a spinning disk. On any modern SSD (NVMe or even a cloud gp3 volume) you want 400 or higher; the calculator scales this with concurrent users.

The remaining two — innodb_flush_method = O_DIRECT and innodb_flush_log_at_trx_commit = 2 — trade a slice of ACID durability for a large throughput win. = 2 means the log is flushed to the OS on every commit but only fsync'd once per second, so a hard crash could lose up to one second of committed transactions. On a VPS with a UPS-backed data center and daily backups this is a trade almost everyone should make; on a bank ledger, do not.

Where the config file actually lives

Do not edit the main my.cnf or mariadb.cnf. Distribution package upgrades will overwrite them and your careful tuning will vanish silently. Drop an override file into the conf.d directory instead — MariaDB reads every .cnf in there in alphabetical order, so 60-erpnext.cnf will win against the shipped defaults.

The 60- prefix matters. Package-shipped configs typically use 50-server.cnf — a higher number ensures your file is read last and wins on conflict.

Benchmarking before you touch anything

This is the section nobody enjoys and everybody skips, so I will be blunt: do not restart your production MariaDB in pursuit of a speedup you cannot measure. Without a baseline you have no way to know whether the tuning helped, hurt, or did nothing at all. You will also have no way to defend the change to the finance director when the same report is slow next month.

Pick one of the three recipes below (bench, sysbench, or slow-log digest) and run it twice: once now, once after the restart. Publish 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")

Restarting MariaDB without breaking Frappe

A cold MariaDB restart with Frappe workers still running produces a cascade of MySQL server has gone away errors in the error log for a few seconds until each worker's pool reconnects. It is cosmetically ugly but harmless — Frappe uses connection retries for exactly this case. Still, the clean sequence is:

# 1. Pause the scheduler so no new background jobs kick off mid-restart
bench --site all set-maintenance-mode on

# 2. Full backup — DB + private files + public files
bench --site erp.yourco.in backup --with-files --compress

# 3. Drop the override file in
sudo cp 60-erpnext.cnf /etc/mysql/mariadb.conf.d/
sudo chmod 644 /etc/mysql/mariadb.conf.d/60-erpnext.cnf

# 4. Sanity-check the file parses BEFORE restarting
sudo mariadbd --help --verbose > /dev/null    # non-zero exit == config error

# 5. Restart MariaDB, then Frappe
sudo systemctl restart mariadb
bench restart

# 6. Verify the new values took effect
mysql -uroot -p -e "
  SHOW VARIABLES WHERE Variable_name IN (
    'innodb_buffer_pool_size',
    'innodb_log_file_size',
    'max_allowed_packet',
    'innodb_flush_log_at_trx_commit'
  );"

# 7. Bring the site back up
bench --site all set-maintenance-mode off

Two failure modes worth naming:

• innodb_log_file_size change refuses to apply. On MariaDB versions before 10.5, changing this required shutting down cleanly and deleting the old ib_logfile* files. From 10.5 onward it is resized on next startup. If you are on an ancient distro release, either upgrade MariaDB first or omit the log-file change from the first pass.
• The service refuses to start after edit. Almost always a typo (extra M, missing quote). sudo journalctl -u mariadb --no-pager | tail -50 shows the exact parse error. Roll back by deleting 60-erpnext.cnf, restart, restore from your snapshot only if that fails.

When tuning isn't enough

Tuning solves "your database has enough RAM to hold its working set and you weren't letting it." It does not solve "your database is fundamentally too big for one machine" or "your query pattern is O(rows × items) for a reason no amount of buffer pool will fix." The signals:

• Buffer-pool hit ratio pinned at 99.9%+ and CPU still saturated. Check with SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%' — if Innodb_buffer_pool_read_requests / (reads + read_requests) is ~1.0 and reports are still slow, you are CPU-bound on query execution, not I/O-bound on page fetching. More RAM will not help.
• Slow queries dominated by a small handful of reports. pt-query-digest (the third recipe above) will surface these. Fix them at the ORM / index level, not the InnoDB level.
• Write throughput is the bottleneck. InnoDB tuning helps reads far more than writes. If your bottleneck is bulk imports or heavy month-end postings, you are looking at a read replica, batching changes, or moving to horizontal partitioning.

In our experience the ordering is: tune first, index second, replica third, partition last. Skipping straight to a replica because "the DB is slow" is a common and expensive mistake — half the time tuning alone gives back 5–20× on read latency and the replica turns out to have been unnecessary.

FAQ

Closing

Self-hosted ERPNext gets a reputation for being slow that is, three years out of four, entirely undeserved. The application layer is fine. The database layer is running with the same tuning as a laptop dev instance because the installer never touched it.

Compute your values with the calculator at the top, benchmark honestly against your own workload, restart in a maintenance window with a backup in hand, and re-benchmark. The delta will make you feel like you upgraded the hardware — because in the way that matters to InnoDB, you did.