Skip navigation links

MySQL Forums :: InnoDB :: Performance Problems for large inserts into INNODB table via LOAD DATA


Advanced Search

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


Subject Views Written By Posted
Performance Problems for large inserts into INNODB table via LOAD DATA 3370 Michael Vitale 12/27/2010 09:59AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1152 Rick James 12/29/2010 10:17AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1646 Michael Vitale 02/27/2011 07:11AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 911 Michael Vitale 02/27/2011 07:18AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1355 Rick James 02/27/2011 09:04AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1543 Michael Vitale 02/28/2011 09:01AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1471 Rick James 02/28/2011 11:12AM


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.