Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

HP - Lesson 2 - 「9547」 #1

Open
9547 opened this issue Aug 24, 2020 · 0 comments
Open

HP - Lesson 2 - 「9547」 #1

9547 opened this issue Aug 24, 2020 · 0 comments

Comments

@9547
Copy link
Owner

9547 commented Aug 24, 2020

题目要求

分值:300
题目描述:使用 sysbench、go-ycsb 和 go-tpc 分别对 TiDB 进行测试并且产出测试报告。
测试报告需要包括以下内容:

  • 部署环境的机器配置(CPU、内存、磁盘规格型号),拓扑结构(TiDB、TiKV 各部署于哪些节点)
  • 调整过后的 TiDB 和 TiKV 配置
  • 测试输出结果
  • 关键指标的监控截图
    • TiDB Query Summary 中的 qps 与 duration
    • TiKV Details 面板中 Cluster 中各 server 的 CPU 以及 QPS 指标
    • TiKV Details 面板中 grpc 的 qps 以及 duration

输出:写出你对该配置与拓扑环境和 workload 下 TiDB 集群负载的分析,提出你认为的 TiDB 的性能的瓶颈所在(能提出大致在哪个模块即 可)

截止时间:下周二(8.25)24:00:00(逾期提交不给分)


集群配置

机器配置

MacBookPro 16'
CPU: 2.6 GHz 6-Core Intel Core i7
Mem: 16GiB

拓扑结构

TiUP Playground 起的单实例服务

sysbench 测试

sysbench.cfg 配置文件:

mysql-host=127.0.0.1
mysql-port=4000
mysql-user=root
mysql-password=
mysql-db=tpcc
time=60
threads=16
report-interval=10
db-driver=mysql

测试过程

