search

xuchuanyin / workbench

0 stars 0 forks source link

2018-10-27 verifying feature 'scan performance enhancement' #72

Open xuchuanyin opened 5 years ago

xuchuanyin commented 5 years ago

VM: 3 node, 16cores, 32GB executor cores: 12, executor memory: 24g driver.cores: 2, driver memory: 10g

spark.scheduler.mode:FIFO shuffle.partitions:42

106G TPCH

xuchuanyin commented 5 years ago

Loading (Origin)

+----------------------+----------+------+--------+--------+--------+--------+--------+--------+--------+--------+
|         query        |    avg   | size |  total |   min  |   max  |   25%  |   50%  |   75%  |   90%  |   95%  |
+----------------------+----------+------+--------+--------+--------+--------+--------+--------+--------+--------+
|          ALL         |  71647.0 |   8  | 573181 |   522  | 446242 |  4531  |  13228 |  74717 | 446242 | 446242 |
| carbon-load-customer |  6746.0  |   1  |  6746  |  6746  |  6746  |  6746  |  6746  |  6746  |  6746  |  6746  |
| carbon-load-lineitem | 446242.0 |   1  | 446242 | 446242 | 446242 | 446242 | 446242 | 446242 | 446242 | 446242 |
|  carbon-load-nation  |  1100.0  |   1  |  1100  |  1100  |  1100  |  1100  |  1100  |  1100  |  1100  |  1100  |
|  carbon-load-orders  |  74717.0 |   1  |  74717 |  74717 |  74717 |  74717 |  74717 |  74717 |  74717 |  74717 |
|   carbon-load-part   |  13228.0 |   1  |  13228 |  13228 |  13228 |  13228 |  13228 |  13228 |  13228 |  13228 |
| carbon-load-partsupp |  26095.0 |   1  |  26095 |  26095 |  26095 |  26095 |  26095 |  26095 |  26095 |  26095 |
|  carbon-load-region  |   522.0  |   1  |   522  |   522  |   522  |   522  |   522  |   522  |   522  |   522  |
| carbon-load-supplier |  4531.0  |   1  |  4531  |  4531  |  4531  |  4531  |  4531  |  4531  |  4531  |  4531  |
+----------------------+----------+------+--------+--------+--------+--------+--------+--------+--------+--------+
xuchuanyin commented 5 years ago

perf-carbon-slave-0001_181027_1050.nmon.xlsx perf-carbon-slave-0002_181027_1050.nmon.xlsx

perf-carbon-slave-0003_181027_1050.nmon.xlsx

xuchuanyin commented 5 years ago

Query (origin)


+------------------+----------+------+---------+--------+--------+--------+--------+--------+--------+--------+
|       query      |    avg   | size |  total  |   min  |   max  |   25%  |   50%  |   75%  |   90%  |   95%  |
+------------------+----------+------+---------+--------+--------+--------+--------+--------+--------+--------+
|        ALL       | 100454.0 |  59  | 5926804 |   670  | 785295 |  9847  |  30411 | 111158 | 342596 | 632443 |
| carbon-query-q01 |  8491.0  |   3  |  25473  |  7652  |  9882  |  7652  |  7939  |  9882  |  9882  |  9882  |
| carbon-query-q02 |  13925.0 |   3  |  41777  |  12241 |  15409 |  12241 |  14127 |  15409 |  15409 |  15409 |
| carbon-query-q03 |  33938.0 |   3  |  101816 |  30411 |  37798 |  30411 |  33607 |  37798 |  37798 |  37798 |
| carbon-query-q04 | 156281.0 |   3  |  468845 | 127874 | 183173 | 127874 | 157798 | 183173 | 183173 | 183173 |
| carbon-query-q05 |  87020.0 |   3  |  261061 |  84715 |  90171 |  84715 |  86175 |  90171 |  90171 |  90171 |
| carbon-query-q06 |   854.0  |   3  |   2564  |   670  |  1154  |   670  |   740  |  1154  |  1154  |  1154  |
| carbon-query-q07 | 123848.0 |   3  |  371545 | 120762 | 126269 | 120762 | 124514 | 126269 | 126269 | 126269 |
| carbon-query-q08 |  78975.0 |   3  |  236926 |  74592 |  85742 |  74592 |  76592 |  85742 |  85742 |  85742 |
| carbon-query-q09 | 118432.0 |   3  |  355297 | 111158 | 127860 | 111158 | 116279 | 127860 | 127860 | 127860 |
| carbon-query-q10 |  22046.0 |   3  |  66138  |  21388 |  22896 |  21388 |  21854 |  22896 |  22896 |  22896 |
| carbon-query-q11 |  13273.0 |   3  |  39820  |  12522 |  14319 |  12522 |  12979 |  14319 |  14319 |  14319 |
| carbon-query-q12 |  8450.0  |   3  |  25350  |  7884  |  9550  |  7884  |  7916  |  9550  |  9550  |  9550  |
| carbon-query-q13 |  24779.0 |   3  |  74339  |  22796 |  25960 |  22796 |  25583 |  25960 |  25960 |  25960 |
| carbon-query-q14 |  2905.0  |   3  |   8716  |  2769  |  2990  |  2769  |  2957  |  2990  |  2990  |  2990  |
| carbon-query-q15 |  6865.0  |   3  |  20596  |  6516  |  7096  |  6516  |  6984  |  7096  |  7096  |  7096  |
| carbon-query-q16 | 427113.0 |   2  |  854226 | 342596 | 511630 | 342596 | 511630 | 511630 | 511630 | 511630 |
| carbon-query-q17 |  53537.0 |   2  |  107075 |  51646 |  55429 |  51646 |  55429 |  55429 |  55429 |  55429 |
| carbon-query-q18 | 726936.0 |   2  | 1453873 | 668578 | 785295 | 668578 | 785295 | 785295 | 785295 | 785295 |
| carbon-query-q19 |  9973.0  |   2  |  19946  |  9847  |  10099 |  9847  |  10099 |  10099 |  10099 |  10099 |
| carbon-query-q20 |  43685.0 |   2  |  87371  |  37798 |  49573 |  37798 |  49573 |  49573 |  49573 |  49573 |
| carbon-query-q21 | 608986.0 |   2  | 1217973 | 585530 | 632443 | 585530 | 632443 | 632443 | 632443 | 632443 |
| carbon-query-q22 |  43038.0 |   2  |  86077  |  42388 |  43689 |  42388 |  43689 |  43689 |  43689 |  43689 |
+------------------+----------+------+---------+--------+--------+--------+--------+--------+--------+--------+
xuchuanyin commented 5 years ago

