But both these tables are located on the same RAID right?
Kh3ops wrote on Sat, 19 March 2011 18:29
How did you measure it?
The fact that you are going to get more writes when using InnoDB instead of MyISAM is that InnoDB needs to fulfill the Durability part of the ACID transaction requirements, which requires more writes to disk.
And especially since you have the default setting for innodb_flush_log_at_trx_commit (which is the 1 as it says in your commented row in your config file).
The setting 1 means that if you don’t explicitly use transactions then each insert will be a separate transaction and hence it needs to flush the transaction log for each write.
Usually this is not a problem performance wise with a RAID controller with cache, but if your SELECT saturates the RAID controller/disk setup then these extra writes would lock up the inserts since it will not continue until they have been performed.
A common tradeoff to get better performance is to set it to:
innodb_flush_log_at_trx_commit = 2
that limits the writes to disk to about 1 per second, but the drawback is that you can also loose up to one second of transactions in the event of power failure/OS crash.