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.

// CacheMgt 提供以下關鍵操作：
CacheMgt cacheMgt = CacheMgt.get();

// 取得所有快取中的快取物件總數
int totalCached = cacheMgt.getElementCount();

// 重設所有快取（代價高昂 — 謹慎使用）
cacheMgt.reset();

// 重設特定表格的快取
cacheMgt.reset(MProduct.Table_Name);

// 重設快取中的特定記錄
cacheMgt.reset(MProduct.Table_Name, productId);

import org.compiere.util.CCache;

public class ProductCategoryCache {

    // 快取宣告：tableName, cacheSize（初始容量）, expireMinutes
    private static CCache<Integer, MProductCategory> s_cache =
        new CCache<>(MProductCategory.Table_Name, 50, 60);

    /**
     * 取得產品類別，使用快取以避免重複的資料庫查詢。
     */
    public static MProductCategory get(int M_Product_Category_ID) {
        // 先檢查快取
        MProductCategory category = s_cache.get(M_Product_Category_ID);
        if (category != null)
            return category;

        // 快取未命中 — 從資料庫載入
        category = new MProductCategory(Env.getCtx(), M_Product_Category_ID, null);

        // 儲存到快取以供未來請求使用
        if (category.get_ID() > 0)
            s_cache.put(M_Product_Category_ID, category);

        return category;
    }
}

// 基本：表格名稱和初始容量
CCache<Integer, MProduct> cache1 = new CCache<>("M_Product", 100);

// 帶過期時間：條目在 120 分鐘後過期
CCache<Integer, MProduct> cache2 = new CCache<>("M_Product", 100, 120);

// 帶分散式快取支援（用於叢集部署）
CCache<Integer, MProduct> cache3 = new CCache<>("M_Product", "M_Product",
    100, 120, false, 500);

// 不過期的快取 — 資料僅在明確重設時重新整理
private static CCache<Integer, MCountry> s_countries =
    new CCache<>(MCountry.Table_Name, 250, 0);

// 定價資料使用 5 分鐘過期的快取
private static CCache<String, BigDecimal> s_priceCache =
    new CCache<>("M_ProductPrice", 500, 5);

// 以組織 + 產品組合為鍵的快取
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;
    // ... 從資料庫載入並快取
}

// 在對產品價格的直接 SQL 更新之後
DB.executeUpdate("UPDATE M_ProductPrice SET PriceStd = 99.99 WHERE ...", null);
CacheMgt.get().reset("M_ProductPrice");  // 使價格快取失效

-- 分析慢查詢
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;

-- 經常篩選欄位的索引
CREATE INDEX idx_custom_table_status
ON custom_table (DocStatus)
WHERE IsActive = 'Y';

-- 常見查詢模式的複合索引
CREATE INDEX idx_custom_table_partner_date
ON custom_table (C_BPartner_ID, DateOrdered DESC);

-- 僅限有效記錄的部分索引（減少索引大小）
CREATE INDEX idx_custom_table_active
ON custom_table (Value)
WHERE IsActive = 'Y';

-- 外鍵索引（對 JOIN 效能至關重要）
CREATE INDEX idx_custom_line_header
ON custom_line_table (custom_header_id);

// 良好：使用 Query 類別和參數（防止 SQL 注入，啟用計畫快取）
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();

// 不好：字串串接（SQL 注入風險，無計畫快取）
String sql = "SELECT * FROM C_Order WHERE C_BPartner_ID=" + bpartnerId;

// 良好：大型結果集僅擷取需要的欄位
int count = new Query(ctx, MOrder.Table_Name,
    "C_BPartner_ID=? AND DocStatus='CO'", null)
    .setParameters(bpartnerId)
    .count();

// 良好：預期單一結果時使用 first() 而非 list()
MBPartner bp = new Query(ctx, MBPartner.Table_Name, "Value=?", null)
    .setParameters(customerCode)
    .first();

// 延遲載入模式
public class OrderProcessor {

    private MBPartner partner;  // 按需載入

    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) {
            // 夥伴僅在需要時載入，並在相同業務夥伴的
            // 訂單間重複使用
            MBPartner bp = getPartner(order);
            // ... 處理訂單
        }
    }
}

