You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@trafodion.apache.org by 乔彦克 <qy...@gmail.com> on 2016/09/12 06:21:48 UTC
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
RE: trafodion query optimization
Posted by Eric Owhadi <er...@esgyn.com>.
Hi Qiao,
I see that your select query is using an equi-predicate on sid, and that
you have partitioned on SID. That is going to be a problem for parallelism,
as all the data to scan will be located on the same region.
Please consider removing the “ON (SID)” from the ddl.
I see that you use UTF8 character set. For columns that don’t need UTF8,
please consider using ISO88591, specially for the KEY columns if the data
permits. (this will decrease the row size and therefore increase scan rate).
For key columns, please use CHAR instead of VARCHAR.
What scan speed to you get with the above changes suggested?
Regards,
Eric
*From:* 乔彦克 [mailto:qyanke@gmail.com]
*Sent:* Monday, September 19, 2016 3:15 AM
*To:* user@trafodion.incubator.apache.org
*Cc:* dev <de...@trafodion.incubator.apache.org>; qifan.chen@esgyn.com;
eric.owhadi@esgyn.com
*Subject:* Re: trafodion query optimization
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 <qi...@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 <er...@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:qyanke@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
Re: trafodion query optimization
Posted by 乔彦克 <qy...@gmail.com>.
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 <qi...@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 <er...@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.owhadi@esgyn.com]
>> *Sent:* Tuesday, September 20, 2016 12:10 AM
>> *To:* '乔彦克' <qy...@gmail.com>; 'user@trafodion.incubator.apache.org' <
>> user@trafodion.incubator.apache.org>
>> *Cc:* 'dev' <de...@trafodion.incubator.apache.org>; Qifan Chen <
>> qifan.chen@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:qyanke@gmail.com <qy...@gmail.com>]
>> *Sent:* Monday, September 19, 2016 11:38 PM
>> *To:* user@trafodion.incubator.apache.org
>> *Cc:* dev <de...@trafodion.incubator.apache.org>; Eric Owhadi <
>> eric.owhadi@esgyn.com>; Qifan Chen <qi...@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 <qi...@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, 乔彦克 <qy...@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 <qi...@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 <er...@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:qyanke@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
>
>
Re: trafodion query optimization
Posted by 乔彦克 <qy...@gmail.com>.
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 <qi...@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 <er...@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.owhadi@esgyn.com]
>> *Sent:* Tuesday, September 20, 2016 12:10 AM
>> *To:* '乔彦克' <qy...@gmail.com>; 'user@trafodion.incubator.apache.org' <
>> user@trafodion.incubator.apache.org>
>> *Cc:* 'dev' <de...@trafodion.incubator.apache.org>; Qifan Chen <
>> qifan.chen@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:qyanke@gmail.com <qy...@gmail.com>]
>> *Sent:* Monday, September 19, 2016 11:38 PM
>> *To:* user@trafodion.incubator.apache.org
>> *Cc:* dev <de...@trafodion.incubator.apache.org>; Eric Owhadi <
>> eric.owhadi@esgyn.com>; Qifan Chen <qi...@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 <qi...@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, 乔彦克 <qy...@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 <qi...@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 <er...@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:qyanke@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
>
>
Re: trafodion query optimization
Posted by Qifan Chen <qi...@esgyn.com>.
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 <er...@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.owhadi@esgyn.com]
> *Sent:* Tuesday, September 20, 2016 12:10 AM
> *To:* '乔彦克' <qy...@gmail.com>; 'user@trafodion.incubator.apache.org' <
> user@trafodion.incubator.apache.org>
> *Cc:* 'dev' <de...@trafodion.incubator.apache.org>; Qifan Chen <
> qifan.chen@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:qyanke@gmail.com <qy...@gmail.com>]
> *Sent:* Monday, September 19, 2016 11:38 PM
> *To:* user@trafodion.incubator.apache.org
> *Cc:* dev <de...@trafodion.incubator.apache.org>; Eric Owhadi <
> eric.owhadi@esgyn.com>; Qifan Chen <qi...@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 <qi...@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, 乔彦克 <qy...@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 <qi...@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 <er...@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:qyanke@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
Re: trafodion query optimization
Posted by Qifan Chen <qi...@esgyn.com>.
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 <er...@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.owhadi@esgyn.com]
> *Sent:* Tuesday, September 20, 2016 12:10 AM
> *To:* '乔彦克' <qy...@gmail.com>; 'user@trafodion.incubator.apache.org' <
> user@trafodion.incubator.apache.org>
> *Cc:* 'dev' <de...@trafodion.incubator.apache.org>; Qifan Chen <
> qifan.chen@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:qyanke@gmail.com <qy...@gmail.com>]
> *Sent:* Monday, September 19, 2016 11:38 PM
> *To:* user@trafodion.incubator.apache.org
> *Cc:* dev <de...@trafodion.incubator.apache.org>; Eric Owhadi <
> eric.owhadi@esgyn.com>; Qifan Chen <qi...@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 <qi...@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, 乔彦克 <qy...@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 <qi...@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 <er...@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:qyanke@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
RE: trafodion query optimization
Posted by Eric Owhadi <er...@esgyn.com>.
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.owhadi@esgyn.com]
*Sent:* Tuesday, September 20, 2016 12:10 AM
*To:* '乔彦克' <qy...@gmail.com>; 'user@trafodion.incubator.apache.org' <
user@trafodion.incubator.apache.org>
*Cc:* 'dev' <de...@trafodion.incubator.apache.org>; Qifan Chen <
qifan.chen@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:qyanke@gmail.com <qy...@gmail.com>]
*Sent:* Monday, September 19, 2016 11:38 PM
*To:* user@trafodion.incubator.apache.org
*Cc:* dev <de...@trafodion.incubator.apache.org>; Eric Owhadi <
eric.owhadi@esgyn.com>; Qifan Chen <qi...@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 <qi...@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, 乔彦克 <qy...@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 <qi...@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 <er...@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:qyanke@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
RE: trafodion query optimization
Posted by Eric Owhadi <er...@esgyn.com>.
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.owhadi@esgyn.com]
*Sent:* Tuesday, September 20, 2016 12:10 AM
*To:* '乔彦克' <qy...@gmail.com>; 'user@trafodion.incubator.apache.org' <
user@trafodion.incubator.apache.org>
*Cc:* 'dev' <de...@trafodion.incubator.apache.org>; Qifan Chen <
qifan.chen@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:qyanke@gmail.com <qy...@gmail.com>]
*Sent:* Monday, September 19, 2016 11:38 PM
*To:* user@trafodion.incubator.apache.org
*Cc:* dev <de...@trafodion.incubator.apache.org>; Eric Owhadi <
eric.owhadi@esgyn.com>; Qifan Chen <qi...@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 <qi...@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, 乔彦克 <qy...@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 <qi...@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 <er...@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:qyanke@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
RE: trafodion query optimization
Posted by Eric Owhadi <er...@esgyn.com>.
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:qyanke@gmail.com]
*Sent:* Monday, September 19, 2016 11:38 PM
*To:* user@trafodion.incubator.apache.org
*Cc:* dev <de...@trafodion.incubator.apache.org>; Eric Owhadi <
eric.owhadi@esgyn.com>; Qifan Chen <qi...@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 <qi...@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, 乔彦克 <qy...@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 <qi...@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 <er...@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:qyanke@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
RE: trafodion query optimization
Posted by Eric Owhadi <er...@esgyn.com>.
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:qyanke@gmail.com]
*Sent:* Monday, September 19, 2016 11:38 PM
*To:* user@trafodion.incubator.apache.org
*Cc:* dev <de...@trafodion.incubator.apache.org>; Eric Owhadi <
eric.owhadi@esgyn.com>; Qifan Chen <qi...@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 <qi...@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, 乔彦克 <qy...@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 <qi...@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 <er...@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:qyanke@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
Re: trafodion query optimization
Posted by 乔彦克 <qy...@gmail.com>.
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 <qi...@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, 乔彦克 <qy...@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 <qi...@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 <er...@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:qyanke@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
>
>
Re: trafodion query optimization
Posted by 乔彦克 <qy...@gmail.com>.
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 <qi...@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, 乔彦克 <qy...@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 <qi...@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 <er...@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:qyanke@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
>
>
Re: trafodion query optimization
Posted by Qifan Chen <qi...@esgyn.com>.
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, 乔彦克 <qy...@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 <qi...@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 <er...@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:qyanke@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
Re: trafodion query optimization
Posted by Qifan Chen <qi...@esgyn.com>.
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, 乔彦克 <qy...@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 <qi...@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 <er...@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:qyanke@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
RE: trafodion query optimization
Posted by Eric Owhadi <er...@esgyn.com>.
Hi Qiao,
I see that your select query is using an equi-predicate on sid, and that
you have partitioned on SID. That is going to be a problem for parallelism,
as all the data to scan will be located on the same region.
Please consider removing the “ON (SID)” from the ddl.
I see that you use UTF8 character set. For columns that don’t need UTF8,
please consider using ISO88591, specially for the KEY columns if the data
permits. (this will decrease the row size and therefore increase scan rate).
For key columns, please use CHAR instead of VARCHAR.
What scan speed to you get with the above changes suggested?
Regards,
Eric
*From:* 乔彦克 [mailto:qyanke@gmail.com]
*Sent:* Monday, September 19, 2016 3:15 AM
*To:* user@trafodion.incubator.apache.org
*Cc:* dev <de...@trafodion.incubator.apache.org>; qifan.chen@esgyn.com;
eric.owhadi@esgyn.com
*Subject:* Re: trafodion query optimization
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 <qi...@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 <er...@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:qyanke@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
Re: trafodion query optimization
Posted by 乔彦克 <qy...@gmail.com>.
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 <qi...@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 <er...@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:qyanke@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
>
>
Re: trafodion query optimization
Posted by 乔彦克 <qy...@gmail.com>.
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 <qi...@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 <er...@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:qyanke@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
>
>
Re: trafodion query optimization
Posted by Qifan Chen <qi...@esgyn.com>.
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 <er...@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:qyanke@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
Re: trafodion query optimization
Posted by Qifan Chen <qi...@esgyn.com>.
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 <er...@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:qyanke@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
RE: trafodion query optimization
Posted by Eric Owhadi <er...@esgyn.com>.
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:qyanke@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
RE: trafodion query optimization
Posted by Eric Owhadi <er...@esgyn.com>.
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:qyanke@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