Performance Tuning and Caching

Overview

• What you’ll learn:
  • How iDempiere’s CacheMgt system and CCache class work, and how to use caching effectively in your custom code
  • How to optimize SQL queries, design indexes for custom tables, and tune PostgreSQL configuration for iDempiere workloads
  • How to tune JVM settings, configure connection pools, monitor performance, and diagnose common bottlenecks
• Prerequisites: Lesson 2 — iDempiere Architecture Overview, Lesson 15 — Building Your First Plugin
• Estimated reading time: 24 minutes

Introduction

A well-configured iDempiere installation can serve hundreds of concurrent users processing thousands of transactions daily. However, performance does not happen by accident — it requires deliberate attention to caching, query optimization, JVM configuration, and database tuning. Performance problems in ERP systems are particularly costly because they affect every user and every business process, from order entry to financial reporting.

This lesson covers performance optimization at every layer of the iDempiere stack: the application-level cache, the Java Virtual Machine, the database engine, and the connection pool. You will learn both the theory behind each optimization and the practical configuration changes to implement it.

The CacheMgt System

iDempiere’s Cache Manager (CacheMgt) is a centralized system that coordinates all application-level caches. It tracks every cache instance, provides global statistics, and supports targeted or full cache resets. Understanding CacheMgt is essential because iDempiere relies heavily on caching to avoid repeated database queries for configuration data, reference lists, and frequently accessed records.

How CacheMgt Works

When iDempiere starts, various subsystems register their caches with CacheMgt. Each cache is identified by a table name (e.g., AD_Column, C_BPartner) and stores key-value pairs in memory. When data changes — through the UI, API, or background processes — the cache manager can selectively invalidate affected caches.

// CacheMgt provides these key operations:
CacheMgt cacheMgt = CacheMgt.get();

// Get total number of cached objects across all caches
int totalCached = cacheMgt.getElementCount();

// Reset all caches (expensive — use sparingly)
cacheMgt.reset();

// Reset caches for a specific table
cacheMgt.reset(MProduct.Table_Name);

// Reset a specific record in caches
cacheMgt.reset(MProduct.Table_Name, productId);

Cache Statistics

Monitor cache effectiveness through the iDempiere System Admin menu. Navigate to System Admin > Cache Management to view all registered caches, their sizes, and hit/miss statistics. This information helps you identify which caches are effective and which might need tuning.

The CCache Class

CCache is iDempiere’s primary cache implementation — a hash map that automatically registers itself with CacheMgt and supports time-based expiration. You will use CCache extensively when building plugins that need to cache data for performance.

Basic Usage

import org.compiere.util.CCache;

public class ProductCategoryCache {

    // Cache declaration: tableName, cacheSize (initial capacity), expireMinutes
    private static CCache<Integer, MProductCategory> s_cache =
        new CCache<>(MProductCategory.Table_Name, 50, 60);

    /**
     * Get a product category, using cache to avoid repeated DB queries.
     */
    public static MProductCategory get(int M_Product_Category_ID) {
        // Check cache first
        MProductCategory category = s_cache.get(M_Product_Category_ID);
        if (category != null)
            return category;

        // Cache miss — load from database
        category = new MProductCategory(Env.getCtx(), M_Product_Category_ID, null);

        // Store in cache for future requests
        if (category.get_ID() > 0)
            s_cache.put(M_Product_Category_ID, category);

        return category;
    }
}

CCache Constructor Parameters

The CCache constructor accepts several parameters that control its behavior:

// Basic: table name and initial capacity
CCache<Integer, MProduct> cache1 = new CCache<>("M_Product", 100);

// With expiration: entries expire after 120 minutes
CCache<Integer, MProduct> cache2 = new CCache<>("M_Product", 100, 120);

// With distributed cache support (for clustered deployments)
CCache<Integer, MProduct> cache3 = new CCache<>("M_Product", "M_Product",
    100, 120, false, 500);

• Table name (first parameter): Used by CacheMgt to identify which caches to reset when data changes in a specific table. Use the actual table name so cache invalidation works correctly.
• Initial capacity: Pre-allocates space for the expected number of entries. Set this to your expected cache size to avoid rehashing.
• Expire minutes: Entries older than this are automatically evicted. Set to 0 for no expiration. Use shorter expiration for frequently changing data and longer expiration (or no expiration) for stable reference data.
• Max size: Limits the maximum number of entries. When exceeded, the least recently used entries are evicted.

Cache Patterns for Common Scenarios

Immutable Reference Data

For data that rarely changes (countries, currencies, UOMs), use long or no expiration:

