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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to