Biao Wu created HAWQ-947:
----------------------------
Summary: set work_mem cannot work
Key: HAWQ-947
URL: https://issues.apache.org/jira/browse/HAWQ-947
Project: Apache HAWQ
Issue Type: Bug
Components: Core
Reporter: Biao Wu
Assignee: Lei Chang
HAWQ version is 2.0.1.0 build dev.
EXPLAIN ANALYZE:
Work_mem: 9554K bytes max, 63834K bytes wanted。
then set work_mem to '512MB',but not work
{code:sql}
test=# EXPLAIN ANALYZE SELECT count(DISTINCT item_sku_id)
test-# FROM gdm_m03_item_sku_da
test-# WHERE item_origin ='中国大陆';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=54177150.69..54177150.70 rows=1 width=8)
Rows out: Avg 1.0 rows x 1 workers.
Max/Last(seg-1:BJHC-HEBE-9014.hadoop.jd.local/seg-1:BJHC-HEBE-9014.hadoop.jd.local)
1/1 rows with 532498/532498 ms to end, start offset by 201/201 ms.
-> Gather Motion 306:1 (slice2; segments: 306)
(cost=54177147.60..54177150.68 rows=1 width=8)
Rows out: Avg 306.0 rows x 1 workers at destination.
Max/Last(seg-1:BJHC-HEBE-9014.hadoop.jd.local/seg-1:BJHC-HEBE-9014.hadoop.jd.local)
306/306 rows with 529394/529394 ms to first row, 532498/532498 ms to end,
start offset b
y 201/201 ms.
-> Aggregate (cost=54177147.60..54177147.61 rows=1 width=8)
Rows out: Avg 1.0 rows x 306 workers.
Max/Last(seg305:BJHC-HEBE-9031.hadoop.jd.local/seg258:BJHC-HEBE-9029.hadoop.jd.local)
1/1 rows with 530367/532274 ms to end, start offset by 396/246 ms.
Executor memory: 9554K bytes avg, 9554K bytes max
(seg305:BJHC-HEBE-9031.hadoop.jd.local).
Work_mem used: 9554K bytes avg, 9554K bytes max
(seg305:BJHC-HEBE-9031.hadoop.jd.local).
Work_mem wanted: 63695K bytes avg, 63834K bytes max
(seg296:BJHC-HEBE-9031.hadoop.jd.local) to lessen workfile I/O affecting 306
workers.
-> Redistribute Motion 306:306 (slice1; segments: 306)
(cost=0.00..53550018.97 rows=819776 width=11)
Hash Key: gdm_m03_item_sku_da.item_sku_id
Rows out: Avg 820083.0 rows x 306 workers at destination.
Max/Last(seg296:BJHC-HEBE-9031.hadoop.jd.local/seg20:BJHC-HEBE-9016.hadoop.jd.local)
821880/818660 rows with 769/771 ms to first row, 524681/525063 ms to e
nd, start offset by 352/307 ms.
-> Append-only Scan on gdm_m03_item_sku_da
(cost=0.00..48532990.00 rows=819776 width=11)
Filter: item_origin::text = '中国大陆'::text
Rows out: Avg 820083.0 rows x 306 workers.
Max/Last(seg46:BJHC-HEBE-9017.hadoop.jd.local/seg5:BJHC-HEBE-9015.hadoop.jd.local)
893390/810582 rows with 28/127 ms to first row, 73062/526318 ms to end, start
off
set by 354/458 ms.
Slice statistics:
(slice0) Executor memory: 1670K bytes.
(slice1) Executor memory: 3578K bytes avg x 306 workers, 4711K bytes max
(seg172:BJHC-HEBE-9024.hadoop.jd.local).
(slice2) * Executor memory: 10056K bytes avg x 306 workers, 10056K bytes
max (seg305:BJHC-HEBE-9031.hadoop.jd.local). Work_mem: 9554K bytes max, 63834K
bytes wanted.
Statement statistics:
Memory used: 262144K bytes
Memory wanted: 64233K bytes
Settings: default_hash_table_bucket_number=6
Dispatcher statistics:
executors used(total/cached/new connection): (612/0/612); dispatcher
time(total/connection/dispatch data): (489.036 ms/192.741 ms/293.357 ms).
dispatch data time(max/min/avg): (37.798 ms/0.011 ms/3.504 ms); consume
executor data time(max/min/avg): (0.016 ms/0.002 ms/0.005 ms); free executor
time(max/min/avg): (0.000 ms/0.000 ms/0.000 ms).
Data locality statistics:
data locality ratio: 0.864; virtual segment number: 306; different host
number: 17; virtual segment number per host(avg/min/max): (18/18/18); segment
size(avg/min/max): (3435087582.693 B/3391891296 B/3489660928 B); segment size
with
penalty(avg/min/max): (3439751300.235 B/3422552064 B/3489660928 B);
continuity(avg/min/max): (0.630/0.118/1.000); DFS metadatacache: 21.704 ms;
resource allocation: 1.773 ms; datalocality calculation: 51.252 ms.
Total runtime: 532774.799 ms
(29 rows)
Time: 532783.403 ms
test=# show Work_mem;
work_mem
----------
512MB
(1 row)
{code}
{code:title=hawq-site.xml}
<configuration>
<property>
<name>hawq_master_address_host</name>
<value>BJHC-HEBE-9014.hadoop.jd.local</value>
<description>The host name of hawq master.</description>
</property>
<property>
<name>hawq_master_address_port</name>
<value>5432</value>
<description>The port of hawq master.</description>
</property>
<property>
<name>hawq_standby_address_host</name>
<value>none</value>
<description>The host name of hawq standby master.</description>
</property>
<property>
<name>hawq_segment_address_port</name>
<value>40000</value>
<description>The port of hawq segment.</description>
</property>
<property>
<name>hawq_dfs_url</name>
<value>adhoc/user/hawq_default</value>
<description>URL for accessing HDFS.</description>
</property>
<property>
<name>hawq_master_directory</name>
<value>/data0/hawq/hawq-data-directory/masterdd</value>
<description>The directory of hawq master.</description>
</property>
<property>
<name>hawq_segment_directory</name>
<value>/data0/hawq/hawq-data-directory/segmentdd</value>
<description>The directory of hawq segment.</description>
</property>
<property>
<name>hawq_master_temp_directory</name>
<value>/data0/hawq/tmp</value>
<description>The temporary directory reserved for hawq
master.</description>
</property>
<property>
<name>hawq_segment_temp_directory</name>
<value>/data0/hawq/tmp</value>
<description>The temporary directory reserved for hawq
segment.</description>
</property>
<property>
<name>hawq_global_rm_type</name>
<value>none</value>
<description>The resource manager type to start for allocating
resource.
'none' means hawq resource manager
exclusively uses whole
cluster; 'yarn' means hawq resource
manager contacts YARN
resource manager to negotiate resource.
</description>
</property>
<property>
<name>hawq_rm_memory_limit_perseg</name>
<value>48GB</value>
<description>The limit of memory usage in a hawq segment when
hawq_global_rm_type is set 'none'.
</description>
</property>
<property>
<name>hawq_rm_nvcore_limit_perseg</name>
<value>16</value>
<description>The limit of virtual core usage in a hawq segment
when
hawq_global_rm_type is set 'none'.
</description>
</property>
<property>
<name>hawq_rm_stmt_vseg_memory</name>
<value>256mb</value>
</property>
<property>
<name>hawq_re_cpu_enable</name>
<value>false</value>
<description>The control to enable/disable CPU resource
enforcement.</description>
</property>
<property>
<name>hawq_re_cgroup_mount_point</name>
<value>/sys/fs/cgroup</value>
<description>The mount point of CGroup file system for resource
enforcement.
For example, /sys/fs/cgroup/cpu/hawq
for CPU sub-system.
</description>
</property>
<property>
<name>hawq_re_cgroup_hierarchy_name</name>
<value>hawq</value>
<description>The name of the hierarchy to accomodate CGroup
directories/files for resource enforcement.
For example, /sys/fs/cgroup/cpu/hawq
for CPU sub-system.
</description>
</property>
<property>
<name>default_hash_table_bucket_number</name>
<value>6</value>
</property>
<property>
<name>log_min_error_statement</name>
<value>DEBUG5</value>
</property>
<property>
<name>hawq_rm_nvseg_perquery_limit</name>
<value>512</value>
</property>
<property>
<name>hawq_rm_nvseg_perquery_perseg_limit</name>
<value>18</value>
</property>
<property>
<name>shared_buffers</name>
<value>256MB</value>
</property>
</configuration>
{code}
Total segment instance count from config file = 17
Thanks
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
