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

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