From: Harrison Fisk (harrisonmysql.com)
Date: Thu Mar 03 2005 - 12:39:21 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hi,

On Mar 3, 2005, at 11:32 AM, 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.
>
> That may work provided I can get the keys rebuilt later using FileSort
> and not KeyCache.
>
> You see the problem isn't in loading the data into the table which
> occurs reasonably fast that because running "Load Data Infile" on an
> empty table will disable the keys until all the data is loaded, so
> explicitly disabling before hand them won't help. After the data is
> loaded, Load Data Infile will then rebuild the keys and will choose to
> use "Repair with keycache" whereas "Repair with filesort" would be
> 100x-1000x faster. There doesn't seem to be any way to get it to use
> "Repair with filesort".
>
> So I could use disable keys as you had suggested and then rebuild them
> manually with myisamchk "repair with sort" provided it doesn't reload
> all the data into a temporary table (which Alter Table usually does).
> If it does create a temporary table it will physically reloads the 500
> million rows a second time and I will need another 100g of free disk
> space.
>
> So is there a solution to this paradox?

It chooses which method to use based on your myisam_sort_buffer_size,
myisam_max_sort_file_size and myisam_max_extra_sort_file_size. Have
you increased the size of these? Keep in mind these are SESSION
variables, so they can be set on the connection right before you LOAD
DATA INFILE.

You always have to tell MySQL how much extra memory/disk it can use, it
can't assume that you want it to use it. That is why it will use
Repair by keycache, since it doesn't use any extra resources. If you
increase the amount of diskspace or memory, then it should use a Repair
by sort instead.

Also keep in mind that Repair by sort doesn't work for UNIQUE or
PRIMARY KEYs.

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]