Thread: keeping a timestamp of the last stats reset (for a db, table and function)

Hi everyone,

I just wrote my first patch, and I need to know whether I missed something
or not. I haven't used C for a really long time, so sickbags on standby,
and if you notice something really stupid don't hesitate to call me an
asshole (according to Simon Phipps that proves we are a healthy open
community).

So what the patch does (or should do)? It tracks when were the stats for a
given object (database, table or function) reset for the last time. This
is useful when you do snapshots of the stats for analysis - when comparing
two snapshots, you have to know whether the stats were reset (in that case
the analysis usually yields random noise and automatic tools get confused
by this).

Tom Lane already recommended a workaround - firing the DBA who randomly
resets statistics, but that's not a good solution I think. First, you have
to be superior to the DBA to be able to fire him ;-) Second, everyone
makes a mistake from time to time. Third, when there are functions to
reset stats, it's nice to provide such info as it makes life much easier.

And there are cases when you don't reset the stats explicitly but the data
are actually gone - e.g. when after a restore or upgrade (OK, this is
solvable using pg_postmaster_start_time).

In short, I think it's a useful feature (I need it and I think there are
others). And I think it's not disruptive.

So what the patch actually does:

- extends PgStat_StatDBEntry, PgStat_StatTableEntry and
PgStat_StatFuncEntry with a new field (stat_reset_timestamp)

- adds functions to read current value from these fields
(pg_stat_get_db_last_stat_reset_time, pg_stat_get_last_stat_reset_time and
pg_stat_get_function_last_stat_reset_time)

- extends the system views with calls to these functions
(pg_stat_database, pg_stat_user_functions and pg_stat_all_tables)

The values are set like this:

- when a database is created, current timestamp is stored in
PgStat_StatDBEntry.stat_reset_timestamp
- by default all tables/functions inherit this timestamp
- when stats for a given table / function are reset, current timestamp is
stored in the stat_reset_timestamp (this happens in
pgstat_recv_resetsinglecounter)
- when stats for the whole database are reset, everything starts from
scratch (this happens in pgstat_recv_resetcounter)

What I'm not sure about:

- I really am not sure about the changes made in pg_proc.h. I'm not sure
how to assign OIDs to the new functions (I've simply chosen values that
are were not used in this file), and I'm not sure about the other columns
(I've copied and modified another function with the same parameter/return
types)

- I'm not sure if there are any other ways how the stat entries can be
created. I've found two ways - directly (when asked for the stats e.g.
from pgstat_get_tab_entry), and indirectly (when processing stats from a
backend e.g. in pgstat_recv_tabstat).

regards
Tomas

Hello

you have to respect pg coding style:

a) not too long lines
b) not C++ line comments

Zdar

Pavel

2010/12/11  <tv@fuzzy.cz>:
> Hi everyone,
>
> I just wrote my first patch, and I need to know whether I missed something
> or not. I haven't used C for a really long time, so sickbags on standby,
> and if you notice something really stupid don't hesitate to call me an
> asshole (according to Simon Phipps that proves we are a healthy open
> community).
>
> So what the patch does (or should do)? It tracks when were the stats for a
> given object (database, table or function) reset for the last time. This
> is useful when you do snapshots of the stats for analysis - when comparing
> two snapshots, you have to know whether the stats were reset (in that case
> the analysis usually yields random noise and automatic tools get confused
> by this).
>
> Tom Lane already recommended a workaround - firing the DBA who randomly
> resets statistics, but that's not a good solution I think. First, you have
> to be superior to the DBA to be able to fire him ;-) Second, everyone
> makes a mistake from time to time. Third, when there are functions to
> reset stats, it's nice to provide such info as it makes life much easier.
>
> And there are cases when you don't reset the stats explicitly but the data
> are actually gone - e.g. when after a restore or upgrade (OK, this is
> solvable using pg_postmaster_start_time).
>
> In short, I think it's a useful feature (I need it and I think there are
> others). And I think it's not disruptive.
>
> So what the patch actually does:
>
> - extends PgStat_StatDBEntry, PgStat_StatTableEntry and
> PgStat_StatFuncEntry with a new field (stat_reset_timestamp)
>
> - adds functions to read current value from these fields
> (pg_stat_get_db_last_stat_reset_time, pg_stat_get_last_stat_reset_time and
> pg_stat_get_function_last_stat_reset_time)
>
> - extends the system views with calls to these functions
> (pg_stat_database, pg_stat_user_functions and pg_stat_all_tables)
>
> The values are set like this:
>
> - when a database is created, current timestamp is stored in
> PgStat_StatDBEntry.stat_reset_timestamp
> - by default all tables/functions inherit this timestamp
> - when stats for a given table / function are reset, current timestamp is
> stored in the stat_reset_timestamp (this happens in
> pgstat_recv_resetsinglecounter)
> - when stats for the whole database are reset, everything starts from
> scratch (this happens in pgstat_recv_resetcounter)
>
> What I'm not sure about:
>
> - I really am not sure about the changes made in pg_proc.h. I'm not sure
> how to assign OIDs to the new functions (I've simply chosen values that
> are were not used in this file), and I'm not sure about the other columns
> (I've copied and modified another function with the same parameter/return
> types)
>
> - I'm not sure if there are any other ways how the stat entries can be
> created. I've found two ways - directly (when asked for the stats e.g.
> from pgstat_get_tab_entry), and indirectly (when processing stats from a
> backend e.g. in pgstat_recv_tabstat).
>
> regards
> Tomas
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>