// Cache with no expiration — data only refreshes on explicit reset
private static CCache<Integer, MCountry> s_countries =
    new CCache<>(MCountry.Table_Name, 250, 0);

Frequently Changing Data

For volatile data (pricing, inventory levels), use short expiration or avoid caching entirely:

// Cache with 5-minute expiration for pricing data
private static CCache<String, BigDecimal> s_priceCache =
    new CCache<>("M_ProductPrice", 500, 5);

Composite Cache Keys

When the cache key involves multiple fields, create a composite key:

// Cache keyed by org + product combination
private static CCache<String, BigDecimal> s_qtyCache =
    new CCache<>("M_StorageOnHand", 1000, 10);

public static BigDecimal getQtyOnHand(int orgId, int productId) {
    String key = orgId + "_" + productId;
    BigDecimal qty = s_qtyCache.get(key);
    if (qty != null) return qty;
    // ... load from database and cache
}

Cache Invalidation

Cache invalidation is one of the hardest problems in software engineering. iDempiere provides several mechanisms:

Automatic Invalidation

When a record is saved through iDempiere’s PO (Persistent Object) framework, the framework automatically calls CacheMgt.reset(tableName, recordId). This means caches registered with the correct table name are automatically invalidated when data changes through the UI or API.

Manual Invalidation

If data changes outside the PO framework (direct SQL updates, external processes), you must manually invalidate affected caches:

// After a direct SQL update to product prices
DB.executeUpdate("UPDATE M_ProductPrice SET PriceStd = 99.99 WHERE ...", null);
CacheMgt.get().reset("M_ProductPrice");  // Invalidate price caches

Cache Reset via UI

Administrators can reset all caches from the System Admin > Cache Reset window. This is useful after bulk data imports or direct database modifications. In a multi-node deployment, cache reset messages are broadcast to all nodes.

Query Optimization

Even with effective caching, many operations require database queries. Optimizing these queries has a direct impact on user experience and system throughput.

Using EXPLAIN to Analyze Queries

PostgreSQL’s EXPLAIN ANALYZE command reveals exactly how the database executes a query and where time is spent:

-- Analyze a slow query
EXPLAIN ANALYZE
SELECT bp.C_BPartner_ID, bp.Name, bp.Value, loc.City
FROM C_BPartner bp
JOIN C_BPartner_Location bpl ON bp.C_BPartner_ID = bpl.C_BPartner_ID
JOIN C_Location loc ON bpl.C_Location_ID = loc.C_Location_ID
WHERE bp.IsCustomer = 'Y'
  AND bp.IsActive = 'Y'
  AND bp.AD_Client_ID = 11
ORDER BY bp.Name;

Key things to look for in the EXPLAIN output:

• Seq Scan on large tables — indicates a missing index.
• Nested Loop with high row counts — may indicate a need for a different join strategy or index.
• Sort operations on large result sets — consider adding an index that matches the ORDER BY clause.
• Actual time vs. estimated time — large discrepancies indicate stale statistics; run ANALYZE on the affected tables.

Index Strategies for Custom Tables

When creating custom tables for your plugins, design indexes thoughtfully:

-- Index for frequently filtered columns
CREATE INDEX idx_custom_table_status
ON custom_table (DocStatus)
WHERE IsActive = 'Y';

-- Composite index for common query patterns
CREATE INDEX idx_custom_table_partner_date
ON custom_table (C_BPartner_ID, DateOrdered DESC);

-- Partial index for active records only (reduces index size)
CREATE INDEX idx_custom_table_active
ON custom_table (Value)
WHERE IsActive = 'Y';

-- Index for foreign keys (critical for JOIN performance)
CREATE INDEX idx_custom_line_header
ON custom_line_table (custom_header_id);

Index design guidelines for iDempiere:

• Always index foreign key columns — iDempiere windows frequently join parent-child tables.
• Index columns used in WHERE clauses of common queries and report parameters.
• Use partial indexes with WHERE IsActive = 'Y' since most queries filter on active records.
• Avoid over-indexing — each index slows down INSERT and UPDATE operations.
• For columns with low cardinality (e.g., IsSOTrx with only Y/N values), indexes are only helpful when combined with other columns or as partial indexes.

Query Best Practices in Plugin Code

// GOOD: Use Query class with parameters (prevents SQL injection, enables plan caching)
List<MOrder> orders = new Query(ctx, MOrder.Table_Name,
    "C_BPartner_ID=? AND DocStatus IN (?,?) AND DateOrdered>=?", trxName)
    .setParameters(bpartnerId, "CO", "CL", startDate)
    .setApplyAccessFilter(true)
    .setOrderBy("DateOrdered DESC")
    .list();

