ITPub博客

首页 > 数据库 > MySQL > MYSQL 写性能测试

MYSQL 写性能测试

原创 MySQL 作者:xchui702 时间:2014-03-07 10:18:15 0 删除 编辑
 

以下是我在当前搭建的POC mysql cluster 环境里,进行并发写测试的数据。情况说明如下:

1.       模拟向10字符,10个数字字段的表中插入数据。

2.       案例1~3, 都是同时插入1000条数据,但是并发的会话数依次增加。

3.       案例4~5, 同时插入10000条数据,并发会话数相同,但是4是使用搭建的POC环境,5是使用我在笔记本上搭建的虚拟环境。

4.       测试涵盖了mysql中三种主要的存储引擎,myisam是不支持事务的引擎,innodb是单节点数据引擎,ndbclustercluster数据引擎。

5.       每个测试案例测试了3次,所以数据应该是比较稳定的。

得到的结论如下,也请大家一起分析。 

1.       在现有的POC环境配置下,随着并发数提高,innodb的每秒处理数随之增大,这个是合理的,随之并发再增大后,时间增大,或不明向减少,表明已经达到压力区了.

2.       Ndbcluster,随着并发数提高,每秒处理数有所增大,但是不大明显,如案例1和案例2的情况;但是并发数再增大后,每秒的处理数反而降低了, 如案例3和案例4的情况。这是不正常的,估计可能跟机器的配置尤其是网络有关系。

3.       Myisam,随着并发数提高,每秒处理数减少,这个是由于它对并发的支持比较弱,是正常的。

4.       相同的测试案例,案例4POC的机器上运行,innodb 的处理次数是100, 而案例5在笔记本的VM上运行,处理次数达到了644. 估计是跟POC的机器配置有关系。 (或者单机上的INNODB,和NDB cluster 集成在一起的INNODB性能不一样,准备向公司申请好的机器
再做POC,目前的机器是在是超线程的CPU, 一台机器物理上一core, 执行ls 命令都慢.)


--
性能测试结果

案例一: 并发2个会话,每秒钟完成处理次数:myisam=365 innodb=36 ndbcluster=72

[mysql@shadcdmsq02 ~]$ mysqlslap --concurrency=2 --iterations=1 \

>             --number-int-cols=10 --number-char-cols=10 -a \

>             --auto-generate-sql-add-autoincrement --number-of-queries=1000 \

>             --auto-generate-sql-load-type=write --engine=myisam,innodb,ndbcluster

Benchmark

        Running for engine myisam

        Average number of seconds to run all queries: 2.773 seconds

        Minimum number of seconds to run all queries: 2.773 seconds

        Maximum number of seconds to run all queries: 2.773 seconds

        Number of clients running queries: 2

        Average number of queries per client: 500

Benchmark

        Running for engine innodb

        Average number of seconds to run all queries: 27.631 seconds

        Minimum number of seconds to run all queries: 27.631 seconds

        Maximum number of seconds to run all queries: 27.631 seconds

        Number of clients running queries: 2

        Average number of queries per client: 500

Benchmark

        Running for engine ndbcluster

        Average number of seconds to run all queries: 13.807 seconds

        Minimum number of seconds to run all queries: 13.807 seconds

        Maximum number of seconds to run all queries: 13.807 seconds

        Number of clients running queries: 2

        Average number of queries per client: 500

案例二: 并发20个回话,每秒钟完成处理次数:myisam=305 innodb=99 ndbcluster=98

[mysql@shadcdmsq02 ~]$ mysqlslap --concurrency=20 --iterations=1 \

>              --number-int-cols=10 --number-char-cols=10 -a \

>              --auto-generate-sql-add-autoincrement --number-of-queries=1000 \

>              --auto-generate-sql-load-type=write --engine=myisam,innodb,ndbcluster

Benchmark

        Running for engine myisam

        Average number of seconds to run all queries: 3.274 seconds

        Minimum number of seconds to run all queries: 3.274 seconds

        Maximum number of seconds to run all queries: 3.274 seconds

        Number of clients running queries: 20

        Average number of queries per client: 50

