注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

事务隔离级不同,myisam和innodb引擎测试  

2017-04-26 15:52:25|  分类: MySQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
以下测试结论:
查询性能 30个并发myisam明显弱于innodb。

DML性能:
 set globa tx_isolation='REPEATABLE-READ'时,myisam显著好于innodb。
 set global tx_isolation='READ-COMMITTED';后,myisam稍弱于innodb。




以下dr_site是myisam表,dr_site_innodb是innodb表,两表表结构及数据量一致。
查询性能:
[root@reserve ~]# /usr/local/mysql/bin/mysqlslap -uroot -p123456YTREWQASDFGHabc --query="select count(*) FROM dr_site WHERE status=1" --number-of-queries=1000 -c 30  -i 10 --create-schema=test
Benchmark
        Average number of seconds to run all queries: 1.321 seconds
        Minimum number of seconds to run all queries: 1.305 seconds
        Maximum number of seconds to run all queries: 1.344 seconds
        Number of clients running queries: 30
        Average number of queries per client: 33

[root@reserve ~]# /usr/local/mysql/bin/mysqlslap -uroot -p123456YTREWQASDFGHabc --query="select count(*) FROM dr_site_innodb WHERE status=1" --number-of-queries=1000 -c 30  -i 10 --create-schema=test
Benchmark
        Average number of seconds to run all queries: 0.827 seconds
        Minimum number of seconds to run all queries: 0.814 seconds
        Maximum number of seconds to run all queries: 0.851 seconds
        Number of clients running queries: 30
        Average number of queries per client: 33

查询性能myisam明显弱于innodb。

==========================================
update 性能:

mysql> set global tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

[root@reserve ~]# /usr/local/mysql/bin/mysqlslap -uroot -p123456YTREWQASDFGHabc --query="UPDATE dr_site SET dayip=dayip+1 where id=floor(13858+rand()*7267)" --number-of-queries=100 -c 30  -i 10 --create-schema=test
Benchmark
        Average number of seconds to run all queries: 1.913 seconds
        Minimum number of seconds to run all queries: 1.887 seconds
        Maximum number of seconds to run all queries: 1.931 seconds
        Number of clients running queries: 30
        Average number of queries per client: 3

[root@reserve ~]# /usr/local/mysql/bin/mysqlslap -uroot -p123456YTREWQASDFGHabc --query="UPDATE dr_site_innodb SET dayip=dayip+1 where id=floor(13858+rand()*7267)" --number-of-queries=100 -c 30  -i 10 --create-schema=test
Benchmark
        Average number of seconds to run all queries: 11.379 seconds
        Minimum number of seconds to run all queries: 8.698 seconds
        Maximum number of seconds to run all queries: 15.000 seconds
        Number of clients running queries: 30
        Average number of queries per client: 3

[root@reserve ~]# /usr/local/mysql/bin/mysqlslap -uroot -p123456YTREWQASDFGHabc --query="delete from dr_site where id=floor(13858+rand()*7267)" --number-of-queries=100 -c 30  -i 10 --create-schema=test
Benchmark
        Average number of seconds to run all queries: 1.078 seconds
        Minimum number of seconds to run all queries: 1.027 seconds
        Maximum number of seconds to run all queries: 1.123 seconds
        Number of clients running queries: 30
        Average number of queries per client: 3

[root@reserve ~]# /usr/local/mysql/bin/mysqlslap -uroot -p123456YTREWQASDFGHabc --query="delete from dr_site_innodb where id=floor(13858+rand()*7267)" --number-of-queries=100 -c 30  -i 10 --create-schema=test
Benchmark
        Average number of seconds to run all queries: 11.888 seconds
        Minimum number of seconds to run all queries: 10.020 seconds
        Maximum number of seconds to run all queries: 13.635 seconds
        Number of clients running queries: 30
        Average number of queries per client: 3

 set globa tx_isolation='REPEATABLE-READ'时,myisam显著好于innodb。


mysql> set global tx_isolation='READ-COMMITTED';

[root@reserve ~]# /usr/local/mysql/bin/mysqlslap -uroot -p123456YTREWQASDFGHabc --query="UPDATE dr_site SET dayip=dayip+1 where id=floor(13858+rand()*7267)" --number-of-queries=100 -c 30  -i 10 --create-schema=test
Benchmark
        Average number of seconds to run all queries: 1.925 seconds
        Minimum number of seconds to run all queries: 1.910 seconds
        Maximum number of seconds to run all queries: 1.948 seconds
        Number of clients running queries: 30
        Average number of queries per client: 3

[root@reserve ~]# /usr/local/mysql/bin/mysqlslap -uroot -p123456YTREWQASDFGHabc --query="UPDATE dr_site_innodb SET dayip=dayip+1 where id=floor(13858+rand()*7267)" --number-of-queries=100 -c 30  -i 10 --create-schema=test
Benchmark
        Average number of seconds to run all queries: 1.692 seconds
        Minimum number of seconds to run all queries: 1.474 seconds
        Maximum number of seconds to run all queries: 2.015 seconds
        Number of clients running queries: 30
        Average number of queries per client: 3

 set global tx_isolation='READ-COMMITTED';后,myisam稍弱于innodb。








sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=root --mysql-password=123456  --mysql-socket=/var/lib/mysql/mysql.sock --threads=10 --mysql-db=test run

sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test \
 --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp_tables_count=10 --oltp-table-size=100000 --rand-init=on prepare



sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \
    --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 \
    --mysql-db=test --tables=10 --table-size=10000 \
    --report-interval=10 \
    --threads=10 --time=20 \
    prepare






Creating table 'sbtest1' Unknown database driver
  评论这张
 
阅读(90)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017