Skip to content

refs #179 K003 for each metric show ratio

Oleg requested to merge issue_179 into master

Description

Added ratio per each metric

Related issue

https://gitlab.com/postgres-ai-team/postgres-checkup/issues/179

Examples

Run current report:

./check -h 127.0.0.1 -p 5432 --username postgres --dbname test --epoch 1 --project test_1 \
   --file resources/checks/K003_top_total_time.sh

Debug query example:

calc_absolute_err

with snap1(j) as (
	    select $snap1$
	       {
  "snapshot_timestamptz": "2019-01-21T17:14:57.380717+03:00",
  "snapshot_timestamptz_s": 1548080097.38072,
  "queries": {
    "a": {
      "rownum": 1,
      "query": "common query",
      "calls": 100,
      "total_time": 800,
      "rows": 18109,
      "shared_blks_hit": 108654,
      "shared_blks_read": 0,
      "shared_blks_dirtied": 0,
      "shared_blks_written": 0,
      "local_blks_hit": 0,
      "local_blks_read": 0,
      "local_blks_dirtied": 0,
      "local_blks_written": 0,
      "temp_blks_read": 0,
      "temp_blks_written": 0,
      "blk_read_time": 0,
      "blk_write_time": 0,
      "md5": "a"
    },
    "b": {
      "rownum": 1,
      "query": "common query 2",
      "calls": 10,
      "total_time": 10,
      "rows": 18109,
      "shared_blks_hit": 108654,
      "shared_blks_read": 0,
      "shared_blks_dirtied": 0,
      "shared_blks_written": 0,
      "local_blks_hit": 0,
      "local_blks_read": 0,
      "local_blks_dirtied": 0,
      "local_blks_written": 0,
      "temp_blks_read": 0,
      "temp_blks_written": 0,
      "blk_read_time": 0,
      "blk_write_time": 0,
      "md5": "b"
    },
    "c": {
      "rownum": 4,
      "query": "other query",
      "calls": 20,
      "total_time": 4000,
      "rows": 15477,
      "shared_blks_hit": 92862,
      "shared_blks_read": 0,
      "shared_blks_dirtied": 0,
      "shared_blks_written": 0,
      "local_blks_hit": 0,
      "local_blks_read": 0,
      "local_blks_dirtied": 0,
      "local_blks_written": 0,
      "temp_blks_read": 0,
      "temp_blks_written": 0,
      "blk_read_time": 0,
      "blk_write_time": 0,
      "md5": "c"
    }
}}
$snap1$::json
	  ), snap2(j) as (
	    select $snap2$
	       {
  "snapshot_timestamptz": "2019-01-21T17:14:57.380717+03:00",
  "snapshot_timestamptz_s": 1548080097.38072,
  "queries": {
    "a": {
      "rownum": 1,
      "query": "common query",
      "calls": 200,
      "total_time": 1000,
      "rows": 18109,
      "shared_blks_hit": 108654,
      "shared_blks_read": 0,
      "shared_blks_dirtied": 0,
      "shared_blks_written": 0,
      "local_blks_hit": 0,
      "local_blks_read": 0,
      "local_blks_dirtied": 0,
      "local_blks_written": 0,
      "temp_blks_read": 0,
      "temp_blks_written": 0,
      "blk_read_time": 0,
      "blk_write_time": 0,
      "md5": "a"
    },
    "b": {
      "rownum": 1,
      "query": "common query 2",
      "calls": 30,
      "total_time": 20,
      "rows": 18109,
      "shared_blks_hit": 108654,
      "shared_blks_read": 0,
      "shared_blks_dirtied": 0,
      "shared_blks_written": 0,
      "local_blks_hit": 0,
      "local_blks_read": 0,
      "local_blks_dirtied": 0,
      "local_blks_written": 0,
      "temp_blks_read": 0,
      "temp_blks_written": 0,
      "blk_read_time": 0,
      "blk_write_time": 0,
      "md5": "b"
    },
    "c": {
      "rownum": 4,
      "query": "other query",
      "calls": 50,
      "total_time": 5000,
      "rows": 15477,
      "shared_blks_hit": 92862,
      "shared_blks_read": 0,
      "shared_blks_dirtied": 0,
      "shared_blks_written": 0,
      "local_blks_hit": 0,
      "local_blks_read": 0,
      "local_blks_dirtied": 0,
      "local_blks_written": 0,
      "temp_blks_read": 0,
      "temp_blks_written": 0,
      "blk_read_time": 0,
      "blk_write_time": 0,
      "md5": "c"
    },
    "d": {
      "rownum": 4,
      "query": "other query 2",
      "calls": 5,
      "total_time": 2,
      "rows": 15477,
      "shared_blks_hit": 92862,
      "shared_blks_read": 0,
      "shared_blks_dirtied": 0,
      "shared_blks_written": 0,
      "local_blks_hit": 0,
      "local_blks_read": 0,
      "local_blks_dirtied": 0,
      "local_blks_written": 0,
      "temp_blks_read": 0,
      "temp_blks_written": 0,
      "blk_read_time": 0,
      "blk_write_time": 0,
      "md5": "d"
    }
}}
	    $snap2$::json
), delta(seconds) as (
select 
  (select j->>'snapshot_timestamptz_s' from snap2)::numeric
   - (select j->>'snapshot_timestamptz_s' from snap1)::numeric
), s1(md5, obj) as (
select _.*
from snap1, lateral json_each(j->'queries') as _
), s2(md5, obj) as (
select _.*
from snap2, lateral json_each(j->'queries') as _
), si as (  
	select s1.md5
	from s1
	intersect
	select s2.md5
	from s2
), sum_si_s1 as ( -- calculate sum(calls) and sum(total_time) for si-s1
    select
        sum((s1.obj->>'calls')::numeric) as sum_calls,
        sum((s1.obj->>'total_time')::numeric) as sum_total_time,
        1 as key
    from s1
    where s1.md5 in (select md5 from si)
  ), sum_si_s2 as ( -- calculate sum(calls) and sum(total_time) for si-s2
    select
        sum((s2.obj->>'calls')::numeric) as sum_calls,
        sum((s2.obj->>'total_time')::numeric) as sum_total_time,
        1 as key
    from s2
    where s2.md5 in (select md5 from si)
  ), sum_s1 as (
  select  
	sum((s1.obj->>'calls')::numeric) as sum_calls,
	sum((s1.obj->>'total_time')::numeric) as sum_total_time,
	sum((s1.obj->>'rows')::numeric) as sum_rows,
	sum((s1.obj->>'shared_blks_hit')::numeric) as sum_shared_blks_hit,
	sum((s1.obj->>'shared_blks_read')::numeric) as sum_shared_blks_read,
	sum((s1.obj->>'shared_blks_dirtied')::numeric) as sum_shared_blks_dirtied,
	sum((s1.obj->>'shared_blks_written')::numeric) as sum_shared_blks_written,
	sum((s1.obj->>'local_blks_hit')::numeric) as sum_local_blks_hit,
	sum((s1.obj->>'local_blks_read')::numeric) as sum_local_blks_read,
	sum((s1.obj->>'local_blks_dirtied')::numeric) as sum_local_blks_dirtied,
	sum((s1.obj->>'local_blks_written')::numeric) as sum_local_blks_written,
	sum((s1.obj->>'temp_blks_read')::numeric) as sum_temp_blks_read,
	sum((s1.obj->>'temp_blks_written')::numeric) as sum_temp_blks_written,
	sum((s1.obj->>'blk_read_time')::numeric) as sum_blk_read_time,
	sum((s1.obj->>'blk_write_time')::numeric) as sum_blk_write_time,
	sum((s1.obj->>'kcache_reads')::numeric) as sum_kcache_reads,
	sum((s1.obj->>'kcache_writes')::numeric) as sum_kcache_writes,
	sum((s1.obj->>'kcache_user_time_ms')::numeric) as sum_kcache_user_time_ms,
	sum((s1.obj->>'kcache_system_time_ms')::numeric) as sum_kcache_system_time_ms,
  1 as key
from s1
), sum_s2 as (
select
	sum((s2.obj->>'calls')::numeric) as sum_calls,
	sum((s2.obj->>'total_time')::numeric) as sum_total_time,
	sum((s2.obj->>'rows')::numeric) as sum_rows,
	sum((s2.obj->>'shared_blks_hit')::numeric) as sum_shared_blks_hit,
	sum((s2.obj->>'shared_blks_read')::numeric) as sum_shared_blks_read,
	sum((s2.obj->>'shared_blks_dirtied')::numeric) as sum_shared_blks_dirtied,
	sum((s2.obj->>'shared_blks_written')::numeric) as sum_shared_blks_written,
	sum((s2.obj->>'local_blks_hit')::numeric) as sum_local_blks_hit,
	sum((s2.obj->>'local_blks_read')::numeric) as sum_local_blks_read,
	sum((s2.obj->>'local_blks_dirtied')::numeric) as sum_local_blks_dirtied,
	sum((s2.obj->>'local_blks_written')::numeric) as sum_local_blks_written,
	sum((s2.obj->>'temp_blks_read')::numeric) as sum_temp_blks_read,
	sum((s2.obj->>'temp_blks_written')::numeric) as sum_temp_blks_written,
	sum((s2.obj->>'blk_read_time')::numeric) as sum_blk_read_time,
	sum((s2.obj->>'blk_write_time')::numeric) as sum_blk_write_time,
	sum((s2.obj->>'kcache_reads')::numeric) as sum_kcache_reads,
	sum((s2.obj->>'kcache_writes')::numeric) as sum_kcache_writes,
	sum((s2.obj->>'kcache_user_time_ms')::numeric) as sum_kcache_user_time_ms,
	sum((s2.obj->>'kcache_system_time_ms')::numeric) as sum_kcache_system_time_ms,
	1 as key
from s2
), diff1 as (   
    select
      abs(sum_s1.sum_calls - sum_si_s1.sum_calls) as sum_calls,
      abs(sum_s1.sum_total_time - sum_si_s1.sum_total_time) as sum_total_time,
      key
    from sum_s1
    join sum_si_s1 using (key)
), diff2 as (   
    select
      abs(sum_s2.sum_calls - sum_si_s2.sum_calls) as sum_calls,
      abs(sum_s2.sum_total_time - sum_si_s2.sum_total_time) as sum_total_time,
      key
    from sum_s2
    join sum_si_s2 using (key)
), diff_calc_rel_err as (   
	select
		abs(sum_si_s2.sum_calls - sum_si_s1.sum_calls) as sum_calls,
		abs(sum_si_s2.sum_total_time - sum_si_s1.sum_total_time) as sum_total_time,
		key
	from sum_si_s2
	join sum_si_s1 using (key)
), calc_error as ( 
	select
		(diff1.sum_calls + diff2.sum_calls)::numeric / 2 as absolute_err_calls,
		(diff1.sum_total_time + diff2.sum_total_time)::numeric / 2 as absolute_err_total_time,
		case when (select sum_calls from diff_calc_rel_err) = 0 then 0 else
			(((diff1.sum_calls + diff2.sum_calls) / 2) * 100) / (select sum_calls from diff_calc_rel_err)
		end as relative_err_calls,
		case when (select sum_total_time from diff_calc_rel_err) = 0 then 0 else
			(((diff1.sum_total_time + diff2.sum_total_time) / 2) * 100) / (select sum_total_time from diff_calc_rel_err)
		end as relative_err_total_time
	from diff1
	join diff2 using (key)
), sum_delta as (
select
	sum((s2.obj->>'calls')::numeric - (s1.obj->>'calls')::numeric) as sum_delta_calls,
	sum((s2.obj->>'total_time')::numeric - (s1.obj->>'total_time')::numeric) as sum_delta_total_time,
	sum((s2.obj->>'rows')::numeric - (s1.obj->>'rows')::numeric) as sum_delta_rows,
	sum((s2.obj->>'shared_blks_hit')::numeric - (s1.obj->>'shared_blks_hit')::numeric) as sum_delta_shared_blks_hit,
	'' as _
	from s1
	join s2 using(md5)
), queries_pre as (
select
   
	(s2.obj->>'calls')::numeric - (s1.obj->>'calls')::numeric as diff_calls,
	( (s2.obj->>'calls')::numeric - (s1.obj->>'calls')::numeric ) / nullif(( select seconds from delta ), 0) as per_sec_calls,
	( (s2.obj->>'calls')::numeric - (s1.obj->>'calls')::numeric ) / nullif(( (s2.obj->>'calls')::numeric - (s1.obj->>'calls')::numeric ), 0) as per_call_calls,
	case when (select sum_delta_calls from sum_delta) = 0 then 0 else round(100 * ((s2.obj->>'calls')::numeric - (s1.obj->>'calls')::numeric) / (select sum_delta_calls from sum_delta)) end as ratio_calls,

	(s2.obj->>'total_time')::numeric - (s1.obj->>'total_time')::numeric as diff_total_time,
	( (s2.obj->>'total_time')::numeric - (s1.obj->>'total_time')::numeric ) / nullif(( select seconds from delta ), 0) as per_sec_total_time,
	( (s2.obj->>'total_time')::numeric - (s1.obj->>'total_time')::numeric ) / nullif(( (s2.obj->>'calls')::numeric - (s1.obj->>'calls')::numeric ), 0) as per_call_total_time,
	case when (select sum_delta_total_time from sum_delta) = 0 then 0 else round(100 * (s2.obj->>'total_time')::numeric / (select sum_delta_total_time from sum_delta)) end as ratio_total_time,

	(s2.obj->>'rows')::numeric - (s1.obj->>'rows')::numeric as diff_rows,
	( (s2.obj->>'rows')::numeric - (s1.obj->>'rows')::numeric ) / nullif(( select seconds from delta ), 0) as per_sec_rows,
	( (s2.obj->>'rows')::numeric - (s1.obj->>'rows')::numeric ) / nullif(( (s2.obj->>'calls')::numeric - (s1.obj->>'calls')::numeric ), 0) as per_call_rows,
	case when (select sum_delta_rows from sum_delta) = 0 then 0 else round(100 * (s2.obj->>'rows')::numeric / (select sum_delta_rows from sum_delta)) end as ratio_rows,

	(s2.obj->>'shared_blks_hit')::numeric - (s1.obj->>'shared_blks_hit')::numeric as diff_shared_blks_hit,
	( (s2.obj->>'shared_blks_hit')::numeric - (s1.obj->>'shared_blks_hit')::numeric ) / nullif(( select seconds from delta ), 0) as per_sec_shared_blks_hit,
	( (s2.obj->>'shared_blks_hit')::numeric - (s1.obj->>'shared_blks_hit')::numeric ) / nullif(( (s2.obj->>'calls')::numeric - (s1.obj->>'calls')::numeric ), 0) as per_call_shared_blks_hit,
	case when (select sum_delta_shared_blks_hit from sum_delta) = 0 then 0 else round(100 * (s2.obj->>'shared_blks_hit')::numeric / (select sum_delta_shared_blks_hit from sum_delta)) end as ratio_shared_blks_hit,

	s1.md5 as md5,
	s1.obj->>'query' as query
from s1
join s2 using(md5)
), queries as (
select
  row_number() over(order by diff_total_time desc) as rownum,
  *
from queries_pre
order by diff_total_time desc
)
-- =======> calculate sum(calls) and sum(total_time) for A, for B, and finally, for I;
-- select * from si  -- >> a b c
-- select * from sum_si  -- >> "280"	"6020"
-- select sum_calls, sum_total_time from sum_s1 -- >> 	"130"	"4810"
-- select sum_calls, sum_total_time from sum_s2	-- >>	"285"	"6022"
-- =======

-- select * from diff1 		-- >>   "150"	"1210"
-- select * from diff2 		-- >>   "5"	"2"
select * from calc_error
Edited by Oleg

Merge request reports