前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Vertica参数的设置

Vertica参数的设置

作者头像
Alfred Zhao
发布2019-05-24 20:27:21
9090
发布2019-05-24 20:27:21
举报

注:只是经验值设定,仅供参考。

RHEL 6.2 CPU 24 Memory 128G 8节点

1.keep more event logs for dc_tuple_mover_events.

代码语言:javascript
复制
select SET_DATA_COLLECTOR_POLICY('TupleMoverEvents', '1000', '100000'); 

default: 1000KB kept in memory, 10000KB kept on disk.

2.keep more event logs for dc_errors.

代码语言:javascript
复制
select SET_DATA_COLLECTOR_POLICY('Errors', '1000', '100000');  

default: 1000KB kept in memory, 10000KB kept on disk. 3.PARAMETER MaxClientSessions: for concurrent queries and data loading jobs

代码语言:javascript
复制
select set_config_parameter('MaxClientSessions', 500);

4.avoid "too many ROS container..."

4.1

代码语言:javascript
复制
select set_config_parameter('ActivePartitionCount', 1); 

-- default: 1

4.2

代码语言:javascript
复制
select set_config_parameter('MoveOutInterval', 1800); 

-- default: 300

4.3

代码语言:javascript
复制
select set_config_parameter('MoveOutMaxAgeTime', 1800); 

-- default: 1800

4.4

代码语言:javascript
复制
select set_config_parameter('MoveOutSizePct', 95); 

-- default: 0

4.5

代码语言:javascript
复制
select set_config_parameter('MergeOutInterval', 300); 

--default: 600

4.6

代码语言:javascript
复制
select set_config_parameter('ContainersPerProjectionLimit', 102400); 

--default: 1024 5.for extension

代码语言:javascript
复制
select ENABLE_LOCAL_SEGMENTS();

---- 24->32, default: 4

代码语言:javascript
复制
select SET_SCALING_FACTOR(4);

6.for loading

代码语言:javascript
复制
select set_config_parameter('MaxDesiredEEBlockSize',33554432); 

default: 8388608.

Maximum desired size of an EE block (used to move tuples between operators), actual block size be larger (must have capacity for at least 2 rows)

7.

代码语言:javascript
复制
SELECT SET_CONFIG_PARAMETER('ParallelizeLocalSegmentLoad', '1');

default: 1 .If true use a DT per local segment, even when sorting

8.RESOURCE POOL general:

代码语言:javascript
复制
alter resource pool general priority 2 plannedconcurrency 12 maxconcurrency 10 queuetimeout 600;

9.RESOURCE POOL load_pool:

代码语言:javascript
复制
alter resource pool load_pool priority 2 runtimepriority MEDIUM plannedconcurrency 12 maxconcurrency 5 queuetimeout NONE;

-- set session resource_pool=load_pool; 10.RESOURCE POOL app_pool:

代码语言:javascript
复制
alter resource pool app_pool priority 2 plannedconcurrency 12 maxconcurrency 10 queuetimeout 600;
alter resource pool app_pool queuetimeout 1200;

11.RESOURCE POOL web_pool:

代码语言:javascript
复制
alter resource pool web_pool priority 100 memorysize '5G' maxmemorysize '10G' plannedconcurrency 12 maxconcurrency 10 queuetimeout 600;
alter resource pool web_pool priority 100 memorysize '5G' maxmemorysize '10G';

12.RESOURCE POOL wosdata

代码语言:javascript
复制
alter resource pool wosdata memorysize '24G' maxmemorysize '24G';

13.alter resource pool tm memorysize default maxconcurrency 4;

代码语言:javascript
复制
alter resource pool tm memorysize '1G' plannedconcurrency 3 maxconcurrency 4;

14.view modified parameters

代码语言:javascript
复制
select node_name, parameter_name, current_value, default_value from configuration_parameters where current_value <> default_value order by 2, 1;

/* node_name | parameter_name | current_value | default_value -----------+------------------------------+---------------+--------------- ALL | ContainersPerProjectionLimit | 102400 | 1024 ALL | MaxClientSessions | 500 | 50 ALL | MaxDesiredEEBlockSize | 33554432 | 8388608 ALL | MergeOutInterval | 300 | 600 ALL | MoveOutInterval | 1800 | 300 (5 rows) */

15.elastic_cluster

代码语言:javascript
复制
select * from elastic_cluster;

16.view user-defined pools

代码语言:javascript
复制
select * from resource_pools
  where name not in (select name from resource_pool_defaults);

