| You need to capture the value at the starting and ending period that you’re interested in and then calculate the delta. I export all the stats tables out every minute and put them into history table on a different system — then query any period of interest. Sorta like Oracle’s stats pack. There are also monitoring tools that do this all for you already. i.e. This is the function I would use to get the information you’re looking for: /* Query Example: select period_start , period_end , to_char(xact_commit + xact_rollback, numeric_format(10, 0)) as tran_cnt , to_char(txid_cnt, numeric_format(10, 0)) as txid_cnt , pg_size_pretty((blks_read + blks_hit) * 16 * 1024) as buf_read , pg_size_pretty(blks_read * 16 * 1024) as fs_read , to_char(blks_hit::numeric / (blks_read + blks_hit) * 100, '999D9999%') as buf_pct , to_char(temp_files, numeric_format(15, 0)) as temp_files , pg_size_pretty(temp_bytes) as tmp_tbl , to_char(tup_returned, numeric_format(15, 0)) as tup_returned , to_char(tup_inserted, numeric_format(15, 0)) as tup_inserted , to_char(tup_updated, numeric_format(15, 0)) as tup_updated , to_char(tup_deleted, numeric_format(15, 0)) as tup_deleted from get_database_stat(‘dynamo', '2018-06-26'::date, '2018-06-30'::date, 60) ; */ create or replace function get_database_stat(_database name, _start_time timestamp(0) with time zone, _end_time timestamp(0) with time zone, _interval int default 1) returns table ( period_start timestamp(0) with time zone, period_end timestamp(0) with time zone, xact_commit bigint, xact_rollback bigint, txid_cnt bigint, blks_read bigint, blks_hit bigint, tup_returned bigint, tup_fetched bigint, tup_inserted bigint, tup_updated bigint, tup_deleted bigint, conflicts bigint, temp_files bigint, temp_bytes bigint, deadlocks bigint, blk_read_time double precision, blk_write_time double precision ) as $$ declare _offset_factor int; begin _offset_factor := (_interval * 60) - ((extract(epoch from date_trunc('minutes', _start_time)))::int % (_interval * 60)); return query select lag(s.hist_stat_time) over (partition by s.datname, s.stats_reset order by s.hist_stat_time) as period_start , s.hist_stat_time as period_end , (s.xact_commit - lag(s.xact_commit) over (partition by s.datname, s.stats_reset order by s.hist_stat_time)) as xact_commit , (s.xact_rollback - lag(s.xact_rollback) over (partition by s.datname, s.stats_reset order by s.hist_stat_time)) as xact_rollback , s.xact_txid - lag(s.xact_txid) over (partition by s.datname, s.stats_reset order by s.hist_stat_time) as txid_cnt , (s.blks_read - lag(s.blks_read) over (partition by s.datname, s.stats_reset order by s.hist_stat_time)) as blks_read , (s.blks_hit - lag(s.blks_hit) over (partition by s.datname, s.stats_reset order by s.hist_stat_time)) as blks_hit , (s.tup_returned - lag(s.tup_returned) over (partition by s.datname, s.stats_reset order by s.hist_stat_time)) as tup_returned , (s.tup_fetched - lag(s.tup_fetched) over (partition by s.datname, s.stats_reset order by s.hist_stat_time)) as tup_fetched , (s.tup_inserted - lag(s.tup_inserted) over (partition by s.datname, s.stats_reset order by s.hist_stat_time)) as tup_inserted , (s.tup_updated - lag(s.tup_updated) over (partition by s.datname, s.stats_reset order by s.hist_stat_time)) as tup_updated , (s.tup_deleted - lag(s.tup_deleted) over (partition by s.datname, s.stats_reset order by s.hist_stat_time)) as tup_deleted , (s.conflicts - lag(s.conflicts) over (partition by s.datname, s.stats_reset order by s.hist_stat_time)) as conflicts , (s.temp_files - lag(s.temp_files) over (partition by s.datname, s.stats_reset order by s.hist_stat_time)) as temp_files , (s.temp_bytes - lag(s.temp_bytes) over (partition by s.datname, s.stats_reset order by s.hist_stat_time)) as temp_bytes , (s.deadlocks - lag(s.deadlocks) over (partition by s.datname, s.stats_reset order by s.hist_stat_time)) as deadlocks , (s.blk_read_time - lag(s.blk_read_time) over (partition by s.datname, s.stats_reset order by s.hist_stat_time)) as blk_read_time , (s.blk_write_time - lag(s.blk_write_time) over (partition by s.datname, s.stats_reset order by s.hist_stat_time)) as blk_write_time from hist_stat_database s where s.datname = _database and s.hist_stat_time >= _start_time and s.hist_stat_time <= _end_time and ((extract(epoch from date_trunc('minutes', s.hist_stat_time)))::int + _offset_factor) % (_interval * 60) = 0 order by s.hist_stat_time offset 1 ; end; $$ language plpgsql stable ;
|