> Hello
>
> you have to respect pg coding style:
>
> a) not too long lines
> b) not C++ line comments

OK, thanks for the notice. I've fixed those two problems.

regards
Tomas

On Sat, Dec 11, 2010 at 4:40 PM,  <tv@fuzzy.cz> wrote:
>> Hello
>>
>> you have to respect pg coding style:
>>
>> a) not too long lines
>> b) not C++ line comments
>
> OK, thanks for the notice. I've fixed those two problems.

Please add this to the currently-open commitfest.

https://commitfest.postgresql.org/action/commitfest_view/open

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Dne 12.12.2010 03:47, Robert Haas napsal(a):
> On Sat, Dec 11, 2010 at 4:40 PM,  <tv@fuzzy.cz> wrote:
>>> Hello
>>>
>>> you have to respect pg coding style:
>>>
>>> a) not too long lines
>>> b) not C++ line comments
>>
>> OK, thanks for the notice. I've fixed those two problems.
>
> Please add this to the currently-open commitfest.
>
> https://commitfest.postgresql.org/action/commitfest_view/open

I've done several small changes to the patch, namely

- added docs for the functions (in SGML)
- added the same thing for background writer

So I think now it's 'complete' and I'll add it to the commit fest in a
few minutes.

regards
Tomas

Tomas Vondra <tv@fuzzy.cz> writes:
> I've done several small changes to the patch, namely

> - added docs for the functions (in SGML)
> - added the same thing for background writer

> So I think now it's 'complete' and I'll add it to the commit fest in a
> few minutes.

Please split this into separate patches for database-level and
table-level tracking, because I think it's highly likely that the latter
will get rejected.  We have had multiple complaints already about the
size of the stats file for databases with many tables.  I don't believe
that it's worth bloating the per-table entries even further with this
information.  Tracking reset time it per-database doesn't seem like a
problem though.
        regards, tom lane

> Tomas Vondra <tv@fuzzy.cz> writes:
>> I've done several small changes to the patch, namely
>
>> - added docs for the functions (in SGML)
>> - added the same thing for background writer
>
>> So I think now it's 'complete' and I'll add it to the commit fest in a
>> few minutes.
>
> Please split this into separate patches for database-level and
> table-level tracking, because I think it's highly likely that the latter
> will get rejected.  We have had multiple complaints already about the
> size of the stats file for databases with many tables.  I don't believe
> that it's worth bloating the per-table entries even further with this
> information.  Tracking reset time it per-database doesn't seem like a
> problem though.

Sorry, it's not possible to split this patch into two, and then choose
just one or both pieces. I could split it, but using just the
'per-database' part would not make sense.

The problems is that right now, when stat's are reset for a single table
or function, the timestamp is set just for that one item, not for the
whole database. So just a blind split of the patch (and using just the
per-database part) would mean the info about tables/functions is lost.

I can prepare an alternative patch, using just per-database timestamps. So
even a reset for a single table/function would set the timestamp for the
whole database.

Tomas

tv@fuzzy.cz writes:
> I can prepare an alternative patch, using just per-database timestamps. So
> even a reset for a single table/function would set the timestamp for the
> whole database.

That seems like quite a bizarre definition.  What I was envisioning was
that we'd track only the time of the last whole-database stats reset.
        regards, tom lane

On Dec 18, 2010, at 8:51 PM, Tom Lane wrote:
> Tomas Vondra <tv@fuzzy.cz> writes:
>> I've done several small changes to the patch, namely
>
>> - added docs for the functions (in SGML)
>> - added the same thing for background writer
>
>> So I think now it's 'complete' and I'll add it to the commit fest in a
>> few minutes.
>
> Please split this into separate patches for database-level and
> table-level tracking, because I think it's highly likely that the latter
> will get rejected.  We have had multiple complaints already about the
> size of the stats file for databases with many tables.  I don't believe
> that it's worth bloating the per-table entries even further with this
> information.  Tracking reset time it per-database doesn't seem like a
> problem though.

Is there a reason this info needs to be tracked in the stats table? I know it's the most obvious place, but since we're
worriedabout the size of it, what about tracking it in pg_class or somewhere else? 
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

Dne 19.12.2010 17:26, Tom Lane napsal(a):
> That seems like quite a bizarre definition.  What I was envisioning was
> that we'd track only the time of the last whole-database stats reset.

Well, but that does not quite work. I need is to know whether the
snapshot is 'consistent' i.e. whether I can compare it to the previous
snapshot and get meaningful results (so that I can perform some analysis
on the difference).

And by 'consistent' I mean that none of the counters was reset since the
previous snapshot. The most obvious and most flexible way to do this is
keeping track of the last reset for each of the counters, which is
exactly what I've done in the patch.

The other possibility I've offered in my previous post is to keep just a
per-database timestamp, and set it whenever some stats in the database
are reset (table/index/function counters or all stats). It definitely is
not as flexible as the previous solution, but it gives me at least some
info that something was reset. But I'd have to throw away the whole
snapshot - in the previous case I could do analysis at least on the
counters that were not reset.

The solution you've offered - keeping only the per-database timestamp,
and not updating it when a table/index/function stats are reset, that's
completely useless for me. It simply does not provide an answer to the
question "Is this snapshot consistent?"

Tomas

Dne 19.12.2010 19:13, Jim Nasby napsal(a):
> Is there a reason this info needs to be tracked in the stats table?
> I know it's the most obvious place, but since we're worried about the
> size of it, what about tracking it in pg_class or somewhere else?

I guess this is the best place for this kind of info, as it is directly
related to the stats items. Well, maybe we could place it to pg_class,
but is it a wise idea?

Tomas

Tomas Vondra <tv@fuzzy.cz> writes:
> Dne 19.12.2010 17:26, Tom Lane napsal(a):
>> That seems like quite a bizarre definition.  What I was envisioning was
>> that we'd track only the time of the last whole-database stats reset.

> Well, but that does not quite work. I need is to know whether the
> snapshot is 'consistent' i.e. whether I can compare it to the previous
> snapshot and get meaningful results (so that I can perform some analysis
> on the difference).

Oh, I see.  Yeah, if that's what you want it for then partial resets
have to change the timestamp too.  I guess if we are careful to document
it properly, this won't be too horrid.
        regards, tom lane

Dne 19.12.2010 20:28, Tom Lane napsal(a):
> Tomas Vondra <tv@fuzzy.cz> writes:
>> Dne 19.12.2010 17:26, Tom Lane napsal(a):
>>> That seems like quite a bizarre definition.  What I was envisioning was
>>> that we'd track only the time of the last whole-database stats reset.
> 
>> Well, but that does not quite work. I need is to know whether the
>> snapshot is 'consistent' i.e. whether I can compare it to the previous
>> snapshot and get meaningful results (so that I can perform some analysis
>> on the difference).
> 
> Oh, I see.  Yeah, if that's what you want it for then partial resets
> have to change the timestamp too.  I guess if we are careful to document
> it properly, this won't be too horrid.
> 
>             regards, tom lane
> 

Well, maybe. I'd prefer the timestamp for each item, as that allows me
to determine which stats were not reset and analyze at least those data.

Plus I won't have time to write the other patch for at least a week, so
let's see whether there are serious objections agains the current patch.

I've never had problems with the pgstat.dat file, but I understand
others might, although this adds "just 8 bytes" to each item.

regards
Tomas

Tomas Vondra <tv@fuzzy.cz> writes:
> Plus I won't have time to write the other patch for at least a week, so
> let's see whether there are serious objections agains the current patch.

If you think this objection is not serious, you're mistaken.

> I've never had problems with the pgstat.dat file, but I understand
> others might, although this adds "just 8 bytes" to each item.

That is not the number of interest.  The number of interest is that it's
8 bytes added onto a struct that currently contains 11 of 'em; in other
words a 9% increase in the size of the stats file, and consequently
about a 9% increase in the cost of updating it.

What is bothering me about that is this: how many of our users ever
reset the stats at all?  Of those, how many reset the stats for just
some tables and not all of them?  And of those, how many care about
having the database remember when they did it, at a per-table level?
I think the answer to each of those questions is "not many", and so
the fraction of our users who will get a benefit from that added
overhead is epsilon cubed.  But approximately 100% of our users will
be paying the overhead.

That just doesn't look like a good tradeoff from here.
        regards, tom lane

I wrote:
> That is not the number of interest.  The number of interest is that it's
> 8 bytes added onto a struct that currently contains 11 of 'em; in other
> words a 9% increase in the size of the stats file, and consequently
> about a 9% increase in the cost of updating it.

Wups, sorry, I was looking at the wrong struct.  It's actually an
addition of 1 doubleword to a struct of 21 of 'em, or about 5%.
That's still an awful lot in comparison to the prospective usefulness
of the information.
        regards, tom lane

Dne 19.12.2010 23:58, Tom Lane napsal(a):
> Tomas Vondra <tv@fuzzy.cz> writes:
>> > Plus I won't have time to write the other patch for at least a week, so
>> > let's see whether there are serious objections agains the current patch.
> If you think this objection is not serious, you're mistaken.

