[GENERAL] tuple statistics update

[Tom DalPozzo]

Hi, I'm using libpq to insert tuples in my table and keep looking at
statistics through psql instead.
I noticed that sometimes n_tuple_ins is not updated even after 1 min that
my transaction committed.
My libpq connection is kept alive. If I close the connection then the stats
get updated.
I know that stats are not instantaneous, but I thought that after a while
that a transaction is committed it would be updated.
Regards
Pupillo

Re: [GENERAL] tuple statistics update

[Adrian Klaver]

On 04/17/2017 09:18 AM, Tom DalPozzo wrote:

Hi, I'm using libpq to insert tuples in my table and keep looking at
statistics through psql instead.
I noticed that sometimes n_tuple_ins is not updated even after 1 min
that my transaction committed.
My libpq connection is kept alive. If I close the connection then the
stats get updated.
I know that stats are not instantaneous, but I thought that after a
while that a transaction is committed it would be updated.


Any of this apply?:

https://www.postgresql.org/docs/9.6/static/monitoring-stats.html

"Another important point is that when a server process is asked to 
display any of these statistics, it first fetches the most recent report 
emitted by the collector process and then continues to use this snapshot 
for all statistical views and functions until the end of its current 
transaction. So the statistics will show static information as long as 
you continue the current transaction. Similarly, information about the 
current queries of all sessions is collected when any such information 
is first requested within a transaction, and the same information will 
be displayed throughout the transaction. This is a feature, not a bug, 
because it allows you to perform several queries on the statistics and 
correlate the results without worrying that the numbers are changing 
underneath you. But if you want to see new results with each query, be 
sure to do the queries outside any transaction block. Alternatively, you 
can invoke pg_stat_clear_snapshot(), which will discard the current 
transaction's statistics snapshot (if any). The next use of statistical 
information will cause a new snapshot to be fetched."



Regards
Pupillo







--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] tuple statistics update

[Tom Lane]

Tom DalPozzo  writes:
> Hi, I'm using libpq to insert tuples in my table and keep looking at
> statistics through psql instead.
> I noticed that sometimes n_tuple_ins is not updated even after 1 min that
> my transaction committed.

If your session is just sitting, that's not surprising.  I think stats
updates are only transmitted to the collector at transaction end (and
even then, only if it's been at least N msec since the last transmission
from the current session).

regards, tom lane


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

Re: [GENERAL] tuple statistics update

[Tom DalPozzo]

2017-04-18 21:42 GMT+02:00 Adrian Klaver :

> On 04/17/2017 09:18 AM, Tom DalPozzo wrote:
>
>> Hi, I'm using libpq to insert tuples in my table and keep looking at
>> statistics through psql instead.
>> I noticed that sometimes n_tuple_ins is not updated even after 1 min
>> that my transaction committed.
>> My libpq connection is kept alive. If I close the connection then the
>> stats get updated.
>> I know that stats are not instantaneous, but I thought that after a
>> while that a transaction is committed it would be updated.
>>
>
> Any of this apply?:
>
> https://www.postgresql.org/docs/9.6/static/monitoring-stats.html
>
> "Another important point is that when a server process is asked to display
> any of these statistics, it first fetches the most recent report emitted by
> the collector process and then continues to use this snapshot for all
> statistical views and functions until the end of its current transaction.
> So the statistics will show static information as long as you continue the
> current transaction. Similarly, information about the current queries of
> all sessions is collected when any such information is first requested
> within a transaction, and the same information will be displayed throughout
> the transaction. This is a feature, not a bug, because it allows you to
> perform several queries on the statistics and correlate the results without
> worrying that the numbers are changing underneath you. But if you want to
> see new results with each query, be sure to do the queries outside any
> transaction block. Alternatively, you can invoke pg_stat_clear_snapshot(),
> which will discard the current transaction's statistics snapshot (if any).
> The next use of statistical information will cause a new snapshot to be
> fetched."
>
> Regards
>> Pupillo
>>
>>
I read it, it seems to say that after N millisec that my transaction ends,
 stat should be current. I also tried pg_stat_clear_snapshot() with no
success.
Regards
Pupillo



>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] tuple statistics update

[Tom DalPozzo]

2017-04-18 22:06 GMT+02:00 Tom Lane :

