Thread: 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)