Frappe Ops Performance

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:

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_value("my_key")
frappe.cache.delete_keys("prefix*")

# Clear all cache (use sparingly)
# bench --site mysite.com clear-cache

---

Gunicorn Workers

Worker Count Formula

workers = (2 * CPU_CORES) + 1

Examples:
  2 CPU cores  →  5 workers
  4 CPU cores  →  9 workers
  8 CPU cores  → 17 workers

Configuration

# Traditional: edit Procfile or supervisor config
# In supervisor.conf:
command=/home/frappe/frappe-bench/env/bin/gunicorn \
    -b 127.0.0.1:8000 \
    -w 9 \                    # Worker count
    --timeout 120 \           # Request timeout (seconds)
    --graceful-timeout 30 \   # Graceful shutdown timeout
    --max-requests 5000 \     # Restart worker after N requests (prevents memory leaks)
    --max-requests-jitter 500 \
    frappe.app:application

# Docker: set via environment variable or command override

Memory Calculation

Each Gunicorn worker consumes 150-300MB RAM. ALWAYS verify total memory fits:

Required RAM = workers * 300MB + MariaDB buffer pool + Redis + OS overhead

Example (4 CPU, 8GB RAM server):
  9 workers * 300MB = 2.7GB (Gunicorn)
  + 2GB (MariaDB innodb_buffer_pool_size)
  + 1GB (Redis total)
  + 1.5GB (OS + other)
  = 7.2GB — fits in 8GB

NEVER set more workers than your RAM allows. Swapping kills performance.

---

Background Workers (RQ)

Worker Queues

Tuning Worker Count

# Supervisor: duplicate worker sections with unique names
# For high-volume sites, increase short/default workers:

[program:frappe-bench-frappe-worker-short-1]
command=bench worker --queue short
...

[program:frappe-bench-frappe-worker-short-2]
command=bench worker --queue short
...

# Docker: scale via docker compose
docker compose up -d --scale queue-short=3 --scale queue-long=2

Diagnosing Job Backlogs

# Check overall health
bench doctor
# Expected: Workers online: N, no pending jobs

# Check specific site queues
bench --site mysite.com show-pending-jobs

# Clear stuck jobs (use when jobs are permanently stuck)
bench purge-jobs

---

CDN Setup for Static Assets

# site_config.json
{
    "cdn_url": "https://cdn.example.com"
}
# All /assets/ URLs will be prefixed with the CDN URL

# ALTERNATIVELY: configure at Nginx level
# location /assets {
#     alias /home/frappe/frappe-bench/sites/assets;
#     expires 1y;
#     add_header Cache-Control "public, immutable";
# }

---

Monitoring

bench doctor

bench doctor
# Output:
# -----Checking scheduler------
# mysite.com: scheduler is running
# Workers online: 3
# -----None Jobs-----

Key Log Locations

Scheduled Job Log (DocType)

Check Setup > Scheduled Job Log in ERPNext UI for:

• Job execution times
• Failed jobs with error details
• Frequency analysis

RQ Dashboard (Optional)

# Install RQ dashboard for web-based job monitoring
pip install rq-dashboard
rq-dashboard --redis-url redis://localhost:11000
# Access at http://localhost:9181

---

Common Bottleneck Diagnosis

---

Scaling Patterns

Vertical Scaling (single server):
  1. Add RAM → increase innodb_buffer_pool_size + Redis maxmemory
  2. Add CPU → increase Gunicorn workers + RQ workers
  3. Use SSD → dramatic improvement for database I/O

Horizontal Scaling (multiple servers):
  1. Separate DB server (MariaDB on dedicated host)
  2. Separate Redis server(s)
  3. Multiple app servers behind load balancer
  4. Read replicas for reporting queries
  5. Kubernetes with frappe_docker for auto-scaling

---

Version Differences

---

Reference Files

Related Skills

• frappe-ops-deployment — Production deployment setup
• frappe-ops-backup — Backup and disaster recovery
• frappe-ops-bench — Bench CLI reference
• frappe-core-database — Database API and query patterns

Performance Anti-Patterns

1. Using default MariaDB settings in production

# WRONG — default innodb_buffer_pool_size is 128MB
# This causes excessive disk I/O on any non-trivial dataset

# CORRECT — set to 50-70% of RAM on dedicated DB server
[mysqld]
innodb_buffer_pool_size = 2G  # For 4GB RAM server

Why: The default 128MB buffer pool means MariaDB constantly reads from disk. This is the single biggest performance improvement for most Frappe installations.

2. No maxmemory on Redis cache

# WRONG — Redis grows unbounded, eventually triggers OOM killer
# (default: no memory limit)

