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 1iQBF9-0002ES-EK for pgsql-admin@arkaria.postgresql.org; Thu, 31 Oct 2019 14:16:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iQBF7-0006E7-Pt for pgsql-admin@arkaria.postgresql.org; Thu, 31 Oct 2019 14:16:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iQBF7-0006Cs-Dm for pgsql-admin@lists.postgresql.org; Thu, 31 Oct 2019 14:16:05 +0000 Received: from impout004aa.msg.chrl.nc.charter.net ([47.43.20.28] helo=impout004.msg.chrl.nc.charter.net) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iQBF3-0002h5-I4 for pgsql-admin@postgresql.org; Thu, 31 Oct 2019 14:16:04 +0000 Received: from cerberus.crazybean.net ([24.151.10.150]) by cmsmtp with ESMTP id QBF0iW5NEwzF8QBF1iKi7p; Thu, 31 Oct 2019 14:15:59 +0000 Authentication-Results: crazybean.net; none X-Authority-Analysis: v=2.3 cv=SdVDicZu 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=oIfrFNJ-AAAA:8 a=eHLRcbzoSKKLMp04Ll4A:9 a=VHHI8y4xfkAMk4Ou:21 a=ofOHSdrY2H1szofa:21 a=QEXdDO2ut3YA:10 a=7fFI58Ib5ENNlXOUcsgA:9 a=AT-A_4HoocKCUjil:21 a=kXKUGOS90wduGyMG:21 a=1qcP_SJ70EMLKqVJ:21 a=_W_S_7VecoQA:10 a=Cg2NcamS17xU0rR7el6q:22 Received: from [10.198.96.85] (69.38.166.222 [69.38.166.222]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id f9728ed0 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Thu, 31 Oct 2019 10:15:58 -0400 (EDT) From: Rui DeSousa Message-Id: <3AE7833D-16F3-4118-91ED-C8384ECA454D@crazybean.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_85978518-B4E2-4A40-8127-974047AEED06" Mime-Version: 1.0 (Mac OS X Mail 12.4 \(3445.104.11\)) Subject: Re: How to know transaction count in hour basic Date: Thu, 31 Oct 2019 10:15:56 -0400 In-Reply-To: Cc: pgsql-admin To: mallikarjun t References: <28DFC4B2-BF00-401F-BBAA-23E35D4BA767@crazybean.net> X-Mailer: Apple Mail (2.3445.104.11) X-CMAE-Envelope: MS4wfLvEt6k+RhXY0zkQAgBRyT+gGqtm570aSVf/dBOMYHeTkqfteySvm7Dxo7zVOd+yDijrGPe5yv4f9OfZc1f2+bvZSU/1bnfRTp80n0WbPBZJFEYbWvQw BEbvm0pk6ueInuFuKbjuEGb5J4eWpKdR9Swg89mXC32WPeOLq1KK9ffkdziOjEwlK1FuWqbt6PlT89H3mv1wNzovDTpW07r8GFaChVpPPu0MfZ+H0fgKzTBx List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_85978518-B4E2-4A40-8127-974047AEED06 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 That is a simple helper function to create a numeric format string for = to_char(): create or replace function numeric_format(_percision int, _scale int) returns varchar(100) as $$ declare=20 _grpCnt int; _decCnt int; begin _grpCnt :=3D ((_percision - _scale) / 3); _decCnt :=3D ((_percision - _scale) % 3); return trim(leading ',' from (repeat('9', _decCnt) || repeat('G999', = _grpCnt) || 'D' || repeat('9', _scale))); end; $$ language plpgsql immutable returns null on null input ; > On Oct 31, 2019, at 10:05 AM, mallikarjun t = wrote: >=20 > Dear Team,=20 >=20 > How this function works, it is not working it is throwing error = message , >=20 > >=20 > Kindly check that, and revert back. >=20 > Regards, > Mallikarjunarao, > +91-8142923383. >=20 > On Thu, Oct 31, 2019 at 5:04 PM mallikarjun t > wrote: > Dear Team, >=20 > Thanks for your support. I will check. >=20 >=20 > Regards, > Mallikarjunarao, > +91-8142923383. >=20 > On Wed, Oct 30, 2019 at 8:59 PM Rui DeSousa > wrote: > You need to capture the value at the starting and ending period that = you=E2=80=99re interested in and then calculate the delta. >=20 >=20 > 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. >=20 > There are also monitoring tools that do this all for you already.=20 >=20 > i.e. This is the function I would use to get the information you=E2=80=99= re looking for: >=20 > /*=20 > Query Example: >=20 > 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) > ; >=20 > */ >=20 > 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)); >=20 > 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 > ; >=20 >=20 >=20 >> 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. >=20 --Apple-Mail=_85978518-B4E2-4A40-8127-974047AEED06 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 That = is a simple helper function to create a numeric format string for = to_char():


create or replace = function numeric_format(_percision int, _scale int)
returns varchar(100)
as $$
declare 
 _grpCnt = int;
 _decCnt int;
begin
    _grpCnt :=3D = ((_percision - _scale) / 3);
    _decCnt = :=3D ((_percision - _scale) % 3);

    return trim(leading ',' = from (repeat('9', _decCnt) || repeat('G999', _grpCnt) || 'D' || = repeat('9', _scale)));
end;
$$ = language plpgsql
  immutable returns null on = null input
;

On Oct = 31, 2019, at 10:05 AM, mallikarjun t <mallit333@gmail.com>= wrote:

Dear Team, 

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

<image.png>

Kindly check that, and revert back.

Regards,
Mallikarjunarao,
+91-8142923383.

On Thu, Oct 31, 2019 at 5:04 PM = mallikarjun t <mallit333@gmail.com> 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 <rui@crazybean.net> wrote:
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=99re = 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=_85978518-B4E2-4A40-8127-974047AEED06--