mysql读写性能测试

http://my.oschina.net/costaxu/blog/108568

近来工作中用mysql较多，但其实自己对mysql性能并不十分了解。前阵子正好读到一篇写用mysqlslap压测mysql性能的文章，于是自己也尝试了一下mysqlslap并且得到一些数据和结论。把压测到的数据与大家分享，如有谬误之处请指正。若需要详细了解本文使用的压力测试工具mysqlslap请移步下面的链接：  

用mysqlslap进行mysql压力测试

mysqlslap官方文档   

概述和测试环境

压测的目的是为了尽量模拟真实情况。测试的表都是由10个int型字段和10个字符串型字段组成。每个测试项目都测试了myisam和innodb两个引擎。测试的方法都是用两个线程并发，一共跑10000个请求。

压测的机器用的是自己的笔记本。CPU是Intel(R) Core(TM)2 Duo CPU     P8400， 4G内存， SATA硬盘。

mysql服务器端和压测的客户端跑在同一台机器上。mysql的版本是5.5.

用到的mysqlslap参数解释如下：

--concurrency=2 两个线程

--iterations=1 执行一次

--number-int-cols=10 10个int型字段
--number-char-cols=10 10个字符串字段
-a 等同于--auto-generate-sql

--auto-generate-sql-add-autoincrement 创建auto increment的主键
--auto-generate-sql-secondary-indexes=2 创建2列索引

--auto-generate-sql-guid-primary 创建guid作为主键

--number-of-queries=10000 所有并发线程的请求次数和
--auto-generate-sql-load-type=write/key/read/update/mixed 测试的query类型
--engine=myisam,innodb 存储引擎

写性能测试

写性能测试1，不开binlog, guid做主键, 无索引

测试命令：

?

1

2

3

4

5

6

7

8

9

mysqlslap   --concurrency=2\

            --iterations=1\

            --number-int-cols=10\

            --number-char-cols=10\

            -a\

            --auto-generate-sql-guid-primary\

            --number-of-queries=10000\

            --auto-generate-sql-load-type=write\

            --engine=myisam,innodb\

结果：

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

Benchmark

        Runningforengine myisam

        Average number of seconds to run all queries: 6.110 seconds

        Minimum number of seconds to run all queries: 6.110 seconds

        Maximum number of seconds to run all queries: 6.110 seconds

        Number of clients running queries: 2

        Average number of queries per client: 5000

 

Benchmark

        Runningforengine innodb

        Average number of seconds to run all queries: 10.291 seconds

        Minimum number of seconds to run all queries: 10.291 seconds

        Maximum number of seconds to run all queries: 10.291 seconds

        Number of clients running queries: 2

        Average number of queries per client: 5000

用myisam每秒处理1639次。

用innodb每秒处理971次。

真实环境中一般会用binlog做数据备份与同步，所以性能会有所下降，有binlog的情况请继续看后面的实验。

写性能测试2， 开binlog, guid做主键， 无索引

测试命令与“写性能测试1”相同，

结果如下：

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

Benchmark

        Runningforengine myisam

        Average number of seconds to run all queries: 14.809 seconds

        Minimum number of seconds to run all queries: 14.809 seconds

        Maximum number of seconds to run all queries: 14.809 seconds

        Number of clients running queries: 2

        Average number of queries per client: 5000

 

Benchmark

        Runningforengine innodb

        Average number of seconds to run all queries: 26.721 seconds

        Minimum number of seconds to run all queries: 26.721 seconds

        Maximum number of seconds to run all queries: 26.721 seconds

        Number of clients running queries: 2

        Average number of queries per client: 5000

myisam每秒处理675次

innodb每秒处理374次

写性能测试3， 开binlog, guid做主键， 有索引

现实情况中，有索引的情况较多。采用 --auto-generate-sql-secondary-indexes=2 创建两列索引。

命令如下:

?

1

2

3

4

5

6

7

8

9

10

mysqlslap   --concurrency=2\

            --iterations=1\

            --number-int-cols=10\

            --number-char-cols=10\

            -a\

            --auto-generate-sql-guid-primary\

            --auto-generate-sql-secondary-indexes=2\

            --number-of-queries=10000\

            --auto-generate-sql-load-type=write\

            --engine=myisam,innodb\

结果如下：

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

Benchmark

        Runningforengine myisam

        Average number of seconds to run all queries: 16.693 seconds

        Minimum number of seconds to run all queries: 16.693 seconds

        Maximum number of seconds to run all queries: 16.693 seconds

        Number of clients running queries: 2

        Average number of queries per client: 5000

 

