RE: Major Difference in response times when using Load Infile utility
> Test 1 > Amount of data - 5.5 million rows. Time Taken - 6+hrs Approximately. > > Test2 > Amount of data - 0.45 million rows. Time Taken - 2 mins approximately. Is this an InnoDB database by any chance? If it is, and it is a clean import, then disable the FOREIGN_KEY_CHECKS. SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0; This is a small tip i picked up on the MySQL documentation that someone had left in the comments and has been to date one of those tips that has literally saved DAYS of my life. a ps Remember to put them back on again after you finish the import SET AUTOCOMMIT = 1; SET FOREIGN_KEY_CHECKS=1; -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Major Difference in response times when using Load Infile utility
Indexing can play a big role in the load time differential. When loading .45 million rows, MySQL may be able to handle the sorting for indexes in memory. With 5.5 million, it's more likely to have to go to a temp file. You then could end up thrashing you disk, causing big slow downs. If you think about it, you'll be reading the import file from disk, MySQL will be writing to disk as it loads the database, and then the indexing will be reading and writing to disk for sorting. That's a lot of activity. Disable or remove your indexes when doing a huge import, then enable or add them when you're done. On Sep 13, 2005, at 7:24 AM, Sujay Koduri wrote: hi , I am using the Load Infile utility to load data from file to MySQL DB. When trying to load different amounts of data, I observed a notable difference in the time taken by that. Test 1 Amount of data - 5.5 million rows. Time Taken - 6+hrs Approximately. Test2 Amount of data - 0.45 million rows. Time Taken - 2 mins approximately. Can some one explain why this difference is coming. Also it will be great if someone can suggest how we can improve the performance of the first test. Thank you sujay -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Major Difference in response times when using Load Infile utility
Are there indexes on the table? Could be that. --Original Message-- From: Sujay Koduri To: mysql Sent: Sep 13, 2005 5:24 AM Subject: Major Difference in response times when using Load Infile utility hi , I am using the Load Infile utility to load data from file to MySQL DB. When trying to load different amounts of data, I observed a notable difference in the time taken by that. Test 1 Amount of data - 5.5 million rows. Time Taken - 6+hrs Approximately. Test2 Amount of data - 0.45 million rows. Time Taken - 2 mins approximately. Can some one explain why this difference is coming. Also it will be great if someone can suggest how we can improve the performance of the first test. Thank you sujay - 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]
Major Difference in response times when using Load Infile utility
hi , I am using the Load Infile utility to load data from file to MySQL DB. When trying to load different amounts of data, I observed a notable difference in the time taken by that. Test 1 Amount of data - 5.5 million rows. Time Taken - 6+hrs Approximately. Test2 Amount of data - 0.45 million rows. Time Taken - 2 mins approximately. Can some one explain why this difference is coming. Also it will be great if someone can suggest how we can improve the performance of the first test. Thank you sujay
