Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
I think tup_fetched and tup_returned are pretty clear. tup_fetched is the number of rows "touched" (looked at, fetched, read from disk) by any query. tup_returned is the number of rows returned to the client. The number returned is e.g. reduced by a where clause during a seq scan (the seq scan would "fetch" all rows, but only "return" e.g. one) –  a_horse_with_no_name Feb 8 '13 at 8:10

1 Answer 1

It seems that pg_stat_database is thoroughly described in the manual.

As to the increasing xact_commit. You might be have AUTOCOMMIT=on. PostgreSQL will always run all your queries in a transaction. In case AUTOCOMMIT=on, COMMIT will issued after each statement executed.

In your case you have 2 statements:

  • previous SELECT * FROM pg_stat_database;
  • your test statement, like SELECT 1

You might try the following scenario:

  • open 2 sessions, S1 and S2;
  • S1: SET autocommit TO off;
  • S1: SELECT * FROM pg_stat_database;
  • S1: SELECT * FROM pg_stat_database;
  • S2: SELECT 1;
  • S1: SELECT * FROM pg_stat_database;
  • S2: SET autocommit TO off;
  • S2: SELECT 1;
  • S1: SELECT * FROM pg_stat_database;

You can continue playing around yourself.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.