limit the max exec time for nasemi/semi join probe by windtalker · Pull Request #9689 · pingcap/tiflash

windtalker · 2024-12-03T02:37:50Z

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:

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

Release note

None

windtalker · 2024-12-03T09:15:50Z

/test pull-unit-test

windtalker · 2024-12-05T01:41:57Z

/test pull-unit-test

Signed-off-by: xufei <xufeixw@mail.ustc.edu.cn>

yibin87 · 2024-12-05T06:55:48Z

dbms/src/Flash/Pipeline/Schedule/Tasks/Task.h

+    {
+        current_task_timer = nullptr;
+        CurrentMemoryTracker::submitLocalDeltaMemory();
+        current_memory_tracker = nullptr;


A little strange: in beforeExec, we startTraceMemory, while in afterExec, we don't call endTraceMemory.

Good point, I should call endTraceMemory instead of set current_memory_tracker directly.

Signed-off-by: xufei <xufeixw@mail.ustc.edu.cn>

dbms/src/Interpreters/Join.cpp

yibin87

Others LGTM

ti-chi-bot · 2024-12-05T08:19:03Z

[APPROVALNOTIFIER] This PR is APPROVED

This pull-request has been approved by: gengliqi, yibin87

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

The pull request process is described here

Details

Needs approval from an approver in each of these files:

~~OWNERS~~ [gengliqi,yibin87]

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 · 2024-12-05T08:19:06Z

[LGTM Timeline notifier]

Timeline:

2024-12-05 07:24:28.149197335 +0000 UTC m=+1312455.768851843: ☑️ agreed by yibin87.
2024-12-05 08:19:05.496064041 +0000 UTC m=+1315733.115718557: ☑️ agreed by gengliqi.

windtalker · 2024-12-05T08:41:05Z

/test pull-integration-test

ti-chi-bot bot added 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. labels Dec 3, 2024

windtalker added 2 commits December 5, 2024 11:41

limit the max exec time for nasemi/semi join probe

05f8f06

Signed-off-by: xufei <xufeixw@mail.ustc.edu.cn>

limit max exec time for nasemi/semi join probe

5bb88e6

Signed-off-by: xufei <xufeixw@mail.ustc.edu.cn>

yibin87 reviewed Dec 5, 2024

View reviewed changes

address comments

cfc3f43

Signed-off-by: xufei <xufeixw@mail.ustc.edu.cn>

windtalker force-pushed the pipeline_max_exec_time branch from 341869e to cfc3f43 Compare December 5, 2024 07:10

yibin87 reviewed Dec 5, 2024

View reviewed changes

dbms/src/Interpreters/Join.cpp Show resolved Hide resolved

yibin87 approved these changes Dec 5, 2024

View reviewed changes

ti-chi-bot bot added needs-1-more-lgtm Indicates a PR needs 1 more LGTM. approved labels Dec 5, 2024

gengliqi approved these changes Dec 5, 2024

View reviewed changes

ti-chi-bot bot added lgtm and removed needs-1-more-lgtm Indicates a PR needs 1 more LGTM. labels Dec 5, 2024

Merge branch 'master' into pipeline_max_exec_time

da59879

ti-chi-bot bot merged commit a3dee48 into pingcap:master Dec 5, 2024

windtalker deleted the pipeline_max_exec_time branch December 6, 2024 00:09

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

limit the max exec time for nasemi/semi join probe#9689
ti-chi-bot[bot] merged 4 commits intopingcap:masterfrom
windtalker:pipeline_max_exec_time

windtalker commented Dec 3, 2024

Uh oh!

windtalker commented Dec 3, 2024

Uh oh!

windtalker commented Dec 5, 2024

Uh oh!

yibin87 Dec 5, 2024

Uh oh!

windtalker Dec 5, 2024

Uh oh!

windtalker Dec 5, 2024

Uh oh!

Uh oh!

yibin87 left a comment

Uh oh!

ti-chi-bot bot commented Dec 5, 2024

Uh oh!

ti-chi-bot bot commented Dec 5, 2024

Uh oh!

windtalker commented Dec 5, 2024

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

windtalker commented Dec 3, 2024

What problem does this PR solve?

What is changed and how it works?

Check List

Release note

Uh oh!

windtalker commented Dec 3, 2024

Uh oh!

windtalker commented Dec 5, 2024

Uh oh!

yibin87 Dec 5, 2024

Choose a reason for hiding this comment

Uh oh!

windtalker Dec 5, 2024

Choose a reason for hiding this comment

Uh oh!

windtalker Dec 5, 2024

Choose a reason for hiding this comment

Uh oh!

Uh oh!

yibin87 left a comment

Choose a reason for hiding this comment

Uh oh!

ti-chi-bot bot commented Dec 5, 2024

Uh oh!

ti-chi-bot bot commented Dec 5, 2024

[LGTM Timeline notifier]

Uh oh!

windtalker commented Dec 5, 2024

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants