Обсуждение: Tracking database activity

Hi, I'm hoping someone can help me wrap my head around some #s I'm using
to track database activity.  I have a script that runs hourly and
queries pg_stat_database and checks age(datfrozenxid) in pg_database.
It logs those stats and the next hour, when it runs, it takes the
differences to check the amount of activity over the last hour and then
logs the new stats.  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?

--
erik jones <erik@myemma.com>
software development
emma(r)

Erik Jones <erik@myemma.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?

            regards, tom lane

Tom Lane wrote:
> Erik Jones <erik@myemma.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@myemma.com>
software development
emma(r)