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.
