tpch 100g 加载失败

🪐 TiDB 技术问题 性能调优
1

用的版本是5.0.0-rc 版本
3种方式都失败

  1. https://github.com/pingcap/tidb-bench
  2. tiup bench tpch prepare
  3. 官方的tpch 的加载程序, 错误和方式1 是一样的

方式1:
我用https://github.com/pingcap/tidb-bench 的源码进行加载tidb, 出现大量

mysql -h tidb -P 4000 -u root -pxxxxx -D tpch < dss.sql
mysql -h tidb -P 4000 -u root -pxxxxx --local-infile=1 -D tpch < NATION.sql
mysql -h tidb -P 4000 -u root -pxxxxx --local-infile=1 -D tpch < REGION.sql
mysql -h tidb -P 4000 -u root -pxxxxx --local-infile=1 -D tpch < PART.sql
ERROR 8004 (HY000) at line 1: Transaction is too large, size: 104857637

方式2: 参考
https://docs.pingcap.com/zh/tidb/stable/v4.0-performance-benchmarking-with-tpch
tiup bench tpch prepare
–host tidb --port 4000 --db tpch100 --password xxxx --user root
–sf 100
–tiflash
–analyze --tidb_build_stats_concurrency 8 --tidb_distsql_scan_concurrency 30

Got signal [hangup] again to exit.
[mysql] 2021/02/23 00:33:31 connection.go:310: invalid connection
Component bench exit with error: exit status 1
Starting component bench: /root/.tiup/components/bench/v1.3.2/tiup-bench tpch --sf=100 --check=true run -D tpch -H tidb -P 4000 -U root -p xxxxx
[Current] Q1: 0.00s
[Current] Q10: 0.00s
[Current] Q2: 34.41s
[Current] Q3: 0.01s
[Current] Q4: 0.01s
[Current] Q5: 0.01s
[Current] Q6: 0.00s

数据明显不全,
customer
15000000
region
5
nation
25
supplier
1000000
part
4862976
partsupp
19452928
orders
0
lineitem
0

用的tidb 是32core128g, tikv 是3台16core/64, tiflash 也是16core/64g 内存, 每台虚拟机陪本地ssd 磁盘.

set global tidb_allow_batch_cop = 1;
set session tidb_opt_distinct_agg_push_down = 1;
set global tidb_distsql_scan_concurrency = 30;
set global tidb_projection_concurrency = 16;
set global tidb_hashagg_partial_concurrency = 16;
set global tidb_hashagg_final_concurrency = 16;
set global tidb_hash_join_concurrency = 16;
set global tidb_index_lookup_concurrency = 16;
set global tidb_index_lookup_join_concurrency = 16;
2
  1. Transaction is too large 的错误解释：[FAQ] tidb 导入大量数据提示 8004 - Transaction is too large, size: xxx
  2. invalid connection 这个可以检查一下 tidb.log 日志，看下有没有报错，或者 grep -i welcome tidb.log 看下 tidb 节点有没有重启的情况
  3. 集群的配置是怎么样的？
3

tpch 是load 一个大文件, 如果是tpch 100g, 最大的文件都是76g

设置autocommit=1/autocommit=0 都试过

依旧是失败

4

mysql 都直接load 就搞定了

5
  1. invalid connection 这个可以检查一下 tidb.log 日志，看下有没有报错，或者 grep -i welcome tidb.log 看下 tidb 节点有没有重启的情况
  2. 集群的配置是怎么样的？

这两个点可以确认一下吗？

6

修改tidb tansaction 的大小, 发现没有效果, 把加载文件进行split 后, 依旧报文件太大,

ERROR 8004 (HY000) at line 1: Transaction is too large, size: 104857637

server_configs:
  tidb:
    binlog.enable: false
    log.level: error
    mem-quota-query: 10737418240
    performance.committer-concurrency: 32
    prepared-plan-cache.capacity: 400
    prepared-plan-cache.enabled: true
    tikv-client.grpc-connection-count: 8
    txn-total-size-limit: 10485760000

修改配置后, 重启了tidb server

7

welcome log 从哪里找?
是客户端这边, 还是服务器那边

8
  1. 修改配置之后重启 tidb-server 是通过 tiup cluster restart 重启的还是通过 tiup cluster reload 重启的？
  2. 报错依然是 104857637 这个数值吗？
  3. welcome log 是在 tidb.log 中的，在 tidb-server 部署的机器上
9

reload, 报错依然是104857637

10
  1. invalid connection 这个可以检查一下 tidb.log 日志，看下有没有报错，或者 grep -i welcome tidb.log 看下 tidb 节点有没有重启的情况
  2. 集群的配置是怎么样的？

这两个点麻烦确认一下，官方文档是用 tiup bench tpch prepare 方式导入数据并进行性能测试的：https://docs.pingcap.com/zh/tidb/stable/v4.0-performance-benchmarking-with-tpch#测试目的
所以先定位一下这种情况下报错的原因。

