MySQL Bugs: #59093: LOAD DATA hangs after millions of rows inserted

Bug #59093	LOAD DATA hangs after millions of rows inserted
Submitted:	21 Dec 2010 16:24	Modified:	21 Jul 2012 20:49
Reporter:	Michael Vitale	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.48, 5.5.8	OS:	Linux (Centos 5.5 Final)
Assigned to:		CPU Architecture:	Any
Tags:	LOAD DATA

Description:
My LOAD DATA job hangs after loading about an additional 1.5 million rows into one table, that already had about 4 million rows in it.  I am using Innodb and here is the DDL I used to create the table, which uses partitioning and 35G datafiles.  I also created on other non-unique index on this table and disabled it before the LOAD DATA run.  It worked faster before I added the partitions and separate datafiles.  I used to use one 400G datafile and then it would hang after adding about 120 million rows.  So something is going amuck with either multiple datafiles or the partitioning stuff.

CREATE DATABASE discoverydb;
CREATE TABLESPACE TSTickets ADD DATAFILE 'TSTickets01' INITIAL_SIZE = 34359738368 EXTENT_SIZE = 2147483648 ENGINE=INNODB;
ALTER  TABLESPACE TSTickets ADD DATAFILE 'TSTickets02' INITIAL_SIZE = 34359738368 ENGINE=INNODB;
ALTER  TABLESPACE TSTickets ADD DATAFILE 'TSTickets03' INITIAL_SIZE = 34359738368 ENGINE=INNODB;
ALTER  TABLESPACE TSTickets ADD DATAFILE 'TSTickets04' INITIAL_SIZE = 34359738368 ENGINE=INNODB;
ALTER  TABLESPACE TSTickets ADD DATAFILE 'TSTickets05' INITIAL_SIZE = 34359738368 ENGINE=INNODB;
ALTER  TABLESPACE TSTickets ADD DATAFILE 'TSTickets06' INITIAL_SIZE = 34359738368 ENGINE=INNODB;
ALTER  TABLESPACE TSTickets ADD DATAFILE 'TSTickets07' INITIAL_SIZE = 34359738368 ENGINE=INNODB;
ALTER  TABLESPACE TSTickets ADD DATAFILE 'TSTickets08' INITIAL_SIZE = 34359738368 ENGINE=INNODB;
ALTER  TABLESPACE TSTickets ADD DATAFILE 'TSTickets09' INITIAL_SIZE = 34359738368 ENGINE=INNODB;
ALTER  TABLESPACE TSTickets ADD DATAFILE 'TSTickets10' INITIAL_SIZE = 34359738368 ENGINE=INNODB;
CREATE TABLE discoverydb.Tickets(
TKT_ID           INT NOT NULL,
aYearMonthDayDir INT NOT NULL,
RUN_ID           INT NOT NULL,
LastUpdate       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
XMTSTimestamp    BIGINT NULL,
FileDateTime     DATETIME NOT NULL,
Attachments      TINYINT NOT NULL,        
MessageID        VARCHAR(300) NULL,
XMTSTicket       VARCHAR(100) NOT NULL,
Maildate         VARCHAR(60) NULL,
Mailsubject      VARCHAR(300) NULL,
Mailfrom         VARCHAR(300) NULL,
MailFrom2        VARCHAR(300) Null,
MailTo           VARCHAR(500) NULL,
Mailcc           VARCHAR(500) NULL,
Mailreplyto      VARCHAR(500) NULL,     
Mailorg          VARCHAR(300) NULL,   
BodyType         VARCHAR(100) NOT NULL,
BodyLength       INT NOT NULL,
MailExportNotes  VARCHAR(5000) NULL,
MailImportNotes  VARCHAR(5000) NULL,
Mailbody         MEDIUMTEXT NOT NULL,
PRIMARY KEY (TKT_ID,aYearMonthDayDir)) TABLESPACE discovery Engine=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
PARTITION BY RANGE (aYearMonthDayDir)
(
PARTITION P1989_1995 VALUES LESS THAN (19960000),
PARTITION P1996      VALUES LESS THAN (19970000),
PARTITION P1997      VALUES LESS THAN (19980000),
PARTITION P1998      VALUES LESS THAN (19990000),
PARTITION P1999      VALUES LESS THAN (20000000),
PARTITION P2000      VALUES LESS THAN (20010000),
PARTITION P2001      VALUES LESS THAN (20020000),
PARTITION P2002      VALUES LESS THAN (20030000),
PARTITION P2003      VALUES LESS THAN (20040000),
PARTITION P2004      VALUES LESS THAN (20050000),
PARTITION P2005      VALUES LESS THAN (20060000),
PARTITION P2006      VALUES LESS THAN (20070000),
PARTITION P2007      VALUES LESS THAN (20080000),
PARTITION P2008      VALUES LESS THAN (20090000),
PARTITION P2009      VALUES LESS THAN (20100000),
PARTITION P2010UP    VALUES LESS THAN (20200000)
);

How to repeat:
it is a python script that invokes LOAD DATA commands on files ready to be imported.

Thank you for the report.

This can be duplicate of bug #51851 fixed in version 5.1.53. Please upgrade to this version or, better, to current 5.1.54, try with it and inform us if problem still exists.

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".

I upgraded to MySQL 5.5.8 and I still have the problem as described before with 5.1.48.  I get around 3-500 rows inserted per second until after about an hour at which time I get around 150-250 inserts per second.

PLEASE REOPEN THIS BUG SINCE I UPGRADED to 5.5 as you requested

the comment is wrong I submitted earlier about inserts per second.

I get about 3000 to 5000 rows inserted per second up to about an hour, but after an hour I get about 150 to 250 inserts per second.

Thank  you for the feedback.

I can not repeat described behavior. You use CREATE TABLESPACE syntax. Do you use MySLQ Cluster? Please also send us your configuration file and output of SHOW TABLE STATUS LIKE 'Tickets'

csv file for show table status

Attachment: tablestatus.csv (application/vnd.ms-excel, text), 511 bytes.

my.cnf file

Attachment: my.cnf (application/octet-stream, text), 1.79 KiB.

I am not using MySQL Cluster.  I have attached the 2 files you requested:
my.cnf
show table status (csv output)

Thank you for the feedback.

I can not repeat described behavior with modern versions. Please try current version 5.1.63 and inform us if issue still exists.

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".