I read that InnoDB is supposed to be better at writing into MySQL than MyISAM, however, I'm trying it out, by running 3000 MySQL queries(Inserting rows, 8 Columns + id)… but results haven't turned out how I'd hoped.
Using InnoDB the execution time is around 5.3 Seconds while for MyISAM it takes around 2.2 Seconds.
My current mysql configuration is:
innodb_io_capacity = 8000
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_log_buffer_size = 32M
innodb_log_file_size = 564M
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 6
innodb_thread_concurrency = 0
Everything else is at its default value.
My server is currently running: Ubuntu 14.04 x64
Has 12GB RAM
6 Cores
And using an SSD (service provider says it provides 10,000 IOPS)
What would be the best configuration for InnoDB if I were to only use InnoDB, and what would be the best configuration for just MyISAM (like would it for some reason be faster if I were to disable InnoDB while not using it.)?
Right now just thinking of using my server for storing stuff, and will need to write more data, than reading it… while later on, thinking of reading more than writing.
Not sure if it matters, but right now running nginx (do not have apache on the server), php 5.6 and MySQL 5.6.19.
Best Answer
MyISAM is faster than innoDB in insertion and that is because it relies on OS to write data to disk while innoDB insures final disk write (fsync()). moreover, innodb MVCC feature reduces the write speed. If the little delay in write is not a problem, stick to innoDB. you don't want to face lots of table crash or long table locks while your table face read and write simultaneously. this page has a lot on optimizing disk I/O of innoDB