Hi!
Creating the indexes after the import will only slow down the operation. MySQL recreates the whole table at CREATE INDEX.
We are working on a fast index create in MySQL-5.1. Until then, the fastest way is to import the rows to a table where indexes have already been created, just like you have been doing.
If you want to import 132 million rows, and the speed is 2000 rows / second, then the import takes 64 000 seconds = 18 hours. Why not wait over the night?
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php
Order MySQL Network from http://www.mysql.com/network/
----- Original Message ----- From: ""Peter J Milanese"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, March 18, 2005 1:00 AM
Subject: Re: Problem: Slow "LOAD FILE" performance with innodb
My first guess is the indexes. Maybe create them after the import. It will nonetheless take a bit of time! ------Original Message------ From: Jarle Aase To: MySQL list Sent: Mar 17, 2005 11:53 PM Subject: Problem: Slow "LOAD FILE" performance with innodb
Hi list,
I'm trying to import some data into a MySQL database. The data-file is 5.5G, and consist of about 132 million rows. The machine is a P4 3GHz with 1G RAM and a single 250 GB ATA-disk for data. The operating system is Debian GNU Linux "testing" with kernel 2.6.8 with hyperthreading support. The version of MySQL is 4.1.10.
The problem is that the import takes "forever". I aborted the import after about 8 hours. When the import starts, the performance is about 20.000 rows/sec. After about 20 million rows, the performance has decreased to around 2000 rows/sec. The CPU is 40 - 50% idle.
Is there a way to boost the performance? The database is idle, and while importing, performance is the key priority (record-locking, transaction-rollbacks and file-integrety/crash-recovery are not required until the data are imported).
I've done some googling, and I've seen the problem described, but I've not found any solutions. The performance improves a litte with "innodb_flush_method = O_DSYNC"; but an initial import of a database does not really need to flush until done.
DROP TABLE IF EXISTS `TestTable`; CREATE TABLE `TestTable` ( `KeyId`BIGINT AUTO_INCREMENT NOT NULL, `Id` bigint NOT NULL default '0', `XId` int NOT NULL DEFAULT '1', `YId` int NOT NULL default '0', `Date` datetime NOT NULL default '0000-00-0000:00:00', `Dtm` int(11) NOT NULL default '0', `Ct` char(3) default NULL, PRIMARY KEY (`KeyId`), KEY ix_id(Id), KEY ix_anlegg(`XId`, `YId`, `Date`), KEY ix_dato(`Date`, `XId`, `YId`), KEY ix_kw(`XId`, `YId`, `Dtm`) ) ENGINE=InnoDB MAX_ROWS=10000000000 ROW_FORMAT=FIXED DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
-- Import data LOAD DATA INFILE '/home/jgaa/data/x.txt' INTO TABLE TestTable FIELDS TERMINATED BY '\t' enclosed by '"' LINES TERMINATED by '\r\n' IGNORE 1 Lines (Id, XId, Date, Dtm, Ct);
From my.cnf:innodb_data_home_dir=/data001/innodb innodb_data_file_path=innodata001:128G:autoextend set-variable = innodb_buffer_pool_size=512M set-variable = inno
----------------- Sent from my NYPL BlackBerry Handheld.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