Loading (PR)

+----------------------+----------+------+--------+--------+--------+--------+--------+--------+--------+--------+
|         query        |    avg   | size |  total |   min  |   max  |   25%  |   50%  |   75%  |   90%  |   95%  |
+----------------------+----------+------+--------+--------+--------+--------+--------+--------+--------+--------+
|          ALL         |  81222.0 |   7  | 568558 |   838  | 441346 |  1360  |  15111 |  75145 | 441346 | 441346 |
| carbon-load-customer |  9495.0  |   1  |  9495  |  9495  |  9495  |  9495  |  9495  |  9495  |  9495  |  9495  |
| carbon-load-lineitem | 441346.0 |   1  | 441346 | 441346 | 441346 | 441346 | 441346 | 441346 | 441346 | 441346 |
|  carbon-load-nation  |   838.0  |   1  |   838  |   838  |   838  |   838  |   838  |   838  |   838  |   838  |
|  carbon-load-orders  |  75145.0 |   1  |  75145 |  75145 |  75145 |  75145 |  75145 |  75145 |  75145 |  75145 |
|   carbon-load-part   |  15111.0 |   1  |  15111 |  15111 |  15111 |  15111 |  15111 |  15111 |  15111 |  15111 |
| carbon-load-partsupp |  25263.0 |   1  |  25263 |  25263 |  25263 |  25263 |  25263 |  25263 |  25263 |  25263 |
|  carbon-load-region  |  1360.0  |   1  |  1360  |  1360  |  1360  |  1360  |  1360  |  1360  |  1360  |  1360  |
+----------------------+----------+------+--------+--------+--------+--------+--------+--------+--------+--------+
xuchuanyin commented 5 years ago

Query (PR)

