Thread: Postgres 9.1 statistics in pg_stat_database
I've been searching for some information about Postgres 9.1 stats which are stored in pg_stat_database - on the web and
onIRC. I found SOME information for 9.2 but even that doesn't seem to be accurate. What I'm after is to get proper
transactionstats ie commits and rollbacks. Also I've no clue what tup_fetched and tup_returned mean.
I made a few tests on a dummy database. I created a database called statistics and one simple table called films in it.
Inserteda few values into it and here is what I found out:
statistics=# select xact_commit, xact_rollback, blks_read, blks_hit,tup_returned, tup_fetched, tup_inserted,
tup_updated,tup_deleted from pg_stat_database where datname='statistics';
xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated |
tup_deleted
-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------
94 | 0 | 182 | 3259 | 18345 | 1293 | 27 | 0 |
0
(1 row)
statistics=# INSERT INTO films VALUES ('UA509', 'Bananas', 105, '1971-04-13', 'Comedy', '85 minutes');
INSERT 0 1
statistics=# select xact_commit, xact_rollback, blks_read, blks_hit,tup_returned, tup_fetched, tup_inserted,
tup_updated,tup_deleted from pg_stat_database where datname='statistics';
xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated |
tup_deleted
-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------
97 | 1 | 182 | 3261 | 18345 | 1293 | 28 | 0 |
0
(1 row)
statistics=# select * from films;
code | title | did | date_prod | kind | len
-------+----------+-----+------------+--------+----------
UA502 | Bananas | 105 | 1971-07-13 | Comedy | 01:22:00
UA503 | BanaAAas | 106 | 1971-07-13 | Comedy | 01:26:00
UA504 | BanaAAas | 106 | 1971-07-13 | Comedy | 01:26:00
UA509 | Bananas | 105 | 1971-04-13 | Comedy | 01:25:00
(4 rows)
statistics=# select xact_commit, xact_rollback, blks_read, blks_hit,tup_returned, tup_fetched, tup_inserted,
tup_updated,tup_deleted from pg_stat_database where datname='statistics';
xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated |
tup_deleted
-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------
99 | 1 | 182 | 3262 | 18349 | 1293 | 28 | 0 |
0
(1 row)
What this shows is that xact_commit increases by 3 after just one row insert. tup_inserted does seem to report correct
value.After simple SELECT, xact_commit increases by 2 - kind of strange as SELECT shouldn't really be increasing
xact_commit? What I'm trying to do is to gather some reasonable data which would give me an idea about how many
transactions/sparticular Database is doing. Could any of the gurus shed some light on this ? I take it that tup_fetched
andtup_returned are some really damn random values which only Postgres developers understand :) Thanks in advance!
=?utf-8?q?Milos_Gajdos?= <gy2kre@centrum.cz> writes:
> What this shows is that xact_commit increases by 3 after just one row insert. tup_inserted does seem to report
correctvalue. After simple SELECT, xact_commit increases by 2 - kind of strange as SELECT shouldn't really be
increasingxact_commit ?
Yes, a SELECT does count as an xact_commit. What I think is confusing
you is that the commit isn't reported to the stats collector right away
unless there are some database-local table access counts to send too.
And fetching from pg_stat_database doesn't result in any such accesses.
So each SELECT results in a local increment of the commit counter but
you won't see it in the stats until after you do an INSERT, or otherwise
do something that involves a countable access to a database-local table.
I do see what seems to be a bug, which is that the "Don't expend a clock
check" short-circuit at the top of pgstat_report_stat() needs to not
fire if "force" is true. That looks like it could result in never
counting a session's last few transactions at all. But that's not
what's affecting you here, it's the intentional optimization to delay
sending transaction counts.
regards, tom lane
On 02/07/2013 11:05 AM, Tom Lane wrote: > =?utf-8?q?Milos_Gajdos?= <gy2kre@centrum.cz> writes: >> What this shows is that xact_commit increases by 3 after just one row insert. tup_inserted does seem to report correctvalue. After simple SELECT, xact_commit increases by 2 - kind of strange as SELECT shouldn't really be increasingxact_commit ? > Yes, a SELECT does count as an xact_commit... > Additionally (correct me if I'm wrong, Tom), background activity like autovacuum will generate countable transactions. I can see transactions increment by a dozen or more on a completely "idle" database if I just wait a minute or two but the number of those transactions, while visible on an idle system, is lost in the noise on a busy database. Other things that generate transactions include connecting to the database (one, it appears) but \d in psql appears to generate a minimum of four. Cheers, Steve
Steve Crawford <scrawford@pinpointresearch.com> writes:
> Additionally (correct me if I'm wrong, Tom), background activity like
> autovacuum will generate countable transactions.
Yes, absolutely.
regards, tom lane
Let me show you my statistics, that confuses me even in terms of this discussion. What am I doing. 1. Create a database *highload*. 2. Execute *select xact_commit from pg_stat_database where datname = 'highload'* from neighbouring database (so that xact_commit is counted to that neighbour) and receive 0. 3. Connect to database from several different clients and get +1 to xact_commit for each connection. 4. Wait for 1 min (several times) and get +2 each minute - so this is autovacuum works. 5. Start executing simple select *select * from pg_user* Each select gives me +4, +5, +6. In pgadmin - even +19. So the first question. 1. Why autovacuum gives +2 2. Why select gives +4 an more ? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Postgres-9-1-statistics-in-pg-stat-database-tp5744283p5769528.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.