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
# 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
========================================================================================================
# 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
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 "LOCK TABLES WRITE" 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: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000(154.54 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (2936.22 per sec.)
other operations: 20000(309.08 per sec.)
Test execution summary:
total time: 64.7090s
total number of events: 10000
total time taken by event execution: 6436.0518
per-request statistics:
min: 9.72ms
avg: 643.61ms
max: 738.83ms
approx.95 percentile: 665.96ms
Threads fairness:
events (avg/stddev): 100.0000/0.00
execution time (avg/stddev): 64.3605/0.19
======================================================================
在大并发情况下，innodb的性能体现出来了。
页:
[1]