Additional Default Metrics

[robbrucks]

Hey Ryan,

Here are some additional metrics that we gather that could be very useful for others. You may want to add them to your base code. Super-user privileges are not needed for these queries.

Thanks, Rob

At cluster/instance level:

• Percent of Transaction ID Wraparound (at 100% cluster shuts down - about 2 billion xacts)

select round(100*max(age(datfrozenxid))::float/2000000000)::int from pg_database;

• Vacuum Freeze Age Percent (autovac kicks in at 100%)

select round(100*max(age(datfrozenxid))::float/(select setting from pg_settings where name = 'autovacuum_freeze_max_age')::float) from pg_database;

• Count of Prepared Transactions

select count(*) from pg_prepared_xacts;

• Oldest Prepared Transaction

select coalesce(max((extract(epoch from clock_timestamp()) - extract(epoch from prepared))::integer),0) from pg_prepared_xacts;

• Percent of Max Prepared Transaction

select case when setting::integer = 0 then 0 else round(100*(select count(*) from pg_prepared_xacts)::float/setting::integer) end from pg_settings where name = 'max_prepared_transactions';

Same ones, but at the DB prototype level:

• Transaction ID Wraparound Percent (at 100% cluster shuts down - about 2 billion xacts)

select round(100*max(age(datfrozenxid))::float/2000000000)::int from pg_database where datname = $1;

• Vacuum Freeze Age Percent (autovac kicks in at 100%)

select round(100*max(age(datfrozenxid))::float/(select setting from pg_settings where name = 'autovacuum_freeze_max_age')::float) from pg_database where datname = $1;

• Count of Prepared Transactions

select count(*) from pg_prepared_xacts where database = $1;

• Oldest Prepared Transaction

select coalesce(max((extract(epoch from clock_timestamp()) - extract(epoch from prepared))::integer),0) from pg_prepared_xacts where database = $1;

[cavaliercoder]

@robbrucks I'm building these out now and reading up on how XIDs work.

Can you tell me the different between your first two queries, as I understand (select setting from pg_settings where name = 'autovacuum_freeze_max_age') should always resolve to 2000000000 anyway and is the runtime upper bound for AGE(datfrozenxid)?

[cavaliercoder]

Thinking this through further... Instead of percentages, would it useful to have a Zabbix items with the raw XID age value and trigger for when the XID age comes within a precise range of the wrap limit? The triggers could use the same thresholds hard coded by Postgres; 10M and 1M as per https://github.com/postgres/postgres/blob/master/src/backend/access/transam/varsup.c#L267.

I find percentages are useful for visuals, but lack precision for triggers. Or should we build both?

[cavaliercoder]

In 038cbe2 I've added a new key pg.db.xid_age which returns the age (by transaction count) of the oldest database xid or the given database xid. These are presented as items in the PostgreSQL MVCC application in the template.

I've also added triggers to the template to match the hard coded thresholds in PostgreSQL; 10M and 1M remaining xids.

Thoughts?

[robbrucks]

That will work.