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 = innodb_additional_mem_pool_size=120M set-variable = innodb_log_file_size=1G set-variable = innodb_log_buffer_size=16M innodb_flush_log_at_trx_commit=0 innodb_flush_method = O_DSYNC Jarle -- Jarle Aase email: [EMAIL PROTECTED] Author of freeware. http://www.jgaa.com news:alt.comp.jgaa War FTP Daemon: http://www.warftp.org War FTP Daemon FAQ: http://www.warftp.org/faq/warfaq.htm Jgaa's PGP key: http://war.jgaa.com/pgp NB: If you reply to this message, please include all relevant information from the conversation in your reply. Thanks. <<< no need to argue - just kill'em all! >>> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
