Re: keeping a timestamp of the last stats reset (for a db, table and function) - Mailing list pgsql-hackers

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