select stats_histograms大量txnNotFound

【版本】5.2.1
dashboard查看select的慢SQL有大量 mysql.stats_histograms where table_id = 296的慢SQL,时间主要是Cop_backoff_txnNotFound

Time: 2021-09-14T01:05:29.175239735+08:00

Txn_start_ts: 427701757281566748

Query_time: 5.149068083

Parse_time: 0

Compile_time: 0.000406489

Rewrite_time: 0.00010761

Optimize_time: 0.000176125

Wait_TS: 0.000005566

Cop_time: 5.148211751 Process_time: 0.004 Wait_time: 0.003 Backoff_time: 5.01 Request_count: 3 Process_keys: 184 Total_keys: 1278 Rocksdb_delete_skipped_count: 191 Rocksdb_key_skipped_count: 2154 Rocksdb_block_cache_hit_count: 409 Rocksdb_block_read_count: 2 Rocksdb_block_read_byte: 130981

Index_names: [stats_histograms:tbl]

Is_internal: true

Digest: 1772d6feeaef69bc4752bc98819036e7f725f5e752a8756f269ddcdfe147b24a

Stats: stats_histograms:425945805994328068

Num_cop_tasks: 3

Cop_proc_avg: 0.001333333 Cop_proc_p90: 0.003 Cop_proc_max: 0.003 Cop_proc_addr: 10.161.67.80:20160

Cop_wait_avg: 0.001 Cop_wait_p90: 0.003 Cop_wait_max: 0.003 Cop_wait_addr: 10.161.67.84:20160

Cop_backoff_txnNotFound_total_times: 17 Cop_backoff_txnNotFound_total_time: 5.01 Cop_backoff_txnNotFound_max_time: 5.01 Cop_backoff_txnNotFound_max_addr: 10.161.67.80:20160 Cop_backoff_txnNotFound_avg_time: 5.01 Cop_backoff_txnNotFound_p90_time: 5.01

Mem_max: 37680

Prepared: false

Plan_from_cache: false

Plan_from_binding: false

Has_more_results: false

KV_total: 0.135352848

PD_total: 0.000006281

Backoff_total: 5.01

Write_sql_response_total: 0

Succ: true

Plan: tidb_decode_plan(‘xA2wMAkzXzQJMAk4NAlteXNxbC5zdGF0c19oaXN0b2dyYW1zLnRhYmxlX2lkLCBtViEAIGlzX2luZGV4LF4hAAFNbkEANGRpc3RpbmN0X2NvdW50YkcAGHZlcnNpb25iIAAMbnVsbHpDAAR0bwFlFGxfc2l6ZWJIAABzJQIIdmVyYiIADGZsYWdiHQAcY29ycmVsYXRuqwDwTGxhc3RfYW5hbHl6ZV9wb3MJOTIJdGltZTo1LjE1cywgbG9vcHM6MiwgQ29uY3VycmVuY3k6T0ZGCTEzLjcgS0IJTi9BCjEJMzBfMTAJJb1iQgAlmzRfdGFzazoge3RvdGFsXwVqUCA2MzPCtXMsIGZldGNoX2hhbmRsZQEWCDAuNAUYKGJ1aWxkOiAxLjAyBRAMd2FpdAEPADUBHwx9LCB0RRhKWQANxCRudW06IDEsIGNvGcMcIDV9CTIzLjERwyAyCTQ3XzgJMV8FxAB0AVIAOil/QTRJgQAsCcMQOnRibCgFIkVLWXUIaGlzQV6QKSwgcmFuZ2U6WzI5NiwyOTZdLCBrZWVwIG9yZGVyOmZhbHNlCTFnCDYxOQ3mKWoAMwGnAHARzRG6JG1heDogNTY5LjklAThwcm9jX2tleXM6IDkyLCBBTAETKDogMW1zLCBycGNfETkBDCllEDU2My4xBT48Y29wcl9jYWNoZV9oaXRfckEhFDogMC4wMCFMCGlrdgl+AHsFOAVSCZs4Mn0sIHNjYW5fZGV0YWlsNcUBewhlc3M2kQAEYWwNEHA0NTIsIHJvY2tzZGI6IHtkZWxldGVfc2tpcHBlZGklCDogMCEiAHk+FgAgNDUxLCBibG9jQTEZsWWcIDogMTYsIHJlYS5EAAUPTGJ5dGU6IDAgQnl0ZXN9fX0JTi9BAQQh4wg1Xzl+4wEAa16qAU1NLQxGpwEEMiwppww0LjM2IYcgbWluOiA0LjA1AQ0IYXZnAQ0AMgEMCHA5NQEMDSYEYXglQS0uCDg4LAEgPhMAOeIAMwFCAQ9pBQUPIeUl8QA0Kf0luQwgMTIuBR4AY27vARxSZXNvbHZlTCFkQW4MX3JwY2HufZ8MNS4xNGFmYGJhY2tvZmZ7dHhuTm90Rm91bmQ6IDUuMDEBHV03QQ8lCgAzAashBhgwcywgcDgwCREB/gUaGGl0ZXJzOjNhvwhza3PaXgIEODJBBnpeAggxOTFSYAIMMTcwM2phAggzOTM2YgJByUFxAGJFYkwxMjcuOSBLQn19fQlOL0EJTi9BCg==’)

Plan_digest: 6fa677a433652b855df12b5c197df0c72fd5a4bc516e08a2cd4f0ea160492a3c

select table_id, is_index, hist_id, distinct_count, version, null_count, tot_col_size, stats_ver, flag, correlation, last_analyze_pos from mysql.stats_histograms where table_id = 296;

重试时间长,可以看一下 tidb log 里面有没有报错。应该会有 warning 发一下看看。另外可以通过 tidb 和 tikv-details 监控看看对应的监控数据。