Mysql – Ram & Concurrency: MySQL InnoDB LOAD INLINE FILE on 50 30GB CSV files

csvmariadbMySQL

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

Best Answer

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.)

RECOMMENDATION #1

Let's presume you have a fair amount of disk space on your cloud server. You create the InnoDB table in stages to watch the effect of diskspace and RAM in slow motion.

Let's say your MyISAM table is called mydb.mytable and you want to convert it to InnoDB.

Try the following:

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ENGINE=InnoDB;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Play with the table.

If you like its performance, then

DROP TABLE mydb.mytable_old;

If you do not like its performance, then

TRUNCATE TABLE mydb.mytable_old;
INSERT INTO mydb.mytable_old SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_zap;
ALTER TABLE mydb.mytable_old RENAME mydb.mytable;
DROP TABLE mydb.mytable_zap;

RECOMMENDATION #2

You mentioned you are running MySQL 5.1. Unless you install the InnoDB Plugin, InnoDB is single threaded. To get InnoDB to be multithreaded:

Install MySQL 5.6
Install MySQL 5.1's InnoDB Plugin if you want to keep MySQL 5.1 (Applicable to MySQL 5.1.38+)

Once you do either one, please configure InnoDB to engage multiple cores. See my past posts:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance

Mysql – Can putting thesql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance

I have a client at my employer's company with 192GB RAM using 162GB Buffer Pool.

Here is what we did for the client

InnoDB Tuning : I set the following options
- innodb_read_io_threads (maxed it to 64)
- innodb_write_io_threads (maxed it to 64)
- innodb_thread_concurrency = 0 (It's default now in MySQL 5.5/5.6)
- innodb_io_capacity (set it to IOPs of Disk, for this client, 20000 [2-0-0-0-0 not typo])
- innodb_buffer_pool_size=162G
- innodb_buffer_pool_instances=2 (Number of Physical Cores)
OS Tuning (numactl --interleave=all)

If you forget numactl --interleave=all, the buffer pool experiences swapping.

Here are my past posts on tuning InnoDB

Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?
Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
Jul 21, 2012 : InnoDB - High disk write I/O on ibdata1 file and ib_logfile0
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?

Best Answer

Related Solutions

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

RECOMMENDATION #1

RECOMMENDATION #2

Mysql – Can putting thesql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance

Related Question