sysbench测试MySQL和IO性能

川长思鸟来 2024-02-18 16:17 17阅读 0赞

### 为什么要测试，测什么东西？ ###

测试的种类非常多，测试的目的也非常多，我这里主要的目的就两个

1.  测试MySQL的极限IO
2.  对比不同版本MySQL，不同参数, 不同硬件，不同系统对MySQL的性能影响

### 为什么选择sysbench ###

1.  因为MySQL官方的测试就是用sysbench哦
2.  尽量选择最新版本的sysbench哦，大于0.4版本的sysbench有实时显示功能

### 如何下载sysbench ###

[http://github.com/akopytov/sysbench][http_github.com_akopytov_sysbench]

### 文档在哪里 ###

[http://github.com/akopytov/sysbench][http_github.com_akopytov_sysbench]

### 如何安装 ###

> \* 基本步骤
> 
> cd sysbench- 1.0;
> 
> ./autogen.sh;
> 
> ./configure — with\-mysql-includes=/usr/local/mysql/include — with\-mysql-libs=/usr/local/mysql/ lib/;
> 
> make;
> 
> make install;
> 
> \* 过程中可能会遇到的故障
> 
> sysbench: error  while loading shared libraries: libmysqlclient.so .20: cannot open shared object file: No such file or directory
> 
> \* 解决方案
> 
> export LD\_LIBRARY\_PATH=/usr/local/mysql/ lib/;
> 
> \* 测试是否安装成功
> 
> shell> sysbench –version
> 
> sysbench 1.0

### 介绍sysbench的核心用法 ###

> 1.  它可以用来测试很多东西，测试io，cpu，mem，mysql，oracle，pg等等。
> 2.  这里主要介绍我关心的两个，IO & MySQL
> 3.  以下前半部分是0.4版本的用法，0.4以上的版本用法不一样，会注明。

#### 一、通用语法 ####

> sysbench \[common- options\] –test=name \[test- options\] command

*  command

> \* prepare
> 
> 准备阶段，也就是装载数据。
> 
> filo test 中： 就是创建指定大小的文件
> 
> oltp test 中： 就是创建指定大小的表
> 
> \* run
> 
> 实际测试阶段
> 
> \* cleanup
> 
> 收尾阶段，清除之前测试的数据。

*  common-options

> 只介绍常用的选项

<table> 
 <thead> 
  <tr> 
   <th>选项</th> 
   <th>描述</th> 
   <th>默认值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>—num-threads</td> 
   <td>多少个线程</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>—max-requests</td> 
   <td>多少个请求，0意味着无限制</td> 
   <td>1000</td> 
  </tr> 
  <tr> 
   <td>—max-time</td> 
   <td>测试多长时间，0意味着无限制</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>—test</td> 
   <td>测试什么模块</td> 
   <td>必须要求</td> 
  </tr> 
  <tr> 
   <td>—report-interval</td> 
   <td>阶段性的汇报测试统计信息,0.4以上版本新增</td> 
  </tr> 
 </tbody> 
</table>

*  —test=fileio 模块的选项

提前注明：—file-test-mode

> \* seqwr
> 
> sequential write
> 
> \* seqrewr
> 
> sequential rewrite
> 
> \* seqrd
> 
> sequential read
> 
> \* rndrd
> 
> random read
> 
> \* rndwr
> 
> random write
> 
> \* rndrw
> 
> combined random read/ write

*  test option for fileio

<table> 
 <thead> 
  <tr> 
   <th>选项</th> 
   <th>描述</th> 
   <th>默认值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>—file-num</td> 
   <td>创建文件的数量</td> 
   <td>128</td> 
  </tr> 
  <tr> 
   <td>—file-block-size</td> 
   <td>IO操作的大小</td> 
   <td>16k</td> 
  </tr> 
  <tr> 
   <td>—file-total-size</td> 
   <td>所有文件的总大小</td> 
   <td>2G</td> 
  </tr> 
  <tr> 
   <td>—file-test-mode</td> 
   <td>seqwr，seqrewr, seqrd, rndrd, rndwr, rndwr(上面已经介绍)</td> 
   <td>必须</td> 
  </tr> 
  <tr> 
   <td>—file-io-mode</td> 
   <td>i/O 模式，sync, async, fastmmap, slowmmap</td> 
   <td>sync</td> 
  </tr> 
  <tr> 
   <td>—file-extra-flags</td> 
   <td>以额外的标记（O_SYNC，O_DSYNC，O_DIRECT）打开</td> 
   <td>–</td> 
  </tr> 
  <tr> 
   <td>—file-fsync-freq</td> 
   <td>多少请求后使用fsync</td> 
   <td>100</td> 
  </tr> 
  <tr> 
   <td>—file-fsync-all</td> 
   <td>每次写IO都必须fsync</td> 
   <td>no</td> 
  </tr> 
  <tr> 
   <td>—file-fsync-mode</td> 
   <td>用什么样的模式来同步文件fsync, fdatasync (see above)</td> 
   <td>fsync</td> 
  </tr> 
  <tr> 
   <td>—file-rw-ratio</td> 
   <td>随机读写请求的比例</td> 
   <td>1.5</td> 
  </tr> 
 </tbody> 
</table>

举例：

> $ sysbench – – num – threads=16 – – test=fileio – – file – total – size=3G – – file – test – mode=rndrw prepare
> 
> $ sysbench – – num – threads=16 – – test=fileio – – file – total – size=3G – – file – test – mode=rndrw run
> 
> $ sysbench – – num – threads=16 – – test=fileio – – file – total – size=3G – – file – test – mode=rndrw cleanup

### OLTP-MySQL ###

此模式用于测试真实数据库性能。在prepare阶段创建表，sbtest默认

> CREATE  TABLE \`sbtest\` (
> 
> \`id\` int( 10) unsigned NOT NULL auto\_increment,
> 
> \`k\` int( 10) unsigned NOT NULL  default ‘0’,
> 
> \`c\` char( 120) NOT NULL  default ”,
> 
> \`pad\` char( 60) NOT NULL  default ”,
> 
> PRIMARY KEY ( \`id\`),
> 
> KEY \`k\` ( \`k\`));

在run阶段

*  simple模式

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div> 
     <span><span>SELECT</span> c <span> FROM</span> sbtest <span>WHERE</span> id=N</span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

*  Point queries

*  Range queries:

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div> 
     <span><span>SELECT</span> c <span> FROM</span> sbtest <span>WHERE</span> id <span>BETWEEN</span> N <span>AND</span> M</span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

*  Range SUM() queries

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div> 
     <span><span>SELECT</span> <span> SUM</span>(K) <span>FROM</span> sbtest <span>WHERE</span> id <span>BETWEEN</span> N <span>and</span> M</span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

*  Range ORDER BY queries

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div> 
     <span><span>SELECT</span> c <span> FROM</span> sbtest <span>WHERE</span> id <span>between</span> N <span>and</span> M <span>ORDER</span> <span> BY</span> c</span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

*  Range DISTINCT queries

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div> 
     <span><span>SELECT</span> <span> DISTINCT</span> c <span>FROM</span> sbtest <span> WHERE</span> id <span>BETWEEN</span> N <span>and</span> M <span>ORDER</span> <span>BY</span> c</span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

*  UPDATEs on index column

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div> 
     <span><span>UPDATE</span> sbtest <span>SET</span> k=k+<span>1</span> <span> WHERE</span> id=N</span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

*  UPDATEs on non-index column:

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div> 
     <span><span>UPDATE</span> sbtest <span>SET</span> c=N <span>WHERE</span> id=M</span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

*  DELETE queries

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div> 
     <span><span>DELETE</span> <span> FROM</span> sbtest <span>WHERE</span> id=N</span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

*  INSERT queries

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div>
      INSERT INTO sbtest VALUES ( 
     <span>…</span>) 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

*  oltp test模式通用参数

<table> 
 <thead> 
  <tr> 
   <th>选项</th> 
   <th>描述</th> 
   <th>默认值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>—oltp-table-name</td> 
   <td>表的名字</td> 
   <td>sbtest</td> 
  </tr> 
  <tr> 
   <td>—oltp-table-size</td> 
   <td>表的行数</td> 
   <td>10000</td> 
  </tr> 
  <tr> 
   <td>—oltp-tables-count</td> 
   <td>表的个数</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>—oltp-dist-type</td> 
   <td>热点数据分布{uniform(均匀分布),Gaussian(高斯分布),special(空间分布)}。默认是special</td> 
   <td>special</td> 
  </tr> 
  <tr> 
   <td>—oltp-dist-pct</td> 
   <td>special：热点数据产生的比例</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>—oltp-dist-res</td> 
   <td>special：热点数据的访问频率</td> 
   <td>75</td> 
  </tr> 
  <tr> 
   <td>—oltp-test-mode</td> 
   <td>simple，complex（以上介绍）</td> 
   <td>complex</td> 
  </tr> 
  <tr> 
   <td>—oltp-read-only</td> 
   <td>只有select 请求</td> 
   <td>off</td> 
  </tr> 
  <tr> 
   <td>—oltp-skip-trx</td> 
   <td>不用事务</td> 
   <td>off</td> 
  </tr> 
  <tr> 
   <td>—oltp-point-selects</td> 
   <td>一个事务中简单select查询数量</td> 
   <td>10</td> 
  </tr> 
  <tr> 
   <td>—oltp-simple-ranges</td> 
   <td>一个事务中简单range查询的数量</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>—oltp-sum-ranges</td> 
   <td>sum range的数量</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>—oltp-order=ranges</td> 
   <td>order range的数量</td> 
   <td>1</td> 
  </tr> 
 </tbody> 
</table>

*  mysql test 参数

> –mysql-host=\[LIST, …\] MySQL server host \[localhost\]
> 
> –mysql-port=\[LIST, …\] MySQL server port \[ 3306\]
> 
> –mysql-socket=\[LIST, …\] MySQL socket
> 
> –mysql-user=STRING MySQL user \[sbtest\]
> 
> –mysql-password=STRING MySQL password \[\]
> 
> –mysql-db=STRING MySQL database name \[sbtest\]
> 
> –mysql-table-engine=STRING storage engine to use  for the test table \{myisam,innodb,bdb,heap,ndbcluster,federated\} \[innodb\]
> 
> –mysql-engine-trx=STRING whether storage engine used is transactional or not \{yes,no,auto\} \[auto\]
> 
> –mysql-ssl=\[on|off\] use SSL connections,  if available in the client  library \[off\]
> 
> –mysql-ssl-cipher=STRING use specific cipher  for SSL connections \[\]
> 
> –mysql-compression=\[on|off\] use compression,  if available in the client  library \[off\]
> 
> –myisam-max-rows=N max-rows parameter for MyISAM tables \[ 1000000\]
> 
> –mysql-debug=\[on|off\] dump all client library calls \[off\]
> 
> –mysql-ignore-errors=\[LIST, …\]list of errors to ignore, or “all” \[ 1213, 1020, 1205\]
> 
> –mysql-dry-run=\[on|off\] Dry run, pretent that all MySQL client API calls are successful without executing them \[off\]

以上0.4版本的语法介绍完毕。

接下来是大于0.4版本的新语法，尤其是—test=oltp模块

用—test=xx.lua (完整路径来传递)来代替

### FileIO实战 ###

> 磁盘：S3610 \* 6 raid10, 内存128G  
> 测试出相关场景下的极限IOPS

*  随机读写（3:2 oltp场景）

> sysbench – – num – threads=16 – – report – interval=3 – – max – requests=0 – – max – time=300 – – test=fileio – – file – num=200 – – file – total – size=200G – – file – test – mode=rndrw – – file – block – size=16384 – – file – extra – flags=direct run

![fileio\_oltp\_32][fileio_oltp_32]

*  随机读写（5：1 oltp场景）

![fileio\_oltp\_51][fileio_oltp_51]

*  随机写

> sysbench – – num – threads=16 – – report – interval=3 – – max – requests=0 – – max – time=300 – – test=fileio – – file – num=200 – – file – total – size=200G – – file – test – mode=rndwr – – file – block – size=16384 – – file – extra – flags=direct run

![fileio\_rndwr][fileio_rndwr]

*  随机读

> sysbench – – num – threads=16 – – report – interval=3 – – max – requests=0 – – max – time=300 – – test=fileio – – file – num=200 – – file – total – size=200G – – file – test – mode=rndrd – – file – block – size=16384 – – file – extra – flags=direct run

![fileio\_rndrd][fileio_rndrd]

### MySQL5.6 vs MySQL5.7 测试 ###

> 磁盘：S3610 \* 6 raid10, 内存128G

*  Point select

> \* 产生数据
> 
> sysbench – – num – threads=128 – – report – interval=3 – – max – requests=0 – – max – time=300 – – test=/root/sysbench – 1 . 0/sysbench/tests/db/select . lua – – mysql – table – engine=innodb – – oltp – table – size=50000000 – – mysql – user=sysbench – – mysql – password=sysbench – – oltp – tables – count=2 – – mysql – host=xx – – mysql – port=3306 prepare
> 
> \* 执行
> 
> sysbench – – num – threads=128 – – report – interval=3 – – max – requests=0 – – max – time=300 – – test=/root/sysbench – 1 . 0/sysbench/tests/db/select . lua – – mysql – table – engine=innodb – – oltp – table – size=50000000 – – mysql – user=sysbench – – mysql – password=sysbench – – oltp – tables – count=2 – – mysql – host=xx – – mysql – port=3306 run

![select\_sysbench][select_sysbench]

*  Point oltp

> \* 产生数据
> 
> sysbench ––num–threads=128 ––report–interval=3 ––max–requests=0 ––max–time=300 ––test=/root/sysbench–1.0/sysbench/tests/db/oltp.lua ––mysql–table–engine=innodb ––oltp–table–size=50000000 ––mysql–user=sysbench ––mysql–password=sysbench ––oltp–tables–count=2 ––mysql–host=xx ––mysql–port=3306 prepare
> 
> \* 执行
> 
> sysbench ––num–threads=128 ––report–interval=3 ––max–requests=0 ––max–time=300 ––test=/root/sysbench–1.0/sysbench/tests/db/oltp.lua ––mysql–table–engine=innodb ––oltp–table–size=50000000 ––mysql–user=sysbench ––mysql–password=sysbench ––oltp–tables–count=2 ––mysql–host=xx ––mysql–port=3306 run

![oltp\_sysbench][oltp_sysbench]

#### 结论 ####

> 1.  在性能方面，虽然官方号称5.7性能比5.6快3倍，但是在实际测试中5.7比5.6却稍微差一点点
> 2.  是否会选择5.7生产环境？当然，因为5.7的新特性太诱人了

#### 参考： ####

> [https://www.percona.com/blog/2016/04/07/mysql-5-7-sysbench-oltp-read-results-really-faster/][https_www.percona.com_blog_2016_04_07_mysql-5-7-sysbench-oltp-read-results-really-faster]  
> [http://dimitrik.free.fr/blog/archives/2013/09/mysql-performance-reaching-500k-qps-with-mysql-57.html][http_dimitrik.free.fr_blog_archives_2013_09_mysql-performance-reaching-500k-qps-with-mysql-57.html]  
> [https://github.com/akopytov/sysbench][https_github.com_akopytov_sysbench]  
> [http://www.mysql.com/why-mysql/benchmarks/][http_www.mysql.com_why-mysql_benchmarks]

[http_github.com_akopytov_sysbench]: http://github.com/akopytov/sysbench
[fileio_oltp_32]: http://www.dbarun.com/wp-content/uploads/2017/08/1ed9c741639343db54b41d1596529f73.jpg
[fileio_oltp_51]: http://www.dbarun.com/wp-content/uploads/2017/08/16798b71ce2abc2aa4c0486fbbb1c046.jpg
[fileio_rndwr]: http://www.dbarun.com/wp-content/uploads/2017/08/c992632da4cb5c4e7de1dd75884be138.jpg
[fileio_rndrd]: http://www.dbarun.com/wp-content/uploads/2017/08/30af51fea0b4cdcf443108e35c5d4a1c.jpg
[select_sysbench]: http://www.dbarun.com/wp-content/uploads/2017/08/28022e6ef8f07ef9806090df16a292ba.jpg
[oltp_sysbench]: http://www.dbarun.com/wp-content/uploads/2017/08/e0fb3aa270aee3717c766b6507771751.jpg
[https_www.percona.com_blog_2016_04_07_mysql-5-7-sysbench-oltp-read-results-really-faster]: https://www.percona.com/blog/2016/04/07/mysql-5-7-sysbench-oltp-read-results-really-faster/
[http_dimitrik.free.fr_blog_archives_2013_09_mysql-performance-reaching-500k-qps-with-mysql-57.html]: http://dimitrik.free.fr/blog/archives/2013/09/mysql-performance-reaching-500k-qps-with-mysql-57.html
[https_github.com_akopytov_sysbench]: https://github.com/akopytov/sysbench
[http_www.mysql.com_why-mysql_benchmarks]: http://www.mysql.com/why-mysql/benchmarks/