收集统计信息慢

dbaspace · 2022 年1 月 18 日 03:52

执行analyze table 一个表4Y，一个晚上没结束，有没有能够调整参数，加速完成

hey-hoho · 2022 年1 月 18 日 04:52

必须要有，参考示例：
set global tidb_build_stats_concurrency=20; （4）
set global tidb_distsql_scan_concurrency=20; （15）
set global tidb_index_serial_scan_concurrency=2; （1）
根据实际机器配置调整，括号中的是默认值，用完记得调回去。

caiyfc · 2022 年1 月 18 日 05:40

按照楼上的参数更改就行，唯一要注意的就是更改参数后，收集统计信息会占用更多的资源，不要在业务高峰期去做这件事

Kongdom · 2022 年1 月 18 日 05:50

可以通过SHOW ANALYZE status查询进度，先确认有没有进行analyze，然后再调优
https://docs.pingcap.com/zh/tidb/stable/sql-statement-show-analyze-status/#show-analyze-status

dbaspace · 2022 年1 月 19 日 02:06

@hey-hoho @Kongdom @caiyfc 感谢我搞搞看，看看能不能完成

Kongdom · 2022 年1 月 19 日 02:07

caiyfc · 2022 年1 月 19 日 05:57

如果解决了，请在帖子里回复一下

dbaspace · 2022 年1 月 19 日 10:12

还没在线搞呢

dbaspace · 2022 年1 月 20 日 02:25

@Kongdom @caiyfc @hey-hoho 各位大佬，，还是不行的，，，通过show 状态为：

1.17 应该是之前执行的，，已经把连接kill 了，为什么还显示running呢？昨天晚上执行的早上还没成功早上也KILL 了，参数已经调整回来:
set global tidb_build_stats_concurrency=4
set global tidb_distsql_scan_concurrency=15;
set global tidb_index_serial_scan_concurrency=1;

Kongdom · 2022 年1 月 20 日 02:35

使用的是kill tidb 进程号的方式么？

dbaspace · 2022 年1 月 20 日 02:37

是的，这个状态显示应该有问题吧？重启下？

Kongdom · 2022 年1 月 20 日 02:40

环境允许的话，重启一下试试。

另外检查一下系统变量的值 tidb_enable_fast_analyze ，之前遇到过开启这个变量后，导致analyze特别慢。

dbaspace · 2022 年1 月 20 日 02:44

tidb_enable_fast_analyze 这个值为0的

Kongdom · 2022 年1 月 20 日 02:46

哦，那就是没启用，和我上次遇到的不是同一个问题。

hey-hoho · 2022 年1 月 20 日 02:48

你调大了以后的值是多少，参考下这个说明

dbaspace · 2022 年1 月 20 日 02:48

[WARN] [session.go:488] [“can not retry txn”] [label=internal] [error="[domain:1]Information schema is out of date."] [IsBatchInsert=false] [IsPessimistic=false] [InRestrictedSQL=true] [tidb_retry_limit=10] [tidb_disable_txn_auto_retry=true]
[2022/01/20 10:47:13.190 +08:00] [WARN] [session.go:514] [“commit failed”] [“finished txn”=“Txn{state=invalid}”] [error="[domain:1]Information schema is out of date."]
[2022/01/20 10:47:13.190 +08:00] [WARN] [session.go:1034] [“run statement error”] [schemaVersion=8034] [error="[domain:1]Information schema is out of date."] [session="{\ “currDBName”: “”,\ “id”: 0,\ “status”: 2,\ “strictMode”: true,\ “user”: null\ }"]
[2022/01/20 10:47:13.193 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:47:13.194 +08:00] [INFO] [2pc.go:1131] [“2PC clean up done”] [txnStartTS=430610010121961665]
[2022/01/20 10:47:13.693 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:47:14.194 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:47:14.694 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:47:15.194 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:47:15.694 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:47:16.194 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:47:16.694 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:47:17.195 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:47:17.695 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
停止的日志，重新登陆后show 记录没了

dbaspace · 2022 年1 月 20 日 02:50

按这个来的

hey-hoho · 2022 年1 月 20 日 03:00

适当再往上加一点，拿个单表测试下看看

dbaspace · 2022 年1 月 20 日 03:04

刚才重启TIDB-SERVER日志：
[2022/01/20 10:45:49.119 +08:00] [INFO] [server.go:377] [“connection closed”] [conn=30988154]
[2022/01/20 10:45:50.090 +08:00] [INFO] [server.go:377] [“connection closed”] [conn=30965952]
[2022/01/20 10:46:02.999 +08:00] [INFO] [server.go:513] ["[server] kill all connections."]
[2022/01/20 10:46:03.001 +08:00] [INFO] [manager.go:267] [“failed to campaign”] [“owner info”="[ddl] /tidb/ddl/fg/owner owner
Manager 40dc7fb7-89c1-4c5f-b551-efa6ae3c1bd5"] [error=“context canceled”]
[2022/01/20 10:46:03.001 +08:00] [INFO] [manager.go:248] [“break campaign loop, context is done”] [“owner info”="[ddl] /tidb/
ddl/fg/owner ownerManager 40dc7fb7-89c1-4c5f-b551-efa6ae3c1bd5"]
[2022/01/20 10:46:03.002 +08:00] [INFO] [manager.go:292] [“revoke session”] [“owner info”="[ddl] /tidb/ddl/fg/owner ownerMana
ger 40dc7fb7-89c1-4c5f-b551-efa6ae3c1bd5"] []
[2022/01/20 10:46:03.006 +08:00] [INFO] [ddl_worker.go:114] ["[ddl] DDL worker closed"] [worker=“worker 1, tp general”] [“tak
e time”=5.907µs]
[2022/01/20 10:46:03.006 +08:00] [INFO] [ddl_worker.go:114] ["[ddl] DDL worker closed"] [worker=“worker 2, tp add index”] [“t
ake time”=2.697µs]
[2022/01/20 10:46:03.006 +08:00] [INFO] [session_pool.go:85] ["[ddl] closing sessionPool"]
[2022/01/20 10:46:03.006 +08:00] [INFO] [delete_range.go:123] ["[ddl] closing delRange"]
[2022/01/20 10:46:03.006 +08:00] [INFO] [ddl.go:533] ["[ddl] DDL closed"] [ID=40dc7fb7-89c1-4c5f-b551-efa6ae3c1bd5] [“take ti
me”=6.481671ms]
[2022/01/20 10:46:03.006 +08:00] [INFO] [ddl.go:424] ["[ddl] stop DDL"] [ID=40dc7fb7-89c1-4c5f-b551-efa6ae3c1bd5]
[2022/01/20 10:46:03.007 +08:00] [ERROR] [domain.go:763] [“load privilege loop watch channel closed”]
[2022/01/20 10:46:03.007 +08:00] [INFO] [manager.go:267] [“failed to campaign”] [“owner info”="[stats] /tidb/stats/owner owne
rManager 40dc7fb7-89c1-4c5f-b551-efa6ae3c1bd5"] [error=“lost watcher waiting for delete”]
[2022/01/20 10:46:03.007 +08:00] [INFO] [manager.go:239] [“etcd session is done, creates a new one”] [“owner info”="[stats] /
tidb/stats/owner ownerManager 40dc7fb7-89c1-4c5f-b551-efa6ae3c1bd5"]
[2022/01/20 10:46:03.007 +08:00] [ERROR] [domain.go:763] [“load privilege loop watch channel closed”]
[2022/01/20 10:46:03.027 +08:00] [WARN] [domain.go:462] [“reload schema in loop, schema syncer need rewatch”]
[2022/01/20 10:46:03.027 +08:00] [INFO] [domain.go:468] [“reload schema in loop, schema syncer need restart”]
[2022/01/20 10:46:03.027 +08:00] [INFO] [schema_validator.go:97] [“the schema validator stops”]
[2022/01/20 10:46:03.027 +08:00] [INFO] [syncer.go:248] ["[ddl] syncer watch global schema finished"]
[2022/01/20 10:46:03.072 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:03.573 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:04.073 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:04.245 +08:00] [WARN] [manager.go:170] [“failed to new session to etcd”] [ownerInfo="[stats] /tidb/stats/ow
ner ownerManager 40dc7fb7-89c1-4c5f-b551-efa6ae3c1bd5"] [error=“rpc error: code = Canceled desc = grpc: the client connection
is closing”]
[2022/01/20 10:46:04.269 +08:00] [WARN] [manager.go:170] [“failed to new session to etcd”] [ownerInfo="[ddl-syncer] /tidb/ddl
/all_schema_versions/40dc7fb7-89c1-4c5f-b551-efa6ae3c1bd5"] [error=“rpc error: code = Canceled desc = grpc: the client connec
tion is closing”]
[2022/01/20 10:46:04.446 +08:00] [INFO] [manager.go:243] [“break campaign loop, NewSession failed”] [“owner info”="[stats] /t
idb/stats/owner ownerManager 40dc7fb7-89c1-4c5f-b551-efa6ae3c1bd5"] [error=“rpc error: code = Canceled desc = grpc: the clien
t connection is closing”]
[2022/01/20 10:46:04.470 +08:00] [ERROR] [domain.go:478] [“reload schema in loop, schema syncer restart failed”] [error=“rpc
error: code = Canceled desc = grpc: the client connection is closing”]
[2022/01/20 10:46:04.573 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:05.073 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:05.574 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:05.693 +08:00] [INFO] [manager.go:292] [“revoke session”] [“owner info”="[stats] /tidb/stats/owner ownerMan
ager 40dc7fb7-89c1-4c5f-b551-efa6ae3c1bd5"] [error=“rpc error: code = Canceled desc = grpc: the client connection is closing”
]
[2022/01/20 10:46:06.074 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:06.574 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:07.074 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:07.574 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:08.074 +08:00] [WARN] [session.go:488] [“can not retry txn”] [label=internal] [error="[domain:1]Information
schema is out of date."] [IsBatchInsert=false] [IsPessimistic=false] [InRestrictedSQL=true] [tidb_retry_limit=10] [tidb_disa
ble_txn_auto_retry=true]
[2022/01/20 10:46:08.074 +08:00] [WARN] [session.go:514] [“commit failed”] [“finished txn”=“Txn{state=invalid}”] [error="[dom
ain:1]Information schema is out of date."]
[2022/01/20 10:46:08.075 +08:00] [WARN] [session.go:1034] [“run statement error”] [schemaVersion=8034] [error=“previous state
ment: update mysql.stats_meta set version = 430609993043279940, count = count + 3, modify_count = modify_count + 7 where tabl
e_id = 6846: [domain:1]Information schema is out of date.”] [session="{\ “currDBName”: “”,\ “id”: 0,\ “status”:
2,\ “strictMode”: true,\ “user”: null\ }"]
[2022/01/20 10:46:08.081 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:08.096 +08:00] [INFO] [2pc.go:1131] [“2PC clean up done”] [txnStartTS=430609993043279940]
[2022/01/20 10:46:08.582 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:09.082 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:09.582 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:10.082 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:10.582 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:11.082 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:11.583 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:12.083 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:12.240 +08:00] [ERROR] [session.go:1841] [“failed to load common global variables.”]
[2022/01/20 10:46:12.240 +08:00] [WARN] [gc_worker.go:211] ["[gc worker] check leader"] [error=“session pool closed”]
[2022/01/20 10:46:12.583 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:13.083 +08:00] [WARN] [session.go:488] [“can not retry txn”] [label=internal] [error="[domain:1]Information
schema is out of date."] [IsBatchInsert=false] [IsPessimistic=false] [InRestrictedSQL=true] [tidb_retry_limit=10] [tidb_disa
ble_txn_auto_retry=true]
[2022/01/20 10:46:13.083 +08:00] [WARN] [session.go:514] [“commit failed”] [“finished txn”=“Txn{state=invalid}”] [error="[dom
ain:1]Information schema is out of date."]
[2022/01/20 10:46:13.084 +08:00] [WARN] [session.go:1034] [“run statement error”] [schemaVersion=8034] [error="[domain:1]Info
rmation schema is out of date."] [session="{\ “currDBName”: “”,\ “id”: 0,\ “status”: 2,\ “strictMode”: true
,\ “user”: null\ }"]
[2022/01/20 10:46:13.087 +08:00] [INFO] [2pc.go:1131] [“2PC clean up done”] [txnStartTS=430609994367107178]
[2022/01/20 10:46:13.087 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:13.588 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:14.088 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:14.588 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:15.088 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:15.588 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:16.089 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:16.589 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:17.089 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:17.589 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:18.089 +08:00] [WARN] [session.go:488] [“can not retry txn”] [label=internal] [error="[domain:1]Information
schema is out of date."] [IsBatchInsert=false] [IsPessimistic=false] [InRestrictedSQL=true] [tidb_retry_limit=10] [tidb_disa
ble_txn_auto_retry=true]
[2022/01/20 10:46:18.089 +08:00] [WARN] [session.go:514] [“commit failed”] [“finished txn”=“Txn{state=invalid}”] [error="[dom
ain:1]Information schema is out of date."]
[2022/01/20 10:46:18.090 +08:00] [WARN] [session.go:1034] [“run statement error”] [schemaVersion=8034] [error="[domain:1]Info
rmation schema is out of date."] [session="{\ “currDBName”: “”,\ “id”: 0,\ “status”: 2,\ “strictMode”: true
,\ “user”: null\ }"]
[2022/01/20 10:46:18.090 +08:00] [INFO] [2pc.go:1131] [“2PC clean up done”] [txnStartTS=430609995677827383]
[2022/01/20 10:46:18.093 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:18.594 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:19.094 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:19.594 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:20.094 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:20.594 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:21.094 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:21.594 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:22.095 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:22.595 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:23.095 +08:00] [WARN] [session.go:488] [“can not retry txn”] [label=internal] [error="[domain:1]Information
schema is out of date."] [IsBatchInsert=false] [IsPessimistic=false] [InRestrictedSQL=true] [tidb_retry_limit=10] [tidb_disa
ble_txn_auto_retry=true]
[2022/01/20 10:46:23.095 +08:00] [WARN] [session.go:514] [“commit failed”] [“finished txn”=“Txn{state=invalid}”] [error="[dom
ain:1]Information schema is out of date."]
[2022/01/20 10:46:23.095 +08:00] [WARN] [session.go:1034] [“run statement error”] [schemaVersion=8034] [error="[domain:1]Info
rmation schema is out of date."] [session="{\ “currDBName”: “”,\ “id”: 0,\ “status”: 2,\ “strictMode”: true
,\ “user”: null\ }"]
[2022/01/20 10:46:23.101 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:23.101 +08:00] [INFO] [2pc.go:1131] [“2PC clean up done”] [txnStartTS=430609996988547448]
[2022/01/20 10:46:23.601 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:24.101 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:24.601 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:25.101 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:25.601 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:26.102 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:26.602 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:27.102 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:27.602 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:28.102 +08:00] [WARN] [session.go:488] [“can not retry txn”] [label=internal] [error="[domain:1]Information
schema is out of date."] [IsBatchInsert=false] [IsPessimistic=false] [InRestrictedSQL=true] [tidb_retry_limit=10] [tidb_disa
ble_txn_auto_retry=true]
[2022/01/20 10:46:28.102 +08:00] [WARN] [session.go:514] [“commit failed”] [“finished txn”=“Txn{state=invalid}”] [error="[dom
ain:1]Information schema is out of date."]
[2022/01/20 10:46:28.103 +08:00] [WARN] [session.go:1034] [“run statement error”] [schemaVersion=8034] [error="[domain:1]Info
rmation schema is out of date."] [session="{\ “currDBName”: “”,\ “id”: 0,\ “status”: 2,\ “strictMode”: true
,\ “user”: null\ }"]
[2022/01/20 10:46:28.115 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]
[2022/01/20 10:46:28.117 +08:00] [INFO] [2pc.go:1131] [“2PC clean up done”] [txnStartTS=430609998299267465]
[2022/01/20 10:46:28.615 +08:00] [INFO] [schema_validator.go:197] [“the schema validator stopped before checking”]

恢复后这个代理没发现异常，业务正常使用.

dbaspace · 2022 年1 月 20 日 03:11

@Kongdom @hey-hoho @caiyfc 在想一个问题，从3版本升级到4版本，能够把3版本的统计信息导入到4版本？不知道你们这个干过没