[email protected]:tidb-in-action <master()>$ sysbench --config-file=sysbench.cfg oltp_point_select --threads=4 --tables=32 --table-size=10000 prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest1'...Creating table 'sbtest4'...Creating table 'sbtest2'...Creating table 'sbtest3'...
Inserting 10000 records into 'sbtest2'
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest2'...
Inserting 10000 records into 'sbtest3'
......
Creating a secondary index on 'sbtest31'...
Creating a secondary index on 'sbtest29'...
Creating table 'sbtest30'...
Inserting 10000 records into 'sbtest30'
Creating a secondary index on 'sbtest30'...
Creating table 'sbtest32'...
Inserting 10000 records into 'sbtest32'
Creating a secondary index on 'sbtest32'...
[email protected]:tidb-in-action <master()>$ sysbench --config-file=sysbench.cfg oltp_point_select --threads=4 --tables=32 --table-size=10000 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 4
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 4 tps: 12695.30 qps: 12695.30 (r/w/o: 12695.30/0.00/0.00) lat (ms,95%): 0.39 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 11625.75 qps: 11625.75 (r/w/o: 11625.75/0.00/0.00) lat (ms,95%): 0.42 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 4 tps: 11053.58 qps: 11053.58 (r/w/o: 11053.58/0.00/0.00) lat (ms,95%): 0.46 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 4 tps: 10459.58 qps: 10459.58 (r/w/o: 10459.58/0.00/0.00) lat (ms,95%): 0.47 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 4 tps: 10912.19 qps: 10912.19 (r/w/o: 10912.19/0.00/0.00) lat (ms,95%): 0.46 err/s: 0.00 reconn/s: 0.00
^C
[email protected]:tidb-in-action <master()>$ sysbench --config-file=sysbench.cfg oltp_update_index --threads=4 --tables=32 --table-si
ze=10000 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 4
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 4 tps: 1781.93 qps: 1781.93 (r/w/o: 0.00/1781.93/0.00) lat (ms,95%): 2.61 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 1684.15 qps: 1684.15 (r/w/o: 0.00/1684.15/0.00) lat (ms,95%): 2.76 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 4 tps: 1616.81 qps: 1616.81 (r/w/o: 0.00/1616.81/0.00) lat (ms,95%): 2.86 err/s: 0.00 reconn/s: 0.00
^C
[email protected]:tidb-in-action <master()>$ sysbench --config-file=sysbench.cfg oltp_read_only --threads=4 --tables=32 --table-size=
10000 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 4
Report intermediate results every 10 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 10s ] thds: 4 tps: 350.35 qps: 5609.03 (r/w/o: 4908.04/0.00/700.99) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 344.19 qps: 5507.00 (r/w/o: 4818.53/0.00/688.48) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 4 tps: 333.51 qps: 5335.56 (r/w/o: 4668.54/0.00/667.02) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 4 tps: 331.48 qps: 5304.13 (r/w/o: 4641.26/0.00/662.87) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 4 tps: 299.69 qps: 4795.28 (r/w/o: 4195.81/0.00/599.47) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 4 tps: 302.40 qps: 4837.92 (r/w/o: 4233.13/0.00/604.79) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            274694
        write:                           0
        other:                           39242
        total:                           313936
    transactions:                        19621  (326.94 per sec.)
    queries:                             313936 (5231.04 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0125s
    total number of events:              19621

Latency (ms):
         min:                                    8.37
         avg:                                   12.23
         max:                                   46.99
         95th percentile:                       15.83
         sum:                               239974.37

Threads fairness:
    events (avg/stddev):           4905.2500/4.60
    execution time (avg/stddev):   59.9936/0.00

go-ycsb 测试

[email protected]:go-ycsb <master()>$ for i in {a..f}; do bin/go-ycsb load mysql -P workloads/workload${i} -p recordcount=10000 -p mysql.host=127.0.0.1 -p mysql.port=4000 --threads 32 && bin/go-ycsb run mysql -P workloads/workload${i} -p recordcount=10000 -p
mysql.host=127.0.0.1 -p mysql.port=4000  --threads 32 && echo "----------------"; done
***************** properties *****************
"workload"="core"
"operationcount"="1000"
"updateproportion"="0.5"
"scanproportion"="0"
"insertproportion"="0"
"recordcount"="10000"
"mysql.host"="127.0.0.1"
"mysql.port"="4000"
"requestdistribution"="uniform"
"threadcount"="32"
"dotransactions"="false"
"readallfields"="true"
"readproportion"="0.5"
**********************************************
Run finished, takes 2.560582193s
INSERT - Takes(s): 2.6, Count: 9984, OPS: 3910.4, Avg(us): 1621, Min(us): 456, Max(us): 7318, 99th(us): 5000, 99.9th(us): 6000, 99.99th(us): 8000
***************** properties *****************
"recordcount"="10000"
"readproportion"="0.5"
"updateproportion"="0.5"
"mysql.host"="127.0.0.1"
"threadcount"="32"
"readallfields"="true"
"scanproportion"="0"
"requestdistribution"="uniform"
"mysql.port"="4000"
"dotransactions"="true"
"workload"="core"
"insertproportion"="0"
"operationcount"="1000"
**********************************************
Run finished, takes 165.374617ms
READ   - Takes(s): 0.2, Count: 486, OPS: 3142.3, Avg(us): 1917, Min(us): 568, Max(us): 8134, 99th(us): 8000, 99.9th(us): 9000, 99.99th(us): 9000
UPDATE - Takes(s): 0.2, Count: 506, OPS: 3330.6, Avg(us): 6746, Min(us): 604, Max(us): 134305, 99th(us): 14000, 99.9th(us): 135000, 99.99th(us): 135000
----------------
***************** properties *****************
"mysql.host"="127.0.0.1"
"readallfields"="true"
"requestdistribution"="uniform"
"workload"="core"
"updateproportion"="0.05"
"recordcount"="10000"
"insertproportion"="0"
"operationcount"="1000"
"readproportion"="0.95"
"scanproportion"="0"
"mysql.port"="4000"
"dotransactions"="false"
"threadcount"="32"
**********************************************
Run finished, takes 2.704303099s
INSERT - Takes(s): 2.7, Count: 9984, OPS: 3699.1, Avg(us): 1780, Min(us): 540, Max(us): 8916, 99th(us): 6000, 99.9th(us): 9000, 99.99th(us): 9000
***************** properties *****************
"workload"="core"
"recordcount"="10000"
"mysql.port"="4000"
"dotransactions"="true"
"threadcount"="32"
"requestdistribution"="uniform"
"operationcount"="1000"
"readallfields"="true"
"readproportion"="0.95"
"scanproportion"="0"
"updateproportion"="0.05"
"mysql.host"="127.0.0.1"
"insertproportion"="0"
**********************************************
Run finished, takes 220.844174ms
READ   - Takes(s): 0.2, Count: 940, OPS: 4461.8, Avg(us): 2244, Min(us): 620, Max(us): 10121, 99th(us): 8000, 99.9th(us): 11000, 99.99th(us): 11000
UPDATE - Takes(s): 0.2, Count: 52, OPS: 257.5, Avg(us): 15549, Min(us): 1025, Max(us): 173212, 99th(us): 174000, 99.9th(us): 174000, 99.99th(us): 174000
----------------
***************** properties *****************
"mysql.host"="127.0.0.1"
"dotransactions"="false"
"threadcount"="32"
"requestdistribution"="uniform"
"insertproportion"="0"
"workload"="core"
"operationcount"="1000"
"scanproportion"="0"
"readproportion"="1"
"mysql.port"="4000"
"readallfields"="true"
"recordcount"="10000"
"updateproportion"="0"
**********************************************
Run finished, takes 2.707801965s
INSERT - Takes(s): 2.7, Count: 9984, OPS: 3698.4, Avg(us): 1717, Min(us): 462, Max(us): 8173, 99th(us): 5000, 99.9th(us): 6000, 99.99th(us): 9000
***************** properties *****************
"recordcount"="10000"
"readproportion"="1"
"operationcount"="1000"
"insertproportion"="0"
"mysql.port"="4000"
"threadcount"="32"
"readallfields"="true"
"scanproportion"="0"
"workload"="core"
"mysql.host"="127.0.0.1"
"dotransactions"="true"
"updateproportion"="0"
"requestdistribution"="uniform"
**********************************************
Run finished, takes 81.953326ms
READ   - Takes(s): 0.1, Count: 992, OPS: 13262.8, Avg(us): 2366, Min(us): 758, Max(us): 7116, 99th(us): 7000, 99.9th(us): 8000, 99.99th(us): 8000
----------------
***************** properties *****************
"readproportion"="0.95"
"mysql.port"="4000"
"operationcount"="1000"
"readallfields"="true"
"insertproportion"="0.05"
"threadcount"="32"
"mysql.host"="127.0.0.1"
"scanproportion"="0"
"requestdistribution"="latest"
"dotransactions"="false"
"workload"="core"
"updateproportion"="0"
"recordcount"="10000"
**********************************************
Run finished, takes 2.810638828s
INSERT - Takes(s): 2.8, Count: 9984, OPS: 3560.1, Avg(us): 1825, Min(us): 506, Max(us): 11998, 99th(us): 6000, 99.9th(us): 7000, 99.99th(us): 12000
***************** properties *****************
"readproportion"="0.95"
"insertproportion"="0.05"
"readallfields"="true"
"requestdistribution"="latest"
"threadcount"="32"
"mysql.port"="4000"
"dotransactions"="true"
"workload"="core"
"scanproportion"="0"
"operationcount"="1000"
"mysql.host"="127.0.0.1"
"updateproportion"="0"
"recordcount"="10000"
**********************************************
Run finished, takes 104.00339ms
INSERT - Takes(s): 0.1, Count: 47, OPS: 532.9, Avg(us): 10138, Min(us): 2336, Max(us): 21438, 99th(us): 22000, 99.9th(us): 22000, 99.99th(us): 22000
READ   - Takes(s): 0.1, Count: 945, OPS: 9995.4, Avg(us): 2379, Min(us): 729, Max(us): 9521, 99th(us): 6000, 99.9th(us): 10000, 99.99th(us): 10000
----------------
***************** properties *****************
"readproportion"="0"
"recordcount"="10000"
"requestdistribution"="uniform"
"threadcount"="32"
"readallfields"="true"
"updateproportion"="0"
"maxscanlength"="1"
"scanlengthdistribution"="uniform"
"workload"="core"
"scanproportion"="0.95"
"dotransactions"="false"
"operationcount"="1000"
"insertproportion"="0.05"
"mysql.port"="4000"
"mysql.host"="127.0.0.1"
**********************************************
Run finished, takes 2.821535235s
INSERT - Takes(s): 2.8, Count: 9984, OPS: 3549.9, Avg(us): 1834, Min(us): 499, Max(us): 9076, 99th(us): 6000, 99.9th(us): 8000, 99.99th(us): 10000
***************** properties *****************
"insertproportion"="0.05"
"mysql.port"="4000"
"mysql.host"="127.0.0.1"
"scanproportion"="0.95"
"readallfields"="true"
"maxscanlength"="1"
"workload"="core"
"readproportion"="0"
"dotransactions"="true"
"updateproportion"="0"
"operationcount"="1000"
"threadcount"="32"
"requestdistribution"="uniform"
"scanlengthdistribution"="uniform"
"recordcount"="10000"
**********************************************
Run finished, takes 174.588135ms
INSERT - Takes(s): 0.2, Count: 49, OPS: 311.0, Avg(us): 3186, Min(us): 934, Max(us): 8682, 99th(us): 9000, 99.9th(us): 9000, 99.99th(us): 9000
SCAN   - Takes(s): 0.2, Count: 943, OPS: 5688.9, Avg(us): 5218, Min(us): 1758, Max(us): 13070, 99th(us): 12000, 99.9th(us): 14000, 99.99th(us): 14000
----------------
***************** properties *****************
"scanproportion"="0"
"recordcount"="10000"
"requestdistribution"="uniform"
"threadcount"="32"
"readallfields"="true"
"readproportion"="0.5"
"operationcount"="1000"
"readmodifywriteproportion"="0.5"
"mysql.host"="127.0.0.1"
"workload"="core"
"updateproportion"="0"
"mysql.port"="4000"
"dotransactions"="false"
"insertproportion"="0"
**********************************************
Run finished, takes 2.968725066s
INSERT - Takes(s): 3.0, Count: 9984, OPS: 3371.1, Avg(us): 2042, Min(us): 661, Max(us): 8365, 99th(us): 6000, 99.9th(us): 7000, 99.99th(us): 9000
***************** properties *****************
"mysql.host"="127.0.0.1"
"recordcount"="10000"
"readallfields"="true"
"mysql.port"="4000"
"operationcount"="1000"
"threadcount"="32"
"readmodifywriteproportion"="0.5"
"readproportion"="0.5"
"updateproportion"="0"
"insertproportion"="0"
"workload"="core"
"dotransactions"="true"
"scanproportion"="0"
"requestdistribution"="uniform"
**********************************************
Run finished, takes 218.880388ms
READ   - Takes(s): 0.2, Count: 992, OPS: 4778.3, Avg(us): 2247, Min(us): 786, Max(us): 11172, 99th(us): 7000, 99.9th(us): 12000, 99.99th(us): 12000
READ_MODIFY_WRITE - Takes(s): 0.2, Count: 487, OPS: 2432.6, Avg(us): 10449, Min(us): 3166, Max(us): 19149, 99th(us): 19000, 99.9th(us): 20000, 99.99th(us): 20000
UPDATE - Takes(s): 0.2, Count: 487, OPS: 2431.8, Avg(us): 8135, Min(us): 2318, Max(us): 16091, 99th(us): 15000, 99.9th(us): 17000, 99.99th(us): 17000
----------------

tpc-c 测试

[email protected]:go-tpc <master(v1.0.3-23-g6eb40da)>$ bin/go-tpc tpcc prepare --warehouses 8 --threads 8
creating table warehouse
creating table district
...
begin to check warehouse 2 at condition 3.3.2.11
begin to check warehouse 8 at condition 3.3.2.8
Finished

[email protected]:go-tpc <master(v1.0.3-23-g6eb40da)>$ bin/go-tpc tpcc run --warehouses 8 --threads 8
[Current] DELIVERY - Takes(s): 9.7, Count: 136, TPM: 845.2, Sum(ms): 11501, Avg(ms): 84, 90th(ms): 112, 99th(ms): 256, 99.9th(ms): 256
[Current] NEW_ORDER - Takes(s): 10.0, Count: 1726, TPM: 10396.9, Sum(ms): 39449, Avg(ms): 22, 90th(ms): 32, 99th(ms): 64, 99.9th(ms): 160
Current] PAYMENT - Takes(s): 10.0, Count: 1550, TPM: 9335.2, Sum(ms): 23230, Avg(ms): 14, 90th(ms): 24, 99th(ms): 40, 99.9th(ms): 128
...

^C
Got signal [interrupt] to exit.
Finished
[Summary] STOCK_LEVEL - Takes(s): 467.8, Count: 3738, TPM: 479.5, Sum(ms): 59598, Avg(ms): 15, 90th(ms): 20, 99th(ms): 32, 99.9th(ms): 64
tpmC: 5505.9

这个过程执行时间太久,中途 Ctrl-C 中断了,虽然最后的 tpmC 结果显示是 5505.9,但对这一结果还是存疑的。

监控输出

TiDB/Duration
image

TiDB/QPS

image

macOS 下面 node_exporter 好像没有采集到 CPU/Mem 等物理机数据
image

TiKV/QPS

image

TiKV/gRPC

image

总结

这是个试验性质的『测试报告』,中间存在了太多的不严谨内容:

  1. 只测试了一两遍,结果缺乏说服力;
  2. 压测服务和对象服务部署在一台机器上,彼此也没有隔离,你不好说是谁把谁的资源给占用了;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant