List:General Discussion« Previous MessageNext Message »
From:Machiel Richards - Gmail Date:December 6 2011 6:40am
Subject:Loading of large amounts of data
View as plain text  
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 

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.

Loading of large amounts of dataMachiel Richards - Gmail6 Dec
  • Re: Loading of large amounts of dataMySQL)7 Dec
    • Re: Loading of large amounts of dataRafael Valenzuela7 Dec