pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcounting transactions
5+ messages / 4 participants
[nested] [flat]
* counting transactions
@ 2015-09-04 20:44 Armand Pirvu (gmail) <armand.pirvu@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Armand Pirvu (gmail) @ 2015-09-04 20:44 UTC (permalink / raw)
To: pgsql-admin
Hi
I have a table and an sql file which has a bunch of inserts
I need to count the transactions that happen
Since the autocommit is ON , aside other internals, I should have roughly the same number of transactions as inserts
I was looking at "xact_commit" column from the "pg_stat_database"
select * from pg_stat_database where datname='alonedb';
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (1,'SdnVwhNC', 'cjisHsjK', 'iuAVZbIU', 'dGm', 'lsu', 'yZn');
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (2,'qXxtnlEi', 'cPtDBHFR', 'CvNWKYbg', 'eDt', 'gpY', 'wtP');
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (3,'XJPRnHhR', 'ZLZQXbyk', 'dylerhdb', 'aLp', 'yAD', 'VCP');
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (4,'AnhPoyFI', 'VzMBtdAk', 'KortOCdo', 'ZSH', 'rME', 'yOH');
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (5,'iKSJEcan', 'GtuSFsfQ', 'alHxFYXr', 'DZN', 'RVA', 'zCP');
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (6,'GiwxKOxF', 'kESBUusk', 'soKzMiDP', 'FYq', 'aHp', 'PHU');
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (7,'piwfYySd', 'WrmjKokB', 'ryndcZjb', 'mgB', 'oXg', 'caZ');
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (8,'yfBzBGLu', 'NlASbtWF', 'NxxjtVVg', 'JuD', 'fNg', 'KUP');
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (9,'wMnntvRV', 'bOrsXviK', 'wETGZIpM', 'Rfd', 'KiZ', 'NDV');
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (10,'ejYyXsnT', 'CbXKywbR', 'ACJKilmi', 'uuc', 'klR', 'kcQ');
select * from pg_stat_database where datname='alonedb';
alonedb=# select * from pg_stat_database where datname='alonedb';
datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | blk_r
ead_time | blk_write_time | stats_reset
-------+---------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+------
---------+----------------+-------------------------------
16386 | alonedb | 1 | 101205 | 17 | 3324 | 955026 | 966533 | 249624 | 100138 | 34 | 50000 | 0 | 0 | 0 | 0 |
0 | 0 | 2015-08-28 16:46:45.332615-05
and after
alonedb=# select * from pg_stat_database where datname='alonedb';
datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | blk_r
ead_time | blk_write_time | stats_reset
-------+---------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+------
---------+----------------+-------------------------------
16386 | alonedb | 1 | 101205 | 17 | 3324 | 955026 | 966533 | 249624 | 100138 | 34 | 50000 | 0 | 0 | 0 | 0 |
0 | 0 | 2015-08-28 16:46:45.332615-05
(1 row)
But I did another variant
1 - select * from pg_stat_database where datname='alonedb';
datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | blk_r
ead_time | blk_write_time | stats_reset
-------+---------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+------
---------+----------------+-------------------------------
16386 | alonedb | 1 | 101296 | 27 | 3509 | 983714 | 1009484 | 264308 | 100168 | 38 | 50000 | 0 | 0 | 0 | 0 |
0 | 0 | 2015-08-28 16:46:45.332615-05
2 - disconnect
3 - reconnect
4 - run the inserts
5 - disconnect
6 - reconnect
7 - select * from pg_stat_database where datname='alonedb';
datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | blk_r
ead_time | blk_write_time | stats_reset
-------+---------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+------
---------+----------------+-------------------------------
16386 | alonedb | 1 | 101309 | 27 | 3512 | 985576 | 1010638 | 265262 | 100178 | 38 | 50000 | 0 | 0 | 0 | 0 |
0 | 0 | 2015-08-28 16:46:45.332615-05
I was expecting both situations to yield similar results
So in my mind several questions
1 - why the difference ?
2 - is there any way to really count the transactions ? In Ingres for example I can look in logdump output or in imadb
Thanks
-- Armand
--
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: counting transactions
@ 2015-09-08 12:56 Kevin Grittner <kgrittn@ymail.com>
parent: Armand Pirvu (gmail) <armand.pirvu@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kevin Grittner @ 2015-09-08 12:56 UTC (permalink / raw)
To: Armand Pirvu (gmail) <armand.pirvu@gmail.com>; pgsql-admin
Armand Pirvu (gmail) <armand.pirvu@gmail.com> wrote:
> is there any way to really count the transactions ? In Ingres for
> example I can look in logdump output or in imadb
If you only care about transactions that modify the database, you
could look at the output of the pg_controldata utility and pick off
the NextXID value.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
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: counting transactions
@ 2015-09-09 01:28 Armand Pirvu (home) <armand.pirvu@gmail.com>
parent: Kevin Grittner <kgrittn@ymail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Armand Pirvu (home) @ 2015-09-09 01:28 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; +Cc: pgsql-admin
Perfect Kevin
Many thanks.
On the same token anyway I can count the number of connects/disconnects for a specific database ?
Thanks
Armand
On Sep 8, 2015, at 7:56 AM, Kevin Grittner <kgrittn@ymail.com> wrote:
> Armand Pirvu (gmail) <armand.pirvu@gmail.com> wrote:
>
>> is there any way to really count the transactions ? In Ingres for
>> example I can look in logdump output or in imadb
>
> If you only care about transactions that modify the database, you
> could look at the output of the pg_controldata utility and pick off
> the NextXID value.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
--
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: counting transactions
@ 2015-09-09 01:48 Keith <keith@keithf4.com>
parent: Armand Pirvu (home) <armand.pirvu@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Keith @ 2015-09-09 01:48 UTC (permalink / raw)
To: Armand Pirvu (home) <armand.pirvu@gmail.com>; +Cc: Kevin Grittner <kgrittn@ymail.com>; pgsql-admin
On Tue, Sep 8, 2015 at 9:28 PM, Armand Pirvu (home) <armand.pirvu@gmail.com>
wrote:
> Perfect Kevin
>
> Many thanks.
> On the same token anyway I can count the number of connects/disconnects
> for a specific database ?
>
>
>
> Thanks
> Armand
>
Set connections and disconnections to be logged in postgresql.conf and then
use pg_badger on the logs. Nothing in the database itself that I'm aware of.
You may also want to look into the pg_stat_statements extension. Be aware
that it does add some overhead to your DB load, but it can provide a wealth
of query statistics for you. pgbadger gives you a lot of that same data as
well, though, just delayed until log analysis runs and limited to the top
20 or so such statements.
http://dalibo.github.io/pgbadger/
Keith
>
> On Sep 8, 2015, at 7:56 AM, Kevin Grittner <kgrittn@ymail.com> wrote:
>
> > Armand Pirvu (gmail) <armand.pirvu@gmail.com> wrote:
> >
> >> is there any way to really count the transactions ? In Ingres for
> >> example I can look in logdump output or in imadb
> >
> > If you only care about transactions that modify the database, you
> > could look at the output of the pg_controldata utility and pick off
> > the NextXID value.
> >
> > --
> > Kevin Grittner
> > EDB: http://www.enterprisedb.com
> > The Enterprise PostgreSQL Company
>
>
>
> --
> 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: counting transactions
@ 2015-09-09 02:23 Armand Pirvu (home) <armand.pirvu@gmail.com>
parent: Keith <keith@keithf4.com>
0 siblings, 0 replies; 5+ messages in thread
From: Armand Pirvu (home) @ 2015-09-09 02:23 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: Kevin Grittner <kgrittn@ymail.com>; pgsql-admin
Great deal Keith
Thanks for the info
Armand
On Sep 8, 2015, at 8:48 PM, Keith <keith@keithf4.com> wrote:
>
>
> On Tue, Sep 8, 2015 at 9:28 PM, Armand Pirvu (home) <armand.pirvu@gmail.com> wrote:
> Perfect Kevin
>
> Many thanks.
> On the same token anyway I can count the number of connects/disconnects for a specific database ?
>
>
>
> Thanks
> Armand
>
>
> Set connections and disconnections to be logged in postgresql.conf and then use pg_badger on the logs. Nothing in the database itself that I'm aware of.
> You may also want to look into the pg_stat_statements extension. Be aware that it does add some overhead to your DB load, but it can provide a wealth of query statistics for you. pgbadger gives you a lot of that same data as well, though, just delayed until log analysis runs and limited to the top 20 or so such statements.
>
> http://dalibo.github.io/pgbadger/
>
> Keith
>
>
>
> On Sep 8, 2015, at 7:56 AM, Kevin Grittner <kgrittn@ymail.com> wrote:
>
> > Armand Pirvu (gmail) <armand.pirvu@gmail.com> wrote:
> >
> >> is there any way to really count the transactions ? In Ingres for
> >> example I can look in logdump output or in imadb
> >
> > If you only care about transactions that modify the database, you
> > could look at the output of the pg_controldata utility and pick off
> > the NextXID value.
> >
> > --
> > Kevin Grittner
> > EDB: http://www.enterprisedb.com
> > The Enterprise PostgreSQL Company
>
>
>
> --
> 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:[~2015-09-09 02:23 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-09-04 20:44 counting transactions Armand Pirvu (gmail) <armand.pirvu@gmail.com>
2015-09-08 12:56 ` Kevin Grittner <kgrittn@ymail.com>
2015-09-09 01:28 ` Armand Pirvu (home) <armand.pirvu@gmail.com>
2015-09-09 01:48 ` Keith <keith@keithf4.com>
2015-09-09 02:23 ` Armand Pirvu (home) <armand.pirvu@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