Hi Qiao,

Glad to know that the DDL change helped.

I think you can also play with the following for better performance.

   1. CQD parallel_num_esps.  This CQD sets the degree of parallelism to a
   fixed value for all parallel layers (like the layer composed of operator
   1,2 and 3 in the query plan).  It is OK to use the CQD during query
   optimization. In production however, you may consider use a different way
   which is my second point below (item 2). The benefit of *not* using
   CQD parallel_num_esps is that this will allow the compiler to choose the
   parallelism based on data size per layer. Your system resource could be
   better controlled this way.
   2. CQD MAX_ESPS_PER_CPU_PER_OP controls the # of ESPs per node. We
    normally allocate 2 ESPs per node (that is the reason there are 6 = 3 * 2
   in the plan). You can alter the CQD MAX_ESPS_PER_CPU_PER_OP to a value so
   that you want maximally X number of ESPs per node (say 4).  The formula for
   the CQD is X / #coresPerNode = X/8. So to boost the degree of parallelism
   from 6 to 12 (or from 2 to 4 ESPs per node), you need to set the CQD to
   4/8=0.5.
   3. From the stats, UID (with total # of distinct value of 8319955) is
   more 'unique' than VID (578912). It will be a good idea to switch the
   position of UID and VID in primary key to favor queries with the search
   condition SID=<u> and V_DATE =<v> and VID=<w>.
   4. The SALT clause (SID, V_DATE, UID, VID) could be reduced to (SID,
   V_DATE, VID) if the above search condition (in item 3) is issued often and
   the expected number of matching rows is small (say less than 200). In this
   case, the compiler may choose a serial plan fragment which is perfect to
   use one ESP to read from one partition where all the matching rows reside.


Thanks --Qifan


LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------
 ---------

4    .    5    root
 1.00E+000
3    .    4    sort_partial_aggr_ro
 1.00E+000
2    .    3    esp_exchange                    1:6(hash2)
 1.00E+000
1    .    2    sort_partial_aggr_le
 1.00E+000
.    .    1    trafodion_scan                  VISIT_FROM_HIVE
8.03E+006

--- SQL operation complete.



On Tue, Sep 20, 2016 at 12:28 AM, Eric Owhadi <eric.owh...@esgyn.com> wrote:

> I also see that you should not be using DIVISION BY, since your V_DATE are
> already on day boundary. Using DIVISION_BY is counter-productive here.
>
>
>
> And sorry I opened wrong file, I had old and new opened at the same time
> and looked wrong window J, you did change the charset and char length, my
> bad,
>
> Eric
>
> *From:* Eric Owhadi [mailto:eric.owh...@esgyn.com]
> *Sent:* Tuesday, September 20, 2016 12:10 AM
> *To:* '乔彦克' <qya...@gmail.com>; 'u...@trafodion.incubator.apache.org' <
> u...@trafodion.incubator.apache.org>
> *Cc:* 'dev' <dev@trafodion.incubator.apache.org>; Qifan Chen <
> qifan.c...@esgyn.com>
> *Subject:* RE: trafodion query optimization
>
>
>
> Hi Qiao
>
>
>
> Optimizer picked 6 as DOP. You can force it higher:
>
>
>
> Use
>
> CQD parallel_num_esps ‘12’;
>
> Then prepare and run the query.
>
>
>
> You may experiment with re-generating you table with SALT 15 and CQD
> parallel_num_esps ‘15’.
>
>
>
> you can even draw a curve
>
> SALT 18, parallel_num_esp ‘18’
>
> SALT  21, parallel_num_esps ‘21’
>
> Etc 3 by 3 until you see that performance stop getting better, and
> actually drops.
>
>
>
> I see you did not change VARCHAR to CHAR and did not use Charset ISO88591:
> this is important to optimize row size. UTF8 is very greedy specially for
> PK columns. When I see your sample sid, I wonder if you really need UTF8.
>
>
>
> Same principle apply for the other columns, but less important than for
> key.
>
>
>
> Ultimately you can change the default value of HBASE_OPTIONS BLOCKSIZE
>
> Default is 65565, you can try doubling or quadrupling it. This will
> improve scan rate but crease any query that does random access.
>
>
>
> Also I checked in an experimental feature to allow scanning in parallel
> without using ESP (using multithreading instead), in case memory resource
> consumed by ESP becomes a bottleneck, but I would not recommend it yet as
> it is “experimental” still…
>
> Except if you start seeing memory pressure…
>
> Hope this helps,
> Eric
>
>
>
>
>
>
>
>
>
> *From:* 乔彦克 [mailto:qya...@gmail.com <qya...@gmail.com>]
> *Sent:* Monday, September 19, 2016 11:38 PM
> *To:* u...@trafodion.incubator.apache.org
> *Cc:* dev <dev@trafodion.incubator.apache.org>; Eric Owhadi <
> eric.owh...@esgyn.com>; Qifan Chen <qifan.c...@esgyn.com>
> *Subject:* Re: trafodion query optimization
>
>
>
> Many thanks  to Eric and Qifan again.
>
> I upload a new log about the new ddl and the query plan.
>
> @Eric, Following your advice I modified the ddl, the sum query now takes
> 25seconds while the old ddl will take 90seconds.(there are 3 nodes in my
> cluster, 8cores(16 v-cores) and 16GRAM), Is there any other optimize
> options to make the sum query more fast (better less than 10seconds)?
>
> @Qifan, I log the output of showstats command in the attachment, looking
> forward more suggestions.
>
>
>
> Thanks again.
>
> Qiao
>
>
>
> Qifan Chen <qifan.c...@esgyn.com>于2016年9月19日周一 下午9:23写道:
>
> Hi Qiao,
>
>
>
> Thank you for the data. It is very helpful.
>
>
>
> There are several things noticed.
>
>    - The key columns are: _SALT_, _DIVISION_1_, SID, V_DATE, UID, VID
>    - The salt column is built from column SID only, which means all rows
>    with identical SID values V will be stored in the same partition.
>    - From the query plan, the compiler assigns 6 executor processes (we
>    call ESP) to read the salted table of 12 partitions: 1 ESP reads 2 salt
>    partitions.
>    - The frequency of V is high when sid='6b2a0957' (~8million rows), all
>    these relevant rows are handled by one execution process out of 6. That
>    probably is the reason of not much parallelism observed.
>    - You can use SQL command *showstats with detail* option to check the
>    frequency on column SID.
>
>
>    - showstats for table TRAFODION.SEABASE.VISIT_FROM_HIVE2 on SID detail;
>
>
>    - If high frequency per unique value on column SID is confirmed, we
>    probably should consider our next step of action. For example, we could add
>    some columns from the primary key to the SALT clause to help spread V of
>    SID to all 12 partitions.
>
> Could you please send us the output of the showstats command above, and
> the showstats command below for all columns in the table?
>
>
>
> showstats for table TRAFODION.SEABASE.VISIT_FROM_HIVE2 on every column;
>
>
>
> Thanks --Qifan
>
>
>
> On Mon, Sep 19, 2016 at 3:15 AM, 乔彦克 <qya...@gmail.com> wrote:
>
> Thanks Eric and Qifan. I am sorry to reply after so long a time because
> I'm on the Chinese mid-autumn festival holiday.
>
> According to Qifan's advice, I upload a log which contains the DDL and the
> query plan hope to get more advice.
>
>
>
> and to Eric, I summit a jira about the block-encoding and the compression,
> https://issues.apache.org/jira/browse/TRAFODION-2195, so I only use the
> hbase compression.
>
> Qifan Chen <qifan.c...@esgyn.com>于2016年9月12日周一 下午10:43写道:
>
> Hi Qiao,
>
>
>
> You can also send us the DDL and the query plan to help with the tuning.
>
>
>
> To generate a query plan, do the following from sqlci, and the plan is in
> text file mylog.
>
>
>
>    1. log mylog clear;
>    2. prepare xx from <your query>;
>    3. explain xx;
>    4. explain options 'f' xx;
>    5. exit;
>
>
>
> Thanks --Qifan
>
>
>
> On Mon, Sep 12, 2016 at 8:21 AM, Eric Owhadi <eric.owh...@esgyn.com>
> wrote:
>
> Hello Qiao,
> When you say whatever the table ddl it limits things a lot, as table ddl
> will help define several things that will drastically improve the degree of
> parallelism and the table size.
>
> The DOP (degree of parallelism) of the scan operator is constrained by the
> number of regions your table uses. So if you want to increase DOP, you need
> to partition your table using the syntax like:
> create table customer_demographics_salt
> (
>  cd_demo_sk int not null
>  , cd_gender char(1)
>  , cd_marital_status char(1)
>  , cd_education_status char(20)
>  , cd_purchase_estimate int
>  , cd_credit_rating char(10)
>  , cd_dep_count int
>  , cd_dep_employed_count int
>  , cd_dep_college_count int
>  , primary key (cd_demo_sk)
> )
> salt using 12 partitions
> ATTRIBUTES ALIGNED FORMAT
> HBASE_OPTIONS
>   (
>     DATA_BLOCK_ENCODING = 'FAST_DIFF',
>     COMPRESSION = 'SNAPPY'
>   );
>
> you can experiment with different values of number of partitions (but pick
> a
> multiple of 3 since you have 3 nodes).
>
> Then the optimizer will pick the DOP with a compromise of resource usage vs
> gain in speed.
> If you want to force higher DOP than what optimizer selected, you can use :
> CQD parallel_num_esps '12';
> To force it to one ESP per partition (assuming you picked 12 partitions).
> You can verify what optimizer picked as DOP by doing an explain on the
> query.
>
> Other important factors plays in performance:
> - use of aligned format (see above example)
> - careful choice of the primary key (bad idea to use a varchar with big max
> size)
> - It is good idea to use compression and encoding (see the most common
> options we use above)
> - you can also increase the HBASE_OPTIONS BLOCKSIZE parameter, but there is
> a drawback: increasing it will increase performance of SCAN but decrease
> performance of keyed access. That is why I did not include it in the
> example
> above.
>
> Hope this helps,
> Regards,
> Eric
>
>
> -----Original Message-----
> From: 乔彦克 [mailto:qya...@gmail.com]
> Sent: Monday, September 12, 2016 1:22 AM
> To: u...@trafodion.incubator.apache.org; dev@trafodion.incubator.
> apache.org
> Subject: trafodion query optimization
>
> Hi all,
>      I executed the sum and count query on my table where the cluster has
> three nodes. I found that the sum query is not well parallel executed(not
> all the three nodes get high load when executing the sum query) and the cpu
> load is very high while the memory load is very low(the machines have 16
> cores and 16GB memory). My sum query on the 12 million data sets takes
> about
> 2 minutes and a half time.
>     So my question is that is there any optimization advice that I can use
> to improve the query performance and maximize the usage of my machines,
> what
> ever the configuration or the table ddl.
>     Any replies is appreciated.
>
> Thanks,
> Qiao
>
>
>
>
>
> --
>
> Regards, --Qifan
>
>
>
>
>
>
>
> --
>
> Regards, --Qifan
>
>
>
>


-- 
Regards, --Qifan

Reply via email to