InnoDB insertion faster

Question

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.

Answer 1

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.

Answer 2

There's an entire document dedicated to bulk loading data into InnoDB. The main points:

1. disable autocommit to avoid an extra log flush for each insert statement: SET autocommit=0;...sql import;COMMIT;

2. disable foreign and unique checks (you can't disable all indexes completely):

   SET unique_checks=0;
   SET foreign_key_checks=0;
   

3. Potentially set innodb_autoinc_lock_mode to 2, instead of 1 (the default). Here is the documentation on this setting.

The third may or may not help you, so I suggest reading that link to see how you are initially loading the data. For instance, if you are breaking the loads into multiple inserts to run concurrently, it will definitely help you to set the value to 2. If you are doing one large multi-line insert, it won't do much (if anything) to help.

Since you are turning of the binary log for this initial insert, you shouldn't care about the gaps in autoincrement numbers (if doing concurrent inserts).

Answer 3

You can use the following methods to speed up inserts:

• If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster.
• When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements. See
• Take advantage of the fact that columns have default values. Insert values explicitly only when the value to be inserted differs from the default. This reduces the parsing that MySQL must do and improves the insert speed.
• See Section 9.5.5, “Bulk Data Loading for InnoDB Tables” for tips specific to InnoDB tables.

Answer 4

Plan A: "Batch" INSERTs -- multiple rows per INSERT statement. Suggest about 1000 rows per statement. autocommit=on, no explicit BEGIN...COMMIT

Plan B: LOAD DATA

If you insert too many rows at once, InnoDB must do more work to be able to rollback the insert if there is a crash. For this reason, I disagree with autocommit=off, which would put the entire set into a single transaction.

LOAD DATA of the entire set of rows may would have the same problem, but it is quite fast.

buffer_pool = 5G out of 6G is on the verge of being too big. If there is any swapping, performance will plummet.

PARTITIONing would probably make it go slower.

SHOW CREATE TABLE -- Secondary keys could be a serious handicap.

Are you using InnoDB? or XtraDB?