Re: How to know transaction count in hour basic

Dear Team,

How this function works, it is not working it is throwing error message ,

[image: image.png]

Kindly check that, and revert back.

Regards,
Mallikarjunarao,
+91-8142923383.

On Thu, Oct 31, 2019 at 5:04 PM mallikarjun t <[email protected]> wrote:

> Dear Team,
>
> Thanks for your support. I will check.
>
>
> Regards,
> Mallikarjunarao,
> +91-8142923383.
>
> On Wed, Oct 30, 2019 at 8:59 PM Rui DeSousa <[email protected]> wrote:
>
>> 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 <[email protected]> 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.*
>>
>>
>>