Consumption of more hard disk space in "Copying to tmp table" phase
Hello,
I have a "evt" table with column "time". Below is the table, its partitions and the records in each partition
CREATE TABLE `evt` (
`key` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`id` binary(26) NOT NULL,
`evthandle` int(10) unsigned NOT NULL,
`mkey` int(10) unsigned NOT NULL DEFAULT '0',
`time` datetime NOT NULL,
`type` int(10) unsigned NOT NULL,
`cid` int(10) unsigned NOT NULL,
`msg` mediumtext,
`srv_precedence` int(10) unsigned DEFAULT '0',
PRIMARY KEY (`key`,`time`),
UNIQUE KEY `id_mkey_time_idx` (`id`,`mkey`,`time`),
KEY `lscp_idx` (`handle`),
KEY `mkey_idx` (`mkey`),
KEY `time_idx` (`time`),
KEY `lscp_and_time_idx` (`handle`,`time`),
KEY `type_idx` (`type`),
KEY `cid_idx` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=152434400 DEFAULT CHARSET=utf8 MAX_ROWS=1000000000 AVG_ROW_LENGTH=500
/*!50500 PARTITION BY RANGE COLUMNS(`time`)
(PARTITION Janp1 VALUES LESS THAN ('2013-02-01') ENGINE = InnoDB,
PARTITION Febp2 VALUES LESS THAN ('2013-03-01') ENGINE = InnoDB,
PARTITION Marp3 VALUES LESS THAN ('2013-04-01') ENGINE = InnoDB,
PARTITION Aprp4 VALUES LESS THAN ('2013-05-01') ENGINE = InnoDB,
PARTITION Mayp5 VALUES LESS THAN ('2013-06-01') ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
----------------------------------------------------------------------------------------------------
Janp1 has 382150 records
Febp2 has 2029741 records
Marp3 has 5514800 records
Aprp4 has 5202504 records
Mayp5 has 2278992 records
p6 has around 35377 records
----------------------------------------------------------------------------------------------------
Below is the "mdl" table and its records count
CREATE TABLE `mdl` (
`mkey` int(10) unsigned NOT NULL AUTO_INCREMENT,
`mdlhandle` int(10) unsigned NOT NULL DEFAULT '0',
`mdlname` varchar(4000) DEFAULT NULL,
`mthandle` int(10) unsigned DEFAULT NULL,
`mdlclass` int(10) unsigned DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`mdltime` datetime DEFAULT '2000-01-01 00:00:00',
`lsphandle` int(10) unsigned DEFAULT NULL,
`dtime` datetime DEFAULT NULL,
`secstring` varchar(255) DEFAULT '*UNKNOWN*',
PRIMARY KEY (`mkey`),
KEY `mdlhandle` (`mdlhandle`),
KEY `idx_mthandle` (`mthandle`),
KEY `idx_mdlclass` (`mdlclass`),
KEY `lsphandle_idx` (`lsphandle`),
KEY `idx_mdlname` (`mdlname`(255)),
KEY `idx_dtime` (`dtime`),
KEY `idx_secstring` (`secstring`),
KEY `idx_lsphandle_secstring` (`lsphandle`,`secstring`)
) ENGINE=InnoDB AUTO_INCREMENT=1087951 DEFAULT CHARSET=utf8
mdl table has 1087950 records.
----------------------------------------------------------------------------------------------------
when I trigger below query for "Febp2" partition that has around 2million records its consuming around 58-60GB of hard disk space during "Copying to tmp tables" & "Sending data" phases.
SELECT `evt`.`time`, `mdl`.`mdlname`, `mdl`.`address`, `creator`.`creator_name`, `evt`.`type`, `evt`.`key`, `landscape`.`name`, `evt`.`msg` FROM ((`rpt`.`evt` `evt` INNER JOIN `rpt`.`mdl` `mdl` ON `evt`.`mkey`=`mdl`.`mkey`) INNER JOIN `rpt`.`creator` `creator` ON `evt`.`cid`=`creator`.`cID`) INNER JOIN `rpt`.`landscape` `landscape` ON (`mdl`.`mdlhandle`=`landscape`.`handle`) AND (`mdl`.`secstring`=`landscape`.`secstring`) WHERE (`evt`.`time`>='2013-02-01 01:00:00' AND `evt`.`time`<'2013-02-28 23:59:01') AND `landscape`.`user`='Administrator' ORDER BY `evt`.`time` DESC
hard disk space for tmp tables creation is proportional i.e. if the above query modified for "Marp3" partitions that has 5.5 million records HD consumption is around 150-160GB that mean for 1 million records is appr. 30GB
This issue is seen irrespective of partitioning.
tunning •tmp_table_size & •max_heap_table_size to 1GB did not help much.
Any help how to fix?
-Raghu