Time in active transaction too long on trigger

[marfouk]

Hi,

First, i wanna thank you for this plugin, its very helpful.

Can you please help me understanding the time displayed in this trigger : PostgreSQL active transaction too long on {HOSTNAME} (time={ITEM.LASTVALUE})

The query used to collecting data is : select coalesce(extract(epoch from max(age(now(), query_start))), 0) from pg_stat_activity where state <> 'idle in transaction' and state <> 'idle' and query NOT LIKE 'autovacuum: %'

For example the coalesce returns 0.0845 s and the trigger send alerts like : PostgreSQL active transaction too long on BDD (time=5m 16s 84ms)

Can you please help me found from where Zabbix get this time=5m 16s 84ms

Thank you

[stephankn]

As you have already seen , Zabbix reads the field query_start from pg_stat_activity.

See PostgreSQL official documentation for an in-depth explanation of the various statistics. https://www.postgresql.org/docs/10/monitoring-stats.html

In short, the table exposes what the database backend processes are currently doing.

In your case a process had been busy since 5m 16s 84ms at the time Zabbix collected the statistics. It is the difference between the current time now() and the time the activity started.

Typical problems to check for are locks blocking access to the data or queries lacking optimization and running extremely long.