refs #179 K003 for each metric show ratio
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:
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