# CORRECT — ALWAYS set maxmemory with eviction policy
maxmemory 512mb
maxmemory-policy allkeys-lru

Why: Without a limit, Redis cache consumes all available memory during heavy usage. The OOM killer then kills random processes, often taking down the entire application.

3. Too many Gunicorn workers for available RAM

# WRONG — 32 workers on a 4GB server
gunicorn -w 32 frappe.app:application
# 32 * 300MB = 9.6GB > 4GB → server swaps → everything slow

# CORRECT — calculate based on available RAM
# workers = min((2 * CPU + 1), (available_ram - db - redis - os) / 300MB)
gunicorn -w 5 frappe.app:application

Why: Each Gunicorn worker uses 150-300MB RAM. Overprovisioning causes swapping, which is orders of magnitude slower than RAM access.

4. N+1 query patterns in custom code

# WRONG — N+1 queries (1 query + N queries in loop)
items = frappe.get_all("Item", fields=["name"])
for item in items:
    warehouse_qty = frappe.db.get_value("Bin",
        {"item_code": item.name, "warehouse": "Main"}, "actual_qty")
# 1001 queries for 1000 items!

# CORRECT — single query with join or get_all with filters
bins = frappe.get_all("Bin",
    filters={"warehouse": "Main"},
    fields=["item_code", "actual_qty"]
)
qty_map = {b.item_code: b.actual_qty for b in bins}

Why: Database roundtrip latency multiplied by N rows creates massive slowdowns. ALWAYS batch database calls.

5. Using get_value in loops instead of get_cached_value

# WRONG — database hit every call
for invoice in invoices:
    customer_name = frappe.db.get_value("Customer", invoice.customer, "customer_name")

# CORRECT — cached lookup (Redis first, DB fallback)
for invoice in invoices:
    customer_name = frappe.db.get_cached_value("Customer", invoice.customer, "customer_name")

Why: get_cached_value checks Redis cache first. For repeated lookups of the same records, this eliminates redundant database queries.

6. Running bench clear-cache in production without understanding impact

# WRONG — clearing all caches during business hours
bench --site mysite.com clear-cache
# Every subsequent request is slow until cache rebuilds

# CORRECT — clear specific caches, or clear during off-peak
frappe.cache.delete_value("specific_key")  # Targeted
# OR schedule clear-cache during maintenance window

Why: clear-cache empties all Redis caches. Every page load, every API call must rebuild its cache from database. This causes a "thundering herd" effect.

7. Not using database indexes on filtered columns

-- WRONG — custom report filtering on unindexed columns
SELECT * FROM `tabSales Invoice`
WHERE custom_region = 'Europe' AND posting_date > '2024-01-01';
-- Full table scan on millions of rows

-- CORRECT — add composite index
ALTER TABLE `tabSales Invoice` ADD INDEX idx_region_date (custom_region, posting_date);

Why: Without indexes, MariaDB scans every row in the table. With proper indexes, the same query reads only matching rows.

8. Ignoring slow query log

# WRONG — slow queries accumulating silently
# No slow_query_log configured
# Users complain about "slow system" with no diagnostic data

# CORRECT — ALWAYS enable in production
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

Why: The slow query log is your primary tool for identifying database bottlenecks. Without it, you are debugging blind.

9. Scaling workers without scaling database connections

# WRONG — 20 Gunicorn workers + 6 RQ workers but max_connections = 50
# Random "Too many connections" errors under load

# CORRECT — ensure max_connections accommodates all workers
# Formula: max_connections >= gunicorn_workers + rq_workers + admin_connections + buffer
[mysqld]
max_connections = 200  # Safe for most deployments

10. SELECT * instead of specifying fields

# WRONG — fetching all 50+ columns when you need 3
docs = frappe.get_all("Sales Invoice")  # SELECT `tabSales Invoice`.*

# CORRECT — specify only needed fields
docs = frappe.get_all("Sales Invoice",
    fields=["name", "customer", "grand_total"],
    filters={"docstatus": 1},
    limit_page_length=100
)

Why: Fetching unnecessary columns wastes memory, network bandwidth, and MariaDB buffer pool space. ALWAYS specify the fields you need.

Performance Tuning Examples

Complete MariaDB Configuration for Frappe

# /etc/mysql/mariadb.conf.d/99-frappe-tuning.cnf
# For 8GB RAM server, shared with Frappe application

[mysqld]
# === InnoDB Settings ===
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = ON

# === Character Set ===
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

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

# === MyISAM (minimal, Frappe uses InnoDB) ===
key_buffer_size = 32M

