OLTPBenchmark教程以及workload自动生成

OLTPBenchmark简介

Benchmark是一个评价方式，在整个计算机领域有着长期的应用。正如维基百科上的解释“As computer architecture advanced, it became more difficult to compare the performance of various computer systems simply by looking at their specifications.Therefore, tests were developed that allowed comparison of different architectures.”Benchmark在计算机领域应用最成功的就是性能测试，主要测试负载的执行时间、传输速度、吞吐量、资源占用率等。
性能调优的两大利器是Benchmark和profile工具。Benchmark用压力测试挖掘整个系统的性能状况，而profile工具最大限度地呈现系统的运行状态和性能指标，方便用户诊断性能问题和进行调优。

OLTPBenchmark是一个多线程负载生成器。该框架旨在能够针对任何启用JDBC的关系数据库产生可变速率，可变混合负载。该框架还提供了数据收集功能。

配置和调优

安装

1）ubuntu虚拟机
2）安装Java(v1.7 or newer)
3）安装ant
4)然后从资源库下载源代码。
git clone https://github.com/oltpbenchmark/oltpbench.git

坑点（大部分博客没提到的）

  1. 您需要安装Apache Ivy。 我们使用Ivy下载项目的所有依赖项。 OLTP-Bench包含一个帮助程序命令，用于将其安装在您的主目录（在$ / ant / lib中）：

     ant bootstrap

  2. 您可以使用以下命令下载所有依赖项：

     ant resolve

  3. 编译项目了：

     ant build

准备数据库

  1. 安装mysql

     https://blog.csdn.net/kylinxjd/article/details/90722062

  2. mysql简单应用

     https://www.cnblogs.com/lfri/p/10437694.html

  3. 建立测试数据库：进入mysql数据库中，建立tpcc数据库

     create database tpcc;

  4. 修改sample_tpcc_config.xml配置内容（注：config文件夹下）修改密码

<!-- Connection details -->

<dbtype>mysql</dbtype>

<driver>com.mysql.jdbc.Driver</driver>

<DBUrl>jdbc:mysql://localhost:3306/tpcc</DBUrl>

<username>root</username>

<password>mysecretpassword</password>

<isolation>TRANSACTION_SERIALIZABLE</isolation>

ubuntu更改mysql配置文件，使其产生日志文件mysql.log

  1. 打开mysql配置文件，将68,69行的注释"#"取消掉，保存关闭
    　　

     sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf

  2. 重启mysql服务

     sudo service mysql restart

  3. 实时查看日志文件

     日志文件所在目录:/var/log/mysql/mysql.log

 sudo tail -f /var/log/mysql/mysql.log

  4. 参考链接

     https://blog.csdn.net/wigger/article/details/92446014

Running a Benchmark and get sql workload ：

  1. 可以使用--help选项获取受支持的基准和选项的列表：

     $ ./oltpbenchmark --help
 usage: oltpbenchmark
 -b,--bench <arg>             [required] Benchmark class. Currently
                              supported: [tpcc, tatp, wikipedia,
                              resourcestresser, twitter, epinions, ycsb,
                              jpab, seats, auctionmark]
 -c,--config <arg>            [required] Workload configuration file
    --clear <arg>             Clear all records in the database for this
                              benchmark
    --create <arg>            Initialize the database for this benchmark
    --dialects-export <arg>   Export benchmark SQL to a dialects file
    --execute <arg>           Execute the benchmark workload
 -h,--help                    Print this help
    --histograms              Print txn histograms
    --load <arg>              Load data using the benchmark's data loader
 -o,--output <arg>            Output file (default System.out)
    --runscript <arg>         Run an SQL script
 -s,--sample <arg>            Sampling window
 -v,--verbose                 Display Messages、

  2. 例子
    例如，以下命令启动tpcc数据库（--create = true --load = true），然后按照config / sample_tpcc_config.xml文件中的说明运行工作负载。 将结果（延迟，吞吐量）汇总到5秒存储桶（-s 5）中，并将输出写入两个文件：outputfile.res（汇总）和outputfile.raw（详细）：

     ./oltpbenchmark -b tpcc -c config/sample_tpcc_config.xml --create=true --load=true --execute=true -s 5 -o outputfile

  3. 数据加载可能是一个漫长的过程，首先要创建一个并填充一个数据库，该数据库可以重复用于多个实验：

     ./oltpbenchmark -b tpcc -c config/sample_tpcc_config.xml --create=true --load=true

  4. 然后，可以使用以下命令在新的或使用的数据库上简单地运行实验。

     ./oltpbenchmark -b tpcc -c config/sample_tpcc_config.xml --execute=true -s 5 -o outputfile

