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.

StackExchange Database Administrators Q#29913, answer score: 12