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>; 'user@trafodion.incubator.apache.org' <
> user@trafodion.incubator.apache.org>
> *Cc:* 'dev' <d...@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:* user@trafodion.incubator.apache.org
> *Cc:* dev <d...@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: user@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
