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