/* pool_id | name | is_internal | memorysize | maxmemorysize | executionparallelism | priority | runtimepriority | runtimeprioritythreshold | queuetimeout | plannedconcurrency | maxconcurrency | runtimecap | singleinitiator -------------------+-----------+-------------+------------+---------------+----------------------+----------+-----------------+--------------------------+--------------+--------------------+----------------+------------+----------------- pool_id | name | is_internal | memorysize | maxmemorysize | executionparallelism | priority | runtimepriority | runtimeprioritythreshold | queuetimeout | plannedconcurrency | maxconcurrency | runtimecap | singleinitiator -------------------+-----------+-------------+------------+---------------+----------------------+----------+-----------------+--------------------------+--------------+--------------------+----------------+------------+----------------- 45035996517090874 | load_pool | f | 0% | | AUTO | 2 | MEDIUM | 2 | -1 | 12 | 5 | | f 45035997831587844 | app_pool | f | 0% | | AUTO | 2 | MEDIUM | 2 | 600 | 12 | 10 | | f 58546795771314766 | web_pool | f | 1G | 5G | AUTO | 5 | MEDIUM | 2 | 300 | 10 | 12 | | f (3 rows) */

17.view the current config of the modified pools

代码语言:javascript
复制
select c.* from resource_pools c, resource_pool_defaults d
where c.name=d.name
   and (
     c.memorysize::varchar <> d.memorysize::varchar
     or c.maxmemorysize::varchar <> d.maxmemorysize::varchar
     or c.executionparallelism::varchar <> d.executionparallelism::varchar
     or c.priority::varchar <> d.priority::varchar
     or c.runtimepriority::varchar <> d.runtimepriority::varchar
     or c.runtimeprioritythreshold::varchar <> d.runtimeprioritythreshold::varchar
     or c.queuetimeout::varchar <> d.queuetimeout::varchar
     or c.runtimecap::varchar <> d.runtimecap::varchar
     or c.plannedconcurrency::varchar <> d.plannedconcurrency::varchar
     or c.maxconcurrency::varchar <> d.maxconcurrency::varchar
     or c.singleinitiator::varchar <> d.singleinitiator::varchar
   );

/* pool_id | name | is_internal | memorysize | maxmemorysize | executionparallelism | priority | runtimepriority | runtimeprioritythreshold | queuetimeout | plannedconcurrency | maxconcurrency | runtimecap | singleinitiator -------------------+---------+-------------+------------+---------------+----------------------+----------+-----------------+--------------------------+--------------+--------------------+----------------+------------+----------------- 45035996273718910 | general | t | | Special: 95% | AUTO | 2 | MEDIUM | 2 | 600 | 12 | 10 | | f 45035996273718920 | refresh | t | 4G | | AUTO | 10 | HIGH | 60 | 300 | 8 | 8 | | t 45035996273718918 | tm | t | 1G | | AUTO | 105 | MEDIUM | 60 | 300 | 3 | 4 | | t 45035996273718916 | wosdata | t | 24G | 24G | | | | | | AUTO | | | (4 rows) */

18.view the default config of the modified pools

代码语言:javascript
复制
select d.* from resource_pools c, resource_pool_defaults d
where c.name=d.name
   and (
     c.memorysize::varchar <> d.memorysize::varchar
     or c.maxmemorysize::varchar <> d.maxmemorysize::varchar
     or c.executionparallelism::varchar <> d.executionparallelism::varchar
     or c.priority::varchar <> d.priority::varchar
     or c.runtimepriority::varchar <> d.runtimepriority::varchar
     or c.runtimeprioritythreshold::varchar <> d.runtimeprioritythreshold::varchar
     or c.queuetimeout::varchar <> d.queuetimeout::varchar
     or c.runtimecap::varchar <> d.runtimecap::varchar
     or c.plannedconcurrency::varchar <> d.plannedconcurrency::varchar
     or c.maxconcurrency::varchar <> d.maxconcurrency::varchar
     or c.singleinitiator::varchar <> d.singleinitiator::varchar
   );

/* pool_id | name | memorysize | maxmemorysize | executionparallelism | priority | runtimepriority | runtimeprioritythreshold | queuetimeout | runtimecap | plannedconcurrency | maxconcurrency | singleinitiator -------------------+---------+------------+---------------+----------------------+----------+-----------------+--------------------------+--------------+------------+--------------------+----------------+----------------- 45035996273718910 | general | | Special: 95% | AUTO | 0 | MEDIUM | 2 | 300 | | AUTO | | f 45035996273718920 | refresh | 0% | | AUTO | -10 | MEDIUM | 60 | 300 | | AUTO | | t 45035996273718918 | tm | 200M | | AUTO | 105 | MEDIUM | 60 | 300 | | AUTO | 3 | t 45035996273718916 | wosdata | 0% | 2G | | | | | | | AUTO | | (4 rows) */

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2014-06-26 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档