OLTP-Benchmark配置和调优

╰半橙微兮° 2022-03-08 14:20 130阅读 0赞

### 一、前言 ###

Benchmark在计算机领域应用最成功的就是性能测试，主要测试负载的执行时间、传输速度、吞吐量、资源占用率等。性能调优的两大利器是Benchmark和profile工具。Benchmark用压力测试挖掘整个系统的性能状况，而profile工具最大限度地呈现系统的运行状态和性能指标，方便用户诊断性能问题和进行调优。参考文章： [https://www.cnblogs.com/sddai/p/7642738.html][https_www.cnblogs.com_sddai_p_7642738.html]

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][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文件）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ0OTMwMA_size_16_color_FFFFFF_t_70][]

###  三、使用 ###

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ0OTMwMA_size_16_color_FFFFFF_t_70 1][]

2、修改sample\_tpcc\_config.xml`配置内容（注：config文件夹下）修改密码`

<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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ0OTMwMA_size_16_color_FFFFFF_t_70 2][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ0OTMwMA_size_16_color_FFFFFF_t_70 3][]

### 四、输出文件解析 ###

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

![20190315160933794.png][]

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][https_github.com_oltpbenchmark_oltpbench_wiki]

[https_www.cnblogs.com_sddai_p_7642738.html]: https://www.cnblogs.com/sddai/p/7642738.html
[https_www.cnblogs.com_sell_archive_2013_07_24_3210198.html]: https://www.cnblogs.com/sell/archive/2013/07/24/3210198.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ0OTMwMA_size_16_color_FFFFFF_t_70]: /images/20220308/d23f70ac9f384f399384fba552a1eca9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ0OTMwMA_size_16_color_FFFFFF_t_70 1]: /images/20220308/ccb2654ef68c478c84ce4fc1f02cd358.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ0OTMwMA_size_16_color_FFFFFF_t_70 2]: /images/20220308/2ad59953533b4c268d1e3aa5abe317bc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ0OTMwMA_size_16_color_FFFFFF_t_70 3]: /images/20220308/77e26ef0051443aebbcf519e6da80ee4.png
[20190315160933794.png]: /images/20220308/10dda4711be845c8b6be671961632991.png
[https_github.com_oltpbenchmark_oltpbench_wiki]: https://github.com/oltpbenchmark/oltpbench/wiki