odoo-performance-tuner

name	odoo-performance-tuner
description	Expert guide for diagnosing and fixing Odoo performance issues: slow queries, worker configuration, memory limits, PostgreSQL tuning, and profiling tools.
risk	safe
source	self

Odoo Performance Tuner

Overview

This skill helps diagnose and resolve Odoo performance problems — from slow page loads and database bottlenecks to worker misconfiguration and memory bloat. It covers PostgreSQL query tuning, Odoo worker settings, and built-in profiling tools.

When to Use This Skill

• Odoo is slow in production (slow page loads, timeouts).
• Getting MemoryError or Worker timeout errors in logs.
• Diagnosing a slow database query using Odoo's profiler.
• Tuning odoo.conf for a specific server spec.

How It Works

1. Activate: Mention @odoo-performance-tuner and describe your performance issue.
2. Diagnose: Share relevant log lines or config and receive a root cause analysis.
3. Fix: Get exact configuration changes with explanations.

Examples

Example 1: Recommended Worker Configuration

# odoo.conf — tuned for a 4-core, 8GB RAM server

workers = 9                   # (CPU_cores × 2) + 1 — never set to 0 in production
max_cron_threads = 2          # background cron jobs; keep ≤ 2 to preserve user-facing capacity
limit_memory_soft = 1610612736  # 1.5 GB — worker is recycled gracefully after this
limit_memory_hard = 2147483648  # 2.0 GB — worker is killed immediately; prevents OOM crashes
limit_time_cpu = 600          # max CPU seconds per request
limit_time_real = 1200        # max wall-clock seconds per request
limit_request = 8192          # max requests before worker recycles (prevents memory leaks)

Example 2: Find Slow Queries with PostgreSQL

-- Step 1: Enable pg_stat_statements extension (run once as postgres superuser)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Step 2: Also add to postgresql.conf and reload:
-- shared_preload_libraries = 'pg_stat_statements'
-- log_min_duration_statement = 1000   -- log queries taking > 1 second

-- Step 3: Find the top 10 slowest average queries
SELECT
    LEFT(query, 100) AS query_snippet,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Step 4: Check for missing indexes causing full table scans
SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'sale_order_line'
  AND correlation < 0.5   -- low correlation = poor index efficiency
ORDER BY n_distinct DESC;

Example 3: Use Odoo's Built-In Profiler

Prerequisites: Run Odoo with ?debug=1 in the URL to enable debug mode.

Menu: Settings → Technical → Profiling

Steps:
  1. Click "Enable Profiling" — set a duration (e.g., 60 seconds)
  2. Navigate to and reproduce the slow action
  3. Return to Settings → Technical → Profiling → View Results

What to look for:
  - Total SQL queries > 100 on a single page  → N+1 query problem
  - Single queries taking > 100ms             → missing DB index
  - Same query repeated many times            → missing cache, use @ormcache
  - Python time high but SQL low             → compute field inefficiency

Best Practices

• ✅ Do: Use mapped(), filtered(), and sorted() on in-memory recordsets — they don't trigger additional SQL.
• ✅ Do: Add PostgreSQL B-tree indexes on columns frequently used in domain filters (partner_id, state, date_order).
• ✅ Do: Enable Odoo's HTTP caching for static assets and put a CDN (Cloudflare, AWS CloudFront) in front of the website.
• ✅ Do: Use @tools.ormcache decorator on methods pulled repeatedly with the same arguments.
• ❌ Don't: Set workers = 0 in production — single-threaded mode serializes all requests and blocks all users on any slow operation.
• ❌ Don't: Ignore limit_memory_soft — workers exceeding it are recycled between requests; without the limit they grow unbounded and crash.
• ❌ Don't: Directly manipulate prefetch_ids on recordsets — rely on Odoo's automatic batch prefetching, which activates by default.

Limitations

• PostgreSQL tuning (shared_buffers, work_mem, effective_cache_size) is highly server-specific and not covered in depth here — use PGTune as a starting baseline.
• The built-in Odoo profiler only captures Python + SQL traces; JavaScript rendering performance requires browser DevTools.
• Odoo.sh managed hosting restricts direct PostgreSQL and odoo.conf access — some tuning options are unavailable.
• Does not cover Redis-based session store or Celery task queue optimizations, which are advanced patterns for very high-traffic instances.