tidb 2.1.8升级到4.0.13,性能下降很多

好的,谢谢哈

hello,还有个疑问,针对这个场景,如果开启Coprocessor Cache 有效果吗

1 个赞

1)你这个是在insert写入上耗时比较严重,跟下推计算这个参数的结果感觉不大;
2)这个参数4.x新增参数,4.x是默认关闭的,理论上也是跟2.x是一样的;

1 个赞

1、不是还有一些select的慢查询吗,我看基本都是Coprocessor 执行耗时。
2、如果跟锁机制不一致有关,那就又有一个疑问了,我们已经按照这种升级方案升级了十多套集群了,目前就这套集群升级后耗时变长,跟其他集群比,这个集群唯一的不同就是访问量比较大,比之前升级的集群多一倍这样,所有4.0版本的集群都是统一模板配置,如果仅跟锁机制有关,感觉也说不通

Coprocessor Cache 主要针对数据很少变化的小表 加速查询的

1)找一个执行慢的 ,把完整的执行计划发出来看看。
2)explain analyze的执行计划也发下,注意 analyze会实际执行这个sql语句,对于dml 操作要小心;

mysql> explain analyze SELECT  id,uid,auth_source,real_name,idcard,auth_status,reason_tx,reason_us,info_id,to_uid,order_id,time,datas,face_id,code,ip,idcard_encode,pic_secret_key,extra_data  FROM certification_sequence     WHERE (uid = 4552846565652 AND auth_status IN ('1')) ORDER BY time DESC;
+----------------------------------+---------+---------+-----------+----------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+---------+---------+
| id                               | estRows | actRows | task      | access object                                                        | execution info                                                                                                                                                                                                                   | operator info                                               | memory  | disk    |
+----------------------------------+---------+---------+-----------+----------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+---------+---------+
| Sort_5                           | 2.41    | 1       | root      |                                                                      | time:3.04ms, loops:2                                                                                                                                                                                                             | tdb_spam_oltp.certification_sequence.time:desc              | 8.36 KB | 0 Bytes |
| └─IndexLookUp_13                 | 2.41    | 1       | root      |                                                                      | time:3ms, loops:2, index_task: {total_time: 1.79ms, fetch_handle: 1.78ms, build: 2.33µs, wait: 4.01µs}, table_task: {total_time: 8.32ms, num: 1, concurrency: 4}                                                                 |                                                             | 16.4 KB | N/A     |
|   ├─IndexRangeScan_11(Build)     | 2.41    | 1       | cop[tikv] | table:certification_sequence, index:uid_status_idx(uid, auth_status) | time:1.78ms, loops:3, cop_task: {num: 1, max: 1.72ms, proc_keys: 1, rpc_num: 1, rpc_time: 1.7ms, copr_cache: disabled}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_keys: 2}                        | range:[45465258839310 1,45465258839310 1], keep order:false | N/A     | N/A     |
|   └─TableRowIDScan_12(Probe)     | 2.41    | 1       | cop[tikv] | table:certification_sequence                                         | time:906.4µs, loops:2, cop_task: {num: 1, max: 793.5µs, proc_keys: 1, tot_proc: 1ms, rpc_num: 1, rpc_time: 779.4µs, copr_cache: disabled}, tikv_task:{time:1ms, loops:1}, scan_detail: {total_process_keys: 1, total_keys: 1}    | keep order:false                                            | N/A     | N/A     |
+----------------------------------+---------+---------+-----------+----------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+---------+---------+
4 rows in set (0.01 sec)

mysql> 

查询都是这类sql,dml不是很方便贴执行计划,其实手动执行很快的,10ms内出结果

问题解决了吗?

还在继续排查

1 个赞

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。