From: Rui DeSousa <rui@crazybean.net>
To: mallikarjun t <mallit333@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: How to know transaction count in hour basic
Date: Wed, 30 Oct 2019 11:29:55 -0400
Message-ID: <28DFC4B2-BF00-401F-BBAA-23E35D4BA767@crazybean.net> (raw)
In-Reply-To: <CAOA8XiWyxx_t4rAjhL4O1xNGChhrzbvw1fmaKu78ximj3PeYJQ@mail.gmail.com>
References: <CAOA8XiWyxx_t4rAjhL4O1xNGChhrzbvw1fmaKu78ximj3PeYJQ@mail.gmail.com>
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
;
> On Oct 30, 2019, at 10:13 AM, mallikarjun t <mallit333@gmail.com> wrote:
>
> Dear Team,
>
> How to check, how many transactions are committed in one hour?
>
> In database level pg_stat_database xact_commit column is there,this is giving only count.
>
> How to get count for hour basis and minutes and seconds basis, how many transactions are committed?
>
> How to check?
>
> Regards,
> Mallikarjunarao,
> +91-8142923383.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rui@crazybean.net, mallit333@gmail.com
Subject: Re: How to know transaction count in hour basic
In-Reply-To: <28DFC4B2-BF00-401F-BBAA-23E35D4BA767@crazybean.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox