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