I've been searching for some information about Postgres 9.1 stats which are 
stored in pg_stat_database - on the web and on IRC. I found SOME information 
for 9.2 but even that doesn't seem to be accurate. What I'm after is to get 
proper transaction stats 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. Inserted a 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/s particular 
Database is doing. Could any of the gurus shed some light on this ? I take it 
that tup_fetched and tup_returned are some really damn random values which only 
Postgres developers understand :) Thanks in advance!


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to