Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iPpvA-0005cR-EQ for pgsql-admin@arkaria.postgresql.org; Wed, 30 Oct 2019 15:30:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iPpv9-0001in-5e for pgsql-admin@arkaria.postgresql.org; Wed, 30 Oct 2019 15:30:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iPpv8-0001ig-Kn for pgsql-admin@lists.postgresql.org; Wed, 30 Oct 2019 15:30:02 +0000 Received: from impout002aa.msg.chrl.nc.charter.net ([47.43.20.26] helo=impout002.msg.chrl.nc.charter.net) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1iPpv5-0001VH-V8 for pgsql-admin@postgresql.org; Wed, 30 Oct 2019 15:30:01 +0000 Received: from cerberus.crazybean.net ([24.151.10.150]) by cmsmtp with ESMTP id Ppv4ittcmh15uPpv4iIPBL; Wed, 30 Oct 2019 15:29:58 +0000 Authentication-Results: crazybean.net; none X-Authority-Analysis: v=2.3 cv=etJrMbhX c=1 sm=1 tr=0 a=D2HTw/M6/8MmcHighGO7rg==:117 a=D2HTw/M6/8MmcHighGO7rg==:17 a=jpOVt7BSZ2e4Z31A5e1TngXxSK0=:19 a=XobE76Q3jBoA:10 a=pGLkceISAAAA:8 a=bokRNQsPDWATRF5J34YA:9 a=QEXdDO2ut3YA:10 a=17pam8C7l_dIwrtBCUUA:9 a=ZxixmQokKIjodoCZ:21 a=_W_S_7VecoQA:10 Received: from [10.198.96.85] (69.38.166.222 [69.38.166.222]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id f0c781f9 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Wed, 30 Oct 2019 11:29:57 -0400 (EDT) From: Rui DeSousa Message-Id: <28DFC4B2-BF00-401F-BBAA-23E35D4BA767@crazybean.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_6832DF73-E89F-4644-B417-6F5922B28D7B" Mime-Version: 1.0 (Mac OS X Mail 12.4 \(3445.104.11\)) Subject: Re: How to know transaction count in hour basic Date: Wed, 30 Oct 2019 11:29:55 -0400 In-Reply-To: Cc: pgsql-admin To: mallikarjun t References: X-Mailer: Apple Mail (2.3445.104.11) X-CMAE-Envelope: MS4wfLDbPHRV0NTMHf6N79Kbc9LDwEEofVJm/K7DBxZd0MFFOHkN7fIO0ptYzFOm1Z14JiooCDL08llyPVNWRsI2Novow9ywvn4ChAFSHzb8nSdorG6cSpq1 yzOfg98h7xayTb2pUmRHu41NyPqZiN9DkXyJU+sc3Zdd1lcWSQFv+mYFmAVRSNtf0paNvZH9mK9/U1iCJyCadA7kZHOB6xiynGBUDG12SEIowmaHoZwQyIOq List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_6832DF73-E89F-4644-B417-6F5922B28D7B Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 You need to capture the value at the starting and ending period that = you=E2=80=99re 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 =E2=80=94 then query any period of interest. = Sorta like Oracle=E2=80=99s stats pack. There are also monitoring tools that do this all for you already.=20 i.e. This is the function I would use to get the information you=E2=80=99= re looking for: /*=20 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(=E2=80=98dynamo', '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 :=3D (_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 =3D _database and s.hist_stat_time >=3D _start_time and s.hist_stat_time <=3D _end_time and ((extract(epoch from date_trunc('minutes', = s.hist_stat_time)))::int + _offset_factor) % (_interval * 60) =3D 0 order by s.hist_stat_time offset 1 ; end; $$ language plpgsql stable ; > On Oct 30, 2019, at 10:13 AM, mallikarjun t = wrote: >=20 > Dear Team, >=20 > How to check, how many transactions are committed in one hour? >=20 > In database level pg_stat_database xact_commit column is there,this is = giving only count. >=20 > How to get count for hour basis and minutes and seconds basis, how = many transactions are committed? >=20 > How to check? >=20 > Regards, > Mallikarjunarao, > +91-8142923383. --Apple-Mail=_6832DF73-E89F-4644-B417-6F5922B28D7B Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 You = need to capture the value at the starting and ending period that = you=E2=80=99re 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 =E2=80=94 then query any period = of interest.  Sorta like Oracle=E2=80=99s 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=E2=80=99= 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(=E2=80=98dynamo', '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 :=3D (_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 =3D _database
      and s.hist_stat_time >=3D = _start_time
      and = s.hist_stat_time <=3D _end_time
    =   and ((extract(epoch from date_trunc('minutes', = s.hist_stat_time)))::int + _offset_factor) % (_interval * 60) =3D = 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.

= --Apple-Mail=_6832DF73-E89F-4644-B417-6F5922B28D7B--