# === Query Cache (DISABLE for MariaDB 10.4+) ===
query_cache_type = 0
query_cache_size = 0

# === 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
log_queries_not_using_indexes = 1

# === Binary Log (for point-in-time recovery) ===
# log_bin = /var/log/mysql/mariadb-bin
# expire_logs_days = 7
# binlog_format = ROW

[mysql]
default-character-set = utf8mb4

Redis Cache Configuration

# config/redis_cache.conf (managed by bench)
# Override in common_site_config.json or custom Redis config

port 13000
bind 127.0.0.1

# Memory limit — ALWAYS set for cache instance
maxmemory 512mb
maxmemory-policy allkeys-lru

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

# Connection limits
maxclients 1000
timeout 300

Gunicorn Production Configuration

# config/gunicorn_config.py (create if not exists)
import multiprocessing

# Worker count: (2 * CPU) + 1
workers = (2 * multiprocessing.cpu_count()) + 1
bind = "127.0.0.1:8000"

# Timeouts
timeout = 120
graceful_timeout = 30
keepalive = 2

# Worker recycling (prevents memory leaks)
max_requests = 5000
max_requests_jitter = 500

# Logging
accesslog = "-"
errorlog = "-"
loglevel = "info"

# Worker class
worker_class = "gthread"
threads = 2

Frappe Caching Patterns

import frappe

# Pattern 1: Use get_cached_value for frequent lookups
# WRONG — hits database every time
def get_company_currency(company):
    return frappe.db.get_value("Company", company, "default_currency")

# CORRECT — cached in Redis, dramatically faster
def get_company_currency(company):
    return frappe.db.get_cached_value("Company", company, "default_currency")


# Pattern 2: Custom cache with expiry
def get_exchange_rate(from_currency, to_currency):
    cache_key = f"exchange_rate:{from_currency}:{to_currency}"
    rate = frappe.cache.get_value(cache_key)
    if rate is None:
        rate = fetch_exchange_rate(from_currency, to_currency)
        frappe.cache.set_value(cache_key, rate, expires_in_sec=3600)  # 1 hour
    return rate


# Pattern 3: Bulk data caching
def get_item_prices():
    cache_key = "all_item_prices"
    prices = frappe.cache.get_value(cache_key)
    if prices is None:
        prices = frappe.db.get_all("Item Price",
            fields=["item_code", "price_list", "price_list_rate"],
            filters={"selling": 1}
        )
        frappe.cache.set_value(cache_key, prices, expires_in_sec=300)
    return prices


# Pattern 4: Hash-based cache for grouped data
def get_user_settings(user):
    settings = frappe.cache.hget("user_settings", user)
    if settings is None:
        settings = frappe.db.get_value("User", user,
            ["language", "time_zone", "desk_theme"], as_dict=True)
        frappe.cache.hset("user_settings", user, settings)
    return settings


# Pattern 5: Cache invalidation on document change
class ItemPrice(Document):
    def on_update(self):
        frappe.cache.delete_value("all_item_prices")

    def on_trash(self):
        frappe.cache.delete_value("all_item_prices")

Database Query Optimization

# WRONG — N+1 query pattern
items = frappe.get_all("Item", fields=["name"])
for item in items:
    price = frappe.db.get_value("Item Price", {"item_code": item.name}, "price_list_rate")
    # This runs len(items) + 1 queries!

# CORRECT — single query with join
items_with_prices = frappe.db.sql("""
    SELECT i.name, i.item_name, ip.price_list_rate
    FROM `tabItem` i
    LEFT JOIN `tabItem Price` ip ON ip.item_code = i.name
    WHERE ip.selling = 1
""", as_dict=True)


# WRONG — fetching all fields when you need only a few
invoices = frappe.get_all("Sales Invoice")  # SELECT * — slow for large tables

# CORRECT — specify only needed fields
invoices = frappe.get_all("Sales Invoice",
    fields=["name", "customer", "grand_total", "status"],
    filters={"docstatus": 1},
    limit_page_length=100
)


# Adding indexes for common query patterns
# In custom app hooks.py:
# after_migrate = ["myapp.patches.add_indexes"]

# myapp/patches/add_indexes.py
def execute():
    import frappe
    frappe.db.add_index("Sales Invoice", ["customer", "posting_date"])
    frappe.db.add_index("Sales Invoice Item", ["item_code"])

Monitoring Script

#!/bin/bash
# frappe-health-check.sh — Quick system health overview
set -e

BENCH_DIR="/home/frappe/frappe-bench"
cd "$BENCH_DIR"

echo "=== Frappe Health Check ==="
echo ""

