Not the answer you need?
Register and ask your own question!

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

geekprophetgeekprophet ContributorInactive User Role Beginner
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

Comments

  • Brad.MickelBrad.Mickel Percona Inactive User Role Beginner
    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.
  • geekprophetgeekprophet Contributor Inactive User Role Beginner
    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
  • geekprophetgeekprophet Contributor Inactive User Role Beginner
    I should point out that the innodb params were NOT commented out when I ran the tests (and yes, the service had been restarted).
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.