Analyze Slow Queries

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: analyze慢

| username: yulei7633

The default value of tidb_dml_batch_size is 2000. You can adjust this parameter according to your needs.

| username: Billmay表妹 | Original post link

Supplement:

[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

| username: zhanggame1 | Original post link

Is the amount of data very large?

| username: 大飞哥online | Original post link

How much data is in the table?

| username: WalterWj | Original post link

Is it a partitioned table?

| username: 路在何chu | Original post link

This answer is so well-structured.

| username: 大飞哥online | Original post link

gpt :face_with_peeking_eye:

| username: yulei7633 | Original post link

The amount of data is not large, basically there is no data. The main reason is that the table structure is too complex, with 2500 fields.

| username: yulei7633 | Original post link

128 partitions

| username: TiDBer_小阿飞 | Original post link

128 partitions, 2500 columns, this…
Are you using TiDB’s RANGE column partitioning for your partitioning rules?
Try analyzing a single partition first? ALTER TABLE XXX ANALYZE PARTITION p1;

| username: tidb菜鸟一只 | Original post link

You can also collect and compile statistics separately for each position. You can try it first to see if it’s slow or not.

| username: yulei7633 | Original post link

PARTITION BY HASH (form_id) PARTITIONS 128; This is the partitioning rule.
Okay, I used to analyze the whole table, I’ll try the duration for a single partition, thank you.

| username: yulei7633 | Original post link

Okay.

| username: WalterWj | Original post link

There are some issues with the global stats merge for partitioned tables, and it will be optimized later.

| username: RenlySir | Original post link

Here are some temporary optimization methods:

  • A better way to do the analyze jobs is:
    1. Set global tidb_partition_prune_mode = ‘static’. In the static mode, when we analyze one partition, we don’t merge stats. So it will be fast.
    2. Run the analyze script to collect stats for each non-partitioned table and each partition.
    3. Set global tidb_partition_prune_mode = ‘dynamic’.
    4. Then for each partitioned table, analyze one partition of it to trigger merging partition stats to global stats.
| username: residentevil | Original post link

The statistics collection in TiDB is quite different from native MySQL. The health of the table changes very quickly, especially when there are frequent writes online, which leads to inaccurate execution plans. :sweat_smile:

| username: zhanggame1 | Original post link

Use optimization measures with caution if they have not been thoroughly tested.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.