Benchmark

        Runningforengine innodb

        Average number of seconds to run all queries: 30.418 seconds

        Minimum number of seconds to run all queries: 30.418 seconds

        Maximum number of seconds to run all queries: 30.418 seconds

        Number of clients running queries: 2

        Average number of queries per client: 5000

myisam每秒处理599次。

innodb每秒处理329次。

写性能测试4，开binglog，auto increment主键，有索引

命令如下：

?

1

2

3

4

5

6

7

8

9

10

mysqlslap   --concurrency=2\

            --iterations=1\

            --number-int-cols=10\

            --number-char-cols=10\

            -a\

            --auto-generate-sql-add-autoincrement\

            --auto-generate-sql-secondary-indexes=2\

            --number-of-queries=10000\

            --auto-generate-sql-load-type=write\

            --engine=myisam,innodb\

结果如下：

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

Benchmark

        Runningforengine myisam

        Average number of seconds to run all queries: 16.785 seconds

        Minimum number of seconds to run all queries: 16.785 seconds

        Maximum number of seconds to run all queries: 16.785 seconds

        Number of clients running queries: 2

        Average number of queries per client: 5000

 

Benchmark

        Runningforengine innodb

        Average number of seconds to run all queries: 28.809 seconds

        Minimum number of seconds to run all queries: 28.809 seconds

        Maximum number of seconds to run all queries: 28.809 seconds

        Number of clients running queries: 2

        Average number of queries per client: 5000

myisam每秒处理595次。

innodb每秒处理347次。

读性能测试

在真实的情况中，我们最主要的读操作其实就是用主键去查找表中的一行。 我主要是对这种行为进行测试。所以采用的load-type = key ,而不是read。 read是对全表进行读取，可是实际上这种情况很少出现。

读性能测试1，guid主键

命令如下：

?

1

2

3

4

5

6

7

8

9

10

mysqlslap   --concurrency=2\

            --iterations=1\

            --number-int-cols=10\

            --number-char-cols=10\

            -a\

            --auto-generate-sql-guid-primary\

            --auto-generate-sql-unique-query-number=10000\

            --auto-generate-sql-load-type=key\

            --number-of-queries=10000\

            --engine=myisam,innodb\

结果如下：

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

Benchmark

        Runningforengine myisam

        Average number of seconds to run all queries: 4.215 seconds

        Minimum number of seconds to run all queries: 4.215 seconds

        Maximum number of seconds to run all queries: 4.215 seconds

        Number of clients running queries: 2

        Average number of queries per client: 5000

 

Benchmark

        Runningforengine innodb

        Average number of seconds to run all queries: 3.917 seconds

        Minimum number of seconds to run all queries: 3.917 seconds

        Maximum number of seconds to run all queries: 3.917 seconds

        Number of clients running queries: 2

        Average number of queries per client: 5000

myisam每秒处理2372次

innodb每秒处理2553次

读性能测试2，auto increment主键

命令如下

?

1

2

3

4

5

6

7

8

9

10

mysqlslap   --concurrency=1\

            --iterations=1\

            --number-int-cols=10\

            --number-char-cols=10\

            -a\

            --auto-generate-sql-add-autoincrement\

            --auto-generate-sql-unique-query-number=10000\

            --auto-generate-sql-load-type=key\

            --number-of-queries=10000\

            --engine=myisam,innodb\

结果如下：

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

Benchmark

        Runningforengine myisam

        Average number of seconds to run all queries: 4.555 seconds

        Minimum number of seconds to run all queries: 4.555 seconds

        Maximum number of seconds to run all queries: 4.555 seconds

        Number of clients running queries: 1

        Average number of queries per client: 10000

 

Benchmark

        Runningforengine innodb

        Average number of seconds to run all queries: 4.402 seconds

        Minimum number of seconds to run all queries: 4.402 seconds

        Maximum number of seconds to run all queries: 4.402 seconds

        Number of clients running queries: 1

        Average number of queries per client: 10000

myisam每秒处理2195次。

innodb每秒处理2273次。

实验结果

项目myisam每秒吞吐量innodb每秒吞吐量

写性能测试1，不开binlog, guid主键, 无索引

1639971

写性能测试2，开binlog,guid主键，无索引

675374

写性能测试3， 开binlog, guid做主键， 有索引

599329

写性能测试4，开binglog，auto increment主键，有索引

595347

读性能测试1，guid主键

23722553

读性能测试2，auto increment主键

21952273

结论

1 在开启了binlog后， mysql写性能下降60%

2 myisam与innodb相比，写的速度更快（快40%）, 读的速度差不多。