MYSQL complex query taking much time to execute

[jacanand]

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,1 3,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,1 00,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?

[przemek]

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.

[gmouse]

please provide something that is actually readable

[jacanand]

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.

[przemek]

OK, one more table is missing here: vmware_relationships

Also for the future it's nice to use any SQL formatter, like: http://www.dpriver.com/pp/sqlformat.htm

Your SQL would look like this:

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_4FROM 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.timeWHERE 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, 81, 86, 85, 89, 5, 82, 93, 94, 95, 21, 25, 48, 66, 120, 1, 40, 63, 100, 18, 47, 19, 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, 113, 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 <= 1345548000000GROUP BY b.deviceid, cpu.time, mem.time, disk.time, net.time;

[jacanand]

Hi Przemek,

Sorry about that. Please find the definition for vmware_relationships table,

CREATE TABLE `vmware_relationships` (
`fromid` int(20) NOT NULL,
`toid` int(20) NOT NULL,
`type` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`fromid`,`toid`,`type`),
KEY `toid` (`toid`),
KEY `toIdType` (`type`,`toid`,`fromid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Thanks,
Jac

[jacanand]

Hi Przemek,

Could you please help on this? It is quite urgent.

Thanks,
Jac

[przemek]

Hey Jac,

After quick view it seems that you have indexes on almost all conditions here. But the way MySQL handles subqueries is probably the culprit here. Also it's impossible to get valid EXPLAIN for this kind of query without any example data. Is it possible to get sql dump (gz) for all these six tables to test with?