Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

limit the max exec time for nasemi/semi join probe #9689

Open
wants to merge 2 commits into
base: master
Choose a base branch
from
Open

limit the max exec time for nasemi/semi join probe #9689

wants to merge 2 commits into from
+250 −27

Conversation

windtalker
Copy link
Contributor

What problem does this PR solve?

Issue Number: close #9433

Problem Summary:

What is changed and how it works?

Limit the max execution time for nasemi/semi join probe.

Testing result:
Testing data set

mysql> desc t1;
+-------+------+------+------+---------+-------+
| Field | Type | Null | Key  | Default | Extra |
+-------+------+------+------+---------+-------+
| id    | int  | NO   |      | NULL    |       |
| value | int  | YES  |      | NULL    |       |
+-------+------+------+------+---------+-------+
2 rows in set (0.00 sec)

mysql> desc t2;
+-------+------+------+------+---------+-------+
| Field | Type | Null | Key  | Default | Extra |
+-------+------+------+------+---------+-------+
| id    | int  | NO   |      | NULL    |       |
| value | int  | YES  |      | NULL    |       |
+-------+------+------+------+---------+-------+
2 rows in set (0.00 sec)

mysql> select id, value, count(*) from t1 group by id, value;
+----+-------+----------+
| id | value | count(*) |
+----+-------+----------+
|  1 |     2 |   524288 |
+----+-------+----------+
1 row in set (0.05 sec)

mysql> select id, value, count(*) from t2 group by id, value;
+----+-------+----------+
| id | value | count(*) |
+----+-------+----------+
|  1 |     3 |    16384 |
+----+-------+----------+
1 row in set (0.04 sec)
mysql> desc t1_null;
+-------+------+------+------+---------+-------+
| Field | Type | Null | Key  | Default | Extra |
+-------+------+------+------+---------+-------+
| id    | int  | YES  |      | NULL    |       |
| value | int  | YES  |      | NULL    |       |
+-------+------+------+------+---------+-------+
2 rows in set (0.00 sec)

mysql> desc t2_null;
+-------+------+------+------+---------+-------+
| Field | Type | Null | Key  | Default | Extra |
+-------+------+------+------+---------+-------+
| id    | int  | YES  |      | NULL    |       |
| value | int  | YES  |      | NULL    |       |
+-------+------+------+------+---------+-------+
2 rows in set (0.00 sec)

mysql> select id, value, count(*) from t1_null group by id, value;
+------+-------+----------+
| id   | value | count(*) |
+------+-------+----------+
|    1 |     2 |   524288 |
+------+-------+----------+
1 row in set (0.04 sec)

mysql> select id, value, count(*) from t2_null group by id, value;
+------+-------+----------+
| id   | value | count(*) |
+------+-------+----------+
|    1 |     3 |    16384 |
+------+-------+----------+
1 row in set (0.04 sec)

Testing sql
semi join:

