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

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.

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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books