Bug #59978 MySql takes much time When Again Runs Load data into MyIsam index table

• Submitted: 7 Feb 2011 7:00
• Modified: 2 Apr 2011 17:44
• Reporter: Saurabh Goyal
• Status: No Feedback
• Category: MySQL Server: MyISAM storage engine
• Severity: S5 (Performance)
• Version: 5.5
• OS: Any
• CPU Architecture: Any
• Tags: Indexing, LOAD DATA, myisam

[7 Feb 2011 7:00] Saurabh Goyal

Description:
Hi,

MySql takes much time When Again Runs Load data into MyIsam index table  .

My ini.cnf Configuration :-

MYSQL Config :-Key-buffer-size 10M
Myisam recovery option :off
Myisam_max_sort_file_size : 100G
sort_buffer_size : 17M

I have Create Table :-

CREATE TABLE `sms_logmastermyisamwithidxD` (                           
                              `bintGuid` bigint(20) NOT NULL AUTO_INCREMENT,                      
                              `vchApiUserId` varchar(10) DEFAULT NULL,                            
                              `vchReceiverMobile` varchar(15) DEFAULT NULL,                       
                              `vchSenderInfo` varchar(20) DEFAULT NULL,                           
                              `tsReceiverTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  
                              `vchMessage` varchar(1024) DEFAULT NULL,                            
                              PRIMARY KEY (`bintGuid`),                                           
                              KEY `vchReceiverMobile` (`vchReceiverMobile`),                      
                              KEY `vchMessage` (`vchMessage`(160))                                
                            ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

then Run :-

LOAD DATA Local INFILE 'D:/Saurabh/DataIntelligence/data/mtlog_api11.2222010/mtlog_api11.csv.0012222010' 
INTO TABLE sms_logmastermyisamwithidxD  FIELDS TERMINATED BY '|' ENCLOSED BY '\''  
LINES TERMINATED BY "\n"(@dummy, @dummy,@dummy, vchApiUserId, @dummy, @dummy, 
vchReceiverMobile, vchSenderInfo, @dummy, @dummy,@dummy, @dummy, @dummy, @dummy, 
@dummy, @dummy,@dummy,tsReceiverTime,@dummy,@dummy, @dummy,vchMessage);

Output are as Follows:-

Processed : 2580095 row(s)affected
Data_length : 519845264
Index_length : 37197824
Query Processing Time :- 134:27 sec
File Size 0.98 GB

When Again Run with 768 MB:-

LOAD DATA Local INFILE 'D:/Saurabh/DataIntelligence/data/mtlog_api11.2222010/mtlog_api11.csv.0812222010' 
INTO TABLE sms_logmastermyisamwithidxD  FIELDS TERMINATED BY '|' ENCLOSED BY '\''  
LINES TERMINATED BY "\n"(@dummy, @dummy,@dummy, vchApiUserId, @dummy, @dummy, 
vchReceiverMobile, vchSenderInfo, @dummy, @dummy,@dummy, @dummy, @dummy, @dummy, 
@dummy, @dummy,@dummy,tsReceiverTime,@dummy,@dummy, @dummy,vchMessage); 

Processed : 1942514 row(s)affected
Data_length : 913239680
Index_length : 133406720
Query Processing Time :- 858.703 sec
File Size 768 MB

That Means It takes much time When Re-run load data on MyIsam Indexed Table.

Thanks with Regards
Saurabh Goyal

How to repeat:
Similar Problem when Run 3 times , 4 time Processing Time increase continuously.

Suggested fix:
I will take less time When run for Second Query.

[2 Mar 2011 17:44] Valeriy Kravchuk

Why do you think it is a bug? 

The bigger your table is, the bigger indexes are, and process to update them with new data takes more time.

[2 Apr 2011 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".