Re: Performance Problems for large inserts into INNODB table via LOAD DATA
Forgot to mention that I removed the partitioning table logic to see if this would help any. Here is my current DDL without partitioning:
CREATE DATABASE discoverydb;
use discoverydb;
CREATE TABLESPACE TSTickets ADD DATAFILE 'TSTickets' INITIAL_SIZE = 34359738368 EXTENT_SIZE = 2147483648 ENGINE=INNODB;
CREATE TABLESPACE TSTicketAtt ADD DATAFILE 'TSTicketAtt01' INITIAL_SIZE = 34359738368 EXTENT_SIZE = 2147483648 ENGINE=INNODB;
ALTER TABLESPACE TSTicketAtt ADD DATAFILE 'TSTicketAtt02' INITIAL_SIZE = 34359738368 ENGINE=INNODB;
drop table if exists Tickets;
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(500) NULL,
XMTSTicket VARCHAR(30) NOT NULL,
Maildate VARCHAR(60) NULL,
Mailsubject VARCHAR(500) NULL,
Mailfrom VARCHAR(500) NULL,
MailFrom2 VARCHAR(500) Null,
MailTo VARCHAR(500) NULL,
Mailcc VARCHAR(500) NULL,
Mailreplyto VARCHAR(500) NULL,
Mailorg VARCHAR(500) 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), UNIQUE KEY iux_TicketsTKT_ID(TKT_ID), KEY ix_TicketsXMTSTICKET(XMTSTICKET), KEY ix_TicketsATTACH(Attachments), KEY ix_TicketsMAILBODY(MAILBODY(100)))
TABLESPACE TSTickets Engine=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
drop table if exists TicketAttachments;
CREATE TABLE discoverydb.TicketAttachments (
FILE_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
LastUpdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
TKT_ID INT NOT NULL,
seqno INT NOT NULL,
XMTSTicket VARCHAR(30) NOT NULL,
MessageID VARCHAR(200) NULL,
FileType VARCHAR(100) NOT NULL,
FileSize INT NOT NULL,
Name VARCHAR(300) NULL,
create_date VARCHAR(40) NULL,
mod_date VARCHAR(40) NULL,
read_date VARCHAR(40) NULL,
Content LONGBLOB NOT NULL,
KEY ix_TicketAttachTKT_ID(TKT_ID), KEY ix_TicketAttachXMTSTICKET(XMTSTICKET), KEY ix_TicketAttachFILETYPE(FILETYPE))
TABLESPACE TSTicketAtt Engine=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;