0

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.

Roberto Chaves
  • 37
  • 2
  • 1
    This might better be asked on **dba.stackexchange.com**. With InnoDB, there's the overhead of transactions. Are those 3000 individual INSERT statements combined together into a transaction, or are those individual transactions? – spencer7593 May 08 '15 at 04:20
  • This might help you: http://stackoverflow.com/questions/20148/myisam-versus-innodb – Stuart Wagner May 08 '15 at 04:25
  • If your query takes 2 seconds to execute, blame the query not storage engine. Thus, the configuration question is irrelevent – akuzminsky May 08 '15 at 18:29
  • @akuzminsky I have no problem with the query, like I said, I was just testing speed. And code + query run at that time. Currently just trying to get the best configuration possible – Roberto Chaves May 09 '15 at 02:09

1 Answers1

0

MyISAM is not crash safe, or transactional.. InnoDB is and is ensuring the data is synced to disk (MyISAM does not) before finishing the statement (or COMMIT).

Try bulking your writes together into a single transaction (BEGIN; INSERT, INSERT; COMMIT), or, running multiple inserts in parallel rather than sequentially (or both at the same time).. these things will improve the performance significantly.

I would also read through the link suggested by Stuart Wagner.

Trent Lloyd
  • 1,832
  • 1
  • 15
  • 13