mysql> explain select count(*) from t1 t1 where id not in (select id from t2 t2 where t1.value > t2.value);
+--------------------------------------------+-----------+--------------+---------------+-------------------------------------------------------------------------------------------------+
| id                                         | estRows   | task         | access object | operator info                                                                                   |
+--------------------------------------------+-----------+--------------+---------------+-------------------------------------------------------------------------------------------------+
| HashAgg_38                                 | 1.00      | root         |               | funcs:count(Column#8)->Column#7                                                                 |
| └─TableReader_40                           | 1.00      | root         |               | MppVersion: 2, data:ExchangeSender_39                                                           |
|   └─ExchangeSender_39                      | 1.00      | mpp[tiflash] |               | ExchangeType: PassThrough                                                                       |
|     └─HashAgg_14                           | 1.00      | mpp[tiflash] |               | funcs:count(1)->Column#8                                                                        |
|       └─Projection_37                      | 419430.40 | mpp[tiflash] |               | test.t1.id                                                                                      |
|         └─HashJoin_36                      | 419430.40 | mpp[tiflash] |               | anti semi join, equal:[eq(test.t1.id, test.t2.id)], other cond:gt(test.t1.value, test.t2.value) |
|           ├─ExchangeReceiver_22(Build)     | 16384.00  | mpp[tiflash] |               |                                                                                                 |
|           │ └─ExchangeSender_21            | 16384.00  | mpp[tiflash] |               | ExchangeType: Broadcast, Compression: FAST                                                      |
|           │   └─TableFullScan_20           | 16384.00  | mpp[tiflash] | table:t2      | keep order:false, stats:pseudo                                                                  |
|           └─TableFullScan_19(Probe)        | 524288.00 | mpp[tiflash] | table:t1      | keep order:false, stats:pseudo                                                                  |
+--------------------------------------------+-----------+--------------+---------------+-------------------------------------------------------------------------------------------------+

na-semi join:

mysql> explain select count(*) from t1_null t1 where id not in (select id from t2_null t2 where t1.value > t2.value);
+--------------------------------------------+-----------+--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------+
| id                                         | estRows   | task         | access object | operator info                                                                                                                  |
+--------------------------------------------+-----------+--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_38                                 | 1.00      | root         |               | funcs:count(Column#8)->Column#7                                                                                                |
| └─TableReader_40                           | 1.00      | root         |               | MppVersion: 2, data:ExchangeSender_39                                                                                          |
|   └─ExchangeSender_39                      | 1.00      | mpp[tiflash] |               | ExchangeType: PassThrough                                                                                                      |
|     └─HashAgg_14                           | 1.00      | mpp[tiflash] |               | funcs:count(1)->Column#8                                                                                                       |
|       └─Projection_37                      | 419430.40 | mpp[tiflash] |               | test.t1_null.id                                                                                                                |
|         └─HashJoin_36                      | 419430.40 | mpp[tiflash] |               | Null-aware anti semi join, equal:[eq(test.t1_null.id, test.t2_null.id)], other cond:gt(test.t1_null.value, test.t2_null.value) |
|           ├─ExchangeReceiver_22(Build)     | 16384.00  | mpp[tiflash] |               |                                                                                                                                |
|           │ └─ExchangeSender_21            | 16384.00  | mpp[tiflash] |               | ExchangeType: Broadcast, Compression: FAST                                                                                     |
|           │   └─TableFullScan_20           | 16384.00  | mpp[tiflash] | table:t2      | keep order:false, stats:pseudo                                                                                                 |
|           └─TableFullScan_19(Probe)        | 524288.00 | mpp[tiflash] | table:t1      | keep order:false, stats:pseudo                                                                                                 |
+--------------------------------------------+-----------+--------------+---------------+--------------------------------------------------------------------------------------------------------------------------------+

The overall sql performance: almost the same

semi join na semi join
before this pr ~ 12.8s ~ 23.2s
after this pr ~12.8s ~23.2s

Max task execution time per round:
Screenshot 2024-12-03 at 10 30 42

As you can see the p999 cpu execution time per round is reduced greatly from 40s(na semi join)/20s(semi join) to 620ms for both na semi jon and semi join

Check List

Tests

  • Unit test
  • Integration test
  • Manual test (add detailed scripts or steps below)
  • No code

Side effects

  • Performance regression: Consumes more CPU
  • Performance regression: Consumes more Memory
  • Breaking backward compatibility

Documentation

  • Affects user behaviors
  • Contains syntax changes
  • Contains variable changes
  • Contains experimental features
  • Changes MySQL compatibility

Release note

None

@ti-chi-bot ti-chi-bot bot added the release-note-none Denotes a PR that doesn't merit a release note. label Dec 3, 2024
@ti-chi-bot ti-chi-bot
Copy link
Contributor

ti-chi-bot bot commented Dec 3, 2024

[APPROVALNOTIFIER] This PR is NOT APPROVED

This pull-request has been approved by:
Once this PR has been reviewed and has the lgtm label, please ask for approval from windtalker, ensuring that each of them provides their approval before proceeding. For more information see the Code Review Process.

The full list of commands accepted by this bot can be found here.

Needs approval from an approver in each of these files:

Approvers can indicate their approval by writing /approve in a comment
Approvers can cancel approval by writing /approve cancel in a comment

@ti-chi-bot ti-chi-bot bot added the size/XL Denotes a PR that changes 500-999 lines, ignoring generated files. label Dec 3, 2024
@windtalker
Copy link
Contributor Author

/test pull-unit-test

@ti-chi-bot ti-chi-bot
Copy link
Contributor

ti-chi-bot bot commented Dec 3, 2024

@windtalker: The following test failed, say /retest to rerun all failed tests or /retest-required to rerun all mandatory failed tests:

Test name Commit Details Required Rerun command
pull-unit-test 341869e link true /test pull-unit-test

Full PR test history. Your PR dashboard.

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository. I understand the commands that are listed here.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers
No reviews
Assignees
No one assigned
Labels
release-note-none Denotes a PR that doesn't merit a release note. size/XL Denotes a PR that changes 500-999 lines, ignoring generated files.
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.

task execution time should have an upper limit in pipeline model
1 participant
@windtalker