11

看了一下你 txn-total-size-limit 这个参数并没有修改成功，要修改成功的话正确的写法应该是 performance.txn-total-size-limit: 10485760000，麻烦调整之后再修改一下配置。

但是 tiup cluster reload 的时候并没有报错，这是 tiup 的一个 bug，待修复：https://github.com/pingcap/tiup/issues/1173

12

使用tiup bench 来加载时
在tidb.log/tidb_stderr.log 没有任何错误

13

那目前数据导入成功了么？

14

修改performance.txn-total-size-limit: 10485760000 后, reload 后, 查看了tidb 机器上, 新配置已经在了

tiup bench tpch 还是相同的错误, tidb 机器上依旧没有任何error 提示.

15

提供一下 tidb.log 日志看下

16

在客户端

generating customers table
generate customers table done
generating suppliers table
generate suppliers table done
generating part/partsupplier tables
generate part/partsupplier tables done
generating orders/lineitem tables
Got signal hangup (Component: bench ; PID: 9616)

Got signal [hangup] to exit.
[mysql] 2021/03/04 00:40:01 connection.go:310: invalid connection
execute prepare failed, err fail to generate orders.tbl, err: context canceled
panic: context canceled

goroutine 66 [running]:
github.com/pingcap/go-tpc/pkg/workload.NewTpcState(0x109f6c0, 0xc00011d380, 0xc00023c000, 0x0)
        github.com/pingcap/go-tpc@v1.0.4-0.20201106030831-2cc8c0ab3409/pkg/workload/base.go:28 +0x301
github.com/pingcap/go-tpc/tpch.Workloader.InitThread(0xc00023c000, 0x18e5bc0, 0xc00021c5a0, 0x109f6c0, 0xc00011d380, 0x0, 0x0, 0x0)
        github.com/pingcap/go-tpc@v1.0.4-0.20201106030831-2cc8c0ab3409/tpch/workload.go:81 +0x54
main.checkPrepare.func1(0xc007423414, 0x10a9b40, 0xc0002128a0, 0xc0074258a0, 0x0)
        github.com/pingcap/tiup@/components/bench/misc.go:25 +0xa9
created by main.checkPrepare
        github.com/pingcap/tiup@/components/bench/misc.go:22 +0x119
Component `bench` exit with error: exit status 2
17

tidb.log

