How to import data into InnoDB tables as fast as MyISAM

Question

I am new to MySQL and I know very little about it.

The problem I am facing is as mentioned below:

Previously I had data which had MyISAM as its MySQL engine and it used to take around 15 minutes to get imported on any machine. However, when I change the engine to InnoDB it's taking almost 90 minutes to get imported on any machine. Kindly suggest me for the same.

Answer 1

Innodb tables are faster for concurrent inserts. See Innodb Performance Optimization Basics

For best performance, you need to tune INNODB engine in my.cnf file.

Assuming, if you have 4GB RAM then try configuring my.cnf file as:

#Innodb
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 512M
innodb_log_file_size = 1G
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 30
innodb_file_per_table=1
innodb_file_format=barracuda
innodb_strict_mode=1
innodb_autoinc_lock_mode = 0

Then restart the MySQL server.

Answer 2

@Omesh has the some great tips & I think were part of the reason my latest imports of 4.5GB & 5.0GB SQL of 36 million rows were successful. I wanted to get one spot where some other additional tips helped.

1. Follow @Omesh MySQL configuration suggestions.

2. Next, look at the actual SQL file you're attempting to import. The tool we used to dump the data added these lines right before the INSERT statement:

   /*!40000 ALTER TABLE `table_name` DISABLE KEYS */;

   What I did was uncomment that line. It disables the checking of the primary keys during insert, allowing it to go faster. If you do not have that line, then you can manually add it:

   ALTER TABLE `table_name` DISABLE KEYS;

   Use caution if this is a live database actively being used.

3. Immediately after the INSERT statement(s), the tool also added this line. Remove the comments as well to re-enable the primary keys:

   /*!40000 ALTER TABLE `table_name` ENABLE KEYS */;

   Again, it should look like this:

   ALTER TABLE `table_name` ENABLE KEYS;

4. Before actually running the insert statement, turn off autocommit, unique_checks, and foreign_key_checks with a query:

   set autocommit=0; set unique_checks=0; set foreign_key_checks=0;

5. Now run your import:

   source /path/to/your/file.sql

6. Once the import has successfully finished, you'll want to commit then re-enable unique_checks and foreign_key_checks:

   commit; set unique_checks=1; set foreign_key_checks=1;

Some other considerations for doing imports that you may want to consider are:

• if you're using a virtual machine, up the available resources (RAM, CPU core count) for the VM until the import is complete

• if you're using a physical machine, maybe borrow some RAM from another machine long enough to complete the import

• if possible, use 2 different disks: 1 dedicated to read from (source of SQL file) and one dedicated for write (where the DB or VM lives)

Hope this helps someone else!