MySQL Forums
Forum List  »  Partitioning

query optimization
Posted by: Srilakshmi Manjunath
Date: October 29, 2015 03:08AM

Hi, I want to optimize the below query , innodb is currently being used , it takes 13 minute execution time , kindly help .
query:

select q.host_name, q.service_name, q.kpi_name , MAX(a0) as 17_Aug_15, MAX(a1) as 31_Aug_15, Min(h) as Min, MAX(i) as Max , AVG(i) as Average from (select p.host_name, p.service_name, p.kpi_name , CASE when DATE(from_unixtime(p.timestamp))='2015-10-17' then p.Average ELSE 0 END as a0 , CASE when DATE(from_unixtime(p.timestamp))='2015-10-20' then p.Average ELSE 0 END as a1 , p.Average as h, p.Average as i from ( select oam_db_perf.System_CpuUsage_Linux.timestamp, oam_db_perf.System_CpuUsage_Linux.host_name, case when 1=1 then 'CPU' end as service_name, oam_db_perf.System_CpuUsage_Linux.kpi_name, ROUND(AVG(oam_db_perf.System_CpuUsage_Linux.percentage_value),2) as Average, MAX(oam_db_perf.System_CpuUsage_Linux.percentage_value*1) as Peak from oam_db_perf.System_CpuUsage_Linux where timestamp between 1442687400 and 1445279400 group by oam_db_perf.System_CpuUsage_Linux.host_name, oam_db_perf.System_CpuUsage_Linux.kpi_name , substring(from_unixtime(timestamp), 1, 7 ) UNION select oam_db_perf.System_CpuUsage_Windows.timestamp, oam_db_perf.System_CpuUsage_Windows.host_name, case when 1=1 then 'CPU' end as service_name, case when 1=1 then 'CPU_Usage' end as kpi_name, ROUND(AVG(oam_db_perf.System_CpuUsage_Windows.percentage_value),2) as Average, MAX(oam_db_perf.System_CpuUsage_Windows.percentage_value*1) as Peak from oam_db_perf.System_CpuUsage_Windows where timestamp between 1442687400 and 1445279400 and oam_db_perf.System_CpuUsage_Windows.kpi_name='5m' group by oam_db_perf.System_CpuUsage_Windows.host_name, oam_db_perf.System_CpuUsage_Windows.kpi_name , substring(from_unixtime(timestamp), 1, 7 ) UNION select oam_db_perf.System_MemAvalaible_Linux.timestamp, oam_db_perf.System_MemAvalaible_Linux.host_name, case when 1=1 then 'Memory' end as service_name, case when 1=1 then 'Memory %' end as kpi_name, (ROUND(AVG(100 - oam_db_perf.System_MemAvalaible_Linux.percentage_value),2)) as Average, (MAX(100 - oam_db_perf.System_MemAvalaible_Linux.percentage_value*1)) as Peak from oam_db_perf.System_MemAvalaible_Linux where oam_db_perf.System_MemAvalaible_Linux.kpi_name like '%Memory_Free%' and timestamp between 1442687400 and 1445279400 group by oam_db_perf.System_MemAvalaible_Linux.host_name, oam_db_perf.System_MemAvalaible_Linux.kpi_name , substring(from_unixtime(timestamp), 1, 7 ) UNION select oam_db_perf.System_MemAvalaible_Windows.timestamp, oam_db_perf.System_MemAvalaible_Windows.host_name, case when 1=1 then 'Memory' end as service_name, oam_db_perf.System_MemAvalaible_Windows.kpi_name, ROUND(AVG(oam_db_perf.System_MemAvalaible_Windows.percentage_value),2) as Average, MAX(oam_db_perf.System_MemAvalaible_Windows.percentage_value*1) as Peak from oam_db_perf.System_MemAvalaible_Windows where timestamp between 1442687400 and 1445279400 and oam_db_perf.System_MemAvalaible_Windows.kpi_name like '%physical memory %%' group by oam_db_perf.System_MemAvalaible_Windows.host_name, oam_db_perf.System_MemAvalaible_Windows.kpi_name , substring(from_unixtime(timestamp), 1, 7 ) UNION select oam_db_perf.System_SwapAvalaible_Linux.timestamp, oam_db_perf.System_SwapAvalaible_Linux.host_name, case when 1=1 then 'Swap' end as service_name, case when 1=1 then 'Swap %' end as kpi_name, (ROUND(AVG(100 - oam_db_perf.System_SwapAvalaible_Linux.percentage_value),2)) as Average, (MAX(100 - oam_db_perf.System_SwapAvalaible_Linux.percentage_value*1)) as Peak from oam_db_perf.System_SwapAvalaible_Linux where oam_db_perf.System_SwapAvalaible_Linux.kpi_name like '%Swap_Free%' and timestamp between 1442687400 and 1445279400 group by oam_db_perf.System_SwapAvalaible_Linux.host_name, oam_db_perf.System_SwapAvalaible_Linux.kpi_name , substring(from_unixtime(timestamp), 1, 7 ) UNION select oam_db_perf.Disk_Usage_Linux.timestamp, oam_db_perf.Disk_Usage_Linux.host_name, case when 1=1 then 'Disk' end as service_name, oam_db_perf.Disk_Usage_Linux.kpi_name, ROUND(AVG(oam_db_perf.Disk_Usage_Linux.percentage_value),2) as Average, MAX(oam_db_perf.Disk_Usage_Linux.percentage_value*1) as Peak from oam_db_perf.Disk_Usage_Linux where timestamp between 1442687400 and 1445279400 group by oam_db_perf.Disk_Usage_Linux.host_name, oam_db_perf.Disk_Usage_Linux.kpi_name , substring(from_unixtime(timestamp), 1, 7 ) UNION select oam_db_perf.System_Volumes_Windows.timestamp, oam_db_perf.System_Volumes_Windows.host_name, case when 1=1 then 'Disk' end as service_name, oam_db_perf.System_Volumes_Windows.kpi_name, (ROUND(AVG(100 - oam_db_perf.System_Volumes_Windows.percentage_value),2)) as Average, (MAX(100 - oam_db_perf.System_Volumes_Windows.percentage_value*1)) as Peak from oam_db_perf.System_Volumes_Windows where timestamp between 1442687400 and 1445279400 and oam_db_perf.System_Volumes_Windows.kpi_name like '%% %' and oam_db_perf.System_Volumes_Windows.kpi_name!='System Reserved' group by oam_db_perf.System_Volumes_Windows.host_name, oam_db_perf.System_Volumes_Windows.kpi_name , substring(from_unixtime(timestamp), 1, 7 )) as p where p.host_name in (select node_name from oam_db.node where cluster_name='AIRTEL_TANZANIA_MOBIQUITY' ) order by p.host_name,p.service_name , p.timestamp) as q group by q.host_name, q.service_name, q.kpi_name;

Options: ReplyQuote


Subject
Views
Written By
Posted
query optimization
2619
October 29, 2015 03:08AM
1379
October 30, 2015 04:58PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.