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>; '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; >> d...@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 > >
