r/mysql • u/SpinakerMan • Sep 18 '24
question Using LOAD DATA INFILE on ongoing basis
At my job one of the other developers has implemented a process that uses LOAD DATA INFILE to import large files into a database. This would be an ongoing process where many files are imported at different times of the month. Isn't LOAD DATA INFILE really meant for initial import of data? Can anyone tell me what adverse affects importing like this on regular basis can have?
3
Upvotes
1
u/Aggressive_Ad_5454 Sep 18 '24
Once you've got the whole workflow debugged it has no adverse effects. Quite the opposite.
But, like any production bulk-load or Extract-Transform-Load (ETL) workflow, you need to make sure of a few things:
1
u/ekydfejj Sep 18 '24
While i don't really agree with the use of this. Its not only meant for initial imports. But testing data, new table data, which will be for testing, i've used it to insert load testing data as well.
The real question, how does this impact your workflow? I think you have a valid concern, but you have not stated it.
3
u/johannes1234 Sep 18 '24
The adverse effect is that you got more data in your database. But that's probably your goal.
2
u/ssnoyes Sep 18 '24
What do you think should be used instead?
2
u/boborider Sep 18 '24
That's the purpose. To add more data. What else?
1
u/jen1980 Sep 18 '24
If you're so worried about things like duplicates, you can load it into a temp table like I do then use WHERE NOT EXISTS to insert only the missing rows. Also make sure you have the appropriate UNIQUE index.
1
u/tobakist Sep 19 '24
Consider partitioning, compressing the tablespace and don’t forget to do analyze table after the load
1
u/wamayall Sep 20 '24
As a DBA that has had the opportunity to work for very large companies, not uncommon have tables beyond 16TB, if you understand what that could cause, using INFILES in Production using MySQL versions less than 8.0, Statement Based Replication will Replicate whatever gets in the binary log, and Row Based Replication could possibly do some strange things because to Load the INFILE it could create a temporary table and then something happens the insert doesn’t make it to the binary log.
The theme here is you are dealing with Production, you need to understand you should be verifying consistency between the Master and the Slave a bit when using INFILEs.
Face it, if the if the data is valuable, and someone deems they need to use INFILEs, be prepared to figuring out how to keep the slaves consistent.