[2021/03/03 19:27:02.314 +08:00] [INFO] [printer.go:33] ["Welcome to TiDB."] ["Release Version"=v5.0.0-rc] [Edition=Community] ["Git Commit Hash"=fd4437dde445012f5b75e5d4f336433cb076c1ee] ["Git Branch"=heads/refs/tags/v5.0.0-rc] ["UTC Build Time"="2021-01-12 06:00:03"] [GoVersion=go1.13] ["Race Enabled"=false] ["Check Table Before Drop"=false] ["TiKV Min Version"=v3.0.0-60965b006877ca7234adaced7890d7b029ed1306]
[2021/03/03 19:27:02.314 +08:00] [INFO] [trackerRecorder.go:28] ["Mem Profile Tracker started"]
[2021/03/03 19:27:02.314 +08:00] [INFO] [printer.go:47] ["loaded config"] [config="{\"host\":\"0.0.0.0\",\"advertise-address\":\"tidb\",\"port\":4000,\"cors\":\"\",\"store\":\"tikv\",\"path\":\"tipd:2379\",\"socket\":\"\",\"lease\":\"45s\",\"run-ddl\":true,\"split-table\":true,\"token-limit\":1000,\"oom-use-tmp-storage\":true,\"tmp-storage-path\":\"/tmp/0_tidb/MC4wLjAuMDo0MDAwLzAuMC4wLjA6MTAwODA=/tmp-storage\",\"oom-action\":\"cancel\",\"mem-quota-query\":10737418240,\"tmp-storage-quota\":-1,\"enable-streaming\":false,\"enable-batch-dml\":false,\"lower-case-table-names\":2,\"server-version\":\"\",\"log\":{\"level\":\"error\",\"format\":\"text\",\"disable-timestamp\":null,\"enable-timestamp\":null,\"disable-error-stack\":null,\"enable-error-stack\":null,\"file\":{\"filename\":\"/data/tidb-deploy/tidb-4000/log/tidb.log\",\"max-size\":300,\"max-days\":0,\"max-backups\":0},\"enable-slow-log\":true,\"slow-query-file\":\"log/tidb_slow_query.log\",\"slow-threshold\":300,\"expensive-threshold\":10000,\"query-log-max-len\":4096,\"record-plan-in-slow-log\":1},\"security\":{\"skip-grant-table\":false,\"ssl-ca\":\"\",\"ssl-cert\":\"\",\"ssl-key\":\"\",\"require-secure-transport\":false,\"cluster-ssl-ca\":\"\",\"cluster-ssl-cert\":\"\",\"cluster-ssl-key\":\"\",\"cluster-verify-cn\":null,\"spilled-file-encryption-method\":\"plaintext\"},\"status\":{\"status-host\":\"0.0.0.0\",\"metrics-addr\":\"\",\"status-port\":10080,\"metrics-interval\":15,\"report-status\":true,\"record-db-qps\":false},\"performance\":{\"max-procs\":0,\"max-memory\":0,\"server-memory-quota\":0,\"memory-usage-alarm-ratio\":0.8,\"stats-lease\":\"3s\",\"stmt-count-limit\":5000,\"feedback-probability\":0.05,\"query-feedback-limit\":512,\"pseudo-estimate-ratio\":0.8,\"force-priority\":\"NO_PRIORITY\",\"bind-info-lease\":\"3s\",\"txn-entry-size-limit\":6291456,\"txn-total-size-limit\":10485760000,\"tcp-keep-alive\":true,\"cross-join\":true,\"run-auto-analyze\":true,\"agg-push-down-join\":false,\"committer-concurrency\":32,\"max-txn-ttl\":3600000,\"mem-profile-interval\":\"1m\",\"index-usage-sync-lease\":\"0s\",\"gogc\":100},\"prepared-plan-cache\":{\"enabled\":true,\"capacity\":400,\"memory-guard-ratio\":0.1},\"opentracing\":{\"enable\":false,\"rpc-metrics\":false,\"sampler\":{\"type\":\"const\",\"param\":1,\"sampling-server-url\":\"\",\"max-operations\":0,\"sampling-refresh-interval\":0},\"reporter\":{\"queue-size\":0,\"buffer-flush-interval\":0,\"log-spans\":false,\"local-agent-host-port\":\"\"}},\"proxy-protocol\":{\"networks\":\"\",\"header-timeout\":5},\"tikv-client\":{\"grpc-connection-count\":8,\"grpc-keepalive-time\":10,\"grpc-keepalive-timeout\":3,\"grpc-compression-type\":\"none\",\"commit-timeout\":\"41s\",\"async-commit\":{\"keys-limit\":256,\"total-key-size-limit\":4096,\"SafeWindow\":2000000000,\"AllowedClockDrift\":500000000},\"max-batch-size\":128,\"overload-threshold\":200,\"max-batch-wait-time\":0,\"batch-wait-size\":8,\"enable-chunk-rpc\":true,\"region-cache-ttl\":600,\"store-limit\":0,\"store-liveness-timeout\":\"5s\",\"copr-cache\":{\"enable\":true,\"capacity-mb\":1000,\"admission-max-ranges\":500,\"admission-max-result-mb\":10,\"admission-min-process-ms\":5},\"ttl-refreshed-txn-size\":33554432},\"binlog\":{\"enable\":false,\"ignore-error\":false,\"write-timeout\":\"15s\",\"binlog-socket\":\"\",\"strategy\":\"range\"},\"compatible-kill-query\":false,\"plugin\":{\"dir\":\"\",\"load\":\"\"},\"pessimistic-txn\":{\"max-retry-count\":256},\"check-mb4-value-in-utf8\":true,\"max-index-length\":3072,\"index-limit\":64,\"table-column-count-limit\":1017,\"graceful-wait-before-shutdown\":0,\"alter-primary-key\":false,\"treat-old-version-utf8-as-utf8mb4\":true,\"enable-table-lock\":false,\"delay-clean-table-lock\":0,\"split-region-max-num\":1000,\"stmt-summary\":{\"enable\":true,\"enable-internal-query\":false,\"max-stmt-count\":200,\"max-sql-length\":4096,\"refresh-interval\":1800,\"history-size\":24},\"repair-mode\":false,\"repair-table-list\":[],\"isolation-read\":{\"engines\":[\"tikv\",\"tiflash\",\"tidb\"]},\"max-server-connections\":0,\"new_collations_enabled_on_first_bootstrap\":false,\"experimental\":{\"allow-expression-index\":false,\"enable-global-kill\":false},\"enable-collect-execution-info\":true,\"skip-register-to-dashboard\":false,\"enable-telemetry\":true,\"labels\":{},\"enable-global-index\":false,\"deprecate-integer-display-length\":false,\"txn-scope\":\"global\",\"enable-enum-length-limit\":true,\"stores-refresh-interval\":60,\"enable-tcp4-only\":false}"]
18

tidb_stderr.log 为空

19

把tpch的数据文件切分为100w 一个文件, 修改修改performance.txn-total-size-limit: 10485760000 后

用https://github.com/pingcap/tidb-bench 的工具可以加载了, 但是这个工具, 没有enable tiflash

20

设置了对应表的 tifalsh 副本的话，往 tidb 灌入数据的时候，会自动同步到 tiflash 的，没有 enable tiflash 问题不大。