0

As the title suggests I'm moving around a lot of data. This is in an environment with a 24 core CPU and 2TB of M.2 storage.

My first question is: given I have 16GB of ram, does that mean it's impossible for me to load a 30GB file into SQL (as the entire thing must be stored in ram?)

The second question: This is a fresh table I'm loading into, so I have the luxury of dictating which engine I can use. I've read that InnoDB is better for writing (in which I'm doing alot of). However, MySQL manual says that the CONCURRENCY clause will work on MyISAM. So which engine should I use?

Any other helpful advice is appreciated.

My mysqld conf:

innodb_buffer_pool_size = 12G
innodb_log_file_size    = 4G
innodb_write_io_threads = 18
Improve this question
2
  • Are all the files being loaded into the same table? What index and FC constraints are on the table? What MariaDB version are you using? Are you programiticly loading this in chunks? Have you increased you innodb_log_file{_size,s_in_group}?
    – danblack
    Dec 4, 2019 at 2:19
  • It is all in the same table. There is no indices, FKs, nor triggers on the table. v10.3.18. In the config I set the innodb_log_file_sile to be 4G... I'll add that to the question.
    – Dellowar
    Dec 4, 2019 at 3:41

2 Answers 2

Reset to default
1

MyISAM is not really in the running anymore. Go with InnoDB.

No, the file/table never needs to be entirely in RAM at once.

Are you using LOAD DATA INFILE? That is probably the best way to do the loading.

Will the 50 be put into a single table? That could be 50 LOADs in a row, or 50 in parallel, with some contention. Or somewhere in between. Since the task is mostly I/O, loading more than a few at a time will not speed things up.

It is common to load data into a temp file, then massage the data to fix errors, normalize, etc. Then copy the cleansed data over to the 'real' table. This intermediate file could be MyISAM or Aria, but there is probably no strong argument for or against the table choice. The 'real' table should be InnoDB; period.

If you use such a staging table, it should probably not have any indexes. All the operations would [probably] be on the entire table, so indexing would be an unused burden.

If this is a Master, there could be replication issues. (Not covered here.)

Improve this answer
1

There are no restrictions on importing/loading a 30GB file to MySQL (Unless your proc/OS are 32 bit which I doubt). The only additional variable I'll suggest to increase is max_allowed_packet=1G... Later you can return it back to something about 128M (after import)

About the second question: As DBA you will select the storage engine depending on the use of the data after imported, not depending exclusively on the import step. So following standards and current support on transactional procedures, backups, reliability and so on, I suggest you to use Innodb, but again this will depend on the future use of your data.

Since 30 GB actually is not that big, concurrency won't make a huge difference here when importing data.

Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.