> Tom DalPozzo  writes:
> > Hi, I'm using libpq to insert tuples in my table and keep looking at
> > statistics through psql instead.
> > I noticed that sometimes n_tuple_ins is not updated even after 1 min that
> > my transaction committed.
>
> If your session is just sitting, that's not surprising.  I think stats
> updates are only transmitted to the collector at transaction end (and
> even then, only if it's been at least N msec since the last transmission
> from the current session).
>
> regards, tom lane
>

Hi, my transaction ended, that I waited 1 min and queried the stat.
I had to wait for session end in order to see the updeted stats.
Did you mean "...to the collector at SESSION end"?

Regards
Pupillo

Re: [GENERAL] tuple statistics update

[Adrian Klaver]

On 04/19/2017 12:28 AM, Tom DalPozzo wrote:

2017-04-18 21:42 GMT+02:00 Adrian Klaver mailto:adrian.kla...@aklaver.com>>:

On 04/17/2017 09:18 AM, Tom DalPozzo wrote:

Hi, I'm using libpq to insert tuples in my table and keep looking at
statistics through psql instead.
I noticed that sometimes n_tuple_ins is not updated even after 1 min
that my transaction committed.
My libpq connection is kept alive. If I close the connection
then the
stats get updated.
I know that stats are not instantaneous, but I thought that after a
while that a transaction is committed it would be updated.


Any of this apply?:

https://www.postgresql.org/docs/9.6/static/monitoring-stats.html


"Another important point is that when a server process is asked to
display any of these statistics, it first fetches the most recent
report emitted by the collector process and then continues to use
this snapshot for all statistical views and functions until the end
of its current transaction. So the statistics will show static
information as long as you continue the current transaction.
Similarly, information about the current queries of all sessions is
collected when any such information is first requested within a
transaction, and the same information will be displayed throughout
the transaction. This is a feature, not a bug, because it allows you
to perform several queries on the statistics and correlate the
results without worrying that the numbers are changing underneath
you. But if you want to see new results with each query, be sure to
do the queries outside any transaction block. Alternatively, you can
invoke pg_stat_clear_snapshot(), which will discard the current
transaction's statistics snapshot (if any). The next use of
statistical information will cause a new snapshot to be fetched."

Regards
Pupillo


I read it, it seems to say that after N millisec that my transaction
ends,  stat should be current. I also tried pg_stat_clear_snapshot()
with no success.


You have two sessions in play, one that is inserting rows, the other in 
psql looking at the stats. It is not clear to me which session you are 
referring to in the above. So maybe an outline of what you are doing. 
Something like:


Session 1 Monitor stats table(?) using command(?)

Session2 Insert rows. The INSERT query

etc

Regards
Pupillo







--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] tuple statistics update

[Tom Lane]

Tom DalPozzo  writes:
> 2017-04-18 22:06 GMT+02:00 Tom Lane :
>> If your session is just sitting, that's not surprising.  I think stats
>> updates are only transmitted to the collector at transaction end (and
>> even then, only if it's been at least N msec since the last transmission
>> from the current session).

> Hi, my transaction ended, that I waited 1 min and queried the stat.
> I had to wait for session end in order to see the updeted stats.
> Did you mean "...to the collector at SESSION end"?

No, I meant what I said.  No amount of just-sitting will cause a session
to do something: it either transmits stats at transaction end or it
doesn't because it thinks it's too soon since its last update.  If you
want to force out the stats, you could wait a second or so and then
do a dummy transaction (even "select 1" will do, in testing).

BTW, a quick look at the code says the threshold is 500 msec, ie,
stats are sent only if it's been at least half a second since the
session's last report.

regards, tom lane


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

Re: [GENERAL] tuple statistics update

[Tom DalPozzo]

Hi,

2017-04-19 15:49 GMT+02:00 Adrian Klaver :

