sysbench shows MyISAM way faster than InnoDB -- am I doing something wrong?

MySQL & MariaDB Other MySQL® Questions
1

I have two different databases running under the same MySQL instance on the same physical server (12-core, 64GB, SSD drives). When I run the sysbench oltp test, I get about 4700 tps against MyISAM but only about 2100 against InnoDB across multiple tests. I have tried tuning InnoDB performance by setting the innodb_buffer_pool_size to 8GB (the whole database is only 250 MB) and the innodb_log_file_size to 1GB, with very minimal change in the results.

The sysbech tests I am running are…[/B]

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test_myisam --db-driver=mysql --mysql-table-engine=myisam prepare

sysbench --test=oltp --oltp-table-size=1000000 --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 --db-driver=mysql --mysql-db=test_myisam run

sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 8

Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using “LOCK TABLES READ” for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting…
(last message repeated 7 times)
Done.

OLTP test statistics:
queries performed:
read: 3751510
write: 0
other: 535930
total: 4287440
transactions: 267965 (4465.96 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 3751510 (62523.51 per sec.)
other operations: 535930 (8931.93 per sec.)

Test execution summary:
total time: 60.0016s
total number of events: 267965
total time taken by event execution: 479.0983
per-request statistics:
min: 1.44ms
avg: 1.79ms
max: 12.51ms
approx. 95 percentile: 2.05ms

Threads fairness:
events (avg/stddev): 33495.6250/226.10
execution time (avg/stddev): 59.8873/0.00

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test_innodb --db-driver=mysql --mysql-table-engine=innodb prepare

sysbench --test=oltp --oltp-table-size=1000000 --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 --db-driver=mysql --mysql-db=test_innodb run

sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 8

Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using “BEGIN” for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting…
(last message repeated 7 times)
Done.

OLTP test statistics:
queries performed:
read: 1782676
write: 0
other: 254668
total: 2037344
transactions: 127334 (2122.15 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1782676 (29710.12 per sec.)
other operations: 254668 (4244.30 per sec.)

Test execution summary:
total time: 60.0023s
total number of events: 127334
total time taken by event execution: 479.3998
per-request statistics:
min: 1.96ms
avg: 3.76ms
max: 851.53ms
approx. 95 percentile: 4.28ms

Threads fairness:
events (avg/stddev): 15916.7500/209.42
execution time (avg/stddev): 59.9250/0.00

2

There are several things that could affect the TPS of your system. Could you please provide the version of MySQL you are using as well as the settings for your database? I’d like to try to recreate your instance to see if i can duplicate your results.

3

Thanks so much for your interest!

I am using Percona Server 5.6 with XtraDB.

Here is the my.cnf file…

[mysqld]
port=5557
basedir=/ha01_mysql/site557/mysql
datadir=/ha01_mysql/site557/mysql/data
socket=/ha01_mysql/site557/mysql/mysqld.sock
tmpdir=/ha01_mysql/site557/mysql/tmp
lc-messages-dir=/ha01_mysql/site557/mysql/share/mysql/english
lc-messages=en_US
user=site557
symbolic-links=0
open_files_limit=10000
lower_case_table_names=1
skip-name-resolve
default_storage_engine=innodb

#–tokudb params
tokudb_cache_size=1GB
tokudb_directio=0

#–innodb params
#innodb_buffer_pool_size=8GB
#innodb_log_file_size=1G
#innodb_flush_log_at_trx_commit=0

[mysqld_safe]
timezone=America/Los_Angeles
pid-file=/ha01_mysql/site557/mysql/mysqld.pid
log-error=/ha01_mysql/site557/mysql/error.log
thp-setting=never

4

I should point out that the innodb params were NOT commented out when I ran the tests (and yes, the service had been restarted).