Postgres 9.1 statistics in pg_stat_database

Поиск
Список
Период
Сортировка
От Milos Gajdos
Тема Postgres 9.1 statistics in pg_stat_database
Дата
Msg-id 20130207191818.DF68FAD3@centrum.cz
обсуждение исходный текст
Список pgsql-general
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! 



В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: feature requests (possibly interested in working on this): functional foreign keys
Следующее
От: Pavan Deolasee
Дата:
Сообщение: Re: REINDEX deadlock - Postgresql -9.1