myisam和innodb讀寫性能對比

網上有很多評論myisam和innodb讀寫性能對比。

讀myisam要比innodb要快，為啥快？

我的論點是：myisam直接從磁盤裡拿資料，而innodb要分兩步，innodb要從記憶體裡首先擷取資料，如果沒有再到磁盤裡拿。而且一開始innodb要有個加熱的過程，也就是說，記憶體裡的資料不是一下子就緩存，而是一點一點的緩存那些熱資料。如果你的記憶體小，資料庫裡的資料量要大于buffer_pool_size設定的值，并發較低，性能就下降。

這也就解釋了在這個場景裡，讀myisam要比innodb要快。

innodb玩的是記憶體，記憶體越大，它的優勢才能發揮出來。

myisam玩的是硬碟IO，轉速越快，它的優勢才能發揮出來。

資料庫做RAID10較合适。

大并發測試

innodb_buffer_pool_size=11G

sync_binlog=0

innodb_flush_log_at_trx_commit = 0

mysql 5.1.43 + innodb_plugin 1.0.6

[root@test ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --max-requests=10000 --num-threads=100 --mysql-host=192.168.1.11    --mysql-port=3306 --mysql-user=admin --mysql-password=admin123 --mysql-db=test --mysql-socket=/tmp/mysql.sock run

sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql

Running the test with following options:

Number of threads: 100

Doing OLTP test.

Running mixed OLTP 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

Maximum number of requests for OLTP test is limited to 10000

Threads started!

Done.

OLTP test statistics:

    queries performed:

        read:                            140014

        write:                           50005

        other:                           20002

        total:                           210021

    transactions:                        10001  (1549.15 per sec.)

    deadlocks:                           0      (0.00 per sec.)

    read/write requests:                 190019 (29433.80 per sec.)

    other operations:                    20002  (3098.29 per sec.)

Test execution summary:

    total time:                          6.4558s

    total number of events:              10001

    total time taken by event execution: 643.9687

    per-request statistics:

         min:                                  9.24ms

         avg:                                 64.39ms

         max:                                450.00ms

         approx.  95 percentile:             150.97ms

Threads fairness:

    events (avg/stddev):           100.0100/6.69

    execution time (avg/stddev):   6.4397/0.01

========================================================================================================

[root@test ~]# sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --max-requests=10000 --num-threads=100 --mysql-host=192.168.1.11    --mysql-port=3306 --mysql-user=admin --mysql-password=admin123 --mysql-db=test --mysql-socket=/tmp/mysql.sock run

Using "LOCK TABLES WRITE" for starting transactions

        read:                            140000

        write:                           50000

        other:                           20000

        total:                           210000

    transactions:                        10000  (154.54 per sec.)

    read/write requests:                 190000 (2936.22 per sec.)

    other operations:                    20000  (309.08 per sec.)

    total time:                          64.7090s

    total number of events:              10000

    total time taken by event execution: 6436.0518

         min:                                  9.72ms

         avg:                                643.61ms

         max:                                738.83ms

         approx.  95 percentile:             665.96ms

    events (avg/stddev):           100.0000/0.00

    execution time (avg/stddev):   64.3605/0.19

======================================================================

在大并發情況下，innodb的性能展現出來了。