// BAD: String concatenation (SQL injection risk, no plan caching)
String sql = "SELECT * FROM C_Order WHERE C_BPartner_ID=" + bpartnerId;

// GOOD: Retrieve only needed columns for large result sets
int count = new Query(ctx, MOrder.Table_Name,
    "C_BPartner_ID=? AND DocStatus='CO'", null)
    .setParameters(bpartnerId)
    .count();

// GOOD: Use first() instead of list() when you expect a single result
MBPartner bp = new Query(ctx, MBPartner.Table_Name, "Value=?", null)
    .setParameters(customerCode)
    .first();

Lazy Loading Patterns

Avoid loading data until it is actually needed. This is particularly important for records with large text fields or many related child records:

// Lazy loading pattern
public class OrderProcessor {

    private MBPartner partner;  // loaded on demand

    private MBPartner getPartner(MOrder order) {
        if (partner == null || partner.getC_BPartner_ID() != order.getC_BPartner_ID()) {
            partner = MBPartner.get(Env.getCtx(), order.getC_BPartner_ID());
        }
        return partner;
    }

    public void processOrders(List<MOrder> orders) {
        for (MOrder order : orders) {
            // Partner is only loaded when needed and reused across orders
            // for the same business partner
            MBPartner bp = getPartner(order);
            // ... process order
        }
    }
}

JVM Tuning for iDempiere

iDempiere runs on the Java Virtual Machine, and JVM configuration significantly affects performance. The key settings are in the idempiereEnv.properties file or the startup script.

Heap Size Configuration

# Set in idempiere-server.sh or idempiere.ini
# Minimum heap size — set equal to max to avoid resize pauses
-Xms2g
# Maximum heap size — typically 50-75% of available RAM
-Xmx4g

Sizing guidelines:

• Small deployment (1-10 users): 1-2 GB heap
• Medium deployment (10-50 users): 2-4 GB heap
• Large deployment (50-200 users): 4-8 GB heap
• Enterprise deployment (200+ users): 8-16 GB heap

Set -Xms equal to -Xmx to prevent the JVM from wasting time growing and shrinking the heap.

Garbage Collection Settings

Modern JVMs (Java 17+) default to the G1 garbage collector, which is generally well-suited for iDempiere. Fine-tune it for your workload:

# Use G1 garbage collector (default in Java 17)
-XX:+UseG1GC
# Set maximum GC pause target (milliseconds)
-XX:MaxGCPauseMillis=200
# Set region size for large heaps
-XX:G1HeapRegionSize=16m
# Enable GC logging for analysis
-Xlog:gc*:file=/opt/idempiere/log/gc.log:time,uptime,level,tags:filecount=5,filesize=20m

For iDempiere workloads characterized by many short-lived objects (request processing) and some long-lived objects (caches), G1’s default behavior is usually optimal. If you observe long GC pauses, consider ZGC (-XX:+UseZGC) which provides sub-millisecond pause times but uses more CPU.

Other JVM Settings

# Metaspace for class metadata (OSGi loads many classes)
-XX:MetaspaceSize=256m
-XX:MaxMetaspaceSize=512m

# Thread stack size (reduce if you have many concurrent users)
-Xss512k

# String deduplication (reduces memory for repeated strings)
-XX:+UseStringDeduplication

PostgreSQL Tuning

The database is often the primary bottleneck in ERP systems. PostgreSQL’s default configuration is extremely conservative and must be tuned for production iDempiere workloads.

Key Configuration Parameters

Edit postgresql.conf with these recommended settings:

# Memory settings
shared_buffers = '2GB'          # 25% of total RAM (e.g., 2GB for 8GB server)
effective_cache_size = '6GB'    # 75% of total RAM (tells planner about OS cache)
work_mem = '64MB'               # Per-sort operation memory (increase for complex reports)
maintenance_work_mem = '512MB'  # For VACUUM, CREATE INDEX, etc.

# Write-ahead log
wal_buffers = '64MB'            # WAL buffer size
checkpoint_completion_target = 0.9  # Spread checkpoint writes
max_wal_size = '2GB'            # Maximum WAL size before checkpoint

# Query planner
random_page_cost = 1.1          # Set to 1.1 for SSD storage (default 4.0 is for HDD)
effective_io_concurrency = 200  # For SSD storage (default 1 is for HDD)

# Parallelism
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_worker_processes = 8

# Connection settings
max_connections = 200           # Match your connection pool max + admin overhead

