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?