Vertica参数的设置

注：只是经验值设定，仅供参考。

RHEL 6.2 CPU 24 Memory 128G 8节点

1.keep more event logs for dc_tuple_mover_events.

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.

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

select set_config_parameter('MaxClientSessions', 500);

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

4.1

select set_config_parameter('ActivePartitionCount', 1); 

-- default: 1

4.2

select set_config_parameter('MoveOutInterval', 1800); 

-- default: 300

4.3

select set_config_parameter('MoveOutMaxAgeTime', 1800); 

-- default: 1800

4.4

select set_config_parameter('MoveOutSizePct', 95); 

-- default: 0

4.5

select set_config_parameter('MergeOutInterval', 300); 

--default: 600

4.6

select set_config_parameter('ContainersPerProjectionLimit', 102400); 

--default: 1024 5.for extension

select ENABLE_LOCAL_SEGMENTS();

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

select SET_SCALING_FACTOR(4);

6.for loading

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.

SELECT SET_CONFIG_PARAMETER('ParallelizeLocalSegmentLoad', '1');

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

8.RESOURCE POOL general:

alter resource pool general priority 2 plannedconcurrency 12 maxconcurrency 10 queuetimeout 600;

9.RESOURCE POOL load_pool:

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:

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:

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

alter resource pool wosdata memorysize '24G' maxmemorysize '24G';

13.alter resource pool tm memorysize default maxconcurrency 4;

alter resource pool tm memorysize '1G' plannedconcurrency 3 maxconcurrency 4;

14.view modified parameters

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

select * from elastic_cluster;

16.view user-defined pools

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

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

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) */