MySQL Forums
Forum List  »  Partitioning

Consumption of more hard disk space in "Copying to tmp table" phase
Posted by: Raghavendra K
Date: April 14, 2014 01:07AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Consumption of more hard disk space in "Copying to tmp table" phase
3898
April 14, 2014 01:07AM


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.