List:General Discussion« Previous MessageNext Message »
From:D. Dante Lorenso Date:February 6 2010 5:11pm
Subject:Trying to avoid bulk insert table locking
View as plain text  
I have a system that imports about 40 million records every 2 days into 
a single table in MySQL.  I was having problems with LOAD DATA 
CONCURRENT LOCAL INFILE where the table I was importing into would lock 
until the import was complete.  Locks would prevent SELECTs also.

I converted the table to MyISAM and removed the AUTO_INCREMENT key and 
that seemed to help a little bit, but apparently not enough because I 
still get locks for my larger file imports (maybe I just don't see the 
locks for the smaller imports).

So, I think I want to test a new strategy:

1) import records into a temporary table

2) have a "merge" stored procedure loop through a cursor and migrate 
batches of records from the temp table to the permanent table in groups 
of perhaps 500-10,000 records.

3) make sure any acquired locks are released between each batch merged.

Has anyone built logic like this already?  Care to share your results 
and findings?  Would this approach work, and is it fairly simple to do?

-- Dante
Trying to avoid bulk insert table lockingD. Dante Lorenso6 Feb
  • Re: Trying to avoid bulk insert table lockingPerrin Harkins6 Feb