Benchmark

        Running for engine innodb

        Average number of seconds to run all queries: 10.030 seconds

        Minimum number of seconds to run all queries: 10.030 seconds

        Maximum number of seconds to run all queries: 10.030 seconds

        Number of clients running queries: 20

        Average number of queries per client: 50

Benchmark

        Running for engine ndbcluster

        Average number of seconds to run all queries: 10.174 seconds

        Minimum number of seconds to run all queries: 10.174 seconds

        Maximum number of seconds to run all queries: 10.174 seconds

        Number of clients running queries: 20

        Average number of queries per client: 50

案例三. 并发100个会话,每秒钟完成处理次数:myisam=229 innodb=75 ndbcluster=76

[mysql@shadcdmsq02 ~]$ mysqlslap --concurrency=100 --iterations=1 \

>               --number-int-cols=10 --number-char-cols=10 -a \

>               --auto-generate-sql-add-autoincrement --number-of-queries=1000 \

>               --auto-generate-sql-load-type=write --engine=myisam,innodb,ndbcluster

Benchmark

        Running for engine myisam

        Average number of seconds to run all queries: 4.348 seconds

        Minimum number of seconds to run all queries: 4.348 seconds

        Maximum number of seconds to run all queries: 4.348 seconds

        Number of clients running queries: 100

        Average number of queries per client: 10

Benchmark

        Running for engine innodb

        Average number of seconds to run all queries: 13.303 seconds

        Minimum number of seconds to run all queries: 13.303 seconds

        Maximum number of seconds to run all queries: 13.303 seconds

        Number of clients running queries: 100

        Average number of queries per client: 10

Benchmark

        Running for engine ndbcluster

        Average number of seconds to run all queries: 13.101 seconds

        Minimum number of seconds to run all queries: 13.101 seconds

        Maximum number of seconds to run all queries: 13.101 seconds

        Number of clients running queries: 100

        Average number of queries per client: 10

--测试案例四: 并发148个会话,插入10000条数据,每秒钟完成处理次数:myisam=195 innodb=100 ndbcluster=49

[mysql@shadcdmsq02 ~]$ mysqlslap --concurrency=148 --iterations=1  \            

                 --number-int-cols=10 --number-char-cols=10 -a      \        

                 --auto-generate-sql-add-autoincrement --number-of-queries=10000  \            

                 --auto-generate-sql-load-type=write --engine=myisam,innodb,ndbcluster

Benchmark

        Running for engine myisam

        Average number of seconds to run all queries: 51.124 seconds

        Minimum number of seconds to run all queries: 51.124 seconds

        Maximum number of seconds to run all queries: 51.124 seconds

        Number of clients running queries: 148

        Average number of queries per client: 67

Benchmark

        Running for engine innodb

        Average number of seconds to run all queries: 99.555 seconds

        Minimum number of seconds to run all queries: 99.555 seconds

        Maximum number of seconds to run all queries: 99.555 seconds

        Number of clients running queries: 148

        Average number of queries per client: 67

Benchmark

        Running for engine ndbcluster

        Average number of seconds to run all queries: 201.856 seconds

        Minimum number of seconds to run all queries: 201.856 seconds

        Maximum number of seconds to run all queries: 201.856 seconds

        Number of clients running queries: 148

        Average number of queries per client: 67

--测试案例五: 一台笔记本上安装的虚拟机,内存512M,测试案例同案例四,每一秒执行次数:innodb=644

[mysql@vm1 ~]$ mysqlslap --concurrency=148 --iterations=1 \

            --number-int-cols=10 --number-char-cols=10 -a \

            --auto-generate-sql-add-autoincrement --number-of-queries=10000 \

            --auto-generate-sql-load-type=write --engine=innodb

Benchmark

        Running for engine innodb

        Average number of seconds to run all queries: 15.512 seconds

        Minimum number of seconds to run all queries: 15.512 seconds

        Maximum number of seconds to run all queries: 15.512 seconds

        Number of clients running queries: 148

        Average number of queries per client: 67

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/674865/viewspace-1102860/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-16

  • 博文量
    38
  • 访问量
    118189