I performed a "read" benchmark on MariaDB-10.0.14 db server using sysbench to compare performance between InnoDB and MyISAM on a single. Below are the sysbench commands i used:

Create test table in subtest database with 1,000,000 records

InnoDB engine:

#sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=testdb --mysql-password=testpw --mysql-socket=/var/lib/mysql/mysql.sock prepare

MyISAM engine:

#sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-db=sbread --mysql-user=testdb --mysql-password=testpw --mysql-socket=/var/lib/mysql/mysql.sock prepare

Perform Read Test:

InnoDB engine:

sysbench --num-threads=1 --max-requests=500000 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-read-only --mysql-db=sbtest --mysql-user=testdb --mysql-password=testpw run

MyISAM engine:

sysbench --num-threads=1 --max-requests=500000 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-read-only --mysql-db=sbread --mysql-user=testdb --mysql-password=testpw run

Test Results:

InnoDB engine:
Thread                   Total Time                         Transactions
1                        3823.6033s                         500000 (130.77 per sec.)

MyISAM engine:
Thread                   Total Time                          Transactions
1                        2148.4370s                          500000 (232.73 per sec.)

I have two questions:

  1. As seen from the results, the total time for MyISAM seems less, which is what i was expecting. But i do not understand why the "transaction per second" of MyISAM performs worse than that of InnoDB. Can somebody explain that to me why this is?

  2. I tried to increase the --num-threads to 2 (to perform multiple concurrency) but with MyISAM, i kept getting an error "failed: 1062 Duplicate entry for primary key". I understand that MyISAM does not support multiple threads, would this be the reason? Insight on this too would be appreciated.

  • The TPS score for MyISAM (232 "transactions" per second) is better than that for InnoDB (130). – Mat Oct 25 '14 at 17:45
  • Thanks for catching that Mat. Then my main question still remains, why does the test on MyISAM using multiple threads does not seem to work? – The Georgia Oct 25 '14 at 18:37
  • isn't oltp.lua a read & write test ? you should use /tests/db/select.lua for select read only test ? – p4guru Oct 26 '14 at 3:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.