[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Bulk INSERT performance question
From:       "Ananda Kumar" <anandkl () gmail ! com>
Date:       2008-07-26 8:24:02
Message-ID: 829b199c0807260112kd9ef50bh83b69bed71f0c608 () mail ! gmail ! com
[Download RAW message or body]


You can also set the bulk_insert_buffer_size=<between 500MB TO 1GB>, based
on available RAM.

This would improve bulk insert on myisam tables.

regards
anandkl


On 7/26/08, mos <mos99@fastmail.fm> wrote:
>
> At 06:46 PM 7/25/2008, you wrote:
>
>> List,
>>
>> I am bulk inserting a huge amount of data into a MyISAM table (a
>> wikipedia page dump).
>> Before I issued SOURCE filename.sql; I did an ALTER TABLE page DISABLE
>> KEYS; LOCK TABLES page WRITE;
>> The dump consists of about 1,200 bulk INSERT statements with roughly
>> 12,000 tuples each.
>> For the first hour or so it performed pretty good, taking about 5
>> seconds for each INSERT and using all the CPU it could get.
>> But a while later time consumption increased up to 10 minutes.
>> In the same time, mysqld is barely using any CPU (less than 2%), there
>> is no significant I/O going on, and there is still unused memory.
>> Thoughts?
>>
>> Thanks a lot
>>
>> --
>> Tobi
>>
>
> Tobi,
>     It will be much faster if you use a Load Data Infile on an empty table.
> Your method is likely slowing down because the table has keys that needs to
> be maintained. You could remove all of the keys, unique or otherwise, and
> build them after the data has been loaded. (Disable Keys will not disable
> unique keys or the primary key).
>
> Mike
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>


[prev in list] [next in list] [prev in thread] [next in thread]