Trying to avoid bulk insert table locking

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Trying to avoid bulk insert table locking

On Sat, Feb 6, 2010 at 12:11 PM, D. Dante Lorenso wrote=
:
> I have a system that imports about 40 million records every 2 days into a
> single table in MySQL. =A0I was having problems with LOAD DATA CONCURRENT
> LOCAL INFILE where the table I was importing into would lock until the
> import was complete. =A0Locks would prevent SELECTs also.

This should not happen with InnoDB tables. Writers should not block
readers. Were you using InnoDB?

- Perrin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg