InnoDB import performance

Question

I'm struggling with bulk importing a quite big InnoDB-Table consisting of roughly 10 Million rows (or 7GB) (which for me is the biggest table I've worked with so far).

I did some research how to improve Inno's import speed and for the moment my setup looks like this:

/etc/mysql/my.cnf/
[...]
innodb_buffer_pool_size = 7446915072 # ~90% of memory
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000
innodb_thread_concurrency=0
innodb_doublewrite = 0
innodb_log_file_size = 1G
log-bin = ""
innodb_autoinc_lock_mode = 2
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_instances=8


import is done via bash script, here is the mysql code:
SET GLOBAL sync_binlog = 1;
SET sql_log_bin = 0;
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
SET SESSION tx_isolation='READ-UNCOMMITTED';
LOAD DATA LOCAL INFILE '$filepath' INTO TABLE monster
COMMIT;

Data is provided in a CSV file.
Currently I test my settings with smaller 'test dumps' with 2 million, 3 million, … rows each and use time import_script.sh to compare performance.

Drawback is I only get a overall running time so I've to wait for the full import to finish to get a result.

My results so far:

• 10 000 rows: <1 second
• 100 000 rows: 10 seconds
• 300 000 rows: 40 seconds
• 2 million rows: 18 minutes
• 3 million rows: 26 minutes
• 4 million rows: (cancelled after 2 hours)

It seems there is no 'cookbook' solution and one has to figure out the optimal mix of settings on their own.
Besides suggestions about what to change in my set up I also would really appreciate more information how I could better benchmark the importing process/gain more insight what is happening and where the bottleneck might be.
I tried to read up the documentation for the settings I'm changing but then again I'm not aware of any side-effects and if I might even decrease performance with a badly chosen value.

For the moment I would like to try a suggestion from chat to use MyISAM during import and change table engine afterwards.
I'd like to try this but for the moment my DROP TABLE query also takes hours to finish. (Which seems another indicator my setting is less then optimal).

Additional information:
The machine I'm currently using has 8GB of RAM and a Solid State Hybrid hard drive w/ 5400RPM.
While we also aim to remove obsolete data from the table in question I still need a somewhat fast import to
a) test automatic data cleanup feature while developing and
b) in case our server crashes we'd like to use our 2nd server as a replacement (which needs up-to-date data, last import took more than 24 hours)

mysql> SHOW CREATE TABLE monster\G
*************************** 1. row ***************************
       Table: monster
Create Table: CREATE TABLE `monster` (
  `monster_id` int(11) NOT NULL AUTO_INCREMENT,
  `ext_monster_id` int(11) NOT NULL DEFAULT '0',
  `some_id` int(11) NOT NULL DEFAULT '0',
  `email` varchar(250) NOT NULL,
  `name` varchar(100) NOT NULL,
  `address` varchar(100) NOT NULL,
  `postcode` varchar(20) NOT NULL,
  `city` varchar(100) NOT NULL,
  `country` int(11) NOT NULL DEFAULT '0',
  `address_hash` varchar(250) NOT NULL,
  `lon` float(10,6) NOT NULL,
  `lat` float(10,6) NOT NULL,
  `ip_address` varchar(40) NOT NULL,
  `cookie` int(11) NOT NULL DEFAULT '0',
  `party_id` int(11) NOT NULL,
  `status` int(11) NOT NULL DEFAULT '2',
  `creation_date` datetime NOT NULL,
  `someflag` tinyint(1) NOT NULL DEFAULT '0',
  `someflag2` tinyint(4) NOT NULL,
  `upload_id` int(11) NOT NULL DEFAULT '0',
  `news1` tinyint(4) NOT NULL DEFAULT '0',
  `news2` tinyint(4) NOT NULL,
  `someother_id` int(11) NOT NULL DEFAULT '0',
  `note` varchar(2500) NOT NULL,
  `referer` text NOT NULL,
  `subscription` int(11) DEFAULT '0',
  `hash` varchar(32) DEFAULT NULL,
  `thumbs1` int(11) NOT NULL DEFAULT '0',
  `thumbs2` int(11) NOT NULL DEFAULT '0',
  `thumbs3` int(11) NOT NULL DEFAULT '0',
  `neighbours` tinyint(4) NOT NULL DEFAULT '0',
  `relevance` int(11) NOT NULL,
  PRIMARY KEY (`monster_id`),
  KEY `party_id` (`party_id`),
  KEY `creation_date` (`creation_date`),
  KEY `email` (`email`(4)),
  KEY `hash` (`hash`(8)),
  KEY `address_hash` (`address_hash`(8)),
  KEY `thumbs3` (`thumbs3`),
  KEY `ext_monster_id` (`ext_monster_id`),
  KEY `status` (`status`),
  KEY `note` (`note`(4)),
  KEY `postcode` (`postcode`),
  KEY `some_id` (`some_id`),
  KEY `cookie` (`cookie`),
  KEY `party_id_2` (`party_id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=13763891 DEFAULT CHARSET=utf8

Answer 1

First, you need to know what you are doing to InnoDB when you plow millions of rows into an InnoDB table. Let's take a look at the InnoDB Architecture.

In the upper left corner, there is an illustration of the InnoDB Buffer Pool. Notice there is a section of it dedicated to the insert buffer. What does that do ? It is ised to migrate changes to secondary indexes from the Buffer Pool to the Insert Buffer inside the system tablespace (a.k.a. ibdata1). By default, innodb_change_buffer_max_size is set to 25. This means that up to 25% of the Buffer Pool can be used for processing secondary indexes.

In your case, you have 6.935 GB for the InnoDB Buffer Pool. A maximum of 1.734 GB will be used for processing your secondary indexes.

Now, look at your table. You have 13 secondary indexes. Each row you process must generate a secondary index entry, couple it with the primary key of the row, and send them as a pair from the Insert Buffer in the Buffer Pool into the Insert Buffer in ibdata1. That happens 13 times with each row. Multiply this by 10 million and you can almost feel a bottleneck coming.

Don't forget that importing 10 million rows in a single transaction will pile up everything into one rollback segment and fill up the UNDO space in ibdata1.

SUGGESTIONS

SUGGESTION #1

My first suggestion for importing this rather large table would be

• Drop all the non-unique indexes
• Import the data
• Create all the non-unique indexes

SUGGESTION #2

Get rid of duplicate indexes. In your case, you have

KEY `party_id` (`party_id`),
KEY `party_id_2` (`party_id`,`status`)

Both indexes start with party_id, you can increase secondary index processing by at least 7.6 % getting rid one index out of 13. You need to eventually run

ALTER TABLE monster DROP INDEX party_id;

SUGGESTION #3

Get rid of indexes you do not use. Look over your application code and see if your queries use all the indexes. You may want to look into pt-index-usage to let it suggest what indexes are not being used.

SUGGESTION #4

You should increase the innodb_log_buffer_size to 64M since the default is 8M. A bigger log buffer may increase InnoDB write I/O performance.

EPILOGUE

Putting the first two suggestions in place, do the following:

• Drop the 13 non-unique indexes
• Import the data
• Create all the non-unique indexes except the party_id index

Perhaps the following may help

CREATE TABLE monster_new LIKE monster;
ALTER TABLE monster_new
  DROP INDEX `party_id`,
  DROP INDEX `creation_date`,
  DROP INDEX `email`,
  DROP INDEX `hash`,
  DROP INDEX `address_hash`,
  DROP INDEX `thumbs3`,
  DROP INDEX `ext_monster_id`,
  DROP INDEX `status`,
  DROP INDEX `note`,
  DROP INDEX `postcode`,
  DROP INDEX `some_id`,
  DROP INDEX `cookie`,
  DROP INDEX `party_id_2`;
ALTER TABLE monster RENAME monster_old;
ALTER TABLE monster_new RENAME monster;

Import the data into monster. Then, run this

ALTER TABLE monster
  ADD INDEX `creation_date`,
  ADD INDEX `email` (`email`(4)),
  ADD INDEX `hash` (`hash`(8)),
  ADD INDEX `address_hash` (`address_hash`(8)),
  ADD INDEX `thumbs3` (`thumbs3`),
  ADD INDEX `ext_monster_id` (`ext_monster_id`),
  ADD INDEX `status` (`status`),
  ADD INDEX `note` (`note`(4)),
  ADD INDEX `postcode` (`postcode`),
  ADD INDEX `some_id` (`some_id`),
  ADD INDEX `cookie` (`cookie`),
  ADD INDEX `party_id_2` (`party_id`,`status`);

GIVE IT A TRY !!!

ALTERNATIVE

You could create a table called monster_csv as a MyISAM table with no indexes and do this:

CREATE TABLE monster_csv ENGINE=MyISAM AS SELECT * FROM monster WHERE 1=2;
ALTER TABLE monster RENAME monster_old;
CREATE TABLE monster LIKE monster_old;
ALTER TABLE monster DROP INDEX `party_id`;

Import your data into monster_csv. Then, use mysqldump to create another import

mysqldump -t -uroot -p mydb monster_csv | sed 's/monster_csv/monster/g' > data.sql

The mysqldump file data.sql will extended INSERT commands importing 10,000-20,000 rows at a time.

Now, just load the mysqldump

mysql -uroot -p mydb < data.sql

Finally, get rid of the MyISAM table

DROP TABLE monster_csv;

Answer 2

I wanted to write a comment (as this is not a definitive answer), but it became too long:

I am going to give you several broad pieces of advice, and we can go into details for each one, if you want:

• Reduce durability (you have already done some of it). Latest versions allow even doing it more. You can go as far as disabling the double write buffer,as corruption is not a problem for imports.
• Increase buffering by: Increase the transaction log size and augment the available buffer pool size. Monitor transaction log file usage and checkpoints. Do not fear huge logs for an import.
• Avoid huge transactions- your rollback will become full of unneeded data. This is probably your biggest problem.
• SQL will be a bottleneck, avoid the SQL overhead (handlersocket, memcached) and/or load it in concurrency with several threads at the same time. Concurrency has to reach a sweet spot, not too much, not too little.
• Load data in primary key order fragmentation can be an isse
• Test InnoDB compression if IO is your bottleneck and CPU and memory doesn't make it slower
• Try creating your secondary keys afterwards (faster in some cases), do not load indexed data- DISABLE KEYS does not affect InnoDB. If not, monitor your insert buffer (maybe overtaking half of your buffer pool).
• Change or disable checksum algorithm- not probably your issue, but it becomes a bottleneck on high end flash cards.
• Last resort: Monitor your server to find your current bottleneck and try to mitigate (InnoDB is very flexible about that).

Remember that some of these are not secure or advisable for non-imports (normal operation).

Answer 3

Most of the good tips has been given so far, but without lots of explanations for the best ones. I will give more details.

First, delaying index creation is a good one, with enough details in other responses. I will not come back on it.

A larger InnoDB log file will help you a lot (if you are using MySQL 5.6 as it is not possible to increase it in MySQL 5.5). You are inserting 7 GB of data, I would recommend a total log size of at least 8 GB (keep innodb_log_files_in_group at its default (2) and bump innodb_log_file_size at 4 GB). This 8 GB is not exact: it should be at at least the import size in the REDO log and probably double or quadruple that size. The reasoning behind InnoDB log size increase it that when the log will become almost full, InnoDB will start to flush aggressively its buffer pool to disk to avoid the log of filling up (when the log is full, InnoDB cannot do any database write until some pages of the buffer pool are written to disk).

A larger InnoDB log file will help you, but you should also insert in primary key order (sort your file before inserting). If you insert in primary key order, InnoDB will fill one page, and then another one, and so on. If you do not insert in primary key order, your next insert might end up in a page that is full and will incur a "page split". This page split will be expensive for InnoDB and will slow down your import.

You already have a buffer pool as large as your RAM allows you and if your table does not fit in it, there is not much you can do except buying more RAM. But it you table fits in the buffer pool but is larger that 75% of your buffer pool, you might try increasing innodb_max_dirty_pages_pct to 85 or 95 during the import (the default value is 75). This configuration parameter tells InnoDB to start aggressively flushing the buffer pool when the percentage of dirty pages reaches this limit. By bumping up this parameter (and if you are lucky on the data size), you might avoid aggressive IO during your import and delay those IO to later.

Maybe (and this is a guess) importing your data in many small transactions will help you. I do not know exactly how the REDO log is built, but if it is buffered in RAM (and disk when too much RAM would be needed) while the transaction is making progress, you might end up with unnecessary IOs. You could try this: once your file is sorted, split it in many chunks (try with 16 MB and other sizes) and import them one by one. This would also allow you to control the progress of your import. If you do not want your data to be partially visible to other reader while you do your import, you could import using a different table name, create the indexes later, and then rename the table.

About your hybrid SSD/5400RPM disk, I do not know about those and how to optimize this. 5400RPM looks slow for a database, but maybe the SSD is avoiding that. Of maybe you are filling the SSD part of your disk with sequential writes to the REDO log and the SSD is hurting performances. I do not know.

A bad tips that you should not try (or be careful with) is the following: do no use multi-thread: it will be very hard to optimize to avoid page splits in InnoDB. If you want to use multi-thread, insert in different tables (or in different partitions of the same table).

If you are considering multi-thread, maybe you have a multi-socket (NUMA) computer. In this case, make sure you avoid the The MySQL swap insanity problem.

If you are using MySQL 5.5, upgrade to MySQL 5.6: it has the option of increasing the REDO log size and has better buffer pool flushing algorithms.

Good luck with your import.