Re: Tracking database activity

Tom Lane wrote:
> Erik Jones <erik(at)myemma(dot)com> writes:
>
>> ... I was previously under the impression that by
>> tracking age(datfrozenxid) in pg_database over time I'd be able to know
>> how many transactions were processed in a certain amount of time.
>> However, I've seend that pg_stat_database.xact_commit +
>> pg_stat_database.xact_rollback < pg_database.age(datfrozenxid) by a
>> factor of as much as 6 for any given time frame. Am I misunderstanding
>> something here? Where is the discrepancy coming from?
>>
>
> The age() calculation will produce a total transaction count across the
> whole installation, not individual databases --- perhaps that's the
> source of your confusion?
>
Yeah, it most likely was. Also, I've realized that in addition to being
a cluster-wide stat, the current datfrozenxid (and it's age) is also a
based on how efficient (auto)vacuum is, i.e given some constant amount
of cluster-wide xacts say, per hour, datfrozenxid will be "higher"
(and, thus, "younger") the more tuples (auto)vacuum is able to process
in that hour. So, given that in the vast majority of cases neither the
rate of cluster-wide xacts nor the rate at which vacuum can process
tuples is constant, the rate at which age(datfrozenxid) changes is going
to be a product of two different rate-of-changes! I've been using a
moving target as a stats metric! Good thing that was on an informal basis.

--
erik jones <erik(at)myemma(dot)com>
software development
emma(r)