# 在 idempiere-server.sh 或 idempiere.ini 中設定
# 最小堆積大小 — 設定為與最大值相同以避免調整大小暫停
-Xms2g
# 最大堆積大小 — 通常為可用 RAM 的 50-75%
-Xmx4g

# 使用 G1 垃圾回收器（Java 17 預設）
-XX:+UseG1GC
# 設定最大 GC 暫停目標（毫秒）
-XX:MaxGCPauseMillis=200
# 設定大型堆積的區域大小
-XX:G1HeapRegionSize=16m
# 啟用 GC 日誌以供分析
-Xlog:gc*:file=/opt/idempiere/log/gc.log:time,uptime,level,tags:filecount=5,filesize=20m

# 類別中繼資料的 Metaspace（OSGi 載入許多類別）
-XX:MetaspaceSize=256m
-XX:MaxMetaspaceSize=512m

# 執行緒堆疊大小（如果有許多同時使用者則減少）
-Xss512k

# 字串去重（減少重複字串的記憶體使用）
-XX:+UseStringDeduplication

# 記憶體設定
shared_buffers = '2GB'          # 總 RAM 的 25%（例如 8GB 伺服器使用 2GB）
effective_cache_size = '6GB'    # 總 RAM 的 75%（告訴規劃器有關 OS 快取）
work_mem = '64MB'               # 每次排序操作的記憶體（複雜報表時增加）
maintenance_work_mem = '512MB'  # 用於 VACUUM、CREATE INDEX 等

# 預寫日誌
wal_buffers = '64MB'            # WAL 緩衝區大小
checkpoint_completion_target = 0.9  # 分散檢查點寫入
max_wal_size = '2GB'            # 檢查點前的最大 WAL 大小

# 查詢規劃器
random_page_cost = 1.1          # SSD 儲存設定為 1.1（預設 4.0 用於 HDD）
effective_io_concurrency = 200  # SSD 儲存（預設 1 用於 HDD）

# 平行處理
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_worker_processes = 8

# 連線設定
max_connections = 200           # 匹配您的連線池最大值 + 管理開銷

# 在忙碌的表格上更頻繁地執行 autovacuum
autovacuum_vacuum_scale_factor = 0.05     # 預設 0.2 — 在 5% 死行時觸發
autovacuum_analyze_scale_factor = 0.02    # 預設 0.1 — 在 2% 變更時更新統計
autovacuum_vacuum_cost_delay = '2ms'      # 預設 2ms — SSD 可接受

# 在 idempiereEnv.properties 或連線池設定中
# 最小閒置連線 — 保持一些連線就緒
db.connection.pool.min=10
# 最大連線 — 限制以防止資料庫過載
db.connection.pool.max=50
# 連線驗證 — 使用前測試連線
db.connection.pool.testOnBorrow=true
# 30 分鐘後驅逐閒置連線
db.connection.pool.minEvictableIdleTimeMillis=1800000

# 在 postgresql.conf 中
log_min_duration_statement = 1000   # 記錄超過 1 秒的查詢
log_statement = 'none'              # 不記錄所有語句（太冗長）
log_line_prefix = '%t [%p] %u@%d '  # 包含時間戳、PID、使用者、資料庫

# 在啟動腳本中啟用 JMX
-Dcom.sun.management.jmxremote
-Dcom.sun.management.jmxremote.port=9090
-Dcom.sun.management.jmxremote.ssl=false
-Dcom.sun.management.jmxremote.authenticate=true

-- 活動查詢及其持續時間
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- 表格存取統計（識別熱門表格）
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;

-- 索引使用統計（識別未使用的索引）
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;

-- 快取命中率（應高於 99%）
SELECT
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

// CacheMgt は以下の主要な操作を提供します：
CacheMgt cacheMgt = CacheMgt.get();

// すべてのキャッシュにわたるキャッシュオブジェクトの合計数を取得
int totalCached = cacheMgt.getElementCount();

// すべてのキャッシュをリセット（コストが高い — 慎重に使用）
cacheMgt.reset();

