Not the answer you need?
Register and ask your own question!

MYSQL complex query taking much time to execute

jacanandjacanand EntrantCurrent User Role Beginner
In my application we have a query to fetch records from 4 tables. The below is the query

SELECT a.DeviceId, cpu.Time as Field_6, a.DeviceHostName as Field_5, avg(cpu.usagePercentage) as Field_1, avg(mem.usagePercentage) as Field_2, avg(disk.read_kb_per_sec)/1024 as Field_3, avg(net.BytesInPerSec)/1024 as Field_4 FROM devices b inner join vmware_relationships vmr on vmr.fromid = b.deviceid and type = 3 inner join devices a on a.deviceid = b.deviceid and a.retire = 0 left join (select deviceid,time,avg(usagePercentage) as usagePercentage from vmware_sum_cpu_perf_stats where summaryunit = 'hourly' group by deviceid,time) cpu on vmr.toid = cpu.deviceid left join (select deviceid,time,avg(usagePercentage) as usagePercentage from vmware_sum_mem_perf_stats where summaryunit = 'hourly' group by deviceid,time) mem on vmr.toid = mem.deviceid and cpu.time = mem.time left join (select deviceid,time,avg(diskUsage_in_kbps) as read_kb_per_sec from vmware_sum_disk_perf_stats where summaryunit = 'hourly' group by deviceid,time) disk on vmr.toid = disk.deviceid and cpu.time = disk.time left join (select deviceid,time,avg(netUsage_in_kbps) as BytesInPerSec from ent_host_perf_net_sum where summaryunit = 'hourly' group by deviceid,time) net on vmr.toid = net.deviceid and cpu.time = net.time WHERE a.DeviceId IN (42,34,69,53,14,16,15,17,52,55,51,59,54,57,12,22,13,92,87,88 ,8,6,9,10,26,44,28,43,2,29,45,30,46,11,7,90,72,83,91,84,80,8 1,86,85,89,5,82,93,94,95,21,25,48,66,120,1,40,63,100,18,47,1 9,49,50,58,41,111,115,116,75,99,74,76,67,61,101,71,68,105,79 ,78,96,102,33,37,38,27,39,31,35,32,36,110,114,109,106,108,11 3,107,77,112,103,70,62,104,60,98,65,56,64,97,20,24,23,3,4,73 ) AND cpu.Time >= 1344252000000 AND cpu.Time <= 1345548000000 GROUP BY b.deviceid,cpu.time,mem.time,disk.time,net.time


And the following are the Create table queries for all the tables,

