To: <pgsql-admin@postgresql.org>
Subject: =?utf-8?q?Postgres_9=2E1_statistics_in_pg=5Fstat=5Fdatabase?=
Date: Thu, 07 Feb 2013 19:16:41 +0100
From: =?utf-8?q?Milos_Gajdos?= <gy2kre@centrum.cz>
MIME-Version: 1.0
Message-Id: <20130207191641.149E5128@centrum.cz>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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 informati=
on 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 wh=
at tup_fetched and tup_returned mean.

I made a few tests on a dummy database. I created a database called statist=
ics and one simple table called films in it. Inserted a few values into it =
and here is what I found out:

statistics=3D# select xact_commit, xact_rollback, blks_read, blks_hit,tup_r=
eturned, tup_fetched, tup_inserted, tup_updated, tup_deleted from pg_stat_d=
atabase where datname=3D'statistics';
 xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fe=
tched | tup_inserted | tup_updated | tup_deleted=20
-------------+---------------+-----------+----------+--------------+-------=
------+--------------+-------------+-------------
          94 |             0 |       182 |     3259 |        18345 |       =
 1293 |           27 |           0 |           0
(1 row)

statistics=3D# INSERT INTO films VALUES ('UA509', 'Bananas', 105, '1971-04-=
13', 'Comedy', '85 minutes');
INSERT 0 1
statistics=3D# select xact_commit, xact_rollback, blks_read, blks_hit,tup_r=
eturned, tup_fetched, tup_inserted, tup_updated, tup_deleted from pg_stat_d=
atabase where datname=3D'statistics';
 xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fe=
tched | tup_inserted | tup_updated | tup_deleted=20
-------------+---------------+-----------+----------+--------------+-------=
------+--------------+-------------+-------------
          97 |             1 |       182 |     3261 |        18345 |       =
 1293 |           28 |           0 |           0
(1 row)

statistics=3D# select * from films;
 code  |  title   | did | date_prod  |  kind  |   len=20=20=20=20
-------+----------+-----+------------+--------+----------
 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=3D# select xact_commit, xact_rollback, blks_read, blks_hit,tup_r=
eturned, tup_fetched, tup_inserted, tup_updated, tup_deleted from pg_stat_d=
atabase where datname=3D'statistics';
 xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fe=
tched | tup_inserted | tup_updated | tup_deleted=20
-------------+---------------+-----------+----------+--------------+-------=
------+--------------+-------------+-------------
          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 inser=
t. tup_inserted does seem to report correct value. After simple SELECT, xac=
t_commit increases by 2 - kind of strange as SELECT shouldn't really be inc=
reasing xact_commit ? What I'm trying to do is to gather some reasonable da=
ta which would give me an idea about how many transactions/s particular Dat=
abase 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!


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