// 特定のテーブルのキャッシュをリセット
cacheMgt.reset(MProduct.Table_Name);

// キャッシュ内の特定のレコードをリセット
cacheMgt.reset(MProduct.Table_Name, productId);

import org.compiere.util.CCache;

public class ProductCategoryCache {

    // キャッシュ宣言：tableName, cacheSize（初期容量）, expireMinutes
    private static CCache<Integer, MProductCategory> s_cache =
        new CCache<>(MProductCategory.Table_Name, 50, 60);

    /**
     * 製品カテゴリを取得し、キャッシュを使用して繰り返しの DB クエリを回避します。
     */
    public static MProductCategory get(int M_Product_Category_ID) {
        // まずキャッシュを確認
        MProductCategory category = s_cache.get(M_Product_Category_ID);
        if (category != null)
            return category;

        // キャッシュミス — データベースからロード
        category = new MProductCategory(Env.getCtx(), M_Product_Category_ID, null);

        // 将来のリクエストのためにキャッシュに格納
        if (category.get_ID() > 0)
            s_cache.put(M_Product_Category_ID, category);

        return category;
    }
}

// 基本：テーブル名と初期容量
CCache<Integer, MProduct> cache1 = new CCache<>("M_Product", 100);

// 期限切れ付き：エントリは 120 分後に期限切れ
CCache<Integer, MProduct> cache2 = new CCache<>("M_Product", 100, 120);

// 分散キャッシュサポート付き（クラスタデプロイメント用）
CCache<Integer, MProduct> cache3 = new CCache<>("M_Product", "M_Product",
    100, 120, false, 500);

// 期限切れなしのキャッシュ — 明示的なリセット時のみデータが更新される
private static CCache<Integer, MCountry> s_countries =
    new CCache<>(MCountry.Table_Name, 250, 0);

// 価格データ用の 5 分間期限切れキャッシュ
private static CCache<String, BigDecimal> s_priceCache =
    new CCache<>("M_ProductPrice", 500, 5);

// 組織 + 製品の組み合わせをキーとするキャッシュ
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;
    // ... データベースからロードしてキャッシュ
}

// 製品価格への直接 SQL 更新後
DB.executeUpdate("UPDATE M_ProductPrice SET PriceStd = 99.99 WHERE ...", null);
CacheMgt.get().reset("M_ProductPrice");  // 価格キャッシュを無効化

-- 遅いクエリを分析
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;

-- 頻繁にフィルタリングされるカラムのインデックス
CREATE INDEX idx_custom_table_status
ON custom_table (DocStatus)
WHERE IsActive = 'Y';

-- 一般的なクエリパターンの複合インデックス
CREATE INDEX idx_custom_table_partner_date
ON custom_table (C_BPartner_ID, DateOrdered DESC);

-- アクティブなレコードのみの部分インデックス（インデックスサイズを削減）
CREATE INDEX idx_custom_table_active
ON custom_table (Value)
WHERE IsActive = 'Y';

-- 外部キーのインデックス（JOIN パフォーマンスに不可欠）
CREATE INDEX idx_custom_line_header
ON custom_line_table (custom_header_id);

// 良い：パラメータ付きの Query クラスを使用（SQL インジェクションを防止、プランキャッシュを有効化）
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();

// 悪い：文字列連結（SQL インジェクションのリスク、プランキャッシュなし）
String sql = "SELECT * FROM C_Order WHERE C_BPartner_ID=" + bpartnerId;

// 良い：大きな結果セットでは必要なカラムのみを取得
int count = new Query(ctx, MOrder.Table_Name,
    "C_BPartner_ID=? AND DocStatus='CO'", null)
    .setParameters(bpartnerId)
    .count();

// 良い：単一の結果を期待する場合は list() ではなく first() を使用
MBPartner bp = new Query(ctx, MBPartner.Table_Name, "Value=?", null)
    .setParameters(customerCode)
    .first();

// 遅延ロードパターン
public class OrderProcessor {

