List:General Discussion« Previous MessageNext Message »
From:mos Date:March 7 2005 5:12pm
Subject:Re: "Load Data Infile ... Repair with KeyCache" --WAY SLOW!
View as plain text  
At 05:18 PM 3/6/2005, Harrison Fisk wrote:
>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.

There was a "[Warning] Warning: Enabling keys got errno 136, retrying" and 
it kept running for almost 2 more days. (Yes, I guess I should have checked 
earlier.)

So apparently it ran out of index space because I didn't build the table 
with Max_Rows=1000000000 (1 billion?). I'm using NTFS so there is no 4gb 
limit. The data file is about 84g. There should have been plenty of disk 
space available for the index file.

I think what bothers me the most about all this is I'm kept in the dark 
about the progress of rebuilding the index (or table). Wouldn't it be nice 
if the "Show Process List" could say "10% complete" or "99% complete"? If 
it sat at 5% complete for a few hours (days?), then I would know something 
was wrong. But if the progress steadily increased every few hours and when 
it got to 99% complete I would know it is almost complete. I could let my 
client know it is 99% complete rather than saying "I don't know" every few 
hours. I would like to see this "% complete" as a feature so people aren't 
kept in the dark. At least then they would know whether the process is hung 
or not.

Just my 2 cents.

Mike


>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 <mos99@stripped> 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
>>>  / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
>>>/_/  /_/\_, /___/\___\_\___/   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/mysql?unsub=1
>>
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:
>>http://lists.mysql.com/mysql?unsub=1
>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

Thread
"Load Data Infile ... Repair with KeyCache" --WAY SLOW!mos2 Mar
  • Re: "Load Data Infile ... Repair with KeyCache" --WAY SLOW!Gleb Paharenko3 Mar
    • Re: "Load Data Infile ... Repair with KeyCache" --WAY SLOW!mos3 Mar
      • Re: "Load Data Infile ... Repair with KeyCache" --WAY SLOW!Harrison Fisk3 Mar
        • Re: "Load Data Infile ... Repair with KeyCache" --WAY SLOW!mos3 Mar
          • Re: "Load Data Infile ... Repair with KeyCache" --WAY SLOW!Harrison Fisk3 Mar
    • Re: "Load Data Infile ... Repair with KeyCache" --WAY SLOW!mos6 Mar
      • Re: "Load Data Infile ... Repair with KeyCache" --WAY SLOW!Harrison Fisk7 Mar
        • Re: "Load Data Infile ... Repair with KeyCache" --WAY SLOW!mos7 Mar