You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "Jibing-Li (via GitHub)" <gi...@apache.org> on 2024/02/27 07:32:01 UTC
[PR] [improvement](statistics)Improve show column stats performance. (#31298) [doris]
Jibing-Li opened a new pull request, #31458:
URL: https://github.com/apache/doris/pull/31458
Use table id to query statistics table while show all columns stats for the table. Before, we were using id, which will execute one sql for each column. Now, we use table id to fetch result of all columns with one sql.
backport https://github.com/apache/doris/pull/31298
<!--Describe your changes.-->
## Further comments
If this is a relatively large or complex change, kick off the discussion at [dev@doris.apache.org](mailto:dev@doris.apache.org) by explaining why you chose the solution you did and what alternatives you considered, etc...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [improvement](statistics)Improve show column stats performance. (#31298) [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #31458:
URL: https://github.com/apache/doris/pull/31458#issuecomment-1965970964
<details>
<summary>TPC-H: <b>Total hot run time: 50291 ms</b></summary>
```
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit fbb5972e4bbae240fd33e15421af7e6fe1d266e2, data reload: false
------ Round 1 ----------------------------------
q1 17647 4402 4357 4357
q2 2031 154 146 146
q3 10288 1955 1946 1946
q4 10145 1263 1334 1263
q5 8430 4047 4063 4047
q6 229 121 122 121
q7 2034 1579 1616 1579
q8 9287 2741 2741 2741
q9 11349 10799 10539 10539
q10 8623 3552 3557 3552
q11 419 250 250 250
q12 460 306 306 306
q13 18344 3964 4061 3964
q14 364 336 330 330
q15 512 457 463 457
q16 694 607 591 591
q17 1127 964 1006 964
q18 7290 6822 6833 6822
q19 1677 1554 1511 1511
q20 548 293 319 293
q21 4478 4132 4120 4120
q22 515 392 405 392
Total cold run time: 116491 ms
Total hot run time: 50291 ms
----- Round 2, with runtime_filter_mode=off -----
q1 4351 4305 4334 4305
q2 317 228 221 221
q3 4187 4141 4146 4141
q4 2752 2740 2755 2740
q5 7379 7297 7262 7262
q6 234 121 119 119
q7 3193 2827 2850 2827
q8 4388 4483 4491 4483
q9 17583 17481 17195 17195
q10 4238 4228 4284 4228
q11 752 697 703 697
q12 1028 836 818 818
q13 6491 3769 3749 3749
q14 436 419 424 419
q15 494 450 447 447
q16 766 707 685 685
q17 3785 3849 3841 3841
q18 8741 8837 8811 8811
q19 1733 1708 1682 1682
q20 2377 2128 2099 2099
q21 8518 8493 8568 8493
q22 1014 978 939 939
Total cold run time: 84757 ms
Total hot run time: 80201 ms
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [improvement](statistics)Improve show column stats performance. (#31298) [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #31458:
URL: https://github.com/apache/doris/pull/31458#issuecomment-1965943902
Thank you for your contribution to Apache Doris.
Don't know what should be done next? See [How to process your PR](https://cwiki.apache.org/confluence/display/DORIS/How+to+process+your+PR)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [improvement](statistics)Improve show column stats performance. (#31298) [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #31458:
URL: https://github.com/apache/doris/pull/31458#issuecomment-1965998261
<details>
<summary>ClickBench: <b>Total hot run time: 30.78 s</b></summary>
```
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit fbb5972e4bbae240fd33e15421af7e6fe1d266e2, data reload: false
query1 0.02 0.02 0.02
query2 0.06 0.02 0.02
query3 0.25 0.04 0.05
query4 1.82 0.06 0.06
query5 0.53 0.53 0.52
query6 1.24 0.62 0.67
query7 0.01 0.01 0.01
query8 0.03 0.02 0.03
query9 0.53 0.47 0.50
query10 0.54 0.52 0.55
query11 0.12 0.09 0.09
query12 0.11 0.09 0.09
query13 0.62 0.62 0.62
query14 0.79 0.77 0.79
query15 0.78 0.76 0.78
query16 0.35 0.36 0.37
query17 1.01 1.02 1.03
query18 0.24 0.24 0.24
query19 1.91 1.87 1.87
query20 0.02 0.02 0.01
query21 15.48 0.55 0.57
query22 2.10 2.36 1.66
query23 17.54 0.94 0.93
query24 5.95 0.95 0.90
query25 1.59 0.12 0.10
query26 0.26 0.14 0.14
query27 0.11 0.11 0.11
query28 7.18 0.75 0.76
query29 12.69 2.10 2.23
query30 0.55 0.54 0.54
query31 2.81 0.38 0.39
query32 3.40 0.49 0.51
query33 3.03 3.13 3.04
query34 15.31 4.83 4.83
query35 4.88 4.85 4.91
query36 1.05 1.02 1.02
query37 0.06 0.05 0.05
query38 0.03 0.02 0.02
query39 0.02 0.01 0.01
query40 0.16 0.14 0.14
query41 0.07 0.02 0.02
query42 0.02 0.01 0.02
query43 0.03 0.01 0.02
Total cold run time: 105.3 s
Total hot run time: 30.78 s
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [improvement](statistics)Improve show column stats performance. (#31298) [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #31458:
URL: https://github.com/apache/doris/pull/31458#issuecomment-1965990055
<details>
<summary>TPC-DS: <b>Total hot run time: 204359 ms</b></summary>
```
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit fbb5972e4bbae240fd33e15421af7e6fe1d266e2, data reload: false
query1 923 394 382 382
query2 6535 2294 2064 2064
query3 6918 206 210 206
query4 20856 17958 18080 17958
query5 19722 6535 6496 6496
query6 290 213 237 213
query7 4165 299 302 299
query8 282 253 245 245
query9 3123 2712 2634 2634
query10 409 288 303 288
query11 11300 10642 10716 10642
query12 115 79 76 76
query13 5575 655 641 641
query14 17284 13133 13913 13133
query15 359 242 241 241
query16 6466 257 271 257
query17 1770 1443 864 864
query18 2330 404 403 403
query19 194 147 149 147
query20 68 74 77 74
query21 184 98 95 95
query22 5294 5154 5235 5154
query23 32693 31887 32090 31887
query24 7080 6480 6482 6480
query25 532 420 409 409
query26 623 167 165 165
query27 2005 291 292 291
query28 6120 2249 2199 2199
query29 2862 2795 2934 2795
query30 239 162 165 162
query31 891 729 742 729
query32 56 61 60 60
query33 388 254 240 240
query34 847 476 480 476
query35 1153 957 993 957
query36 1549 1680 1503 1503
query37 90 61 58 58
query38 3117 2915 2944 2915
query39 1356 1319 1313 1313
query40 232 98 100 98
query41 37 32 33 32
query42 86 81 81 81
query43 635 560 660 560
query44 1122 706 710 706
query45 238 228 229 228
query46 1232 987 1011 987
query47 1909 1766 1800 1766
query48 989 709 679 679
query49 624 369 359 359
query50 860 648 601 601
query51 4811 4678 4656 4656
query52 92 77 79 77
query53 446 318 320 318
query54 2645 2417 2481 2417
query55 90 77 90 77
query56 204 186 199 186
query57 1329 1058 1091 1058
query58 208 195 204 195
query59 3461 3248 3280 3248
query60 209 184 211 184
query61 84 86 82 82
query62 846 472 495 472
query63 470 334 336 334
query64 2628 1455 1450 1450
query65 3623 3591 3575 3575
query66 814 362 377 362
query67 15832 17122 17177 17122
query68 7303 650 656 650
query69 558 348 338 338
query70 1831 1753 1872 1753
query71 390 302 315 302
query72 6647 3459 3399 3399
query73 724 328 317 317
query74 6324 5812 5901 5812
query75 4603 3642 3799 3642
query76 4538 1171 1225 1171
query77 530 248 248 248
query78 12717 11921 12207 11921
query79 8563 653 653 653
query80 2938 417 382 382
query81 518 227 228 227
query82 1323 96 105 96
query83 191 139 134 134
query84 255 68 65 65
query85 1402 302 285 285
query86 420 366 366 366
query87 3230 3003 2997 2997
query88 5152 2349 2345 2345
query89 371 278 277 277
query90 1674 205 214 205
query91 156 124 122 122
query92 61 50 51 50
query93 4755 535 592 535
query94 751 206 208 206
query95 1140 1045 1066 1045
query96 657 327 337 327
query97 6550 6502 6442 6442
query98 185 168 182 168
query99 2896 891 818 818
Total cold run time: 310872 ms
Total hot run time: 204359 ms
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [improvement](statistics)Improve show column stats performance. (#31298) [doris]
Posted by "Jibing-Li (via GitHub)" <gi...@apache.org>.
Jibing-Li merged PR #31458:
URL: https://github.com/apache/doris/pull/31458
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [improvement](statistics)Improve show column stats performance. (#31298) [doris]
Posted by "Jibing-Li (via GitHub)" <gi...@apache.org>.
Jibing-Li commented on PR #31458:
URL: https://github.com/apache/doris/pull/31458#issuecomment-1965944282
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [improvement](statistics)Improve show column stats performance. (#31298) [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #31458:
URL: https://github.com/apache/doris/pull/31458#issuecomment-1966002960
Load test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Load test result on commit fbb5972e4bbae240fd33e15421af7e6fe1d266e2 with default session variables
Stream load json: 21 seconds loaded 2358488459 Bytes, about 107 MB/s
Stream load orc: 58 seconds loaded 1101869774 Bytes, about 18 MB/s
Stream load parquet: 31 seconds loaded 861443392 Bytes, about 26 MB/s
Insert into select: 21.3 seconds inserted 10000000 Rows, about 469K ops/s
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org