# 1. Bench doctor
echo "--- Scheduler & Workers ---"
bench doctor 2>/dev/null || echo "bench doctor failed"
echo ""

# 2. MariaDB stats
echo "--- MariaDB ---"
mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';"
mysql -e "SELECT ROUND(@@innodb_buffer_pool_size/1024/1024) AS 'Buffer Pool (MB)';"
echo ""

# 3. Redis memory
echo "--- Redis Cache ---"
redis-cli -p 13000 INFO memory | grep used_memory_human
redis-cli -p 13000 INFO memory | grep maxmemory_human
echo ""

# 4. Disk usage
echo "--- Disk Usage ---"
du -sh sites/*/private/backups/ 2>/dev/null
du -sh sites/*/public/files/ 2>/dev/null
echo ""

# 5. System resources
echo "--- System ---"
free -h | head -2
echo "Load: $(uptime | awk -F'load average:' '{print $2}')"
echo "CPU cores: $(nproc)"

Slow Query Analysis Workflow

# 1. Enable slow query log (if not already)
mysql -e "SET GLOBAL slow_query_log = 1;"
mysql -e "SET GLOBAL long_query_time = 1;"
mysql -e "SET GLOBAL log_queries_not_using_indexes = 1;"

# 2. Wait for data collection (run during peak hours)

# 3. Analyze top slow queries by frequency
mysqldumpslow -t 10 -s c /var/log/mysql/slow.log

# 4. Analyze top slow queries by total time
mysqldumpslow -t 10 -s t /var/log/mysql/slow.log

# 5. For specific slow query, run EXPLAIN
mysql -e "EXPLAIN SELECT * FROM \`tabSales Invoice\` WHERE customer = 'ABC' AND posting_date > '2024-01-01';"
# Look for:
#   type = ALL → needs index
#   rows > 10000 → needs optimization
#   Extra: Using filesort → consider index on ORDER BY column
#   Extra: Using temporary → consider restructuring query

# 6. Add index if needed
mysql -e "ALTER TABLE \`tabSales Invoice\` ADD INDEX idx_customer_date (customer, posting_date);"

Performance Tuning Workflows

Workflow 1: Initial Production Tuning

1. Assess server resources
   $ free -h           (total RAM)
   $ nproc             (CPU cores)
   $ df -h             (disk space and type — SSD vs HDD)
   |
2. Calculate resource allocation
   Gunicorn workers = (2 * CPU) + 1
   MariaDB buffer pool = 25-40% of RAM (shared server)
   Redis maxmemory = 256MB-1GB (depending on dataset)
   Remaining = OS + headroom
   |
3. Configure MariaDB
   Edit /etc/mysql/mariadb.conf.d/99-frappe-tuning.cnf
   Set innodb_buffer_pool_size, slow_query_log, character set
   $ sudo systemctl restart mariadb
   |
4. Configure Redis
   Edit bench config/redis_cache.conf
   Set maxmemory and maxmemory-policy allkeys-lru
   $ sudo supervisorctl restart frappe-bench-redis-cache
   |
5. Configure Gunicorn
   Edit supervisor config: set -w [workers] --timeout 120
   Add --max-requests 5000 --max-requests-jitter 500
   $ sudo supervisorctl restart frappe-bench-frappe-web
   |
6. Verify
   $ bench doctor
   $ curl -o /dev/null -s -w '%{time_total}\n' https://mysite.com
   Compare response times before/after

Workflow 2: Diagnose Slow Page Loads

1. Identify the slow page
   Note URL, time of day, frequency
   |
2. Check server-side response time
   Browser DevTools > Network tab > check TTFB (Time To First Byte)
   |
   +-- TTFB > 2s? → Server-side bottleneck
   |   |
   |   +-- Check MariaDB slow query log
   |       $ mysqldumpslow -t 5 -s t /var/log/mysql/slow.log
   |       |
   |       +-- Slow queries found?
   |           +-- Run EXPLAIN on slow query
   |           +-- Add missing indexes
   |           +-- Optimize Python code (N+1 patterns)
   |
   +-- TTFB < 500ms but page still slow?
       → Client-side bottleneck (JS, CSS, large assets)
       |
       +-- Enable CDN for static assets
       +-- Check for unminified custom JavaScript
       +-- Use browser DevTools Performance tab
   |
3. Check Gunicorn worker saturation
   $ sudo supervisorctl status
   Are all workers busy? If yes → increase worker count
   |
4. Check Redis cache effectiveness
   $ redis-cli -p 13000 INFO stats | grep keyspace_hits
   $ redis-cli -p 13000 INFO stats | grep keyspace_misses
   Hit ratio should be > 90%
   |