CREATE TABLE `vmware_sum_disk_perf_stats` (
`Time` bigint(20) NOT NULL DEFAULT '0',
`deviceId` int(20) NOT NULL DEFAULT '0',
`summaryUnit` enum('Hourly','Daily','Weekly','Monthly') NOT NULL DEFAULT 'Hourly',
`Id` varchar(50) NOT NULL DEFAULT '',
`numberRead` float DEFAULT NULL,
`numberWrite` float DEFAULT NULL,
`read_kb_per_sec` float DEFAULT NULL,
`write_kb_per_sec` float DEFAULT NULL,
`diskUsage_in_kbps` float DEFAULT NULL,
`numberReadWrite` float DEFAULT NULL,
`deviceLatency` bigint(20) DEFAULT NULL,
`readLatency` bigint(20) DEFAULT NULL,
`writeLatency` bigint(20) DEFAULT NULL,
`queueLatency` bigint(20) DEFAULT NULL,
PRIMARY KEY (`deviceId`,`Time`,`summaryUnit`,`Id`),
KEY `index2` (`deviceId`,`Id`,`summaryUnit`,`Time`),
KEY `SummaryUnit` (`summaryUnit`,`Time`),
KEY `index` (`summaryUnit`,`deviceId`,`Time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `vmware_sum_cpu_perf_stats` (
`Time` bigint(20) NOT NULL DEFAULT '0',
`deviceId` int(20) NOT NULL DEFAULT '0',
`summaryUnit` enum('Hourly','Daily','Weekly','Monthly') NOT NULL DEFAULT 'Hourly',
`usagePercentage` float DEFAULT NULL,
`usageMhz` float DEFAULT NULL,
`used` float DEFAULT NULL,
`guaranteed` float DEFAULT NULL,
`extra` float DEFAULT NULL,
`ready` float DEFAULT NULL,
`wait` float DEFAULT NULL,
`sys` float DEFAULT NULL,
`idle` float DEFAULT NULL,
PRIMARY KEY (`deviceId`,`Time`,`summaryUnit`),
KEY `time` (`deviceId`,`summaryUnit`,`Time`),
KEY `index` (`summaryUnit`,`deviceId`,`Time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `vmware_sum_mem_perf_stats` (
`Time` bigint(20) NOT NULL DEFAULT '0',
`deviceId` int(20) NOT NULL DEFAULT '0',
`summaryUnit` enum('Hourly','Daily','Weekly','Monthly') NOT NULL DEFAULT 'Hourly',
`usagePercentage` float DEFAULT '0',
`consumed` float DEFAULT '0',
`zero` float DEFAULT '0',
`state` int(20) DEFAULT '0',
`active` float DEFAULT '0',
`shared` float DEFAULT '0',
`granted` float DEFAULT '0',
`overhead` float DEFAULT '0',
`swapin` float DEFAULT '0',
`swapped` float DEFAULT '0',
`swapOut` float DEFAULT '0',
`swapUsed` float DEFAULT '0',
`swapTarget` float DEFAULT '0',
`unreserved` float DEFAULT '0',
`vmmemctl` float DEFAULT '0',
`vmmemctlTarget` float DEFAULT '0',
`heapFree` float DEFAULT '0',
`heap` float DEFAULT '0',
`reservedCapacity` float DEFAULT '0',
PRIMARY KEY (`deviceId`,`Time`,`summaryUnit`),
KEY `index2` (`Time`,`summaryUnit`,`deviceId`),
KEY `SummaryUnit` (`summaryUnit`,`Time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE `ent_host_perf_net_sum` (
`DeviceId` int(11) NOT NULL,
`summaryUnit` enum('Hourly','Daily','Weekly','Monthly') NOT NULL DEFAULT 'Hourly',
`Time` bigint(20) NOT NULL DEFAULT '0',
`GmtOffset` bigint(20) NOT NULL DEFAULT '0',
`Day` bigint(20) NOT NULL DEFAULT '0',
`Hour` int(11) NOT NULL DEFAULT '0',
`InterfaceId` varchar(50) NOT NULL,
`NoECBAvailCnt` float DEFAULT NULL,
`BytesInPerSec` float DEFAULT NULL,
`BytesOutPerSec` float DEFAULT NULL,
`PacketsCollisionsPerSec` float DEFAULT NULL,
`PacketsInPerSec` float DEFAULT NULL,
`PacketsInErrorsPerSec` float DEFAULT NULL,
`PacketsInDiscardedPerSec` float DEFAULT NULL,
`PacketsInUnicastPerSec` float DEFAULT NULL,
`PacketsInNonunicastPerSec` float DEFAULT NULL,
`PacketsInCompressedPerSec` float DEFAULT NULL,
`PacketsInDroppedPerSec` float DEFAULT NULL,
`PacketsInUnknownPerSec` float DEFAULT NULL,
`PacketsInFrameAlignmentErrorsPerSec` float DEFAULT NULL,
`PacketsInFifoOverrunErrorsPerSec` float DEFAULT NULL,
`PacketsOutPerSec` float DEFAULT NULL,
`PacketsOutErrorsPerSec` float DEFAULT NULL,
`PacketsOutDiscardedPerSec` float DEFAULT NULL,
`PacketsOutUnicastPerSec` float DEFAULT NULL,
`PacketsOutNonunicastPerSec` float DEFAULT NULL,
`PacketsOutCompressedPerSec` float DEFAULT NULL,
`PacketsOutDroppedPerSec` float DEFAULT NULL,
`PacketsOutCarrierErrorsPerSec` float DEFAULT NULL,
`netUsage_in_kbps` float DEFAULT NULL,
PRIMARY KEY (`DeviceId`,`summaryUnit`,`Time`,`InterfaceId`),
KEY `index2` (`summaryUnit`,`DeviceId`,`InterfaceId`,`Time`),
KEY `Time` (`Time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


Also I have attached the explain query.

Can someone give some suggestions to improve the performance on this query?

Comments

  • przemekprzemek Percona Support Engineer Percona Staff Role
    Jacanand, you forgot to provide definition for table 'devices' used in "inner join devices".
    Also, at the first look, this query seems to be too complicated to be fast, especially all the sub-queries most likely make it slower.
  • gmousegmouse Mod Squad Inactive User Role Beginner
    please provide something that is actually readable
  • jacanandjacanand Entrant Current User Role Beginner
    Hi Przemek,

    Thanks for your reply. I have pasted the definition of Devices tables here.

    CREATE TABLE `devices` (
    `DeviceId` int(20) NOT NULL AUTO_INCREMENT,
    `deviceIpAddress` varchar(255) NOT NULL DEFAULT '',
    `DeviceHostName` varchar(100) DEFAULT NULL,
    `ReadCommunityString` varchar(30) DEFAULT NULL,
    `SNMPPort` int(10) unsigned DEFAULT NULL,
    `RMAgentPort` int(10) unsigned NOT NULL DEFAULT '0',
    `TrapPort` int(10) unsigned DEFAULT NULL,
    `Active` int(10) unsigned NOT NULL DEFAULT '1',
    `PollFlag` int(10) unsigned NOT NULL DEFAULT '1',
    `HostOs` varchar(127) DEFAULT NULL,
    `LastCollectTime` bigint(20) unsigned DEFAULT NULL,
    `LastPollTime` bigint(20) unsigned DEFAULT NULL,
    `LastTrapTime` bigint(20) unsigned DEFAULT NULL,
    `HostOSName` varchar(50) DEFAULT NULL,
    `Parameters` blob,
    `AgentUpdateTime` varchar(100) DEFAULT NULL,
    `Retire` int(10) unsigned NOT NULL DEFAULT '0',
    `DeviceDescription` blob,
    `ParentDeviceId` int(20) DEFAULT NULL,
    `DomainName` varchar(35) DEFAULT NULL,
    `agentVersion` varchar(15) DEFAULT '',
    `monitorAgent` tinyint(4) DEFAULT '0',
    `lastPingTimestamp` bigint(20) DEFAULT '0',
    `lastPingSucceeded` tinyint(4) DEFAULT '1',
    `backupServerTimeZoneId` varchar(50) DEFAULT NULL,
    `backupWindowStartTime` varchar(20) DEFAULT NULL,
    `backupWindowEndTime` varchar(20) DEFAULT NULL,
    `vendorVersion` varchar(100) DEFAULT '',
    `statusCode` tinyint(1) DEFAULT '0',
    `statusDescription` varchar(255) DEFAULT '',
    `password` varchar(128) NOT NULL DEFAULT '41EBE6DA5C3E3253',
    `sslEnabled` tinyint(4) NOT NULL DEFAULT '0',
    PRIMARY KEY (`DeviceId`),
    KEY `DeviceId` (`DeviceId`,`HostOs`),
    KEY `ParentDeviceId` (`ParentDeviceId`),
    KEY `Retire` (`Retire`),
    KEY `DeviceId_2` (`DeviceId`,`Retire`),
    KEY `HostOs` (`HostOs`)
    ) ENGINE=MyISAM AUTO_INCREMENT=6526 DEFAULT CHARSET=latin1;

    Yes. It is a complex query which will be generated dynamically in my java code. Could you please advice on this how to make it more faster.

    Thanks,
    Jac.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.