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.