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
toREAD-COMMITTED
orREAD-UNCOMMITTED
[actuallyREAD-COMMITED
] - set
innodb_flush_log_at_trx_commit
to0
or2
[actually0
] - 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.
asked Dec 6, 2012 at 7:13
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.
answered Dec 6, 2012 at 16:06
RolandoMySQLDBARolandoMySQLDBA
175k31 gold badges303 silver badges496 bronze badges
2
There's an entire document dedicated to bulk loading data into InnoDB. The main points:
- disable autocommit to avoid an extra log flush for each insert statement:
SET autocommit=0;...sql import;COMMIT;
disable foreign and unique checks [you can't disable all indexes completely]:
SET unique_checks=0; SET foreign_key_checks=0;
- 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].
answered Dec 6, 2012 at 14:26
Derek DowneyDerek Downey
23k11 gold badges76 silver badges104 bronze badges
2
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 multipleVALUES
lists to insert several rows at a time. This is considerably faster [many times faster in some cases] than using separate single-rowINSERT
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 usingINSERT
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.
answered Dec 9, 2016 at 7:55
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?
answered Dec 11, 2012 at 0:15
Rick JamesRick James
70.5k4 gold badges40 silver badges97 bronze badges
1