Mailing-List: contact dev-help@drill.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@drill.apache.org
MIME-Version: 1.0
In-Reply-To: <ABB190CA-40C7-4746-932D-8D697255F077@maprtech.com>
References: <ABB190CA-40C7-4746-932D-8D697255F077@maprtech.com>
Date: Thu, 24 Sep 2015 22:01:22 -0700
Message-ID: 
 <CAK55V=+rJo5vkJCZ5BbrMPRskPeCHJE7FCOLHdVpDP+sA3am0g@mail.gmail.com>
Subject: Re: Improving Performance of SELECT * FROM hive.table LIMIT 0
From: Jinfeng Ni <jinfengni99@gmail.com>
To: dev@drill.apache.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

"FragmentExecutor took 1,070,926 ms to create RecordBatch tree."

1,070,926 ms ~ 17.x  minutes. In other words, the majority of 18
minutes of execution in hive case is spent on the initialization of
Hive readers. If we want to improve "limit n", we probably should make
"lazy" initialization of Hive reader; only when Drill has to read rows
from reader, we do the initialization. Otherwise, to initialize all
the readers before reading any single row means long setup time for
limit "n" query, when n is relative small.

For the second case, the 94 seconds query time seems to be too long as
well. I guess most of the time is spent on parquet reader
initialization (?)


On Thu, Sep 24, 2015 at 9:32 PM, Sudheesh Katkam <skatkam@maprtech.com> wro=
te:
> Hey y'all,
>
> ### Short Question:
>
> How do we improve performance of SELECT * FROM plugin.table LIMIT 0?
>
> ### Extended Question:
>
> While investigating DRILL-3623 <https://issues.apache.org/jira/browse/DRI=
LL-3623>, I did an analysis to see where we spend time for SELECT * FROM hi=
ve.table LIMIT 0 query.
>
> ## Setup:
> Copy the drill/sample-data/region.parquet (x 20000) into a DFS (MapR-FS i=
n my case) directory named region. Create a Hive external table pointing to=
 region. Run Drill with default configuration.
>
> ## Now there are two ways to query this table:
>
>> SELECT * FROM hive.region LIMIT 0;
> +--------------+---------+------------+
> | r_regionkey  | r_name  | r_comment  |
> +--------------+---------+------------+
> +--------------+---------+------------+
> No rows selected (1203.179 seconds)
> ...
>
>> SELECT * FROM dfs.test.region LIMIT 0;
> +--------------+---------+------------+
> | r_regionkey  | r_name  | r_comment  |
> +--------------+---------+------------+
> +--------------+---------+------------+
> No rows selected (94.396 seconds)
>
> Currently, we use HiveRecordReader for the first case and ParquetRecordRe=
ader in the second case. With DRILL-3209 <https://issues.apache.org/jira/br=
owse/DRILL-3209>, both queries will use ParquetRecordReader. However, for f=
ormats that are non-native to Drill or other storage plugins, we still face=
 this problem. Summarizing the query profile,
> +-------+-----------+---------------+----------------+
> | Query | Fragments | Planning time | Execution time |
> +-------+-----------+---------------+----------------+
> | hive  | 1         | ~2 min        | ~18 min        |
> | dfs   | 1         | ~1 min        | ~33 sec        |
> +-------+-----------+---------------+----------------+
>
> ## The time hogs:
>
> # Planning time in both cases needs to improve. How?
>
> # With respect to execution, in the first case ImplCreator.getExec(=E2=80=
=A6) call in the FragmentExecutor took 1,070,926 ms to create RecordBatch t=
ree. There are 20,000 readers being initialized in HiveScanBatchCreator. Ho=
w do we avoid this? What are the implications of chained impersonation (ope=
ning readers in ctor() rather than in setup())?
>
> ### Extending further:
>
> This can be generalized to any "LIMIT n" query with n is a small number. =
For n > 0, we parallelize scanning. So LIMIT 1 query runs faster than LIMIT=
 0. However there is a sweet "n" after which parallelization hurts.
>
> ###
>
> Thank you,
> Sudheesh
>