Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
Thank you for your suggestions. I'll try suggestion #2 right now, and I'll adjust innodb_read_io_threads, innodb_write_io_threads, and `innodb_io_capacity'. –  inohiro Dec 10 '12 at 5:10
add comment

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

share|improve this answer
    
Thank you for your answer! Bulk insert looks so fast, and I'll try it later. –  inohiro Dec 10 '12 at 5:05
add comment

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?

share|improve this answer
    
Thank you for your answer. I'm using InnoDB. I prefer Plan A to Plan B. LOAD DATA look like so fast, but we need to write data to text at once as CSV, then use LOAD DATA right? / I'll set the buffer_pool_size to 4GB. –  inohiro Dec 11 '12 at 6:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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