0

I'm bulk loading a few thousand CSVs totaling ~260GB and ~4.5 billion rows to MySQL using mysqlimport calls on each CSV executed within a bash script. The MySQL installation and the data itself are on an external drive as I didn't have sufficient disk space on my machine to accommodate. My first attempt seemed to stall after a certain juncture (~24h), so I created another table without the indexes to see if processing sped up some (figured it was slowing things down to have to reindex the table after loading each CSV).

I'm now about 36h in and a little over 50% of the way complete, but stumped as to how I could speed up processing (or if it's even possible). Is there a better way to go about loading this much data to a MySQL table.

Edit: Processing seems to be slowing as it goes. Roughly 50% of data loaded in the first 36h, only an additional ~10% in the 24h since.

Edit2: I've updated innodb_buffer_pool_size to 4x its default value (128MB --> 512MB) and loading appears to be humming along--a single file was taking 15min+ to load prior to tuning, individual files are loading within ~1min now. There's been a corresponding slight increase in RAM usage (~4.5G to ~5.6G).

Improve this question
14
  • 1
    increasing global innodb_buffer_pool_size will possibly help and can be done dynamically - don't exceed the amount of RAM available as OOM won't help you. innodb_flush_log_at_trx_commit=2 will remove some IO overhead at the sacrifice of durability. set back to 1 later.
    – danblack
    Commented Mar 8, 2020 at 22:06
  • 1
    You are trying to insert each CSV file in a single transaction, MySQL may choke on that. Cut the biggest files in reasonable chunks.
    – Gerard H. Pille
    Commented Mar 8, 2020 at 22:16
  • 1
    @Chris What is the result displayed for SELECT @@innodb_change_buffer_max_size? If it is not 50, SET GLOBAL innodb_change_buffer_max_size=50 to ingest as much as possible to reduce length of time required. Also, what is result of SELECT @innodb_flush_neighbors? If it is not 2, SET GLOBAL innodb_flush_neighbors=2 to deal with all rows in one extent being handled on 1 cycle. This should help reduce SHOW GLOBAL STATUS LIKE '%pages_dirty%' in the least amount of time.
    – Wilson Hauck
    Commented Mar 8, 2020 at 23:45
  • 1
    @Chris, innodb_flush_neighbors=2 would be best long-term. innodb_change_buffer_max=20 until you are loading significant data, then 50 for the duration + a couple hours to get the flushing done to your data tables. You will know when SHOW GLOBAL STATUS LIKE '%pages_dirty%'; result is less than 100.
    – Wilson Hauck
    Commented Mar 9, 2020 at 9:26
  • 1
    on a 16G machine, probably 10G of innodb buffer pool size would of been a good starting point.
    – danblack
    Commented Mar 9, 2020 at 23:37

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.