5. Monitor over time
   Log response times, identify patterns (peak hours, specific pages)

Workflow 3: Diagnose Background Job Delays

1. Check scheduler health
   $ bench doctor
   Expected: scheduler running, workers online, no pending jobs
   |
2. Check pending jobs
   $ bench --site mysite.com show-pending-jobs
   |
   +-- Many pending short/default jobs?
   |   → Increase short/default workers in supervisor config
   |
   +-- Many pending long jobs?
   |   → Increase long workers OR optimize long-running job code
   |
   +-- Jobs stuck (same jobs for hours)?
       → $ bench purge-jobs (clears all pending)
       → Investigate why jobs are failing (check worker logs)
   |
3. Check worker logs
   $ tail -100 logs/worker.log
   Look for: exceptions, timeouts, memory errors
   |
4. If RQ workers crash repeatedly
   Check available memory (workers may be OOM killed)
   $ dmesg | grep -i "out of memory"
   → Reduce Gunicorn workers to free RAM
   → Or add more server RAM

Workflow 4: Database Performance Audit

1. Enable monitoring
   SET GLOBAL slow_query_log = 1;
   SET GLOBAL long_query_time = 1;
   SET GLOBAL log_queries_not_using_indexes = 1;
   |
2. Collect data for 24 hours (including peak)
   |
3. Analyze slow queries
   $ mysqldumpslow -t 20 -s c /var/log/mysql/slow.log  (by count)
   $ mysqldumpslow -t 20 -s t /var/log/mysql/slow.log  (by time)
   |
4. For each top slow query:
   a. Run EXPLAIN
   b. Check if index exists on filtered columns
   c. Check if query uses SELECT *
   d. Check for N+1 patterns in calling Python code
   |
5. Fix identified issues
   - Add indexes: ALTER TABLE `tabXxx` ADD INDEX idx_name (col1, col2)
   - Optimize queries: specify fields, add filters, use limit
   - Add caching: get_cached_value, frappe.cache.set_value
   |
6. Measure improvement
   Compare slow query count before/after
   Compare page load times
   |
7. Document findings
   Update LESSONS.md with discovered patterns

Workflow 5: Scale from Single Server to Multi-Server

Stage 1: Vertical scaling (exhaust single server first)
  - Upgrade RAM → increase buffer pool + Redis
  - Upgrade CPU → increase Gunicorn + RQ workers
  - Move to SSD → immediate I/O improvement
  |
Stage 2: Separate database
  - Move MariaDB to dedicated server
  - Update common_site_config.json: db_host
  - Give DB server 70% RAM as buffer pool
  - Benchmark: usually 30-50% improvement
  |
Stage 3: Separate Redis
  - Move Redis to dedicated server (or managed service)
  - Update common_site_config.json: redis_cache, redis_queue
  - Low effort, moderate improvement
  |
Stage 4: Multiple app servers
  - Two or more Frappe app servers behind load balancer
  - Shared NFS/GlusterFS for sites/ directory
  - Sticky sessions for WebSocket connections
  - Significant complexity increase
  |
Stage 5: Read replicas (for reporting)
  - MariaDB read replica for heavy reports
  - Custom code routes report queries to replica
  - Complex but enables massive read scalability

Workflow 6: Emergency Performance Triage

SCENARIO: Production site unresponsive

1. Check server is alive
   $ uptime (load average > CPU count = overloaded)
   $ free -h (no free memory = OOM risk)
   |
2. Check processes
   $ sudo supervisorctl status (any FATAL/STOPPED?)
   $ sudo systemctl status nginx
   $ sudo systemctl status mariadb
   |
   +-- Processes down? → Restart them
       $ sudo supervisorctl restart all
       $ sudo systemctl restart nginx
   |
3. Check for OOM kills
   $ dmesg | tail -50 | grep -i "oom\|killed"
   → Reduce workers, increase RAM
   |
4. Check disk space
   $ df -h (any filesystem > 95%?)
   → Clean old backups, logs, tmp files
   → $ bench --site mysite.com clear-cache
   |
5. Check MariaDB
   $ mysql -e "SHOW PROCESSLIST;"
   → Many sleeping connections? Increase wait_timeout
   → Long running query? KILL [process_id]
   |
6. Check Redis
   $ redis-cli -p 13000 ping (should return PONG)
   $ redis-cli -p 13000 INFO memory | grep used_memory_human
   → Memory full? Flush cache: redis-cli -p 13000 FLUSHDB
   |
7. Temporary relief
   $ bench --site mysite.com set-maintenance-mode on
   Fix root cause, then disable maintenance mode