You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Alexander Reshetov <al...@gmail.com> on 2016/12/08 17:08:17 UTC
Performance degradation for UNION ALL parquet data sources
Hi,
I have one data file which I converted to parquet with CTAS.
It took about 35 seconds to execute next query:
select action['login'], count(*) from dfs.datastore.events group by
action['login'];
After splitting original source to 4 equal parts I created 4 view on
this parts (events_0, events_1, events_2, events_3):
create view dfs.datastore.events_combined as
select t0.`timestamp` as event_time, t0.client_id, t0.action from
dfs.datastore.events_0 t0
union all
select t1.`timestamp` as event_time, t1.client_id, t1.action from
dfs.datastore.events_1 t1
union all
select t2.`timestamp` as event_time, t2.client_id, t2.action from
dfs.datastore.events_2 t2
union all
select t3.`timestamp` as event_time, t3.client_id, t3.action from
dfs.datastore.events_3 t3;
When I make same query but on this view it executes much slower -
about 500 seconds.
select action['login'], count(*) from dfs.datastore.events_combined
group by action['login'];
I expected to see same execution time, but it degraded too much. What
could cause it and/or could it be solved somehow?
Re: Performance degradation for UNION ALL parquet data sources
Posted by Alexander Reshetov <al...@gmail.com>.
On Thu, Dec 8, 2016 at 11:45 PM, Jinfeng Ni <jn...@apache.org> wrote:
> Can you please check the Explain plan output for the original query
> and the query against view, and see if there is any difference in the
> two query plans? The difference might be caused by UNION ALL operator,
> which might lead to different parallelization mode.
Hi,
Here is outputs.
All data in one file.
0: jdbc:drill:zk=local> explain plan for select action['login'],
count(*) from dfs.datastore.events_parquest group by action['login'];
+------+------+
| text | json |
+------+------+
| 00-00 Screen
00-01 Project(EXPR$0=[$0], EXPR$1=[$1])
00-02 UnionExchange
01-01 Project(EXPR$0=[$0], EXPR$1=[$1])
01-02 HashAgg(group=[{0}], EXPR$1=[$SUM0($1)])
01-03 Project(EXPR$0=[$0], EXPR$1=[$1])
01-04 HashToRandomExchange(dist0=[[$0]])
02-01 UnorderedMuxExchange
03-01 Project(EXPR$0=[$0], EXPR$1=[$1],
E_X_P_R_H_A_S_H_F_I_E_L_D=[hash32AsDouble($0)])
03-02 HashAgg(group=[{0}], EXPR$1=[COUNT()])
03-03 Project(EXPR$0=[ITEM($0, 'login')])
03-04 Scan(groupscan=[ParquetGroupScan
[entries=[ReadEntryWithPath [path=file:/mnt/data/events_parquest]],
selectionRoot=file:/mnt/data/events_parquest, numFiles=1,
usedMetadataFile=false, columns=[`action`.`login`]]])
Data split in 4 files and combined with UNION ALL
0: jdbc:drill:zk=local> explain plan for select action['login'],
count(*) from dfs.datastore.parquet_synthetic_events_large_partition_all
group by action['login'];
+------+------+
| text | json |
+------+------+
| 00-00 Screen
00-01 Project(EXPR$0=[$0], EXPR$1=[$1])
00-02 UnionExchange
01-01 Project(EXPR$0=[$0], EXPR$1=[$1])
01-02 HashAgg(group=[{0}], EXPR$1=[$SUM0($1)])
01-03 Project(EXPR$0=[$0], EXPR$1=[$1])
01-04 HashToRandomExchange(dist0=[[$0]])
02-01 UnorderedMuxExchange
03-01 Project(EXPR$0=[$0], EXPR$1=[$1],
E_X_P_R_H_A_S_H_F_I_E_L_D=[hash32AsDouble($0)])
03-02 HashAgg(group=[{0}], EXPR$1=[COUNT()])
03-03 Project(EXPR$0=[ITEM($2, 'login')])
03-04 UnionAll(all=[true])
03-06 Project(timestamp=[$0],
client_id=[$1], action=[$2])
03-08 UnionAll(all=[true])
03-10 Project(timestamp=[$0],
client_id=[$1], action=[$2])
03-12 UnionAll(all=[true])
03-14 Project(timestamp=[$0],
client_id=[$1], action=[$2])
03-16
Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=file:/mnt/data/parquet_synthetic_events_large_partition_0]],
selectionRoot=file:/mnt/data/parquet_synthetic_events_large_partition_0,
numFiles=1, usedMetadataFile=false, columns=[`timestamp`, `client_id`,
`action`]]])
03-13 Project(timestamp=[$0],
client_id=[$1], action=[$2])
03-15
Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=file:/mnt/data/parquet_synthetic_events_large_partition_1]],
selectionRoot=file:/mnt/data/parquet_synthetic_events_large_partition_1,
numFiles=1, usedMetadataFile=false, columns=[`timestamp`, `client_id`,
`action`]]])
03-09 Project(timestamp=[$0],
client_id=[$1], action=[$2])
03-11
Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=file:/mnt/data/parquet_synthetic_events_large_partition_2]],
selectionRoot=file:/mnt/data/parquet_synthetic_events_large_partition_2,
numFiles=1, usedMetadataFile=false, columns=[`timestamp`, `client_id`,
`action`]]])
03-05 Project(timestamp=[$0],
client_id=[$1], action=[$2])
03-07 Scan(groupscan=[ParquetGroupScan
[entries=[ReadEntryWithPath
[path=file:/mnt/data/parquet_synthetic_events_large_partition_3]],
selectionRoot=file:/mnt/data/parquet_synthetic_events_large_partition_3,
numFiles=1, usedMetadataFile=false, columns=[`timestamp`, `client_id`,
`action`]]])
Re: Performance degradation for UNION ALL parquet data sources
Posted by Jinfeng Ni <jn...@apache.org>.
Can you please check the Explain plan output for the original query
and the query against view, and see if there is any difference in the
two query plans? The difference might be caused by UNION ALL operator,
which might lead to different parallelization mode.
On Thu, Dec 8, 2016 at 9:08 AM, Alexander Reshetov
<al...@gmail.com> wrote:
> Hi,
>
> I have one data file which I converted to parquet with CTAS.
>
> It took about 35 seconds to execute next query:
>
> select action['login'], count(*) from dfs.datastore.events group by
> action['login'];
>
> After splitting original source to 4 equal parts I created 4 view on
> this parts (events_0, events_1, events_2, events_3):
>
> create view dfs.datastore.events_combined as
> select t0.`timestamp` as event_time, t0.client_id, t0.action from
> dfs.datastore.events_0 t0
> union all
> select t1.`timestamp` as event_time, t1.client_id, t1.action from
> dfs.datastore.events_1 t1
> union all
> select t2.`timestamp` as event_time, t2.client_id, t2.action from
> dfs.datastore.events_2 t2
> union all
> select t3.`timestamp` as event_time, t3.client_id, t3.action from
> dfs.datastore.events_3 t3;
>
>
> When I make same query but on this view it executes much slower -
> about 500 seconds.
>
> select action['login'], count(*) from dfs.datastore.events_combined
> group by action['login'];
>
>
> I expected to see same execution time, but it degraded too much. What
> could cause it and/or could it be solved somehow?