    private MBPartner partner;  // 必要に応じてロード

    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) {
            // パートナーは必要な時にのみロードされ、同じビジネスパートナーの
            // 受注間で再利用されます
            MBPartner bp = getPartner(order);
            // ... 受注を処理
        }
    }
}

# idempiere-server.sh または idempiere.ini で設定
# 最小ヒープサイズ — リサイズの一時停止を避けるために最大と同じに設定
-Xms2g
# 最大ヒープサイズ — 通常、利用可能な RAM の 50-75%
-Xmx4g

# G1 ガベージコレクターを使用（Java 17 のデフォルト）
-XX:+UseG1GC
# 最大 GC 一時停止目標を設定（ミリ秒）
-XX:MaxGCPauseMillis=200
# 大きなヒープのリージョンサイズを設定
-XX:G1HeapRegionSize=16m
# 分析用の GC ロギングを有効化
-Xlog:gc*:file=/opt/idempiere/log/gc.log:time,uptime,level,tags:filecount=5,filesize=20m

# クラスメタデータ用の Metaspace（OSGi は多くのクラスをロード）
-XX:MetaspaceSize=256m
-XX:MaxMetaspaceSize=512m

# スレッドスタックサイズ（同時ユーザーが多い場合は削減）
-Xss512k

# 文字列重複排除（繰り返し文字列のメモリを削減）
-XX:+UseStringDeduplication

# メモリ設定
shared_buffers = '2GB'          # 合計 RAM の 25%（例：8GB サーバーで 2GB）
effective_cache_size = '6GB'    # 合計 RAM の 75%（OS キャッシュについてプランナーに通知）
work_mem = '64MB'               # ソート操作ごとのメモリ（複雑なレポートでは増加）
maintenance_work_mem = '512MB'  # VACUUM、CREATE INDEX などに使用

# 先行書き込みログ
wal_buffers = '64MB'            # WAL バッファサイズ
checkpoint_completion_target = 0.9  # チェックポイント書き込みを分散
max_wal_size = '2GB'            # チェックポイント前の最大 WAL サイズ

# クエリプランナー
random_page_cost = 1.1          # SSD ストレージの場合 1.1 に設定（デフォルト 4.0 は HDD 用）
effective_io_concurrency = 200  # SSD ストレージ用（デフォルト 1 は HDD 用）

# 並列処理
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_worker_processes = 8

# 接続設定
max_connections = 200           # コネクションプールの最大値 + 管理オーバーヘッドに一致

# ビジーなテーブルで autovacuum をより頻繁に実行
autovacuum_vacuum_scale_factor = 0.05     # デフォルト 0.2 — 5% のデッドロウでトリガー
autovacuum_analyze_scale_factor = 0.02    # デフォルト 0.1 — 2% の変更で統計を更新
autovacuum_vacuum_cost_delay = '2ms'      # デフォルト 2ms — SSD では許容可能

# idempiereEnv.properties またはコネクションプール設定で
# 最小アイドル接続 — いくつかの接続を準備状態に保つ
db.connection.pool.min=10
# 最大接続 — データベースの過負荷を防ぐために制限
db.connection.pool.max=50
# 接続の検証 — 使用前に接続をテスト
db.connection.pool.testOnBorrow=true
# 30 分後にアイドル接続を退避
db.connection.pool.minEvictableIdleTimeMillis=1800000

# postgresql.conf で
log_min_duration_statement = 1000   # 1 秒以上かかるクエリをログに記録
log_statement = 'none'              # すべてのステートメントをログに記録しない（冗長すぎる）
log_line_prefix = '%t [%p] %u@%d '  # タイムスタンプ、PID、ユーザー、データベースを含む

# 起動スクリプトで JMX を有効化
-Dcom.sun.management.jmxremote
-Dcom.sun.management.jmxremote.port=9090
-Dcom.sun.management.jmxremote.ssl=false
-Dcom.sun.management.jmxremote.authenticate=true

-- アクティブなクエリとその実行時間
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- テーブルアクセス統計（ホットテーブルの特定）
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;

-- インデックス使用統計（未使用インデックスの特定）
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;

-- キャッシュヒット率（99% 以上であるべき）
SELECT
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;