I know there were problems with pgstats.stat and I/O (for example this
thread discussing an I/O storm
http://archives.postgresql.org/pgsql-hackers/2008-01/msg01095.php).

But I thought those issues were already resolved and I have not noticed
any such issue recently. Am I missing something?

> What is bothering me about that is this: how many of our users ever
> reset the stats at all?  Of those, how many reset the stats for just
> some tables and not all of them?  And of those, how many care about
> having the database remember when they did it, at a per-table level?
> I think the answer to each of those questions is "not many", and so
> the fraction of our users who will get a benefit from that added
> overhead is epsilon cubed.  But approximately 100% of our users will
> be paying the overhead.

Sure, I understand that only a fraction of the users will use the patch
I've proposed. That's why I'm saying that the per-database timestamp
would be sufficient (although I'd prefer the per-record timestamp).

regards
Tomas

Dne 20.12.2010 00:03, Tom Lane napsal(a):
> I wrote:
>> That is not the number of interest.  The number of interest is that it's
>> 8 bytes added onto a struct that currently contains 11 of 'em; in other
>> words a 9% increase in the size of the stats file, and consequently
>> about a 9% increase in the cost of updating it.
> 
> Wups, sorry, I was looking at the wrong struct.  It's actually an
> addition of 1 doubleword to a struct of 21 of 'em, or about 5%.
> That's still an awful lot in comparison to the prospective usefulness
> of the information.
> 
>             regards, tom lane
> 

OK, so here goes the simplified patch - it tracks one reset timestamp
for a background writer and for each database.

regards
Tomas

PS: I've noticed Magnus posted a patch to track recovery conflicts,
adding a new view pg_stat_database_conflicts. I have not checked it yet
but it should not influence this patch.

2010/12/23 Tomas Vondra <tv@fuzzy.cz>:
> Dne 20.12.2010 00:03, Tom Lane napsal(a):
>> I wrote:
>>> That is not the number of interest.  The number of interest is that it's
>>> 8 bytes added onto a struct that currently contains 11 of 'em; in other
>>> words a 9% increase in the size of the stats file, and consequently
>>> about a 9% increase in the cost of updating it.
>>
>> Wups, sorry, I was looking at the wrong struct.  It's actually an
>> addition of 1 doubleword to a struct of 21 of 'em, or about 5%.
>> That's still an awful lot in comparison to the prospective usefulness
>> of the information.
>>
>>                       regards, tom lane
>>
>
> OK, so here goes the simplified patch - it tracks one reset timestamp
> for a background writer and for each database.

I think you forgot the attachment.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Dne 23.12.2010 20:09, Robert Haas napsal(a):
> 2010/12/23 Tomas Vondra <tv@fuzzy.cz>:
>> Dne 20.12.2010 00:03, Tom Lane napsal(a):
>>> I wrote:
>>>> That is not the number of interest.  The number of interest is that it's
>>>> 8 bytes added onto a struct that currently contains 11 of 'em; in other
>>>> words a 9% increase in the size of the stats file, and consequently
>>>> about a 9% increase in the cost of updating it.
>>>
>>> Wups, sorry, I was looking at the wrong struct.  It's actually an
>>> addition of 1 doubleword to a struct of 21 of 'em, or about 5%.
>>> That's still an awful lot in comparison to the prospective usefulness
>>> of the information.
>>>
>>>                       regards, tom lane
>>>
>>
>> OK, so here goes the simplified patch - it tracks one reset timestamp
>> for a background writer and for each database.
>
> I think you forgot the attachment.

Yes, I did. Thanks!

Tomas

tv@fuzzy.cz wrote:
> - I really am not sure about the changes made in pg_proc.h. I'm not sure
> how to assign OIDs to the new functions (I've simply chosen values that
> are were not used in this file), and I'm not sure about the other columns
> (I've copied and modified another function with the same parameter/return
> types)
>   

The description of the columns is at the beginning of pg_proc.h, the 
part that begins with CATALOG(pg_proc,1255)...  The descriptions of some 
of the first 11 fields are mostly straighforward.  The first fun part is 
that how may times the information expected in the second "VARIABLE 
LENGTH FIELDS" section repeats varies based on the parameters listed.  
The other thing that's usually confusing is that the types for the 
values are all expressed as type OID numbers.  For example, if you see 
"25", that's the OID of the "text" type.  You can see the whole list with:

select oid,typname from pg_type;

And if you go back to the file with that list in handle, a lot more of 
it should make sense.  If you see a multiple parameter type list like 
"23 21", that's a function whose input values are of types (int4,int2),

As for getting a new OID, if you go into src/include/catalog/ and run 
the unused_oids script, it will give you some help in figuring which 
have been used and which not.  It's not worth getting too stressed about 
the number you choose in the patch submission, because commits between 
when you got your free number and when your patch is considered for 
commit can make your choice worthless anyway.  There's a process 
referred to as "catversion bump", where the database catalog version get 
updated to reflect things like new pg_proc information, that committers 
take care of as one of the last adjustments before final commit.  Doing 
a final correction to the OID choice is a part every committer knows to 
look at.

I wrote a talk that covered some additional trivia in this area, as well 
as other things people tend to get confused about in the source code, 
that you can find at 
http://www.pgcon.org/2010/schedule/attachments/142_HackingWithUDFs.pdf ; 
that might be helpful for some other things you might wonder about 
eventually.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services and Support        www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

Tomas Vondra wrote:
> OK, so here goes the simplified patch - it tracks one reset timestamp
> for a background writer and for each database.
>   

Adding timestamps like this was something I wanted to do after adding 
pg_stat_reset_shared to 9.0, so since you've beaten me to it I'll review 
your patch and make sure it all works the way I was hoping instead.  The 
whole per-table idea was never going to fly given how few people touch 
this area at all, but the way you're tracking things now seems reasonable.

When you post an updated version of a patch that's already being tracked 
on the CommitFest app, please try to remember to add that update to the 
tracker there.  I just did that for this 12/23 update so that's covered 
already.

Next problem is that the preferred method for submitted patches uses 
context diffs.  See http://wiki.postgresql.org/wiki/Working_with_Git for 
some information about the somewhat annoying way you have to setup git 
to generate those.  Don't worry about that for this round though.  I 
don't care about the diff formatting given the code involved, but it's 
something you should sort out if you do another update.

> PS: I've noticed Magnus posted a patch to track recovery conflicts,
> adding a new view pg_stat_database_conflicts. I have not checked it yet
> but it should not influence this patch.
>   

I need to do some testing of that anyway, so I'll take a look at any 
potential clash as part of my review.  I want to check how this 
interacts with track_functions resets too.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services and Support        www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

On Thu, Dec 23, 2010 at 2:41 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
>>> OK, so here goes the simplified patch - it tracks one reset timestamp
>>> for a background writer and for each database.
>>
>> I think you forgot the attachment.
>
> Yes, I did. Thanks!

This patch no longer applies.  Please update.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Dne 30.1.2011 23:22, Robert Haas napsal(a):
> On Thu, Dec 23, 2010 at 2:41 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
>>>> OK, so here goes the simplified patch - it tracks one reset timestamp
>>>> for a background writer and for each database.
>>>
>>> I think you forgot the attachment.
>>
>> Yes, I did. Thanks!
>
> This patch no longer applies.  Please update.

I've updated the patch - rebased to the current HEAD.

regards
Tomas

Thinking I should start with why I think this patch is neat...most of 
the servers I deal with are up 24x7 minus small amounts of downtime, 
presuming everyone does their job right that is.  In that environment, 
having a starting timestamp for when the last stats reset happened lets 
you quickly compute some figures in per-second terms that are pretty 
close to actual average activity on the server.  Some examples of how I 
would use this:

psql -c "
SELECT CAST(buffers_backend * block_size AS numeric) / seconds_uptime / 
(1024*1024)   AS backend_mb_per_sec
FROM (SELECT *,extract(epoch from now()-stats_reset) AS seconds_uptime, (SELECT cast(current_setting('block_size') AS
int8))AS block_size  FROM pg_stat_bgwriter) AS raw WHERE raw.seconds_uptime > 0
 
"backend_mb_per_sec
--------------------  4.27150807681618

psql -c "
SELECT datname,CAST(xact_commit AS numeric) / seconds_uptime   AS commits_per_sec
FROM (SELECT *,extract(epoch from now()-stats_reset) AS seconds_uptime  FROM pg_stat_database) AS raw WHERE
raw.seconds_uptime> 0
 
"
 datname  |  commits_per_sec  
-----------+--------------------template1 | 0.0338722604313051postgres  | 0.0363144438470267gsmith    |
0.0820573653236174pgbench  |  0.059147072347085
 

Now I reset, put some load on the system and check the same stats 
afterward; watch how close these match up:

$ psql -d pgbench -c "select pg_stat_reset()"
$ pgbench -j 4 -c 32 -T 30 pgbench
transaction type: TPC-B (sort of)
scaling factor: 100
query mode: simple
number of clients: 32
number of threads: 4
duration: 30 s
number of transactions actually processed: 6604
tps = 207.185627 (including connections establishing)
tps = 207.315043 (excluding connections establishing)
 datname  |  commits_per_sec  
-----------+--------------------pgbench   |   183.906308135572

Both these examples work as I expected, and some playing around with the 
patch didn't find any serious problems with the logic it implements.  
One issue though, an oversight I think can be improved upon; watch what 
happens when I create a new database:

$ createdb blank
$ psql -c "select datname,stats_reset from pg_stat_database where 
datname='blank'"datname | stats_reset
---------+-------------blank   |

That's not really what I would hope for here.  One major sort of 
situation I'd like this feature to work against is the one where someone 
asks for help but has never touched their database stats before, which 
is exactly what I'm simulating here.  In this case that person would be 
out of luck, the opposite of the experience I'd like a newbie to have at 
this point.

The logic Tomas put in here to initialize things in the face of never 
having a stat reset is reasonable.  But I think to really be complete, 
this needs to hook database creation and make sure the value gets 
initialized with the current timestamp, not just be blank.  Do that, and 
I think this will make a nice incremental feature on top of the existing 
stats structure.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

Dne 4.2.2011 03:37, Greg Smith napsal(a):
> Thinking I should start with why I think this patch is neat...most of
> the servers I deal with are up 24x7 minus small amounts of downtime,
> presuming everyone does their job right that is.  In that environment,
> having a starting timestamp for when the last stats reset happened lets
> you quickly compute some figures in per-second terms that are pretty
> close to actual average activity on the server.  Some examples of how I
> would use this:
> 
> psql -c "
> SELECT
>  CAST(buffers_backend * block_size AS numeric) / seconds_uptime /
> (1024*1024)
>    AS backend_mb_per_sec
> FROM
>  (SELECT *,extract(epoch from now()-stats_reset) AS seconds_uptime,
>  (SELECT cast(current_setting('block_size') AS int8)) AS block_size
>   FROM pg_stat_bgwriter) AS raw WHERE raw.seconds_uptime > 0
> "
> backend_mb_per_sec
> --------------------
>   4.27150807681618
> 
> psql -c "
> SELECT
>  datname,CAST(xact_commit AS numeric) / seconds_uptime
>    AS commits_per_sec
> FROM
>  (SELECT *,extract(epoch from now()-stats_reset) AS seconds_uptime
>   FROM pg_stat_database) AS raw WHERE raw.seconds_uptime > 0
> "
> 
>  datname  |  commits_per_sec  -----------+--------------------
> template1 | 0.0338722604313051
> postgres  | 0.0363144438470267
> gsmith    | 0.0820573653236174
> pgbench   |  0.059147072347085
> 
> Now I reset, put some load on the system and check the same stats
> afterward; watch how close these match up:
> 
> $ psql -d pgbench -c "select pg_stat_reset()"
> $ pgbench -j 4 -c 32 -T 30 pgbench
> transaction type: TPC-B (sort of)
> scaling factor: 100
> query mode: simple
> number of clients: 32
> number of threads: 4
> duration: 30 s
> number of transactions actually processed: 6604
> tps = 207.185627 (including connections establishing)
> tps = 207.315043 (excluding connections establishing)
> 
>  datname  |  commits_per_sec  -----------+--------------------
> pgbench   |   183.906308135572
> 
> Both these examples work as I expected, and some playing around with the
> patch didn't find any serious problems with the logic it implements. 
> One issue though, an oversight I think can be improved upon; watch what
> happens when I create a new database:
> 
> $ createdb blank
> $ psql -c "select datname,stats_reset from pg_stat_database where
> datname='blank'"
> datname | stats_reset
> ---------+-------------
> blank   |
> 
> That's not really what I would hope for here.  One major sort of
> situation I'd like this feature to work against is the one where someone
> asks for help but has never touched their database stats before, which
> is exactly what I'm simulating here.  In this case that person would be
> out of luck, the opposite of the experience I'd like a newbie to have at
> this point.

Are you sure about it? Because when I create a database, the field is
NULL - that's true. But once I connect to the database, the stats are
updated and the field is set (thanks to the logic in pgstat.c).

In that case 'stat_reset=NULL' would mean 'no-one ever touched this db'
which seems quite reasonable to me.

========================================================================

$ createdb testdb1
$ createdb testdb2

$ psql -d testdb1 -c "select stats_reset from pg_stat_database where                     datname =
'testdb2'"stats_reset
-------------

(1 row)

$ psql -d testdb2 -c "\q"

$ psql -d testdb1 -c "select stats_reset from pg_stat_database where                     datname = 'testdb2'"
stats_reset
-------------------------------2011-02-04 19:03:23.938929+01
(1 row)

========================================================================

But maybe I've missed something and it does not work the way I think it
does.

regards
Tomas

Tomas Vondra wrote:
> Because when I create a database, the field is
> NULL - that's true. But once I connect to the database, the stats are
> updated and the field is set (thanks to the logic in pgstat.c).
>

OK--so it does what I was hoping for, I just didn't test it the right
way.  Let's call that a documentation issue and move on.

Attached is an updated patch that fixes the docs and some other random
bits.  Looks ready for committer to me now.  Make sure to adjust
PGSTAT_FILE_FORMAT_ID, do a cat version bump, and set final OIDs for the
new functions.

Below is what changed since the last posted version, mainly as feedback
for Tomas:

-Explained more clearly that pg_stat_reset and
pg_stat_reset_single_counters will both touch the database reset time,
and that it's initialized upon first connection to the database.

-Added the reset time to the list of fields in pg_stat_database and
pg_stat_bgwriter.

-Fixed some tab/whitespace issues.  It looks like you had tab stops set
at 8 characters during some points when you were editing non-code
files.  Also, there were a couple of spot where you used a tab while
text in the area used spaces.  You can normally see both types of errors
if you read a patch, they showed up as misaligned things in the context
diff.

-Removed some extra blank lines that didn't fit the style of the
surrounding code.

Basically, all the formatting bits I'm nitpicking about I found just by
reading the patch itself; they all stuck right out.  I'd recommend a
pass of that before submitting things if you want to try and avoid those
in the future.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index ca83421..100f938 100644
*** a/doc/src/sgml/monitoring.sgml
--- b/doc/src/sgml/monitoring.sgml
*************** postgres: <replaceable>user</> <replacea
*** 267,273 ****
        by backends (that is, not by the background writer), how many times
        those backends had to execute their own fsync calls (normally the
        background writer handles those even when the backend does its own
!       write), and total buffers allocated.
       </entry>
       </row>

--- 267,273 ----
        by backends (that is, not by the background writer), how many times
        those backends had to execute their own fsync calls (normally the
        background writer handles those even when the backend does its own
!       write), total buffers allocated, and time of last statistics reset.
       </entry>
       </row>

*************** postgres: <replaceable>user</> <replacea
*** 278,286 ****
        number of transactions committed and rolled back in that database,
        total disk blocks read, total buffer hits (i.e., block
        read requests avoided by finding the block already in buffer cache),
!       number of rows returned, fetched, inserted, updated and deleted, and
        total number of queries cancelled due to conflict with recovery (on
!       standby servers).
       </entry>
       </row>

--- 278,286 ----
        number of transactions committed and rolled back in that database,
        total disk blocks read, total buffer hits (i.e., block
        read requests avoided by finding the block already in buffer cache),
!       number of rows returned, fetched, inserted, updated and deleted, the
        total number of queries cancelled due to conflict with recovery (on
!       standby servers), and time of last statistics reset.
       </entry>
       </row>

*************** postgres: <replaceable>user</> <replacea
*** 663,668 ****
--- 663,681 ----
       </row>

       <row>
+       <entry><literal><function>pg_stat_get_db_stat_reset_time</function>(<type>oid</type>)</literal></entry>
+       <entry><type>timestamptz</type></entry>
+       <entry>
+        Time of the last statistics reset for the database.  Initialized to the
+        system time during the first connection to each database.  The reset time
+        is updated when you call <function>pg_stat_reset</function> on the
+        database, as well as upon execution of
+        <function>pg_stat_reset_single_table_counters</function> against any
+        table or index in it.
+       </entry>
+      </row>
+
+      <row>
        <entry><literal><function>pg_stat_get_numscans</function>(<type>oid</type>)</literal></entry>
        <entry><type>bigint</type></entry>
        <entry>
*************** postgres: <replaceable>user</> <replacea
*** 1125,1130 ****
--- 1138,1153 ----
         <varname>bgwriter_lru_maxpages</varname> parameter
        </entry>
       </row>
+
+      <row>
+       <entry><literal><function>pg_stat_get_bgwriter_stat_reset_time()</function></literal></entry>
+       <entry><type>timestamptz</type></entry>
+       <entry>
+         Time of the last statistics reset for the background writer, updated
+         when executing <function>pg_stat_reset_shared('bgwriter')</function>
+         on the database cluster.
+       </entry>
+      </row>

       <row>
        <entry><literal><function>pg_stat_get_buf_written_backend()</function></literal></entry>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 718e996..904714f 100644
*** a/src/backend/catalog/system_views.sql
--- b/src/backend/catalog/system_views.sql
*************** CREATE VIEW pg_stat_database AS
*** 523,529 ****
              pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
              pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
              pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted,
!             pg_stat_get_db_conflict_all(D.oid) AS conflicts
      FROM pg_database D;

  CREATE VIEW pg_stat_database_conflicts AS
--- 523,530 ----
              pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
              pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
              pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted,
!             pg_stat_get_db_conflict_all(D.oid) AS conflicts,
!             pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset
      FROM pg_database D;

  CREATE VIEW pg_stat_database_conflicts AS
*************** CREATE VIEW pg_stat_bgwriter AS
*** 570,576 ****
          pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
          pg_stat_get_buf_written_backend() AS buffers_backend,
          pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,
!         pg_stat_get_buf_alloc() AS buffers_alloc;

  CREATE VIEW pg_user_mappings AS
      SELECT
--- 571,578 ----
          pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
          pg_stat_get_buf_written_backend() AS buffers_backend,
          pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,
!         pg_stat_get_buf_alloc() AS buffers_alloc,
!         pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;

  CREATE VIEW pg_user_mappings AS
      SELECT
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 301568f..9529ed9 100644
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
*************** pgstat_get_db_entry(Oid databaseid, bool
*** 3160,3165 ****
--- 3160,3167 ----
          result->n_conflict_bufferpin = 0;
          result->n_conflict_startup_deadlock = 0;

+         result->stat_reset_timestamp = GetCurrentTimestamp();
+
          memset(&hash_ctl, 0, sizeof(hash_ctl));
          hash_ctl.keysize = sizeof(Oid);
          hash_ctl.entrysize = sizeof(PgStat_StatTabEntry);
*************** pgstat_read_statsfile(Oid onlydb, bool p
*** 3438,3443 ****
--- 3440,3451 ----
       * load an existing statsfile.
       */
      memset(&globalStats, 0, sizeof(globalStats));
+
+     /*
+      * Set the current timestamp (will be kept only in case we can't load an
+      * existing statsfile.
+      */
+     globalStats.stat_reset_timestamp = GetCurrentTimestamp();

      /*
       * Try to open the status file. If it doesn't exist, the backends simply
*************** pgstat_recv_resetcounter(PgStat_MsgReset
*** 4052,4057 ****
--- 4060,4067 ----
      dbentry->n_tuples_deleted = 0;
      dbentry->last_autovac_time = 0;

+     dbentry->stat_reset_timestamp = GetCurrentTimestamp();
+
      memset(&hash_ctl, 0, sizeof(hash_ctl));
      hash_ctl.keysize = sizeof(Oid);
      hash_ctl.entrysize = sizeof(PgStat_StatTabEntry);
*************** pgstat_recv_resetsharedcounter(PgStat_Ms
*** 4083,4088 ****
--- 4093,4099 ----
      {
          /* Reset the global background writer statistics for the cluster. */
          memset(&globalStats, 0, sizeof(globalStats));
+         globalStats.stat_reset_timestamp = GetCurrentTimestamp();
      }

      /*
*************** pgstat_recv_resetsinglecounter(PgStat_Ms
*** 4107,4112 ****
--- 4118,4125 ----
      if (!dbentry)
          return;

+     /* Set the reset timestamp for the whole database */
+     dbentry->stat_reset_timestamp = GetCurrentTimestamp();

      /* Remove object if it exists, ignore it if not */
      if (msg->m_resettype == RESET_TABLE)
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index a95ba8b..f0d9393 100644
*** a/src/backend/utils/adt/pgstatfuncs.c
--- b/src/backend/utils/adt/pgstatfuncs.c
*************** extern Datum pg_stat_get_db_conflict_sna
*** 77,88 ****
--- 77,90 ----
  extern Datum pg_stat_get_db_conflict_bufferpin(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_db_conflict_startup_deadlock(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_db_conflict_all(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_db_stat_reset_time(PG_FUNCTION_ARGS);

  extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_buf_fsync_backend(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_buf_alloc(PG_FUNCTION_ARGS);
*************** pg_stat_get_db_tuples_deleted(PG_FUNCTIO
*** 1134,1139 ****
--- 1136,1159 ----

      PG_RETURN_INT64(result);
  }
+
+ Datum
+ pg_stat_get_db_stat_reset_time(PG_FUNCTION_ARGS)
+ {
+     Oid            dbid = PG_GETARG_OID(0);
+     TimestampTz result;
+     PgStat_StatDBEntry *dbentry;
+
+     if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+         result = 0;
+     else
+         result = dbentry->stat_reset_timestamp;
+
+     if (result == 0)
+         PG_RETURN_NULL();
+     else
+         PG_RETURN_TIMESTAMPTZ(result);
+ }

  Datum
  pg_stat_get_db_conflict_tablespace(PG_FUNCTION_ARGS)
*************** pg_stat_get_bgwriter_maxwritten_clean(PG
*** 1261,1266 ****
--- 1281,1292 ----
  }

  Datum
+ pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS)
+ {
+     PG_RETURN_TIMESTAMPTZ(pgstat_fetch_global()->stat_reset_timestamp);
+ }
+
+ Datum
  pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS)
  {
      PG_RETURN_INT64(pgstat_fetch_global()->buf_written_backend);
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index f8b5d4d..eb7cc86 100644
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 3069 (  pg_stat_get_db
*** 3131,3136 ****
--- 3131,3138 ----
  DESCR("statistics: recovery conflicts in database caused by buffer deadlock");
  DATA(insert OID = 3070 (  pg_stat_get_db_conflict_all PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_
_null__null_ pg_stat_get_db_conflict_all _null_ _null_ _null_ )); 
  DESCR("statistics: recovery conflicts in database");
+ DATA(insert OID = 3116 (  pg_stat_get_db_stat_reset_time PGNSP PGUID 12 1 0 0 f f f t f s 1 0 1184 "26" _null_ _null_
_null__null_    pg_stat_get_db_stat_reset_time _null_ _null_ _null_ )); 
+ DESCR("statistics: last reset for a database");
  DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_
_null__null_ _null_ pg_stat_get_bgwriter_timed_checkpoints _null_ _null_ _null_ )); 
  DESCR("statistics: number of timed checkpoints started by the bgwriter");
  DATA(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_
_null__null_ _null_ pg_stat_get_bgwriter_requested_checkpoints _null_ _null_ _null_ )); 
*************** DATA(insert OID = 2772 ( pg_stat_get_bgw
*** 3141,3146 ****
--- 3143,3150 ----
  DESCR("statistics: number of buffers written by the bgwriter for cleaning dirty buffers");
  DATA(insert OID = 2773 ( pg_stat_get_bgwriter_maxwritten_clean PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_
_null__null_ _null_ pg_stat_get_bgwriter_maxwritten_clean _null_ _null_ _null_ )); 
  DESCR("statistics: number of times the bgwriter stopped processing when it had written too many buffers while
cleaning");
+ DATA(insert OID = 3117 ( pg_stat_get_bgwriter_stat_reset_time PGNSP PGUID 12 1 0 0 f f f t f s 0 0 1184 "" _null_
_null__null_ _null_    pg_stat_get_bgwriter_stat_reset_time _null_ _null_ _null_ )); 
+ DESCR("statistics: last reset for the bgwriter");
  DATA(insert OID = 2775 ( pg_stat_get_buf_written_backend PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_ _null_
_null__null_ pg_stat_get_buf_written_backend _null_ _null_ _null_ )); 
  DESCR("statistics: number of buffers written by backends");
  DATA(insert OID = 3063 ( pg_stat_get_buf_fsync_backend PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_ _null_
_null__null_ pg_stat_get_buf_fsync_backend _null_ _null_ _null_ )); 
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 9f4e0ca..342f459 100644
*** a/src/include/pgstat.h
--- b/src/include/pgstat.h
*************** typedef struct PgStat_StatDBEntry
*** 508,513 ****
--- 508,514 ----
      PgStat_Counter n_conflict_snapshot;
      PgStat_Counter n_conflict_bufferpin;
      PgStat_Counter n_conflict_startup_deadlock;
+     TimestampTz stat_reset_timestamp;


      /*
*************** typedef struct PgStat_GlobalStats
*** 584,589 ****
--- 585,591 ----
      PgStat_Counter buf_written_backend;
      PgStat_Counter buf_fsync_backend;
      PgStat_Counter buf_alloc;
+     TimestampTz stat_reset_timestamp;
  } PgStat_GlobalStats;

On 6.2.2011 08:17, Greg Smith wrote:
> Below is what changed since the last posted version, mainly as feedback
> for Tomas:
> 
> -Explained more clearly that pg_stat_reset and
> pg_stat_reset_single_counters will both touch the database reset time,
> and that it's initialized upon first connection to the database.
> 
> -Added the reset time to the list of fields in pg_stat_database and
> pg_stat_bgwriter.
> 
> -Fixed some tab/whitespace issues.  It looks like you had tab stops set
> at 8 characters during some points when you were editing non-code
> files.  Also, there were a couple of spot where you used a tab while
> text in the area used spaces.  You can normally see both types of errors
> if you read a patch, they showed up as misaligned things in the context
> diff.
> 
> -Removed some extra blank lines that didn't fit the style of the
> surrounding code.
> 
> Basically, all the formatting bits I'm nitpicking about I found just by
> reading the patch itself; they all stuck right out.  I'd recommend a
> pass of that before submitting things if you want to try and avoid those
> in the future.

Thanks for fixing the issues and for the review in general. I have to
tune the IDE a bit to produce the right format and be a bit more careful
when creating the patches.

regards
Tomas

On Sun, Feb 6, 2011 at 08:17, Greg Smith <greg@2ndquadrant.com> wrote:
> Tomas Vondra wrote:
>>
>> Because when I create a database, the field is
>> NULL - that's true. But once I connect to the database, the stats are
>> updated and the field is set (thanks to the logic in pgstat.c).
>>
>
> OK--so it does what I was hoping for, I just didn't test it the right way.
>  Let's call that a documentation issue and move on.
>
> Attached is an updated patch that fixes the docs and some other random bits.
>  Looks ready for committer to me now.  Make sure to adjust
> PGSTAT_FILE_FORMAT_ID, do a cat version bump, and set final OIDs for the new
> functions.

... and the regression tests expected output.


> -Fixed some tab/whitespace issues.  It looks like you had tab stops set at 8

I added a few more whitespace fixes, mainly in the "whitespace at end
of line" category (git diff shows it pretty clearly)


With that, applied. Thanks!

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/