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.

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

Tune the perfomance of hash aggregate operator #7450

Closed
XuHuaiyu opened this issue Aug 21, 2018 · 2 comments
Closed

Tune the perfomance of hash aggregate operator #7450

XuHuaiyu opened this issue Aug 21, 2018 · 2 comments
Assignees
Labels
sig/execution SIG execution type/enhancement The issue or PR belongs to an enhancement.
Milestone

Comments

@XuHuaiyu
Copy link
Contributor

XuHuaiyu commented Aug 21, 2018

After #7268 , all the aggregate evaluation was switched to new the evaluation
framework, but there still remains some optimization to be done to speed up
the evaluation, reduce the memory usage or cache miss ratio. We need to test
the new framework through some benchmarks, and profile the execution process
to find the optimize points.

related issues:
#6952

The details of performance tuning are shown in the following comments, and the
optimization points we could focus on are listed as check list in every comment.

Note: These optimizations are planned to be finished before 2018/09/09 CST,
it would be nice if you can push your pull requests and get them merged before
that deadline. ^_^

@XuHuaiyu XuHuaiyu added type/enhancement The issue or PR belongs to an enhancement. sig/execution SIG execution labels Aug 21, 2018
@XuHuaiyu XuHuaiyu added this to the 2.1 milestone Aug 21, 2018
@XuHuaiyu XuHuaiyu self-assigned this Aug 21, 2018
@shenli shenli changed the title tune the perfomance of hash aggregate operator under new framework Tune the perfomance of hash aggregate operator Aug 21, 2018
@XuHuaiyu
Copy link
Contributor Author

T1:

DataSet row count group count
TPC-H 50G 75,000,000 5,000,000
sql flame graph profile graph
select avg(o_totalprice) from orders group by o_custkey; q1.txt q1-torch.txt

Note: the name suffix of flame graph and profile graph should be changed to .svg

explain:

mysql> desc select avg(o_totalprice) from orders group by o_custkey;
+-----------------------+-------------+------+-------------------------------------------------------------------------+
| id                    | count       | task | operator info                                                           |
+-----------------------+-------------+------+-------------------------------------------------------------------------+
| HashAgg_9             | 4973568.00  | root | group by:col_2, funcs:avg(col_0, col_1)                                 |
| └─TableReader_10      | 4973568.00  | root | data:HashAgg_5                                                          |
|   └─HashAgg_5         | 4973568.00  | cop  | group by:tpch50.orders.o_custkey, funcs:avg(tpch50.orders.o_totalprice) |
|     └─TableScan_8     | 75000000.00 | cop  | table:orders, range:[-inf,+inf], keep order:false                       |
+-----------------------+-------------+------+-------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Optimize point already found:

  • Define HashAggFinalWorker.groupSet as stringSet instead of MVMap
  • Define HashAggIntermData.groupKeys as []string

Through the above optimization, we can avoid the cost of:

  1. 3.77% runtime.stringToByteSliceof CPU cost introduced by HashAggPartialWorker.shuffleIntermData
  2. 0.75% runtime.sliceToString of CPU cost introduced by baseHashAggWorker.getPartialResult
  3. 8.86% MVMap.Get and MVMap.Put of CPU cost introduced by access to MVMap(we may introduce new cost of access to stringSet which would cost less than before)

@XuHuaiyu XuHuaiyu added the help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. label Aug 23, 2018
@XuHuaiyu
Copy link
Contributor Author

T2:

DataSet row count group count
TPC-H 50G 75,000,000 5,000,000
sql flame graph profile graph
select avg(distinct o_totalprice) from orders group by o_custkey; q2.txt q2-torch.txt

Note: the name suffix of flame graph and profile graph should be changed to .svg

explain:

mysql> desc select avg(distinct o_totalprice) from orders group by o_custkey;
+---------------------+-------------+------+----------------------------------------------------------------------------------+
| id                  | count       | task | operator info                                                                    |
+---------------------+-------------+------+----------------------------------------------------------------------------------+
| HashAgg_5           | 4973568.00  | root | group by:tpch50.orders.o_custkey, funcs:avg(distinct tpch50.orders.o_totalprice) |
| └─TableReader_9     | 75000000.00 | root | data:TableScan_8                                                                 |
|   └─TableScan_8     | 75000000.00 | cop  | table:orders, range:[-inf,+inf], keep order:false                                |
+---------------------+-------------+------+----------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Optimize point already found:

  • Define a Datum slice buffer in HashAggExec to replace the definition of vals in HashAggExec.getGroupKey

Through this optimization, we can avoid the cost of:
4.5% runtime.makeslice introduced by the definition of vals in HashAggExec.getGropuKey

  • Define HashAggExec.groupMap as stringSet instead of MVMap

Through this optimization, we can avoid the cost of:
17.5% MVMap.Get and MVMap.Put introduced by the access to MVMap, but it'll bring new cost of runtime.byteSliceToString and access to stringSet, so we need to verify whether this optimization is beneficial.

@XuHuaiyu XuHuaiyu removed the help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. label Aug 29, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
sig/execution SIG execution type/enhancement The issue or PR belongs to an enhancement.
Projects
None yet
Development

No branches or pull requests

1 participant