pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres 9.1 statistics in pg_stat_database
5+ messages / 4 participants
[nested] [flat]
* Postgres 9.1 statistics in pg_stat_database
@ 2013-02-07 18:16 Milos Gajdos <gy2kre@centrum.cz>
2013-02-07 19:05 ` Re: Postgres 9.1 statistics in pg_stat_database Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Milos Gajdos @ 2013-02-07 18:16 UTC (permalink / raw)
To: pgsql-admin
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-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres 9.1 statistics in pg_stat_database
2013-02-07 18:16 Postgres 9.1 statistics in pg_stat_database Milos Gajdos <gy2kre@centrum.cz>
@ 2013-02-07 19:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-02-07 19:24 ` Re: Postgres 9.1 statistics in pg_stat_database Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2013-02-07 19:05 UTC (permalink / raw)
To: Milos Gajdos <gy2kre@centrum.cz>; +Cc: pgsql-admin
=?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 correct value. After simple SELECT, xact_commit increases by 2 - kind of strange as SELECT shouldn't really be increasing xact_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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres 9.1 statistics in pg_stat_database
2013-02-07 18:16 Postgres 9.1 statistics in pg_stat_database Milos Gajdos <gy2kre@centrum.cz>
2013-02-07 19:05 ` Re: Postgres 9.1 statistics in pg_stat_database Tom Lane <tgl@sss.pgh.pa.us>
@ 2013-02-07 19:24 ` Steve Crawford <scrawford@pinpointresearch.com>
2013-02-07 19:48 ` Re: Postgres 9.1 statistics in pg_stat_database Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Steve Crawford @ 2013-02-07 19:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Milos Gajdos <gy2kre@centrum.cz>; pgsql-admin
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 correct value. After simple SELECT, xact_commit increases by 2 - kind of strange as SELECT shouldn't really be increasing xact_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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres 9.1 statistics in pg_stat_database
2013-02-07 18:16 Postgres 9.1 statistics in pg_stat_database Milos Gajdos <gy2kre@centrum.cz>
2013-02-07 19:05 ` Re: Postgres 9.1 statistics in pg_stat_database Tom Lane <tgl@sss.pgh.pa.us>
2013-02-07 19:24 ` Re: Postgres 9.1 statistics in pg_stat_database Steve Crawford <scrawford@pinpointresearch.com>
@ 2013-02-07 19:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-09-04 10:55 ` Re: Postgres 9.1 statistics in pg_stat_database boraldomaster <boraldomaster@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2013-02-07 19:48 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: Milos Gajdos <gy2kre@centrum.cz>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres 9.1 statistics in pg_stat_database
2013-02-07 18:16 Postgres 9.1 statistics in pg_stat_database Milos Gajdos <gy2kre@centrum.cz>
2013-02-07 19:05 ` Re: Postgres 9.1 statistics in pg_stat_database Tom Lane <tgl@sss.pgh.pa.us>
2013-02-07 19:24 ` Re: Postgres 9.1 statistics in pg_stat_database Steve Crawford <scrawford@pinpointresearch.com>
2013-02-07 19:48 ` Re: Postgres 9.1 statistics in pg_stat_database Tom Lane <tgl@sss.pgh.pa.us>
@ 2013-09-04 10:55 ` boraldomaster <boraldomaster@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: boraldomaster @ 2013-09-04 10:55 UTC (permalink / raw)
To: pgsql-admin
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-tp5744283p576952...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-09-04 10:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-07 18:16 Postgres 9.1 statistics in pg_stat_database Milos Gajdos <gy2kre@centrum.cz>
2013-02-07 19:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-02-07 19:24 ` Steve Crawford <scrawford@pinpointresearch.com>
2013-02-07 19:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-09-04 10:55 ` boraldomaster <boraldomaster@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox