Open aylei opened 2 years ago
zhangjinpeng87
commented
2 years ago @aylei We also noticed this paper and found the same issue yesterday. After turn on the column engine (tiflash) we produce more than 10x max A even in the deployment all TiDB's components in a single node. It would produce much more higher number than the paper said when deploy these components in distributed nodes.
zhangjinpeng87
commented
2 years ago @elenamilkai @johnchronis PTAL
elenamilkai
commented
2 years ago Hello @aylei and @zhangjinpeng1987, thank you both for your comments.
We enable tiflash for experiments run with TiDB. You can verify that from our code here where for each SSB query we enforce that the tables are read from the tiflash replica by using the /*+ read_from_storage(tiflash[HAT.LINEORDER, HAT.PART, HAT.CUSTOMER, HAT.DATE, HAT.SUPPLIER”]) */.
If tiflash was not enabled with the command: ALTER TABLE HAT.LINEORDER SET TIFLASH REPLICA 1; then every query running with /*+ read_from_storage(tiflash[HAT.LINEORDER, HAT.PART, HAT.CUSTOMER, HAT.DATE, HAT.SUPPLIER”]) */ enforced returns an error and that leads to the benchmark failing without any performance measurements returned.
We also checked the query plan when running a query with the /*+ read_from_storage(tiflash[HAT.LINEORDER, HAT.PART, HAT.CUSTOMER, HAT.DATE, HAT.SUPPLIER”]) */ added, and the query plan shows that the tables are read from the tiflash replica.
Using the /*+ read_from_storage(tiflash[HAT.LINEORDER, HAT.PART, HAT.CUSTOMER, HAT.DATE, HAT.SUPPLIER”]) */ was the only way for us to manually enforce reading from the tiflash replica since the query optimizer of tidb determines by itself whether to use or not the tiflash based on the cost estimation as it is mentioned here. If there is another way to do it and more efficient we will be happy to discuss.
@aylei can you please run your query after adding the /*+ read_from_storage(tiflash[HAT.LINEORDER, HAT.PART, HAT.CUSTOMER, HAT.DATE, HAT.SUPPLIER”]) */ or you could use the HATtrick code that enables tiflash for every query. Do you still observe tiflash not being used?
@zhangjinpeng1987 We also observed that using Tiflash leads to better analytical performance, that is why we used it in the experiments reported in our paper and in our benchmark . I am happy to discuss if there is any tuning that we need to do to increase the throughput. Btw, we didn’t over-tune any of the systems in the paper, we tuned only the very basics (e.g, buffer pool size).
zhangjinpeng87
commented
2 years ago @elenamilkai BTW, we used a minimal TiDB(deploy all components in a 20core node) produced some data and use the Results.ipynb try to produce the throughput graph, but encountered a lot of index out of range errors.
frontier-SF1.csv
dbsid
commented
2 years ago @elenamilkai read_from_storage only takes effect for the tables with TiFlash replicas. Take below query for example.
SELECT /*+ read_from_storage(tiflash[HAT.LINEORDER, HAT.PART, HAT.CUSTOMER, HAT.DATE, HAT.SUPPLIER, HAT.FRESHNESS1, HAT.FRESHNESS2, HAT.FRESHNESS3, HAT.FRESHNESS4]) */ DISTINCT F_TXNNUM, F_CLIENTNUM FROM (SELECT C_CITY, S_CITY, D_YEAR, SUM(LO_REVENUE) AS REVENUE FROM HAT.CUSTOMER, HAT.LINEORDER, HAT.SUPPLIER, HAT.DATE WHERE LO_CUSTKEY = C_CUSTKEY AND LO_SUPPKEY = S_SUPPKEY AND LO_ORDERDATE = D_DATEKEY AND (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND D_YEAR >= 1992 AND D_YEAR <= 1997 GROUP BY C_CITY, S_CITY, D_YEAR ORDER BY D_YEAR ASC, REVENUE DESC) AS TMP1 CROSS JOIN (SELECT * FROM HAT.FRESHNESS1 UNION ALL SELECT * FROM HAT.FRESHNESS2 UNION ALL SELECT * FROM HAT.FRESHNESS3 UNION ALL SELECT * FROM HAT.FRESHNESS4) AS TMP2 ORDER BY F_CLIENTNUM;
By default only the FRESHNESS tables are read from TiFlash, Lineorder/Part/Date_Dim/Supplier are read from TiKV. TiDB will not throw exceptions even there is no TiFlash replicas for some of the tables.
id task estRows operator info actRows execution info memory disk
Sort_36 root 4 Column#66 4 time:1.49s, loops:2 1.52 KB 0 Bytes
└─HashAgg_38 root 4 group by:Column#65, Column#66, funcs:firstrow(Column#65)->Column#65, funcs:firstrow(Column#66)->Column#66 4 time:1.49s, loops:3, partial_worker:{wall_time:1.490383782s, concurrency:5, task_num:1, tot_wait:7.451706447s, tot_exec:26.046µs, tot_time:7.451740523s, max:1.490354757s, p95:1.490354757s}, final_worker:{wall_time:1.490412728s, concurrency:5, task_num:2, tot_wait:7.451815226s, tot_exec:51.491µs, tot_time:7.45187142s, max:1.490385964s, p95:1.490385964s} 15.5 KB N/A
└─HashJoin_41 root 24.00 CARTESIAN inner join 96 time:1.49s, loops:2, build_hash_table:{total:47.1ms, fetch:47.1ms, build:8.45µs}, probe:{concurrency:5, total:7.45s, max:1.49s, probe:30.2µs, fetch:7.45s} 17.6 KB 0 Bytes
├─TableReader_73 root 4 data:ExchangeSender_72 4 time:47.1ms, loops:2, cop_task: {num: 4, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} N/A N/A
│ └─ExchangeSender_72 cop[tiflash] 4 ExchangeType: PassThrough 4 tiflash_task:{proc max:44.2ms, min:20.2ms, p80:44.2ms, p95:44.2ms, iters:4, tasks:4, threads:4} N/A N/A
│ └─Union_43 cop[tiflash] 4 0 tiflash_task:{proc max:0s, min:0s, p80:0s, p95:0s, iters:0, tasks:4, threads:0} N/A N/A
│ ├─TableFullScan_65 cop[tiflash] 1 table:FRESHNESS1, keep order:false, stats:pseudo 1 tiflash_task:{proc max:27.8ms, min:0s, p80:27.8ms, p95:27.8ms, iters:1, tasks:4, threads:1} N/A N/A
│ ├─TableFullScan_67 cop[tiflash] 1 table:FRESHNESS2, keep order:false, stats:pseudo 1 tiflash_task:{proc max:21.2ms, min:0s, p80:21.2ms, p95:21.2ms, iters:1, tasks:4, threads:1} N/A N/A
│ ├─TableFullScan_69 cop[tiflash] 1 table:FRESHNESS3, keep order:false, stats:pseudo 1 tiflash_task:{proc max:44.2ms, min:0s, p80:44.2ms, p95:44.2ms, iters:1, tasks:4, threads:1} N/A N/A
│ └─TableFullScan_71 cop[tiflash] 1 table:FRESHNESS4, keep order:false, stats:pseudo 1 tiflash_task:{proc max:22ms, min:0s, p80:22ms, p95:22ms, iters:1, tasks:4, threads:1} N/A N/A
└─Sort_74 root 6.00 hat.date.d_year, Column#52:desc 24 time:1.49s, loops:2 9.05 KB 0 Bytes
└─HashAgg_76 root 6.00 group by:hat.customer.c_city, hat.date.d_year, hat.supplier.s_city, funcs:sum(hat.lineorder.lo_revenue)->Column#52, funcs:firstrow(hat.date.d_year)->hat.date.d_year 24 time:1.49s, loops:6, partial_worker:{wall_time:1.489855746s, concurrency:5, task_num:1, tot_wait:7.447612365s, tot_exec:241.536µs, tot_time:7.447879724s, max:1.489772505s, p95:1.489772505s}, final_worker:{wall_time:1.489971727s, concurrency:5, task_num:5, tot_wait:7.448667228s, tot_exec:342.399µs, tot_time:7.449015534s, max:1.48986546s, p95:1.48986546s} 73.7 KB N/A
└─Projection_77 root 366.13 hat.customer.c_city, hat.lineorder.lo_revenue, hat.supplier.s_city, hat.date.d_year 348 time:1.49s, loops:2, Concurrency:OFF 37.2 KB N/A
└─IndexJoin_84 root 366.13 inner join, inner:TableReader_80, outer key:hat.lineorder.lo_orderdate, inner key:hat.date.d_datekey, equal cond:eq(hat.lineorder.lo_orderdate, hat.date.d_datekey) 348 time:1.49s, loops:2, inner:{total:12.3ms, concurrency:5, task:3, construct:398.9µs, fetch:11.7ms, build:173.9µs}, probe:293.7µs 91.0 KB N/A
├─HashJoin_103 root 366.13 inner join, equal:[eq(hat.lineorder.lo_custkey, hat.customer.c_custkey)] 348 time:1.48s, loops:7, build_hash_table:{total:80.1ms, fetch:80ms, build:112.6µs}, probe:{concurrency:5, total:7.42s, max:1.48s, probe:8.09ms, fetch:7.41s} 31.6 KB 0 Bytes
│ ├─HashJoin_135 root 51010.67 inner join, equal:[eq(hat.supplier.s_suppkey, hat.lineorder.lo_suppkey)] 52071 time:1.48s, loops:55, build_hash_table:{total:36.6ms, fetch:36.6ms, build:23.6µs}, probe:{concurrency:5, total:7.42s, max:1.48s, probe:625.8ms, fetch:6.79s} 25.6 KB 0 Bytes
│ │ ├─TableReader_145 root 17 data:Selection_144 17 time:35.4ms, loops:2, cop_task: {num: 3, max: 36.7ms, min: 24.5ms, avg: 29.1ms, p95: 36.7ms, max_proc_keys: 853, p95_proc_keys: 853, tot_proc: 54ms, rpc_num: 3, rpc_time: 87.4ms, copr_cache_hit_ratio: 0.00} 743 Bytes N/A
│ │ │ └─Selection_144 cop[tikv] 17 or(eq(hat.supplier.s_city, "UNITED KI1"), eq(hat.supplier.s_city, "UNITED KI5")) 17 tikv_task:{proc max:25ms, min:6ms, p80:25ms, p95:25ms, iters:14, tasks:3}, scan_detail: {total_process_keys: 2000, total_process_keys_size: 269830, total_keys: 17653, rocksdb: {delete_skipped_count: 889, key_skipped_count: 17110, block: {cache_hit_count: 381, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
│ │ │ └─TableFullScan_143 cop[tikv] 2000 table:SUPPLIER, keep order:false 2000 tikv_task:{proc max:24ms, min:6ms, p80:24ms, p95:24ms, iters:14, tasks:3} N/A N/A
│ │ └─TableReader_138 root 6147848 data:Selection_137 6153097 time:1.35s, loops:6021, cop_task: {num: 322, max: 823.6ms, min: 141.7µs, avg: 48.2ms, p95: 421.6ms, max_proc_keys: 255599, p95_proc_keys: 255564, tot_proc: 10.9s, tot_wait: 10ms, rpc_num: 322, rpc_time: 15.5s, copr_cache_hit_ratio: 0.90} 93.7 MB N/A
│ │ └─Selection_137 cop[tikv] 6147848 not(isnull(hat.lineorder.lo_custkey)), not(isnull(hat.lineorder.lo_orderdate)), not(isnull(hat.lineorder.lo_suppkey)) 6153097 tikv_task:{proc max:539ms, min:0s, p80:315ms, p95:442ms, iters:6417, tasks:322}, scan_detail: {total_process_keys: 6022970, total_process_keys_size: 838679633, total_keys: 10270375, rocksdb: {delete_skipped_count: 260213, key_skipped_count: 10274993, block: {cache_hit_count: 18921, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
│ │ └─TableFullScan_136 cop[tikv] 6147848 table:LINEORDER, keep order:false 6153097 tikv_task:{proc max:539ms, min:0s, p80:306ms, p95:436ms, iters:6417, tasks:322} N/A N/A
│ └─TableReader_148 root 217 data:Selection_147 217 time:80ms, loops:2, cop_task: {num: 3, max: 80.4ms, min: 36.1ms, avg: 58.3ms, p95: 80.4ms, max_proc_keys: 12486, p95_proc_keys: 12486, tot_proc: 156ms, rpc_num: 3, rpc_time: 174.7ms, copr_cache_hit_ratio: 0.00} 5.33 KB N/A
│ └─Selection_147 cop[tikv] 217 or(eq(hat.customer.c_city, "UNITED KI1"), eq(hat.customer.c_city, "UNITED KI5")) 217 tikv_task:{proc max:76ms, min:25ms, p80:76ms, p95:76ms, iters:43, tasks:3}, scan_detail: {total_process_keys: 30000, total_process_keys_size: 4221582, total_keys: 204974, rocksdb: {delete_skipped_count: 1145, key_skipped_count: 206156, block: {cache_hit_count: 175, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
│ └─TableFullScan_146 cop[tikv] 30000 table:CUSTOMER, keep order:false 30000 tikv_task:{proc max:73ms, min:18ms, p80:73ms, p95:73ms, iters:43, tasks:3} N/A N/A
└─TableReader_80 root 0.86 data:Selection_79 335 time:11.1ms, loops:6, cop_task: {num: 3, max: 4.29ms, min: 2.73ms, avg: 3.57ms, p95: 4.29ms, max_proc_keys: 142, p95_proc_keys: 142, tot_proc: 8ms, rpc_num: 3, rpc_time: 10.6ms, copr_cache_hit_ratio: 0.00} N/A N/A
└─Selection_79 cop[tikv] 0.86 ge(hat.date.d_year, 1992), le(hat.date.d_year, 1997) 335 tikv_task:{proc max:3ms, min:2ms, p80:3ms, p95:3ms, iters:8, tasks:3}, scan_detail: {total_process_keys: 335, total_process_keys_size: 45725, total_keys: 347, rocksdb: {delete_skipped_count: 0, key_skipped_count: 24, block: {cache_hit_count: 1073, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
└─TableRangeScan_78 cop[tikv] 1 table:DATE, range: decided by [hat.lineorder.lo_orderdate], keep order:false 335 tikv_task:{proc max:2ms, min:2ms, p80:2ms, p95:2ms, iters:8, tasks:3} N/A N/AAfter enable TiFlash Replica Manully, All the table are read from TiFlash
alter table CUSTOMER set tiflash replica 1;
alter table DATE set tiflash replica 1;
alter table FRESHNESS1 set tiflash replica 1;
alter table HISTORY set tiflash replica 1;
alter table LINEORDER set tiflash replica 1;
alter table PART set tiflash replica 1;
alter table SUPPLIER set tiflash replica 1;Expected execution plan
id task estRows operator info actRows execution info memory disk
Sort_40 root 8 Column#61 8 time:365.6ms, loops:2 3.03 KB 0 Bytes
└─HashAgg_44 root 8 group by:Column#60, Column#61, funcs:firstrow(Column#60)->Column#60, funcs:firstrow(Column#61)->Column#61 8 time:365.6ms, loops:5, partial_worker:{wall_time:365.572377ms, concurrency:5, task_num:1, tot_wait:1.825773608s, tot_exec:34.447µs, tot_time:1.825826486s, max:365.527349ms, p95:365.527349ms}, final_worker:{wall_time:365.616124ms, concurrency:5, task_num:4, tot_wait:1.827711163s, tot_exec:122.408µs, tot_time:1.827838892s, max:365.586252ms, p95:365.586252ms} 11.6 KB N/A
└─HashJoin_50 root 8 CARTESIAN inner join 8 time:364.9ms, loops:2, build_hash_table:{total:364.7ms, fetch:364.7ms, build:7.91µs}, probe:{concurrency:5, total:1.82s, max:364.7ms, probe:14.9µs, fetch:1.82s} 9.39 KB 0 Bytes
├─TableReader_125 root 8 data:ExchangeSender_124 8 time:40.1ms, loops:2, cop_task: {num: 8, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} N/A N/A
│ └─ExchangeSender_124 cop[tiflash] 8 ExchangeType: PassThrough 8 tiflash_task:{proc max:30.9ms, min:22ms, p80:27ms, p95:30.9ms, iters:8, tasks:8, threads:8} N/A N/A
│ └─Union_83 cop[tiflash] 8 0 tiflash_task:{proc max:0s, min:0s, p80:0s, p95:0s, iters:0, tasks:8, threads:0} N/A N/A
│ ├─TableFullScan_54 cop[tiflash] 1 table:FRESHNESS1, keep order:false, stats:pseudo 1 tiflash_task:{proc max:25.5ms, min:0s, p80:0s, p95:25.5ms, iters:1, tasks:8, threads:1} N/A N/A
│ ├─TableFullScan_56 cop[tiflash] 1 table:FRESHNESS2, keep order:false, stats:pseudo 1 tiflash_task:{proc max:23ms, min:0s, p80:0s, p95:23ms, iters:1, tasks:8, threads:1} N/A N/A
│ ├─TableFullScan_58 cop[tiflash] 1 table:FRESHNESS3, keep order:false, stats:pseudo 1 tiflash_task:{proc max:26.8ms, min:0s, p80:0s, p95:26.8ms, iters:1, tasks:8, threads:1} N/A N/A
│ ├─TableFullScan_60 cop[tiflash] 1 table:FRESHNESS4, keep order:false, stats:pseudo 1 tiflash_task:{proc max:30.9ms, min:0s, p80:0s, p95:30.9ms, iters:1, tasks:8, threads:1} N/A N/A
│ ├─TableFullScan_62 cop[tiflash] 1 table:FRESHNESS5, keep order:false, stats:pseudo 1 tiflash_task:{proc max:22.6ms, min:0s, p80:0s, p95:22.6ms, iters:1, tasks:8, threads:1} N/A N/A
│ ├─TableFullScan_64 cop[tiflash] 1 table:FRESHNESS6, keep order:false, stats:pseudo 1 tiflash_task:{proc max:21.2ms, min:0s, p80:0s, p95:21.2ms, iters:1, tasks:8, threads:1} N/A N/A
│ ├─TableFullScan_66 cop[tiflash] 1 table:FRESHNESS7, keep order:false, stats:pseudo 1 tiflash_task:{proc max:22.6ms, min:0s, p80:0s, p95:22.6ms, iters:1, tasks:8, threads:1} N/A N/A
│ └─TableFullScan_68 cop[tiflash] 1 table:FRESHNESS8, keep order:false, stats:pseudo 1 tiflash_task:{proc max:27ms, min:0s, p80:0s, p95:27ms, iters:1, tasks:8, threads:1} N/A N/A
└─Projection_126 root 1 1->Column#67 1 time:364.7ms, loops:2, Concurrency:OFF 380 Bytes N/A
└─HashAgg_161 root 1 funcs:count(Column#69)->Column#66 1 time:364.7ms, loops:2, partial_worker:{wall_time:364.628738ms, concurrency:5, task_num:1, tot_wait:1.82271006s, tot_exec:5.11µs, tot_time:1.822725474s, max:364.565227ms, p95:364.565227ms}, final_worker:{wall_time:364.645032ms, concurrency:5, task_num:1, tot_wait:1.822934311s, tot_exec:35.63µs, tot_time:1.822974345s, max:364.603211ms, p95:364.603211ms} 7.81 KB N/A
└─TableReader_163 root 1 data:ExchangeSender_162 1 time:364.5ms, loops:2, cop_task: {num: 1, max: 0s, proc_keys: 0, copr_cache_hit_ratio: 0.00} N/A N/A
└─ExchangeSender_162 cop[tiflash] 1 ExchangeType: PassThrough 1 tiflash_task:{time:301.2ms, loops:1, threads:24} N/A N/A
└─HashAgg_130 cop[tiflash] 1 funcs:count(1)->Column#69 1 tiflash_task:{time:301.2ms, loops:1, threads:1} N/A N/A
└─HashJoin_72 cop[tiflash] 20800.67 inner join, equal:[eq(hat.date.d_datekey, hat.lineorder.lo_orderdate)] 1070 tiflash_task:{time:297.2ms, loops:99, threads:24} N/A N/A
├─ExchangeReceiver_76 cop[tiflash] 6.99 7 tiflash_task:{time:46.2ms, loops:1, threads:24} N/A N/A
│ └─ExchangeSender_75 cop[tiflash] 6.99 ExchangeType: Broadcast 7 tiflash_task:{time:24.3ms, loops:1, threads:1} N/A N/A
│ └─Selection_74 cop[tiflash] 6.99 eq(hat.date.d_weeknuminyear, 6), eq(hat.date.d_year, 1994) 7 tiflash_task:{time:24.3ms, loops:1, threads:1} N/A N/A
│ └─TableFullScan_73 cop[tiflash] 2557 table:DATE, keep order:false 2557 tiflash_task:{time:24.3ms, loops:1, threads:1} N/A N/A
└─Selection_78 cop[tiflash] 349090.27 ge(hat.lineorder.lo_discount, 5), ge(hat.lineorder.lo_quantity, 26), le(hat.lineorder.lo_discount, 7), le(hat.lineorder.lo_quantity, 35), not(isnull(hat.lineorder.lo_orderdate)) 340748 tiflash_task:{time:290.2ms, loops:99, threads:24} N/A N/A
└─TableFullScan_77 cop[tiflash] 6221295 table:LINEORDER, keep order:false 6290002 tiflash_task:{time:192.2ms, loops:99, threads:24} N/A N/A@elenamilkai read_from_storage only takes effect for the tables with TiFlash replicas. Take below query for example.
SELECT /*+ read_from_storage(tiflash[HAT.LINEORDER, HAT.PART, HAT.CUSTOMER, HAT.DATE, HAT.SUPPLIER, HAT.FRESHNESS1, HAT.FRESHNESS2, HAT.FRESHNESS3, HAT.FRESHNESS4]) */ DISTINCT F_TXNNUM, F_CLIENTNUM FROM (SELECT C_CITY, S_CITY, D_YEAR, SUM(LO_REVENUE) AS REVENUE FROM HAT.CUSTOMER, HAT.LINEORDER, HAT.SUPPLIER, HAT.DATE WHERE LO_CUSTKEY = C_CUSTKEY AND LO_SUPPKEY = S_SUPPKEY AND LO_ORDERDATE = D_DATEKEY AND (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND D_YEAR >= 1992 AND D_YEAR <= 1997 GROUP BY C_CITY, S_CITY, D_YEAR ORDER BY D_YEAR ASC, REVENUE DESC) AS TMP1 CROSS JOIN (SELECT * FROM HAT.FRESHNESS1 UNION ALL SELECT * FROM HAT.FRESHNESS2 UNION ALL SELECT * FROM HAT.FRESHNESS3 UNION ALL SELECT * FROM HAT.FRESHNESS4) AS TMP2 ORDER BY F_CLIENTNUM;By default only the FRESHNESS tables are read from TiFlash, Lineorder/Part/Date_Dim/Supplier are read from TiKV. TiDB will not throw exceptions even there is no TiFlash replicas for some of the tables.
id task estRows operator info actRows execution info memory disk Sort_36 root 4 Column#66 4 time:1.49s, loops:2 1.52 KB 0 Bytes └─HashAgg_38 root 4 group by:Column#65, Column#66, funcs:firstrow(Column#65)->Column#65, funcs:firstrow(Column#66)->Column#66 4 time:1.49s, loops:3, partial_worker:{wall_time:1.490383782s, concurrency:5, task_num:1, tot_wait:7.451706447s, tot_exec:26.046µs, tot_time:7.451740523s, max:1.490354757s, p95:1.490354757s}, final_worker:{wall_time:1.490412728s, concurrency:5, task_num:2, tot_wait:7.451815226s, tot_exec:51.491µs, tot_time:7.45187142s, max:1.490385964s, p95:1.490385964s} 15.5 KB N/A └─HashJoin_41 root 24.00 CARTESIAN inner join 96 time:1.49s, loops:2, build_hash_table:{total:47.1ms, fetch:47.1ms, build:8.45µs}, probe:{concurrency:5, total:7.45s, max:1.49s, probe:30.2µs, fetch:7.45s} 17.6 KB 0 Bytes ├─TableReader_73 root 4 data:ExchangeSender_72 4 time:47.1ms, loops:2, cop_task: {num: 4, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} N/A N/A │ └─ExchangeSender_72 cop[tiflash] 4 ExchangeType: PassThrough 4 tiflash_task:{proc max:44.2ms, min:20.2ms, p80:44.2ms, p95:44.2ms, iters:4, tasks:4, threads:4} N/A N/A │ └─Union_43 cop[tiflash] 4 0 tiflash_task:{proc max:0s, min:0s, p80:0s, p95:0s, iters:0, tasks:4, threads:0} N/A N/A │ ├─TableFullScan_65 cop[tiflash] 1 table:FRESHNESS1, keep order:false, stats:pseudo 1 tiflash_task:{proc max:27.8ms, min:0s, p80:27.8ms, p95:27.8ms, iters:1, tasks:4, threads:1} N/A N/A │ ├─TableFullScan_67 cop[tiflash] 1 table:FRESHNESS2, keep order:false, stats:pseudo 1 tiflash_task:{proc max:21.2ms, min:0s, p80:21.2ms, p95:21.2ms, iters:1, tasks:4, threads:1} N/A N/A │ ├─TableFullScan_69 cop[tiflash] 1 table:FRESHNESS3, keep order:false, stats:pseudo 1 tiflash_task:{proc max:44.2ms, min:0s, p80:44.2ms, p95:44.2ms, iters:1, tasks:4, threads:1} N/A N/A │ └─TableFullScan_71 cop[tiflash] 1 table:FRESHNESS4, keep order:false, stats:pseudo 1 tiflash_task:{proc max:22ms, min:0s, p80:22ms, p95:22ms, iters:1, tasks:4, threads:1} N/A N/A └─Sort_74 root 6.00 hat.date.d_year, Column#52:desc 24 time:1.49s, loops:2 9.05 KB 0 Bytes └─HashAgg_76 root 6.00 group by:hat.customer.c_city, hat.date.d_year, hat.supplier.s_city, funcs:sum(hat.lineorder.lo_revenue)->Column#52, funcs:firstrow(hat.date.d_year)->hat.date.d_year 24 time:1.49s, loops:6, partial_worker:{wall_time:1.489855746s, concurrency:5, task_num:1, tot_wait:7.447612365s, tot_exec:241.536µs, tot_time:7.447879724s, max:1.489772505s, p95:1.489772505s}, final_worker:{wall_time:1.489971727s, concurrency:5, task_num:5, tot_wait:7.448667228s, tot_exec:342.399µs, tot_time:7.449015534s, max:1.48986546s, p95:1.48986546s} 73.7 KB N/A └─Projection_77 root 366.13 hat.customer.c_city, hat.lineorder.lo_revenue, hat.supplier.s_city, hat.date.d_year 348 time:1.49s, loops:2, Concurrency:OFF 37.2 KB N/A └─IndexJoin_84 root 366.13 inner join, inner:TableReader_80, outer key:hat.lineorder.lo_orderdate, inner key:hat.date.d_datekey, equal cond:eq(hat.lineorder.lo_orderdate, hat.date.d_datekey) 348 time:1.49s, loops:2, inner:{total:12.3ms, concurrency:5, task:3, construct:398.9µs, fetch:11.7ms, build:173.9µs}, probe:293.7µs 91.0 KB N/A ├─HashJoin_103 root 366.13 inner join, equal:[eq(hat.lineorder.lo_custkey, hat.customer.c_custkey)] 348 time:1.48s, loops:7, build_hash_table:{total:80.1ms, fetch:80ms, build:112.6µs}, probe:{concurrency:5, total:7.42s, max:1.48s, probe:8.09ms, fetch:7.41s} 31.6 KB 0 Bytes │ ├─HashJoin_135 root 51010.67 inner join, equal:[eq(hat.supplier.s_suppkey, hat.lineorder.lo_suppkey)] 52071 time:1.48s, loops:55, build_hash_table:{total:36.6ms, fetch:36.6ms, build:23.6µs}, probe:{concurrency:5, total:7.42s, max:1.48s, probe:625.8ms, fetch:6.79s} 25.6 KB 0 Bytes │ │ ├─TableReader_145 root 17 data:Selection_144 17 time:35.4ms, loops:2, cop_task: {num: 3, max: 36.7ms, min: 24.5ms, avg: 29.1ms, p95: 36.7ms, max_proc_keys: 853, p95_proc_keys: 853, tot_proc: 54ms, rpc_num: 3, rpc_time: 87.4ms, copr_cache_hit_ratio: 0.00} 743 Bytes N/A │ │ │ └─Selection_144 cop[tikv] 17 or(eq(hat.supplier.s_city, "UNITED KI1"), eq(hat.supplier.s_city, "UNITED KI5")) 17 tikv_task:{proc max:25ms, min:6ms, p80:25ms, p95:25ms, iters:14, tasks:3}, scan_detail: {total_process_keys: 2000, total_process_keys_size: 269830, total_keys: 17653, rocksdb: {delete_skipped_count: 889, key_skipped_count: 17110, block: {cache_hit_count: 381, read_count: 0, read_byte: 0 Bytes}}} N/A N/A │ │ │ └─TableFullScan_143 cop[tikv] 2000 table:SUPPLIER, keep order:false 2000 tikv_task:{proc max:24ms, min:6ms, p80:24ms, p95:24ms, iters:14, tasks:3} N/A N/A │ │ └─TableReader_138 root 6147848 data:Selection_137 6153097 time:1.35s, loops:6021, cop_task: {num: 322, max: 823.6ms, min: 141.7µs, avg: 48.2ms, p95: 421.6ms, max_proc_keys: 255599, p95_proc_keys: 255564, tot_proc: 10.9s, tot_wait: 10ms, rpc_num: 322, rpc_time: 15.5s, copr_cache_hit_ratio: 0.90} 93.7 MB N/A │ │ └─Selection_137 cop[tikv] 6147848 not(isnull(hat.lineorder.lo_custkey)), not(isnull(hat.lineorder.lo_orderdate)), not(isnull(hat.lineorder.lo_suppkey)) 6153097 tikv_task:{proc max:539ms, min:0s, p80:315ms, p95:442ms, iters:6417, tasks:322}, scan_detail: {total_process_keys: 6022970, total_process_keys_size: 838679633, total_keys: 10270375, rocksdb: {delete_skipped_count: 260213, key_skipped_count: 10274993, block: {cache_hit_count: 18921, read_count: 0, read_byte: 0 Bytes}}} N/A N/A │ │ └─TableFullScan_136 cop[tikv] 6147848 table:LINEORDER, keep order:false 6153097 tikv_task:{proc max:539ms, min:0s, p80:306ms, p95:436ms, iters:6417, tasks:322} N/A N/A │ └─TableReader_148 root 217 data:Selection_147 217 time:80ms, loops:2, cop_task: {num: 3, max: 80.4ms, min: 36.1ms, avg: 58.3ms, p95: 80.4ms, max_proc_keys: 12486, p95_proc_keys: 12486, tot_proc: 156ms, rpc_num: 3, rpc_time: 174.7ms, copr_cache_hit_ratio: 0.00} 5.33 KB N/A │ └─Selection_147 cop[tikv] 217 or(eq(hat.customer.c_city, "UNITED KI1"), eq(hat.customer.c_city, "UNITED KI5")) 217 tikv_task:{proc max:76ms, min:25ms, p80:76ms, p95:76ms, iters:43, tasks:3}, scan_detail: {total_process_keys: 30000, total_process_keys_size: 4221582, total_keys: 204974, rocksdb: {delete_skipped_count: 1145, key_skipped_count: 206156, block: {cache_hit_count: 175, read_count: 0, read_byte: 0 Bytes}}} N/A N/A │ └─TableFullScan_146 cop[tikv] 30000 table:CUSTOMER, keep order:false 30000 tikv_task:{proc max:73ms, min:18ms, p80:73ms, p95:73ms, iters:43, tasks:3} N/A N/A └─TableReader_80 root 0.86 data:Selection_79 335 time:11.1ms, loops:6, cop_task: {num: 3, max: 4.29ms, min: 2.73ms, avg: 3.57ms, p95: 4.29ms, max_proc_keys: 142, p95_proc_keys: 142, tot_proc: 8ms, rpc_num: 3, rpc_time: 10.6ms, copr_cache_hit_ratio: 0.00} N/A N/A └─Selection_79 cop[tikv] 0.86 ge(hat.date.d_year, 1992), le(hat.date.d_year, 1997) 335 tikv_task:{proc max:3ms, min:2ms, p80:3ms, p95:3ms, iters:8, tasks:3}, scan_detail: {total_process_keys: 335, total_process_keys_size: 45725, total_keys: 347, rocksdb: {delete_skipped_count: 0, key_skipped_count: 24, block: {cache_hit_count: 1073, read_count: 0, read_byte: 0 Bytes}}} N/A N/A └─TableRangeScan_78 cop[tikv] 1 table:DATE, range: decided by [hat.lineorder.lo_orderdate], keep order:false 335 tikv_task:{proc max:2ms, min:2ms, p80:2ms, p95:2ms, iters:8, tasks:3} N/A N/AAfter enable TiFlash Replica Manully, All the table are read from TiFlash
alter table CUSTOMER set tiflash replica 1; alter table DATE set tiflash replica 1; alter table FRESHNESS1 set tiflash replica 1; alter table HISTORY set tiflash replica 1; alter table LINEORDER set tiflash replica 1; alter table PART set tiflash replica 1; alter table SUPPLIER set tiflash replica 1;Expected execution plan
id task estRows operator info actRows execution info memory disk Sort_40 root 8 Column#61 8 time:365.6ms, loops:2 3.03 KB 0 Bytes └─HashAgg_44 root 8 group by:Column#60, Column#61, funcs:firstrow(Column#60)->Column#60, funcs:firstrow(Column#61)->Column#61 8 time:365.6ms, loops:5, partial_worker:{wall_time:365.572377ms, concurrency:5, task_num:1, tot_wait:1.825773608s, tot_exec:34.447µs, tot_time:1.825826486s, max:365.527349ms, p95:365.527349ms}, final_worker:{wall_time:365.616124ms, concurrency:5, task_num:4, tot_wait:1.827711163s, tot_exec:122.408µs, tot_time:1.827838892s, max:365.586252ms, p95:365.586252ms} 11.6 KB N/A └─HashJoin_50 root 8 CARTESIAN inner join 8 time:364.9ms, loops:2, build_hash_table:{total:364.7ms, fetch:364.7ms, build:7.91µs}, probe:{concurrency:5, total:1.82s, max:364.7ms, probe:14.9µs, fetch:1.82s} 9.39 KB 0 Bytes ├─TableReader_125 root 8 data:ExchangeSender_124 8 time:40.1ms, loops:2, cop_task: {num: 8, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} N/A N/A │ └─ExchangeSender_124 cop[tiflash] 8 ExchangeType: PassThrough 8 tiflash_task:{proc max:30.9ms, min:22ms, p80:27ms, p95:30.9ms, iters:8, tasks:8, threads:8} N/A N/A │ └─Union_83 cop[tiflash] 8 0 tiflash_task:{proc max:0s, min:0s, p80:0s, p95:0s, iters:0, tasks:8, threads:0} N/A N/A │ ├─TableFullScan_54 cop[tiflash] 1 table:FRESHNESS1, keep order:false, stats:pseudo 1 tiflash_task:{proc max:25.5ms, min:0s, p80:0s, p95:25.5ms, iters:1, tasks:8, threads:1} N/A N/A │ ├─TableFullScan_56 cop[tiflash] 1 table:FRESHNESS2, keep order:false, stats:pseudo 1 tiflash_task:{proc max:23ms, min:0s, p80:0s, p95:23ms, iters:1, tasks:8, threads:1} N/A N/A │ ├─TableFullScan_58 cop[tiflash] 1 table:FRESHNESS3, keep order:false, stats:pseudo 1 tiflash_task:{proc max:26.8ms, min:0s, p80:0s, p95:26.8ms, iters:1, tasks:8, threads:1} N/A N/A │ ├─TableFullScan_60 cop[tiflash] 1 table:FRESHNESS4, keep order:false, stats:pseudo 1 tiflash_task:{proc max:30.9ms, min:0s, p80:0s, p95:30.9ms, iters:1, tasks:8, threads:1} N/A N/A │ ├─TableFullScan_62 cop[tiflash] 1 table:FRESHNESS5, keep order:false, stats:pseudo 1 tiflash_task:{proc max:22.6ms, min:0s, p80:0s, p95:22.6ms, iters:1, tasks:8, threads:1} N/A N/A │ ├─TableFullScan_64 cop[tiflash] 1 table:FRESHNESS6, keep order:false, stats:pseudo 1 tiflash_task:{proc max:21.2ms, min:0s, p80:0s, p95:21.2ms, iters:1, tasks:8, threads:1} N/A N/A │ ├─TableFullScan_66 cop[tiflash] 1 table:FRESHNESS7, keep order:false, stats:pseudo 1 tiflash_task:{proc max:22.6ms, min:0s, p80:0s, p95:22.6ms, iters:1, tasks:8, threads:1} N/A N/A │ └─TableFullScan_68 cop[tiflash] 1 table:FRESHNESS8, keep order:false, stats:pseudo 1 tiflash_task:{proc max:27ms, min:0s, p80:0s, p95:27ms, iters:1, tasks:8, threads:1} N/A N/A └─Projection_126 root 1 1->Column#67 1 time:364.7ms, loops:2, Concurrency:OFF 380 Bytes N/A └─HashAgg_161 root 1 funcs:count(Column#69)->Column#66 1 time:364.7ms, loops:2, partial_worker:{wall_time:364.628738ms, concurrency:5, task_num:1, tot_wait:1.82271006s, tot_exec:5.11µs, tot_time:1.822725474s, max:364.565227ms, p95:364.565227ms}, final_worker:{wall_time:364.645032ms, concurrency:5, task_num:1, tot_wait:1.822934311s, tot_exec:35.63µs, tot_time:1.822974345s, max:364.603211ms, p95:364.603211ms} 7.81 KB N/A └─TableReader_163 root 1 data:ExchangeSender_162 1 time:364.5ms, loops:2, cop_task: {num: 1, max: 0s, proc_keys: 0, copr_cache_hit_ratio: 0.00} N/A N/A └─ExchangeSender_162 cop[tiflash] 1 ExchangeType: PassThrough 1 tiflash_task:{time:301.2ms, loops:1, threads:24} N/A N/A └─HashAgg_130 cop[tiflash] 1 funcs:count(1)->Column#69 1 tiflash_task:{time:301.2ms, loops:1, threads:1} N/A N/A └─HashJoin_72 cop[tiflash] 20800.67 inner join, equal:[eq(hat.date.d_datekey, hat.lineorder.lo_orderdate)] 1070 tiflash_task:{time:297.2ms, loops:99, threads:24} N/A N/A ├─ExchangeReceiver_76 cop[tiflash] 6.99 7 tiflash_task:{time:46.2ms, loops:1, threads:24} N/A N/A │ └─ExchangeSender_75 cop[tiflash] 6.99 ExchangeType: Broadcast 7 tiflash_task:{time:24.3ms, loops:1, threads:1} N/A N/A │ └─Selection_74 cop[tiflash] 6.99 eq(hat.date.d_weeknuminyear, 6), eq(hat.date.d_year, 1994) 7 tiflash_task:{time:24.3ms, loops:1, threads:1} N/A N/A │ └─TableFullScan_73 cop[tiflash] 2557 table:DATE, keep order:false 2557 tiflash_task:{time:24.3ms, loops:1, threads:1} N/A N/A └─Selection_78 cop[tiflash] 349090.27 ge(hat.lineorder.lo_discount, 5), ge(hat.lineorder.lo_quantity, 26), le(hat.lineorder.lo_discount, 7), le(hat.lineorder.lo_quantity, 35), not(isnull(hat.lineorder.lo_orderdate)) 340748 tiflash_task:{time:290.2ms, loops:99, threads:24} N/A N/A └─TableFullScan_77 cop[tiflash] 6221295 table:LINEORDER, keep order:false 6290002 tiflash_task:{time:192.2ms, loops:99, threads:24} N/A N/A
@dbsid Would you mind to send a pull request to fix it?
dbsid
commented
2 years ago @elenamilkai here comes the fix pr https://github.com/UWHustle/HATtrick/pull/2 I keep the pr as draft first.
elenamilkai
commented
2 years ago Hi @zhangjinpeng1987, we also enabled the tiflash replicas for all the tables manually in a configuration file which we didn't include in the repo. We run the configuration file before the execution of the benchmark and after we add the initial data in the database, we did it that way because it takes some time to replicate the data to tiflash especially for higher scale factors. So after that we initiate the benchmarking process. For the freshness tables we decided to keep it into the benchmark because the numbers of these tables changes during the benchmarking (based on the clients) and we had to create replicas in real time.
here is the content of my config file:
use hat;
ALTER TABLE lineorder SET TIFLASH REPLICA 1;
ALTER TABLE date SET TIFLASH REPLICA 1;
ALTER TABLE part SET TIFLASH REPLICA 1;
ALTER TABLE supplier SET TIFLASH REPLICA 1;
ALTER TABLE customer SET TIFLASH REPLICA 1;
ALTER TABLE history SET TIFLASH REPLICA 1;
I am evaluating TiDB using HATtrick and find that all analytic queries are actually served by TiKV, e.g.
I think the reason is that the benchmark program does not enable tiflash for these tables via:
BTW, since HATtrick was used to benchmark TiDB in the paper How Good is My HTAP System? (SIGMOD 2022). Did the benchmark result in that paper suffer this issue?
ref: https://docs.pingcap.com/tidb/stable/use-tiflash