Thank you two for your patient and helpful replies.
I will experiment the salt partition numbers and the other options.
Some new problems happened, so I have to write a new letter to the dear
email list.
Many many thanks.
Qiao

Qifan Chen <qifan.c...@esgyn.com>于2016年9月20日周二 下午9:30写道:

> 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