From: Machiel Richards - Gmail
Date: December 6 2011 6:40am
Subject: Loading of large amounts of data
List-Archive: http://lists.mysql.com/mysql/226452
Message-Id: <4EDDB8E0.8020001@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------010009040501000204030808"

--------------010009040501000204030808
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Good day all

     I have someone who has asked me the following, however due to not 
having that many years of experience in these type of volumes, I am 
posting this as I know that someone will probably be able to answer it 
better than me.

     (This will also give me a learning opportunity to see what to do)

_*Client Question: *_

Well let me describe the issue.

1.I require to load records into a MySQL database table - no problem so 
far ;-)

2.The table represents "stock" that will be being searched and 
transacted (i.e. sold, which involves changing flags on the record) by a 
live system.

3.The stock table will be big --millions or tens of millions of rows

4.Stock is uniquely identified by two fields -- a supplier ID (numeric) 
and a serial number (varchar)

5.Transaction volumes may also be very high

6.Stock must be available to the system 24/7

7.I will need to replenish the stock table from a file, one or more 
times a day -- potentially loading tens or hundreds of thousands of rows 
each time

8.The DB will be a master-slave: reporting and recon files off the 
slave, transactions off the master (and presumably replenishment into 
master)

I can go into a lot more detail about the process I am using (using an 
ETL tool called Talend) ... but the essential question is around 
strategies for doing this kind of dynamic loading:

1.How to insert data (high volumes) into the live table without locking 
it and affecting transaction performance (Insert low_priority?)

2.How to speed up inserts, even when there are two unique key 
constraints. My observation is obvious -- that inserts get slower and 
slower as the table grows (date based partitions of some kind maybe?).

3.General principles/ strategies in dealing with situations like this.



Can someone please assist.

--------------010009040501000204030808--