OLTP-Benchmark配置和调优

一、前言

OLTPbenchmark是一个多线程负载生成器。该框架被设计成能够针对任何启用JDBC的关系数据库产生可变速率、可变混合负载。该框架还提供数据收集功能，例如，每事务类型的延迟和吞吐量日志。OLTP-Benchmark是一种模块化、可扩展和可配置的OLTP基准测试工具。

二、安装

1、前期准备：

1）ubuntu 16.04；（我测试的时候用的是这个版本，大家也可以用别的操作系统）

2）安装好java;

3）安装好ant ,参考：https://www.cnblogs.com/sell/archive/2013/07/24/3210198.html

2、从github上下载oltp-benchmark的源代码

git clone https://github.com/oltpbenchmark/oltpbench.git

3、进入到oltpbench中用ant进行编译（注：主要起作用的是里边的build.xml文件）

OLTP-Benchmark配置和调优

三、使用

1、建立测试数据库：进入mysql数据库中，建立tpcc数据库 mysql> create database tpcc;

OLTP-Benchmark配置和调优

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

<!-- config/sample_tpcc_config.xml -->
    <!-- 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>

3、启动tcpp数据库，按sample_tpcc_config.xml文件描述执行，结果汇总到输出文件中：

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

OLTP-Benchmark配置和调优

4、然后，只需在一个新的或使用过的数据库上使用以下命令就可以运行一个实验。

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

OLTP-Benchmark配置和调优

四、输出文件解析

原始输出是每个事务类型的开始时间（Java微秒）和持续时间（微秒）的列表。

OLTP-Benchmark配置和调优

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

【参考文章】

1、https://github.com/oltpbenchmark/oltpbench/wiki

OLTP-Benchmark配置和调优

一、前言

二、安装

三、使用

四、输出文件解析

相关推荐