InnoDB insertion faster I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think can allow more loose settings for insertion speed. At the moment, I'm using the following techniques.


disable log_bin
enable skip-innodb-doublewrite
set transaction_isolation to READ-COMMITTED or READ-UNCOMMITTED (actually READ-COMMITED)
set innodb_flush_log_at_trx_commit to 0 or 2 (actually 0)
set innodb_buffer_pool_size to 5GB (system has 6GB RAM)


Are there any more techniques for faster insertion on InnoDB?
And do I have to modify innodb_io_read_thread and innodb_io_write_thread ?
If you need more information, please tell me.
SUGGESTION #1

If your machine has multiple cores, you need to increase the following:

[mysqld]
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000


What are these?


innodb_read_io_threads - The number of I/O threads for read operations in InnoDB.
innodb_write_io_threads - The number of I/O threads for write operations in InnoDB.
innodb_io_capacity - An upper limit on the I/O activity performed by the InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the insert buffer. 


SUGGESTION #2

In order for separate data and indexes from the system tablespace (ibdata1), you need to do perform a complete restructuring of InnoDB. Sounds complicated, but is very straightforward. I wrote about this in the DBA StackExchange (Aug 29, 2012) and in StackOverflow (Oct 29, 2010). The basic steps are


Run SET GLOBAL innodb_fast_shutdown = 0;
mysqldump all data to a SQL dump
service mysql stop
Delete the following files


ibdata1
ib_logfile0
ib_logfile1

service mysql start


Before you run service mysql start, add this line to my.cnf

innodb_open_files=32768


That way, there will be file handles dedicated to the each individual table. The default is 300. File handles have been known to get cached. There will be a slowdown if you set this  very high and hit the ceiling quickly. This should not be the case if you are working a small number of tables.