请问下tidb怎么进行tpc-H的测试

[benben (御剑飞行)]

• 【TiDB 版本】：4.0.0
• 【问题描述】：请问下tidb怎么进行tpc-H的测试,测试下tidb作为数据仓库的性能.以及主要的衡量指标是啥

[yilong (yi888long)]

可以参考文档

https://pingcap.com/docs-cn/stable/benchmark/v4.0-performance-benchmarking-with-tpch/

[benben (御剑飞行)]

发现了一个文档上的错误, 这个下推计算的参数是会话级别的. set global tidb_opt_distinct_agg_push_down = 1; SQL 错误 [1105] [HY000]: Variable ‘tidb_opt_distinct_agg_push_down’ is a SESSION variable and can’t be used with SET GLOBAL

[benben (御剑飞行)]

按文档准备后,手动执行SQL么?

[来了老弟]

你好，

该参数是为 session 级别，我们会优化一下文档，

运行 sql 文件即可

[benben (御剑飞行)]

[CUR] Q22 - Takes(s): 0.6, Count: 1, TPM: 101.7, Sum(ms): 990, Avg(ms): 990, 95th(ms): 1000, 99th(ms): 1000, 99.9th(ms): 1000

请问上述 COUNT,TPM, CUR/SUM的含义是?

[JaySon-Huang (JaySon-PingCAP)]

for i in {1..22}; do
    tiup bench tpch --count 3 --host 172.16.4.41 -P 4000 --threads 1 --queries=q$i run > $i.log;
done

Count: 执行的次数
Sum: 执行查询语句消耗的时长
Avg: Sum / Count

如果用 tiup bench 看 TPC-H 查询消耗时长可以参照上面的语句写到 shell 脚本中执行（会每个语句执行三次，取平均数）。TPC-H 关注上面三个输出参数即可。

TPM,95th,99th,99.9th 的结果主要适用于 TPC-C 测试。https://baike.baidu.com/item/tpmC

[benben (御剑飞行)]

[tidb@tidb1 ~]$ tiup bench tpch --sf=10 --db tpch_10 --host 172.16.8.36 run -p kaifa#pm Starting component bench: /home/tidb/.tiup/components/bench/v0.0.2/bench tpch --sf=10 --db tpch_10 --host 172.16.8.36 run -p kaifa#pm [CUR] Q1 - Takes(s): 8.9, Count: 1, TPM: 6.7, Sum(ms): 41076, Avg(ms): 41076, 95th(ms): 16000, 99th(ms): 16000, 99.9th(ms): 16000 [CUR] Q2 - Takes(s): 2.0, Count: 1, TPM: 30.1, Sum(ms): 46926, Avg(ms): 46926, 95th(ms): 16000, 99th(ms): 16000, 99.9th(ms): 16000 [SUM] Q1 - Takes(s): 58.9, Count: 1, TPM: 1.0, Sum(ms): 41076, Avg(ms): 41076, 95th(ms): 16000, 99th(ms): 16000, 99.9th(ms): 16000 [SUM] Q2 - Takes(s): 12.0, Count: 1, TPM: 5.0, Sum(ms): 46926, Avg(ms): 46926, 95th(ms): 16000, 99th(ms): 16000, 99.9th(ms): 16000 [CUR] Q3 - Takes(s): 4.3, Count: 1, TPM: 14.0, Sum(ms): 17711, Avg(ms): 17711, 95th(ms): 16000, 99th(ms): 16000, 99.9th(ms): 16000 [CUR] Q4 - Takes(s): 0.2, Count: 1, TPM: 285.0, Sum(ms): 4073, Avg(ms): 4073, 95th(ms): 8000, 99th(ms): 8000, 99.9th(ms): 8000 [SUM] Q1 - Takes(s): 158.9, Count: 1, TPM: 0.4, Sum(ms): 41076, Avg(ms): 41076, 95th(ms): 16000, 99th(ms): 16000, 99.9th(ms): 16000 [SUM] Q2 - Takes(s): 112.0, Count: 1, TPM: 0.5, Sum(ms): 46926, Avg(ms): 46926, 95th(ms): 16000, 99th(ms): 16000, 99.9th(ms): 16000 [SUM] Q3 - Takes(s): 94.3, Count: 1, TPM: 0.6, Sum(ms): 17711, Avg(ms): 17711, 95th(ms): 16000, 99th(ms): 16000, 99.9th(ms): 16000 [SUM] Q4 - Takes(s): 90.2, Count: 1, TPM: 0.7, Sum(ms): 4073, Avg(ms): 4073, 95th(ms): 8000, 99th(ms): 8000, 99.9th(ms): 8000 [mysql] 2020/06/09 08:59:55 packets.go:36: unexpected EOF panic: runtime error: invalid memory address or nil pointer dereference [signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x4f8832]

goroutine 52 [running]: database/sql.(*Rows).close(0x0, 0x0, 0x0, 0x0, 0x0) /usr/local/go/src/database/sql/sql.go:3063 +0x72 database/sql.(*Rows).Close(0x0, 0x30, 0x8beb00) /usr/local/go/src/database/sql/sql.go:3059 +0x33 github.com/pingcap/go-tpc/tpch.Workloader.Run(0xc000198000, 0xe64d80, 0x9c28c0, 0xc0001a6120, 0x0, 0x9b8cc0, 0xc0000af070) /go/pkg/mod/github.com/pingcap/go-tpc@v1.0.4-0.20200511044254-883c1a10d555/tpch/workload.go:154 +0x4d7 main.execute(0x9c28c0, 0xc0001a6120, 0x9c71e0, 0xc0001b0000, 0x8fb1ae, 0x3, 0x0, 0x0, 0x0) /home/jenkins/agent/workspace/bench-tiup-mirror-update/components/bench/misc.go:60 +0x34f main.executeWorkload.func2(0xc0001a2010, 0x9c2880, 0xc0001b2000, 0x9c71e0, 0xc0001b0000, 0x8fb1ae, 0x3, 0x0) /home/jenkins/agent/workspace/bench-tiup-mirror-update/components/bench/misc.go:105 +0xc8 created by main.executeWorkload /home/jenkins/agent/workspace/bench-tiup-mirror-update/components/bench/misc.go:103 +0x16f Error: run /home/tidb/.tiup/components/bench/v0.0.2/bench (wd:/home/tidb/.tiup/data/S1N6hk5) failed: exit status 2

[benben (御剑飞行)]

报错了,麻烦看看具体是啥原因

[yilong (yi888long)]

看下你的内存是不是不够了。

2020/06/09 08:59:55 packets.go:36: unexpected EOF panic: runtime error: invalid memory address or nil pointer dereference [signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x4f8832]

[JaySon-Huang (JaySon-PingCAP)]

1. dmesg -T | grep -i oom 看下有没有错误信息
2. 查看下 TiDB 的日志，相应的时间段有没有什么错误信息

[benben (御剑飞行)]

Jun 9 09:00:03 2020] VM Periodic Tas invoked oom-killer: gfp_mask=0x201da, order=0, oom_score_adj=0 [Tue Jun 9 09:00:03 2020] [] oom_kill_process+0x254/0x3d0 [Tue Jun 9 09:00:03 2020] [ pid ] uid tgid total_vm rss nr_ptes swapents oom_score_adj name [Tue Jun 9 09:00:03 2020] haproxy invoked oom-killer: gfp_mask=0x201da, order=0, oom_score_adj=0 [Tue Jun 9 09:00:03 2020] [] oom_kill_process+0x254/0x3d0 [Tue Jun 9 09:00:03 2020] [ pid ] uid tgid total_vm rss nr_ptes swapents oom_score_adj name [Tue Jun 9 09:00:03 2020] alertmanager invoked oom-killer: gfp_mask=0x201da, order=0, oom_score_adj=0 [Tue Jun 9 09:00:03 2020] [] oom_kill_process+0x254/0x3d0 [Tue Jun 9 09:00:03 2020] [ pid ] uid tgid total_vm rss nr_ptes swapents oom_score_adj name [Tue Jun 9 09:27:42 2020] prometheus invoked oom-killer: gfp_mask=0x201da, order=0, oom_score_adj=0 [Tue Jun 9 09:27:42 2020] [] oom_kill_process+0x254/0x3d0 [Tue Jun 9 09:27:42 2020] [ pid ] uid tgid total_vm rss nr_ptes swapents oom_score_adj name [Tue Jun 9 09:27:42 2020] VM Periodic Tas invoked oom-killer: gfp_mask=0x201da, order=0, oom_score_adj=0 [Tue Jun 9 09:27:42 2020] [] oom_kill_process+0x254/0x3d0 [Tue Jun 9 09:27:42 2020] [ pid ] uid tgid total_vm rss nr_ptes swapents oom_score_adj name

tidb日志没有相应的报错, 9.27是我执行sysbench的时候.

[JaySon-Huang (JaySon-PingCAP)]

看起来像是 prometheus 因为 oom 被.操作系统 kill 了。
有点奇怪，prometheus 被 kill 理论上不影响 SQL 执行。可以看下 TiDB 在当时有没有重启？

另外目前如果需要完整地跑完 TPC-H 10，最好相关的 TiDB 实例独立部署在 32GB 内存机器上：相关issue tidb#14103

[benben (御剑飞行)]

机器有45G,内存,执行过程中一直用free -g监控内存,结果它全用完了,然后就OOM了.

[yilong (yi888long)]

1.请问在操作前，free -g 看下有多少可用 内存

2.请查看tidb.log 是否有重启 ，可以 grep Welcome

3. tiup bench tpch --sf=10 --db tpch_10 麻烦能够把 --sf=10 改为 1 试试吗？