InnoDB vs. MyISAM insert query time

Question

I have a large MySQL table (~10 Million Rows, 6.5G) Which i use for read & write. It is MyISAM, and i get a lot of locks due to MyISAM's all table lock on writes.

I decided to try and move to InnoDB which is recommended for read/write tables, and it locks only specific rows on writes.

After converting, I tested insert statements, and it turns out it takes ~15 times more (from 0.1 sec to 1.5 sec) in the InnoDB table than in the MyISAM table. Why is that?

I haven't configured anything for InnoDB yet, and plan to add partitions also, but this figure is still unexpected for me. Of course the tables are the same, same indexes etc.

Additional info according to requests:

2 indexes. primary is data_id of type Big INT, and non unique user_id of type varchar(255).

Inserts are of ~150 rows together that have the same user_id.

Size of indexes: 200 MB in MyISAM, 400MB in InnoDB

Answer 1

A related answer suggests that setting the innodb_flush_log_at_trx_commit variable to 2 is likely to improve performance when the ratio of writes to reads is relatively high. See the documentation for more.

Answer 2

I think, InnoDB implements a true ACID, and does a lot of fsync()s to save the data. And MyISAM is not a true ACID and does less fsync()s.

There are recomendations to kill fsync when you need to load huge data in

If you want to load data into InnoDB quickly:
* use as large an InnoDB buffer cache as possible
* make the InnoDB log files as large as possible
* minimize the number of unique indexes on your tables
* disable all calls to fsync from InnoDB. You have to hack the code to
get this, or look at the Google patch. Of course, you only want to run
in this mode when loading the table.

And lists says:

MyISAM always runs in the 'nosync' mode, that is, it never calls fsync() to flush the files to disk.

InnoDB's nosync is useful in testing if some OS/computer is extremely slow in fsync(). But it should not be used in a production system.

The same message says, that InnoDB sometimes uses another sync method:

Then InnoDB uses fsync() to flush both the data and log files. If O_DSYNC is specified, InnoDB uses O_SYNC to open and flush the log files, but uses fsync() to flush the data files. If O_DIRECT is specified (available on some Linux versions starting from MySQL-4.0.14), InnoDB uses O_DIRECT to open the data files, and uses fsync() to flush both the data and log files. Note that InnoDB does not use fdatasync() or O_DSYNC because there have been problems with them on many Unix flavors.