TiDB 4.0.x use '500 partitions table' "slow query log" generate large files

[buggithubs]

Feature Request

Is your feature request related to a problem? Please describe:

TiDB 4.0.9

CREATE TABLE `order_line` (
  `ol_o_id` int(11) NOT NULL,
  `ol_d_id` int(11) NOT NULL,
  `ol_w_id` int(11) NOT NULL,
  `ol_number` int(11) NOT NULL,
  `ol_i_id` int(11) NOT NULL,
  `ol_supply_w_id` int(11) DEFAULT NULL,
  `ol_delivery_d` datetime DEFAULT NULL,
  `ol_quantity` int(11) DEFAULT NULL,
  `ol_amount` decimal(6,2) DEFAULT NULL,
  `ol_dist_info` char(24) DEFAULT NULL,
  PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY HASH( `ol_w_id` )
PARTITIONS 500

SELECT
  ol_d_id,
  sum (ol_amount)
FROM
  order_line
WHERE
  (ol_w_id, ol_d_id, ol_o_id) IN (
    (...),
    (...),
    (...),
    (...),
    (...),
    (...),
    (...),
    (...),
    (...),
    (...)
  )
GROUP BY
  ol_d_id

# Time: 2020-12-29T08:27:47.492876712+08:00
# Txn_start_ts: 421842561689190469
# User@Host: root[root] @ 172.16.5.102 [172.16.5.102]
# Conn_ID: 29872
# Query_time: 0.389392309
# Parse_time: 0.000005808
# Compile_time: 0.188565627
# Rewrite_time: 0.000441318
# Cop_time: 0.404893155 Process_time: 0.21 Wait_time: 0.015 Request_count: 502 Total_keys: 5210 Process_keys: 200
# DB: db100
# Index_names: [order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY,order_line:PRIMARY]

// Huge content
// Only a small part of the data is shown below

 Plan: tidb_decode_plan('ysdJWDAJM18xNTA4CTAJNjc4LjI0NTgzNjgwAQHwbTIJZGIxMDAub3JkZXJfbGluZS5vbF9kX2lkLCBDb2x1bW4jMTIJMTAJdGltZToxNzUuMjI2MDc5bXMsIGxvb3BzOjUsIENvbmN1cnJlbmN5Ok9GRgkxLjc1NzgxMjUgS0IJTi9BCjEJNl8xNTExUoEAIGdyb3VwIGJ5OmaKACRmdW5jczpzdW0oEZQMMyktPhEMBDIsDSEcZmlyc3Ryb3cVJgw0KS0+XlYANtUAFDEyODM0bS7VACBQYXJ0aWFsQ28Z3BQ0LCBGaW46FAAkCTE2Ljk2ODc1IA3tHDIJMF8xNTEyUu0AHXYcNC45ODEzNzYddgwJTi9BAQQMCjMJNgFEMDUJMAkxLjM1NTg5OTK2KQFO9wAUYW1vdW50PToAM0I6AU43AEEOHTUANK41AF5QAAAJTVIcNy44MDg4Njg5BQQxLKZ7AQgyMi4yaAIMNAkxMiE4NDYJMAkyLjExODU5MjQ5DQEQNwlvcigJAxRhbmQoZXFS7QAAd0X7FDEwNCksIGonACEUFCwgMSksIFpIAABvCUgUNDY5MykpdkwAsnMAADKecwAQKSwgb3L+6gAV6gAznncA/nYAMnYAIYX+YAG6YAEANZ7pACXW

Describe the feature you'd like:

Reduce 'slow query log' document cost;
Association optimization "PD dashboard-slow query"

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

[tiancaiamao]

After we cherry-pick #22061 to 4.0, there will be much fewer partitions after pruning.

[zz-jason]

It's more like an enhancement requirement other than a feature request, I moved it out of the kanban. @qw4990 PTAL whether we can improve the log size once lots of partitions are queried.

[mjonss]

/component tablepartition

[Defined2014]

The problem is related with static prune. We will remove it later. You could follow #56490