/frappe-ops-performance

name: frappe-ops-performance
description: >
  Use when tuning MariaDB, configuring Redis memory, sizing Gunicorn workers, setting up CDN, or profiling slow queries.
  Prevents performance bottlenecks from default configurations, memory exhaustion, and unoptimized database queries.
  Covers MariaDB tuning, Redis configuration, Gunicorn worker sizing, CDN setup, slow query log analysis, Python profiling, request profiling.
  Keywords: performance, MariaDB, Redis, Gunicorn, CDN, slow query, profiling, tuning, optimization, workers, slow page, loading time, ERPNext slow, why is it slow, page takes long, timeout..
license: MIT
compatibility: "Claude Code, Claude.ai Projects, Claude API. Frappe v14-v16."
metadata:
  author: OpenAEC-Foundation
  version: "2.0"

Performance Tuning

Frappe/ERPNext performance depends on four layers: database (MariaDB), cache (Redis), application server (Gunicorn), and background workers (RQ). ALWAYS tune all four layers together — optimizing one while ignoring others creates new bottlenecks.

Quick Reference

# Check system health
bench doctor

# Show pending background jobs
bench --site mysite.com show-pending-jobs

# Clear all caches
bench --site mysite.com clear-cache
bench --site mysite.com clear-website-cache

# Purge stuck background jobs
bench purge-jobs

# Enable MariaDB slow query log
# In /etc/mysql/mariadb.conf.d/50-server.cnf:
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/slow.log
# long_query_time = 1

# Check Gunicorn worker count
# In Procfile or supervisor config: -w [workers]
# Formula: workers = (2 * CPU_CORES) + 1

---

Performance Decision Tree

What is slow?
|
+-- Page loads are slow?
|   +-- Check Gunicorn workers (are they saturated?)
|   +-- Check MariaDB slow query log
|   +-- Check Redis memory (is cache evicting?)
|   +-- Enable CDN for static assets
|
+-- Background jobs are delayed?
|   +-- bench doctor (check worker count and pending jobs)
|   +-- Increase RQ worker count
|   +-- Check for long-running jobs blocking queues
|
+-- Database queries are slow?
|   +-- Enable slow query log
|   +-- Run EXPLAIN on slow queries
|   +-- Add indexes on frequently filtered columns
|   +-- Use get_cached_value instead of get_value
|
+-- Server runs out of memory?
|   +-- Reduce Gunicorn workers
|   +-- Set Redis maxmemory
|   +-- Check MariaDB innodb_buffer_pool_size
|   +-- Look for memory leaks in custom code
|
+-- High CPU usage?
|   +-- Profile Python code (cProfile)
|   +-- Check for N+1 query patterns
|   +-- Review custom scheduled jobs

---

MariaDB Tuning

Critical Settings

# /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
# InnoDB buffer pool — MOST important setting
# Set to 50-70% of available RAM on dedicated DB server
# Set to 25-40% of RAM on shared server
innodb_buffer_pool_size = 2G

# Buffer pool instances (1 per GB of buffer pool)
innodb_buffer_pool_instances = 2

# Log file size (larger = better write performance, slower recovery)
innodb_log_file_size = 256M

# Flush method — use O_DIRECT to avoid double buffering
innodb_flush_method = O_DIRECT

# Character set (ALWAYS use utf8mb4 for Frappe)
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Key buffer for MyISAM (Frappe uses InnoDB, keep small)
key_buffer_size = 32M

# Query cache (DISABLE for MariaDB 10.4+ / MySQL 8.0+)
query_cache_type = 0
query_cache_size = 0

# Connection limits
max_connections = 200
wait_timeout = 600
interactive_timeout = 600

# Temp tables
tmp_table_size = 64M
max_heap_table_size = 64M

# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

Slow Query Analysis

# Enable slow query log (runtime, no restart needed)
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

# Analyze slow queries with mysqldumpslow
mysqldumpslow -t 10 -s c /var/log/mysql/slow.log
# -t 10: top 10 queries
# -s c: sort by count (use -s t for total time)

# Use EXPLAIN to analyze specific queries
EXPLAIN SELECT * FROM `tabSales Invoice` WHERE customer = 'ABC';
# Look for: type=ALL (full table scan), rows > 10000, Using filesort

Index Optimization

-- Check for missing indexes on frequently filtered columns
SHOW INDEX FROM `tabSales Invoice`;

-- Add index for common filter patterns
ALTER TABLE `tabSales Invoice` ADD INDEX idx_customer_date (customer, posting_date);

-- Frappe way: add index via DocType definition
-- In doctype JSON: set "in_list_view" or "search_index" on fields
-- OR use hooks.py:
-- after_migrate = ["myapp.patches.add_custom_indexes"]

---

Redis Configuration

Memory Management

# /etc/redis/redis.conf (or bench config/redis_cache.conf)

# Set maximum memory — NEVER let Redis use all available RAM
maxmemory 512mb

# Eviction policy — allkeys-lru is best for cache use
maxmemory-policy allkeys-lru

# Disable persistence for cache Redis (performance boost)
save ""
appendonly no

Frappe Redis Architecture

Frappe uses THREE Redis instances:

| Instance | Default Port | Purpose | Memory Guide | |---|---|---|---| | redis-cache | 13000 | Document cache, session data | 256MB-1GB | | redis-queue | 11000 | RQ job queues | 128MB-512MB | | redis-socketio | 12000 | Real-time events | 64MB-256MB |

ALWAYS set `maxmemory` on redis-cache. Without it, Redis grows unbounded and can trigger OOM killer.

Frappe Caching API

import frappe

# Basic Redis cache
frappe.cache.set_value("my_key", {"data": "value"})
result = frappe.cache.get_value("my_key")

# get_cached_value — cached database lookup (ALWAYS prefer over get_value for reads)
value = frappe.db.get_cached_value("Customer", "CUST-001", "customer_name")
# Equivalent to get_value but caches in Redis — dramatically faster for repeated reads

# Hashed cache (group related values)
frappe.cache.hset("settings", "key1", "value1")
frappe.cache.hget("settings", "key1")

# Clear specific cache
frappe.cache.delete_valu