+------------------+----------+------+----------+--------+--------+--------+--------+--------+--------+--------+
|       query      |    avg   | size |   total  |   min  |   max  |   25%  |   50%  |   75%  |   90%  |   95%  |
+------------------+----------+------+----------+--------+--------+--------+--------+--------+--------+--------+
|        ALL       | 115180.0 |  110 | 12669857 |   669  | 828345 |  10279 |  40612 | 119848 | 482097 | 644056 |
| carbon-query-q01 |  8935.0  |   5  |   44675  |  8071  |  9773  |  8480  |  8941  |  9410  |  9773  |  9773  |
| carbon-query-q02 |  12649.0 |   5  |   63246  |  11896 |  13170 |  12435 |  12687 |  13058 |  13170 |  13170 |
| carbon-query-q03 |  28844.0 |   5  |  144222  |  23237 |  37808 |  24794 |  28752 |  29631 |  37808 |  37808 |
| carbon-query-q04 | 163223.0 |   5  |  816117  | 142800 | 179034 | 160760 | 164506 | 169017 | 179034 | 179034 |
| carbon-query-q05 |  91206.0 |   5  |  456030  |  89768 |  92671 |  89797 |  91323 |  92471 |  92671 |  92671 |
| carbon-query-q06 |   705.0  |   5  |   3528   |   669  |   746  |   675  |   706  |   732  |   746  |   746  |
| carbon-query-q07 | 125001.0 |   5  |  625007  | 119555 | 135216 | 119848 | 123989 | 126399 | 135216 | 135216 |
| carbon-query-q08 |  79695.0 |   5  |  398476  |  71693 |  86435 |  75729 |  80580 |  84039 |  86435 |  86435 |
| carbon-query-q09 | 123867.0 |   5  |  619335  | 112025 | 129744 | 123594 | 124777 | 129195 | 129744 | 129744 |
| carbon-query-q10 |  24074.0 |   5  |  120370  |  22100 |  26564 |  22645 |  23914 |  25147 |  26564 |  26564 |
| carbon-query-q11 |  13622.0 |   5  |   68113  |  12402 |  14445 |  13374 |  13666 |  14226 |  14445 |  14445 |
| carbon-query-q12 |  9395.0  |   5  |   46979  |  7963  |  10761 |  8777  |  9001  |  10477 |  10761 |  10761 |
| carbon-query-q13 |  25080.0 |   5  |  125401  |  24136 |  26198 |  24415 |  25214 |  25438 |  26198 |  26198 |
| carbon-query-q14 |  2821.0  |   5  |   14109  |  2706  |  2934  |  2811  |  2812  |  2846  |  2934  |  2934  |
| carbon-query-q15 |  7329.0  |   5  |   36648  |  7151  |  7487  |  7244  |  7331  |  7435  |  7487  |  7487  |
| carbon-query-q16 | 287500.0 |   5  |  1437501 | 126998 | 482097 | 187993 | 309204 | 331209 | 482097 | 482097 |
| carbon-query-q17 |  53480.0 |   5  |  267400  |  50586 |  57222 |  52067 |  53333 |  54192 |  57222 |  57222 |
| carbon-query-q18 | 756783.0 |   5  |  3783916 | 655453 | 828345 | 677395 | 810296 | 812427 | 828345 | 828345 |
| carbon-query-q19 |  9657.0  |   5  |   48285  |  8837  |  10279 |  9407  |  9701  |  10061 |  10279 |  10279 |
| carbon-query-q20 |  50254.0 |   5  |  251271  |  45690 |  53185 |  48496 |  51288 |  52612 |  53185 |  53185 |
| carbon-query-q21 | 611878.0 |   5  |  3059391 | 591724 | 644056 | 592380 | 600968 | 630263 | 644056 | 644056 |
| carbon-query-q22 |  47967.0 |   5  |  239837  |  40612 |  59808 |  41729 |  42719 |  54969 |  59808 |  59808 |
+------------------+----------+------+----------+--------+--------+--------+--------+--------+--------+--------+
xuchuanyin commented 5 years ago

perf-carbon-slave-0002_181027_1454.nmon.xlsx perf-carbon-slave-0003_181027_1454.nmon.xlsx perf-carbon-slave-0001_181027_1454.nmon.xlsx

xuchuanyin commented 5 years ago
  Origin ScanPR ScanPR/Origin
carbon-load-customer 6746 9495 1.407500741
carbon-load-lineitem 446242 441346 0.989028375
carbon-load-nation 1100 838 0.761818182
carbon-load-orders 74717 75145 1.005728281
carbon-load-part 13228 15111 1.142349562
carbon-load-partsupp 26095 25263 0.968116497
carbon-load-region 522 1360 2.605363985
carbon-load-supplier 4531 NA #VALUE!
QUERY AVG 100454 115180 1.146594461
carbon-query-q01 8491 8935 1.052290661
carbon-query-q02 13925 12649 0.908366248
carbon-query-q03 33938 28844 0.849902764
carbon-query-q04 156281 163223 1.044419987
carbon-query-q05 87020 91206 1.048103884
carbon-query-q06 854 705 0.825526932
carbon-query-q07 123848 125001 1.009309799
carbon-query-q08 78975 79695 1.009116809
carbon-query-q09 118432 123867 1.045891313
carbon-query-q10 22046 24074 1.091989477
carbon-query-q11 13273 13622 1.02629398
carbon-query-q12 8450 9395 1.11183432
carbon-query-q13 24779 25080 1.012147383
carbon-query-q14 2905 2821 0.971084337
carbon-query-q15 6865 7329 1.067589221
carbon-query-q16 427113 287500 0.673123974
carbon-query-q17 53537 53480 0.998935316
carbon-query-q18 726936 756783 1.041058635
carbon-query-q19 9973 9657 0.968314449
carbon-query-q20 43685 50254 1.150371981
carbon-query-q21 608986 611878 1.004748878
carbon-query-q22 43038 47967 1.114526697