Hi,
On Mar 6, 2005, at 12:51 PM, mos wrote:
At 10:07 PM 3/2/2005, you wrote:Hello.
You may use ALTER TABLE .. DISABLE KEYS to speed up the loading process
on the MyISAM table.
I gave that a try but I had to cancel "Alter Table ... Enable Keys" after 49 hours. I find it amazing that it takes only 6.25 hours to load 450 million rows into the table using Load Data Infile with the keys disabled, but over 49 hours (maybe a lot more) to rebuild 3 indexes.
Why do you find that amazing? The import is pretty much a raw file copy by MySQL. That 6 hours is just copying data from one place to another. When you create indexes MySQL has to go through and sort the data. While doing so it has to create a special structure and maintain it and write it to disk. Creating an index is *much* more effort than a simple file copy.
Even with 100g of disk space dedicated to the sort file. It started using "Repair with Filesort" for the first 7 hours, then it switched to "Repair with KeyCache".
It should only use one of the Repair methods. If it switched then something was wrong with Repair by filesort (maybe out of disk space?) Look in your error log and see if there is any message there about it. What did you have your myisam_* variables set to?
I don't know anyone who can afford to take their database down for a few days (weeks?) while building an index.
How often do you need to load in 450 million rows? Generally this only occurs once, or in a batch process, such as once per month. I don't know what your expectations are, but this is never going to be a very fast process under any DBMS. Importing 450M rows will take some decent amount of time.
Mike
mos <[EMAIL PROTECTED]> wrote:
> I have a 50g CSV file that I am trying to import into an empty MyISAM
> table. It appears to go fine except after 10 hours it hasn't completed. A
> "Show Process List" shows Info="load data infile ..." and State="Repair
> with keycache". The table has a few hundred million rows of data. I assume
> it is using "Repair with keycache" to rebuild the indexes after the data
> has been loaded.
>
> From what I've read "Repair with keycache" is going to take a huge amount
> of time to complete. How do I disable "Repair with keycache" and use
> "Repair with sort" instead (which should be faster, right?)? I'm using
> MySQL 4.1.10 on Win XP and 1gb ram.
>
> TIA
>
> Mike
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Regards,
Harrison
-- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com
Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
