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.

Improve this question
6
  • I suppose you meant MyISAM with those "2.2 Seconds". Otherwise - MyISAM does not support ACID (transactions) so it does a lot less work than InnoDB - but it has to lock the entire table for some operations where InnoDB can only locks specific rows - so the difference should be other way around on parallel concurent operations.
    – jkavalik
    Commented May 8, 2015 at 5:23
  • Yes.. sorry @user1786423
    – Roberto Chaves
    Commented May 8, 2015 at 5:25
  • Ah, I hit enter too fast and then edited previous comment instead of making another one - check the "exteded version" please :)
    – jkavalik
    Commented May 8, 2015 at 5:27
  • @user1786423 Alright, thanks, will try that later. But do you know if my current configuration is optimal?
    – Roberto Chaves
    Commented May 8, 2015 at 5:37
  • Well, it does not seem wrong to me, but I don't have enough experience to weight its optimality.
    – jkavalik
    Commented May 8, 2015 at 5:46

2 Answers 2

Reset to default
1

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

Improve this answer
1

600 inserts/sec is more than out-of-the-box MySQL can do. You have done some tuning; more is possible.

innodb_flush_log_at_trx_commit = 1 is the default, but it incurs a write after every transaction. =2 can be significantly faster.

Batching inserts, if feasible for your application, can speed up INSERTs 10-fold.

How many secondary indexes are there? Each must be updated, sooner or later, whether MyISAM or InnoDB.

Are your INSERTs single-threaded?

If you want even higher speed ingestion, look at staging-table techniques.

Improve this answer
5
  • Im not sure what you mean by single-threaded. The queries are set up individually, so I'm running 3000 different queries, not a single query with 3000 rows getting inserted. And time of execution includes the time that the code takes to run as well, which I purposely made long so that I could not only compare the speed the two storage engines run, but also my 2 different services (have 2 servers with different companies). I assume about 1-1.5 seconds of execution time went to running the code (have random strings being generated, bunch of math, ect.)
    – Roberto Chaves
    Commented May 9, 2015 at 2:14
  • Just tested inserting 30,000 rows. In a single query. (The code loops 30,000 times, so current result is the code run for 7.7 seconds, while the query itself took 0.04 seconds). Gonna try what you recommended and see if it changes anything in terms of a single query. (Im more worried about multiple queries being run at once since that's what is most likely to happen)
    – Roberto Chaves
    Commented May 9, 2015 at 2:18
  • Spoke too soon, turns out I have not set the max packet size, and it's at a default of 16M, so it didn't work. Increased size to 256M... not sure if recommended. Query took: 0.956 seconds. Also tried using an MyISAM table, took 0.6 seconds. Using what you recommended (of increasing number to 2) my query took 0.75 seconds.
    – Roberto Chaves
    Commented May 9, 2015 at 2:30
  • Are those timings "good enough"? max_packet_size should not affect timings unless you have huge rows? Is the Query cache turned off? Are you always running with a "cold cache"? Or "warm cache"? innodb_* settings have zero impact on MyISAM tables; if you are implying that "2" made a difference in the MyISAM test, I claim something else changed (perhaps the "coldness" of the cache). Benchmarking is tricky.
    – Rick James
    Commented May 9, 2015 at 19:10
  • No, it was just for comparison (Since I changed my code around to have a single query with multiple row inserts). I did not test MyISAM after changing the value, since I know it wouldn't make a difference. @Rick James
    – Roberto Chaves
    Commented May 9, 2015 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.