PostgreSQL Timeout Configuration for iDempiere Transaction and Lock Management

[Md Rifatul Islam]

Purpose

iDempiere uses custom forms and processes to support business operations. Our iDempiere database size is more than 800 GB. Due to unexpected application or process issues, a form or process may not complete its database transaction properly. As a result, a transaction may remain open or inactive and continue holding database resources.

In some cases, tables or individual rows may also remain locked for an extended period. These inactive transactions and persistent locks can consume resources and affect database performance. They may also prevent other iDempiere processes or users from accessing the affected data.

PostgreSQL Configuration

To reduce the impact of such situations, PostgreSQL timeout parameters can be configured. The idle_in_transaction_session_timeout parameter can automatically terminate sessions that remain idle while a transaction is open. This helps release the transaction and associated database resources when an application does not close it properly.

The lock_timeout parameter limits the amount of time a transaction can wait to acquire a database lock. This prevents processes from waiting indefinitely when another transaction is holding the required lock.

Expected Benefits

These timeout settings provide a safety mechanism to handle application-level transaction and locking issues. They help release database resources and prevent tables or rows from staying locked for a long time. This can improve database stability, availability, and overall iDempiere application performance.

However, the timeout values should be set carefully to avoid affecting valid long-running transactions or processes.

I would appreciate feedback from the iDempiere community on recommended values for these parameters and any potential impact on iDempiere functionality.

[Jesús Castillo]

Hi,

First, an 800GB database is a pretty big problem. I would recommend looking into that first. You might have very large temporary tables. When I detect a disproportionate increase in database size, I run the following query to see which tables are taking up the most space:


SELECT schemaname, tablename,
pg_size_pretty(pg_total_relation_size(tablename::regclass::oid)),pg_total_relation_size(tablename::regclass::oid)
FROM pg_tables
WHERE schemaname NOT IN('pg_catalog', 'information_schema')
ORDER BY 4 desc

It’s not exact, but it helps give you an idea.

Second, check the processes that take a long time. Given the size, it seems like there may be a query performance issue. I’ve improved many queries with a simple index. Using cache in your processes also helps a lot. To get an idea of which ones take longer, you can try this SQL:


SELECT statistic_seconds/statistic_count, name FROM ad_process p WHERE statistic_count>0 ORDER BY statistic_seconds/statistic_count desc;

Third, review the performance of both your application and your database. If either one is slow, that can cause transactions to run much longer.

Fourth, monitor transactions to see whether you can detect any that take longer than normal. There may be a table or failure that is blocking everything.

Atte: Jesus Castillo.

--
You received this message because you are subscribed to the Google Groups "iDempiere" group.
To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/idempiere/892eb4b9-7a6c-4648-8cb0-6982d250cf10n%40googlegroups.com.