MySQL Forums
Forum List  »  InnoDB

Re: Performance Problems for large inserts into INNODB table via LOAD DATA
Posted by: Michael Vitale
Date: February 27, 2011 07:18AM

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;

Options: ReplyQuote




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.