5.输出测试结果

tree

1）outputfile.csv文件

1,NewOrder,1552636020.754667,1060362,1,0

1,NewOrder,1552636020.771344,1063626,0,0

1,NewOrder,1552636020.814723,1032441,1,0

1,NewOrder,1552636020.834709,1032416,0,0

2,Payment,1552636020.846932,1032458,1,0

1,NewOrder,1552636020.866866,1032402,0,0

2,Payment,1552636020.879025,1032456,1,0

1,NewOrder,1552636020.899003,1032423,0,0

4,Delivery,1552636020.910746,1032813,1,0

1,NewOrder,1552636020.931861,1031605,0,0

2,Payment,1552636020.943956,1031711,1,0

3,OrderStatus,1552636020.963955,1000540,0,0

2,Payment,1552636020.965030,1030622,0,0

1,NewOrder,1552636020.975824,1031838,1,0

1,NewOrder,1552636020.995788,1031832,0,0

2,Payment,1552636021.007641,1032168,1,0

1,NewOrder,1552636021.027646,1036094,0,0

 

.....

2)outputfile.res

time(sec), throughput(req/sec), avg_lat(ms), min_lat(ms), 25th_lat(ms), median_lat(ms), 75th_lat(ms), 90th_lat(ms), 95th_lat(ms), 99th_lat(ms), max_lat(ms), tp (req/s) scaled

0,61.400,963.312,4.874,1027.659,1031.848,1032.603,1052.913,1103.849,1143.807,1164.396,0.001

5,44.200,1045.141,1000.406,1031.491,1032.232,1035.805,1096.963,1129.627,1212.744,1293.055,0.001

10,43.600,1045.837,1000.573,1031.469,1032.104,1036.122,1104.670,1148.358,1244.217,1260.179,0.001

15,46.600,1042.841,1000.287,1031.314,1031.985,1032.844,1082.899,1134.970,1216.445,1292.666,0.001

20,44.200,1045.100,1000.537,1031.463,1032.079,1032.676,1115.551,1156.173,1225.113,1316.622,0.001

25,46.200,1043.236,1000.704,1031.588,1032.019,1033.004,1086.174,1117.592,1172.245,1348.143,0.001

30,45.200,1044.267,1000.581,1031.408,1031.999,1032.763,1112.190,1142.687,1179.271,1253.554,0.001

35,44.400,1044.880,999.879,1031.625,1032.006,1032.636,1084.527,1131.762,1296.677,1400.209,0.001

40,45.800,1043.545,1000.146,1031.203,1032.019,1032.796,1084.817,1155.087,1235.182,1304.080,0.001

45,46.400,1043.098,1000.032,1031.457,1032.112,1032.822,1093.252,1124.189,1263.628,1284.308,0.001

50,47.000,1042.467,1000.434,1031.277,1032.082,1032.728,1100.484,1132.784,1211.839,1300.353,0.001

55,34.000,1046.623,1000.332,1031.529,1032.063,1032.712,1096.575,1148.514,1292.676,1295.753,0.001

3） sql workload:mysql.log

其他资料

  1. 以下是TPCC配置文件的示例config/sample_tpcc_config.xml:

     <?xml version="1.0"?>
 <parameters>
 
 <!-- Connection details -->
 <dbtype>mysql</dbtype>
 <driver>com.mysql.jdbc.Driver</driver>
 <DBUrl>jdbc:mysql://localhost:3306/tpcc</DBUrl>
 <username>root</username>
 <password></password>
 <isolation>TRANSACTION_SERIALIZABLE</isolation>
 
 <!-- Scale factor is the number of warehouses in TPCC -->
 <scalefactor>2</scalefactor>
 
 <!-- The workload -->
 <terminals>2</terminals>
 <works>
     <work>
       <time>60</time>
       <rate>10000</rate>
       <weights>45,43,4,4,4</weights>
     </work>
 </works>
 
 <!-- TPCC specific -->  
	<transactiontypes>
 	<transactiontype>
 		<name>NewOrder</name>
 	</transactiontype>
 	<transactiontype>
 		<name>Payment</name>
 	</transactiontype>
 	<transactiontype>
 		<name>OrderStatus</name>
 	</transactiontype>
 	<transactiontype>
 		<name>Delivery</name>
 	</transactiontype>
 	<transactiontype>
 		<name>StockLevel</name>
 	</transactiontype>
	</transactiontypes>

参考文章

https://github.com/oltpbenchmark/oltpbench/wiki
posted @ 2020-03-10 21:57  差生_G  阅读(1018)  评论(1)  编辑  收藏  举报