> On 04/19/2017 12:28 AM, Tom DalPozzo wrote:
>
>> 2017-04-18 21:42 GMT+02:00 Adrian Klaver > >:
>>
>> On 04/17/2017 09:18 AM, Tom DalPozzo wrote:
>>
>> Hi, I'm using libpq to insert tuples in my table and keep looking
>> at
>> statistics through psql instead.
>> I noticed that sometimes n_tuple_ins is not updated even after 1
>> min
>> that my transaction committed.
>> My libpq connection is kept alive. If I close the connection
>> then the
>> stats get updated.
>> I know that stats are not instantaneous, but I thought that after
>> a
>> while that a transaction is committed it would be updated.
>>
>>
>> Any of this apply?:
>>
>> https://www.postgresql.org/docs/9.6/static/monitoring-stats.html
>> 
>>
>> "Another important point is that when a server process is asked to
>> display any of these statistics, it first fetches the most recent
>> report emitted by the collector process and then continues to use
>> this snapshot for all statistical views and functions until the end
>> of its current transaction. So the statistics will show static
>> information as long as you continue the current transaction.
>> Similarly, information about the current queries of all sessions is
>> collected when any such information is first requested within a
>> transaction, and the same information will be displayed throughout
>> the transaction. This is a feature, not a bug, because it allows you
>> to perform several queries on the statistics and correlate the
>> results without worrying that the numbers are changing underneath
>> you. But if you want to see new results with each query, be sure to
>> do the queries outside any transaction block. Alternatively, you can
>> invoke pg_stat_clear_snapshot(), which will discard the current
>> transaction's statistics snapshot (if any). The next use of
>> statistical information will cause a new snapshot to be fetched."
>>
>> Regards
>> Pupillo
>>
>>
>> I read it, it seems to say that after N millisec that my transaction
>> ends,  stat should be current. I also tried pg_stat_clear_snapshot()
>> with no success.
>>
>
> You have two sessions in play, one that is inserting rows, the other in
> psql looking at the stats. It is not clear to me which session you are
> referring to in the above. So maybe an outline of what you are doing.
> Something like:
>
> Session 1 Monitor stats table(?) using command(?)
>
> Session2 Insert rows. The INSERT query
>


I have a psql session open.
Now, through another task which uses libpq, I open a new connection and
send these commands via PQexec:
CREATE TABLE stato (ID BIGINT,DATI BYTEA);
CREATE INDEX stato_IDX ON stato (ID);
INSERT INTO stato VALUES (0,'\x6C72B55EA171DE63F229A37135CB5DE4A845FD9E');
INSERT INTO stato VALUES (1,'\x9822A5A113EE5FBBA03C6B58A139DD46D4476B8D');

As it's done, I send the following commands via psql session, waiting at
least 1 sec before each one:

ginopino=# select count(*) from stato;
 count
---
 2
(1 row)

ginopino=# select relname,n_tup_ins,n_tup_upd  from pg_stat_user_tables
where relname='stato';
 relname | n_tup_ins | n_tup_upd
-+---+---
 stato   | 0 | 0
(1 row)

Repeat just in case
ginopino=# select count(*) from stato;
 count
---
 2
(1 row)

ginopino=# select relname,n_tup_ins,n_tup_upd  from pg_stat_user_tables
where relname='stato';
 relname | n_tup_ins | n_tup_upd
-+---+---
 stato   | 0 | 0
(1 row)

n_tup_ins still 0 while count is 2.

Now, I terminate my libpq task and then, from psql:
ginopino=# select relname,n_tup_ins,n_tup_upd  from pg_stat_user_tables
where relname='stato';
 relname | n_tup_ins | n_tup_upd
-+---+---
 stato   | 2 | 0
(1 row)

Now n_tup_ins is 2.

Thanks
Pupillo


>
> etc
>
>> Regards
>> Pupillo
>>
>>
>>
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] tuple statistics update

[Tom DalPozzo]

Hi,

2017-04-19 16:37 GMT+02:00 Tom Lane :

> Tom DalPozzo  writes:
> > 2017-04-18 22:06 GMT+02:00 Tom Lane :
> >> If your session is just sitting, that's not surprising.  I think stats
> >> updates are only transmitted to the collector at transaction end (and
> >> even then, only if it's been at least N msec since the last transmission
> >> from the current session).
>
> > Hi, my transaction ended, that I waited 1 min and queried the stat.
> > I had to wait for session end in order to see the updeted stats.
> > Did you mean "...to the collector at SESSION end"?
>
> No, I meant what I said.  No amount of just-sitting will cause a session
> to do something: it either transmits stats at transaction end or it
> doesn't because it thinks it's too soon since its last update.  If you
> want to force out the stats, you could wait a second or so and then
> do a dummy transaction (even "select 1" will do, in testing).
>
> BTW, a quick look at the code says the threshold is 500 msec, ie,
> stats are sent only if it's been at least half a second since the
> session's last report.
>

I tried with a dummy select with no success. I detailed my test in my
answer to Adrian Klaver.
I'll avoid to duplicate it here.
Regards
Pupillo




>
> regards, tom lane
>