This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to find the average running time and average resource utilization of running queries in Greenplum.
I am using the table of GPCC which are available in GPPERFMON database. So if you don’t configure GPCC, you can’t use below query.
Using below query, you can find the total average run time, average CPU, average Memory occupied by long-running queries which took more than 20 minutes and checking for only last 10 days.
As per your requirement, you can change the minutes and days filter value.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
select sh.ctime ,query_text ,username ,db as DatabaseName ,rsqname as ResourceQueueName ,avg(tfinish-tstart) as AverageRunTime ,count(*) as TotalExecution ,round(avg(100 - cpu_idle)::numeric,2) as AverageCPUUsed ,round(max(100 - cpu_idle)::numeric,2) as MaxCPUUsed ,round(avg(mem_actual_used)/power(1024,3)::numeric,2) as AverageMemoryUsed ,round(max(mem_actual_used)/power(1024,3)::numeric,2) as MaxMemoryUsed from system_history sh, queries_history qh where sh.ctime between date_trunc('day',localtimestamp - interval '10 days') and date_trunc('day',localtimestamp) and sh.ctime=qh.ctime and db not in ('gpperfmon') and date_part('hour',tfinish - tstart)*60 + date_part('minute',tfinish - tstart) > 20 group by sh.ctime,query_text,username, db,rsqname; |
Leave a Reply