Tuning Guidelines by Server Size

Autovacuum Configuration

PostgreSQL’s autovacuum process reclaims dead row space and updates statistics. For iDempiere’s write-heavy tables (C_Order, C_Invoice, AD_PInstance), tune autovacuum to run more aggressively:

# Run autovacuum more frequently on busy tables
autovacuum_vacuum_scale_factor = 0.05     # Default 0.2 — trigger at 5% dead rows
autovacuum_analyze_scale_factor = 0.02    # Default 0.1 — update stats at 2% changes
autovacuum_vacuum_cost_delay = '2ms'      # Default 2ms — acceptable for SSDs

Connection Pool Configuration

iDempiere uses a database connection pool to reuse database connections rather than creating a new connection for each request. Proper pool sizing is critical.

# In idempiereEnv.properties or connection pool configuration
# Minimum idle connections — keep some connections ready
db.connection.pool.min=10
# Maximum connections — limit to prevent database overload
db.connection.pool.max=50
# Connection validation — test connections before use
db.connection.pool.testOnBorrow=true
# Evict idle connections after 30 minutes
db.connection.pool.minEvictableIdleTimeMillis=1800000

The maximum pool size should be less than PostgreSQL’s max_connections setting, leaving room for administrative connections and other applications. A good rule of thumb: set the pool maximum to the expected number of concurrent active users plus 20% overhead.

Monitoring and Profiling

Identifying Slow Queries

Enable PostgreSQL’s slow query log to identify performance bottlenecks:

# In postgresql.conf
log_min_duration_statement = 1000   # Log queries taking more than 1 second
log_statement = 'none'              # Don't log all statements (too verbose)
log_line_prefix = '%t [%p] %u@%d '  # Include timestamp, PID, user, database

JVM Monitoring

Use JMX (Java Management Extensions) to monitor iDempiere’s JVM in real-time:

# Enable JMX in startup script
-Dcom.sun.management.jmxremote
-Dcom.sun.management.jmxremote.port=9090
-Dcom.sun.management.jmxremote.ssl=false
-Dcom.sun.management.jmxremote.authenticate=true

Connect to the JMX port using tools like VisualVM, JConsole, or Grafana with a JMX exporter. Monitor these key metrics:

• Heap usage: Should stay below 80% of max heap. Consistently higher indicates a memory leak or insufficient heap.
• GC frequency and pause times: Frequent long pauses indicate heap sizing or GC algorithm issues.
• Thread count: Excessively high thread counts suggest thread leaks or too many concurrent sessions.
• CPU usage: Sustained high CPU may indicate inefficient queries or insufficient caching.

PostgreSQL Monitoring Queries

-- Active queries and their duration
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Table access statistics (identifies hot tables)
SELECT relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 20;

-- Index usage statistics (identifies unused indexes)
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;

-- Cache hit ratio (should be above 99%)
SELECT
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

Common Performance Bottlenecks

Here are the most frequent performance issues encountered in iDempiere deployments and their solutions:

Problem: Slow Window Loading

Cause: Windows with many fields and lookups generate numerous queries to populate dropdown lists and reference data.

Solution: Ensure Application Dictionary caches are warmed up. Review the window’s tabs and remove unnecessary fields. Add indexes on lookup columns.

Problem: Report Timeouts

Cause: Complex reports with large date ranges query millions of rows without proper indexing.

Solution: Add indexes that match the report’s WHERE clause parameters. Use materialized views for complex aggregations. Increase work_mem for sort-heavy reports.

Problem: Document Processing Slowdowns

Cause: Completing orders or invoices with many lines triggers cascading model validators and callouts.

Solution: Profile model validators to identify slow ones. Use batch processing for bulk operations. Ensure validators use cached data rather than querying the database for each line.

Problem: High Memory Usage

Cause: Large caches, memory leaks in plugins, or sessions not being properly cleaned up.

Solution: Review CCache sizes and expiration settings. Use heap dump analysis (jmap, Eclipse MAT) to identify memory hogs. Implement session timeout policies.

Summary

Performance tuning is a systematic discipline, not a one-time task. You learned how to leverage iDempiere’s CCache system for application-level caching, optimize database queries with proper indexing and EXPLAIN analysis, tune the JVM for your workload, configure PostgreSQL for production use, and monitor the entire stack to identify and resolve bottlenecks. Apply these techniques incrementally, measuring the impact of each change, and your iDempiere deployment will serve your users reliably at scale. In the next lesson, we shift focus to the user interface and explore advanced UI customization techniques using ZK components and dashboard gadgets.