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

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